Question $2$: Storage Organization $(15$ marks$)$

Consider the following relation:

Dept $($did: integer, budget: integer, floor: integer, mgr$\_$eid: integer$)$

lof

The building has ten floors, and department budgets vary from $$10,000$ to $$1$ million.

You can assume a uniform distribution of values. For the following query, which of the

listed index choices would you choose to speed up the query? Assume that index$-$only

plans are possible $($i$.$e$.,$ if data is obtainable from an index, data records need not be

fetched from disk.$)$ You must justify your answer by showing the approximate I$/$O cost of

each index choice. Circle your chosen index.

Query: Find the dids of departments that are on the $10$th floor and have a budget of less

than $$15,000.$

$($a$)$ Clustered hash index on the floor field of Dept.

$($b$)$ Unclustered hash index on the floor field of Dept.

$($c$)$ Clustered B$+$ tree index on $$ floor, budget $>$ fields of Dept.

$($d$)$ Clustered B$+$ tree index on the budget field of Dept.

$($e$)$ No index.