Following Question is based on File organization, Storage and Indexing Topic. Co
ID: 3576066 • Letter: F
Question
Following Question is based on File organization, Storage and Indexing Topic.
Consider the following relations:
Employee (eid: integer, ename: varchar, salary: integer, age: integer, did: integer)
Department (did: integer, budget: integer, floor: integer, mgr_eid: integer)
Field description:
Field name = Field Description
Eid means employee Id
Ename means employee name
Sal means salary
Did means department Id
Mgr_eid means manager's eid
Salaries range from $10,000 to $50,000, ages vary from 20 to 60, each department has about five employees on average, there are 10 floors, and budgets vary from $10,000 to $2 million. You can assume uniform distributions of values.
For the following query, which of the listed index choices would you choose to speed up the query and explain why?
Query: Find the dids of departments that are on the 10th floor and have a budget of the department is less than $15,000.
(a) Clustered hash index on the floor field of Department table.
(b) Unclustered hash index on the floor field of Department table.
(c) Clustered B+ tree index on {floor, budget} fields of Department table.
(d) Clustered B+ tree index on the budget field of Department table.
(e) No index.
Field name = Field Description
Eid means employee Id
Ename means employee name
Sal means salary
Did means department Id
Mgr_eid means manager's eid
Explanation / Answer
Solution: option (c) Clustered B+ tree index on {floor, budget} fields of Department table.
Explanantion: We should create a clustered dense B+ tree index (c) on floor, budget fields of Dept, since the records would be ordered on these fields then. So when executing this query, the first record with floor = 10 must be retrieved, and then the other records with floor = 10 can be read in order of budget. Note that this plan, which is the best for this query, is not an index-only plan (must look up dids).