Consider the following relation: Employees(eid: integer, sal: integer, age: real
ID: 3713004 • Letter: C
Question
Consider the following relation: Employees(eid: integer, sal: integer, age: real, did: integer), where attribute eid is the primary key of the relation. There is a hash index on eid and also a B+-tree index on age a. Explain how the DBMS could use the available indexes to enforce the constraint that eid is the primary key of the relation when Employees tuples are inserted, deleted, or updated. Discuss all three cases, namely, tuple insertions, deletions, and updates indexes. (An English description is sufficient, but be precise and concrete.) of the indexes. (An English description is sfficient, but be precise and concrete.) b. Give an example of an update that is definitely speeded up because of the available c. Give an example of an update that is definitely slowed down because of the presenceExplanation / Answer
Solution:
a)
Given that there is a clustered index on 'Eid':
To enforce the key constraint on 'Eid' column. declare Eid clustered index as unique and dense.
To declare 'Eid' as unique, the condition is there should be only one data entry for each 'Eid' value.
To declare as dense, the condition is for each eid value in a data record there should be at least one matching data entry in the index
b)
Index on eid column is a clustered index which speeds up insert, updates and search operations.
Suppose if we want to update the 'Did' of all the employees whose eid's are in a particular range the operation is executed much faster due to the index on eid.
In a clustered index data is sorted and to retrieve range based data from sorted data requires less number of 110 operations and is executed faster
c)
Index on age column is an unclustered index. An update operation on an unclustered index column requires more amount of time as data in an unclustered index is not sorted and each record is saved on a different page on the disk.
Updating the age of all employees by adding 5, this operation slows down the process.
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)