Consider the following relation: Emp(employee-id: int, salary:real , age:int, de

Question

Consider the following relation:

             Emp(employee-id: int, salary:real , age:int, dept-id:integer).

There is a clustered index in employee-id and unclustered index on age.

1. How would you use the indices to enforce the constraint that employee-id is a key.

2. Give an example of an update that is definitely speeded up because of the availability of indices?

3. Give an example of an update that is definitely slowed down because of the availability of indices?

4. Can you give an example of an example that is neither speeded up nor slowed down because of the indices?

Explanation / Answer

1. So as to enforce the constraint that employee-id is a key, we will have to make the clustered index on employee-id unique and dense.
i.e. dense ensures there is a entry for each employee-id & unique makes sure that there is exactly one data entry for each such value.

2. The index on employee-id would speed up the update operation on department/salary of employees whose eid’s are in a particular
range as the records could be accessed faster.

3. Updation of age by addition or subtraction of years for all employees would be slowed down since we would have to update the index on age.

4. Updation of department/salary for a particular id of employee would have no advantage form the indices.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.