1.What main conclusions can you draw from the discussion of the ?ve basic ?le organizations discussed in Section 8.4? Which of the ?ve organizations would you choose for a ?le where the most frequent operations are as follows?
1. Search for records based on a range of field values.
2. Perform inserts and scans, where the order of records does not matter.
3. Search for a record based on a particular ?eld value.
2.Consider the following relations:
Emp(eid: integer, ename: varchar, sal: integer, age: integer, did: integer) Dept(did: integer, budget: integer, ?oor: integer, mgr eid: integer)
Salaries range from $10,000 to $100,000, ages vary from 20 to 80, each department has about ?ve employees on average, there are 10 ?oors, and budgets vary from $10,000 to $1 million. You can assume uniform distributions of values.
For each of the following queries, which of the listed index choices would you choose to speed up the query? If your database system does not consider index-only plans (i.e., data records are always retrieved even if enough information is available in the index entry), how would your answer change? Explain brie?y.
1. Query: Print ename, age, and sal for all employees.
(a) Clustered hash index on ename,age,sal_ ?elds of Emp.
(b) Unclustered hash index on ename,age,sal_ ?elds of Emp. (c) Clustered B+ tree index on ename,age,sal_ ?elds of Emp.
(d) Unclustered hash index on eid,did_ ?elds of Emp.
(e) No index.
2. Query: Find the dids of departments that are on the 10th ?oor and have a budget of less than $15,000.
(a) Clustered hash index on the floor ?eld of Dept.
(b) Unclustered hash index on the floor ?eld of Dept.
(c) Clustered B+ tree index on floor,budget_ ?elds of Dept.
(d) Clustered B+ tree index on the budget ?eld of Dept.
(e) No index.