The goal is for you to work with storage and indexing.
You will answer questions 8.3, 8.7 #1-4, 8.10 #2-4, 8.11 from the textbook. For your convenience they are reproduced below.
Consider a relation stored as a randomly ordered file for which the only index is an unclustered index on a field called sal. If you want to retrieve all records with sal > 20, is using the index always the best alternative? Explain.
If you were about to create an index on a relation, what considerations would guide your choice? Discuss:
Consider the following relation:
Emp(eid: integer, sal: integer, age: real, did: integer)
There is a clustered index on eid and an unclustered index on age
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 range from $10,000 to $100,000, ages vary from 20 to 80, each department has about five employees on average, there are 10 floors, 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 briefly.
You may (and are even encouraged) to work in groups of up to three (3) students. You only need to turn in one solution for each group unless there is a problem. Place all group members names on the solution. All members of the group must know the details and reasoning of the solution. If only some members worked on and understand a particular problem solution then this should be noted on what you turn in by giving the name(s) of the people who accomplished the work for that problem.
There is an assignment link on Moodle. Turn in a file with your answers. It can be in any reasonable format (PDF, RTF, MS Word, Open Office, ....).