1.Consider the instance of the Students relation shown in Figure , sorted by age: For the purposes of this question, assume that these tuples are stored in a sorted file in the order shown; the ?rst 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
sid name login age gpa
53831 Madayan madayan@music 11 1.8
53650 Smith guldu@music 12 2.0
53832 Guldu jones@cs 18 3.4
53666 Jones smith@ee 19 3.2
53688 Smith smith@math 19 3.8
Figure An Instance of the Students Relation, Sorted by age
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).
4. A clustered index on age using Alternative (1).
5. A clustered index on age using Alternative (2).
6. A clustered index on age using Alternative (3).
7. An unclustered index on gpa using Alternative (1).
8. An unclustered index on gpa using Alternative (2).
9. An unclustered index on gpa using Alternative (3).
10. A clustered index on gpa using Alternative (1).
11. A clustered index on gpa using Alternative (2).
12. A clustered index on gpa using Alternative (3).
2.Answer the following questions about ?les and indexes:
1. What operations are supported by the ?le of records abstraction?
2. What is an index on a ?le of records? What is a search key for an index? Why do we need indexes?
3. What alternatives are available for the data entries in an index?
4. What is the di?erence between a primary index and a secondary index? What is a duplicate data entry in an index? Can a primary index contain duplicates?
5. What is the di?erence between a clustered index and an unclustered index? If an index contains data records as ‘data entries,’ can it be unclustered?
6. How many clustered indexes can you create on a ?le? Would you always create at least one clustered index for a ?le?
7. Consider Alternatives (1), (2) and (3) for ‘data entries’ in an index, as discussed in Section 8.2. Are all of them suitable for secondary indexes? Explain.