Programming Question

COP4710HW #4
Instructions: Write your answer neatly and concisely. Illegible scribbles or unclear solutions will result
in lost credit. Submit your answers in pdf, doc, or docx to the Assignment on Webcources by the due
date.
1. (Exercise 8.4) Consider the instance of the Students relation shown in Figure 8.1, sorted by age:
For the purposes of this question, assume that these tuples are stored in a sorted file in the order
shown; the first tuple is on page 1 the second tuple is also on page 1; and so on. Each page can
store up to three data records; so the fourth tuple is on page 2.
Explain what the data entries in each of the following indexes contain. If the order of entries is
significant, say so and explain why. If such an index cannot be constructed, say so and explain
why.
1. An unclustered index on age using Alternative (1).
2. An unclustered index on age using Alternative (2).
3. An unclustered index on age using Alternative (3).
5. A clustered index on age using Alternative (2).
8. An unclustered index on gpa using Alternative (2).
11. A clustered index on gpa using Alternative (2).
2. (Exercise 8.11) 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.