Indexing problem

Question

Consider the following relations.
Emp(eid:integer,ename:varchar,sal:integer,age:integer,did:integer)
Dept(did:integer,budget:integer,floor:integer,mgr_eid:integer)
Salaries ranges from 10000 to 100000 ages vary from 20 to 80, each department has about five employees on average, there are 10 floors, and budgets vary from 10000 too 1 million. You can assume uniform distribution of values.
For following query, which of the listed index choices would you choose to speed up the query?

Query:Find the dids of departments that are on the 10th floor and have a budget of less than 15000
(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 od Dept
(D) Clustered B+ tree index on the budge field of Dept

I feel in first paragraph what all important is Dept table.

Comments

I would ideally use option (A) because hashing works well for equal queries (floor no 10 )and then option (D)(10000=<x<15000). i mean (A)->(D).because B+trees works well on range queries.

for reference: https://gateoverflow.in/2141/gate2011_39

Answer 1

This should be sequential access so cannot use hashed index....index on both will give faster access ...so ans should be C

Comments

did you mean something like first ordered floor and then budget for each floor and an index made upon the combination of them? Also isnt hash index is always faster than the indices requiring sequential access? Cant we still prepare hashinndex on such combination of field? Then let the hash function give same hash for the same combination of field at the time of searching? Also what is clusterred hashing and unclustered hashing?
Feel havent read about it...any good source?

I think I have some understanding of primary, secondary, clustered, B tree. B+ tree indices as the explanation can be found in books like Korth or Elmasri Navathe's book. However I cannot find explanation of "clustered / unclustered hash and B+ tree indices" Any reference book? @Arjun sir?

---

@Pavan. I agree with you. Even I think answer should be (C) for following reasons:

option A) you hash the floor but then afterwards, since the data on budget is unsorted, you need to traverse large no of sequential blocks (i.e. all the budget values for that floor )to get to that answer.

option B) Since the data on floor is unsorted in the original table, first you need to get to that floor data which might be on different block each time. So again, large no of blocks are traversed which may be even very much higher than clustered.

option C) first you reach the bottom of the tree using the floor. This traversal is very less if we take into account the no of floors. Then all the budget values from 0-15000 will be in order i.e. blocks for all the budget values will be sequentially ordered since this is clustered B+ tree on budget values as well.

so, total blocks traversed = sequntially arranged blocks to get to appropriate floor(max 10) + sequntially arranged blocks to cover all budget values till 15000

option D) first you get to budget value of 0 and then start traversing sequntially looking if the floor value is 10.

So, total blocks traversed=no of budget values less than 15000 * 10