Exercise Database Management Systems (CO3021) - Chapter 3: Indexing Structures for Files
Question 3.1. What are indexes? Give at least three examples.
Question 3.2. What are primary, secondary, and clustering indexes? Give at least one example for each.
Question 3.3. Compare primary, secondary, and clustering indexes with each other. Which are dense
and which are not? Explain the characteristics in their corresponding data file that make them dense or
sparse.
Question 3.4. Why can at most one primary or clustering index created on a data file, but zero or many
secondary indexes? Give an example to demonstrate your answer.
Question 3.5. Distinguish between single-level indexes and multilevel indexes. Give an example to
demonstrate your answer
Question 3.2. What are primary, secondary, and clustering indexes? Give at least one example for each.
Question 3.3. Compare primary, secondary, and clustering indexes with each other. Which are dense
and which are not? Explain the characteristics in their corresponding data file that make them dense or
sparse.
Question 3.4. Why can at most one primary or clustering index created on a data file, but zero or many
secondary indexes? Give an example to demonstrate your answer.
Question 3.5. Distinguish between single-level indexes and multilevel indexes. Give an example to
demonstrate your answer
3 trang
30/12/2022
500
Bạn đang xem tài liệu "Exercise Database Management Systems (CO3021) - Chapter 3: Indexing Structures for Files", để tải tài liệu gốc về máy hãy click vào nút Download ở trên.
File đính kèm:
- exercise_database_management_systems_co3021_chapter_3_indexi.pdf
Nội dung text: Exercise Database Management Systems (CO3021) - Chapter 3: Indexing Structures for Files
- (ii) the number of first-level index entries and the number of first-level index blocks; (iii) the number of levels needed if we make it into a multilevel index; (iv) the total number of blocks required by the multilevel index; (v) the number of block accesses needed to search for and retrieve a record from the file—given its Ssn value—using the primary index. d. Suppose that the file is not ordered by the key field Ssn and we want to construct a secondary index on Ssn. Repeat the previous exercise (part c) for the secondary index and compare with the primary index. e. Suppose that the file is not ordered by the nonkey field Department_code and we want to construct a secondary index on Department_code, using option 3, with an extra level of indirection that stores record pointers. Assume there are 1,000 distinct values of Department_code and that the EMPLOYEE records are evenly distributed among these values. Calculate: (i) the index blocking factor bfri (which is also the index fan-out fo); (ii) the number of blocks needed by the level of indirection that stores record pointers; (iii) the number of first-level index entries and the number of first-level index blocks; (iv) the number of levels needed if we make it into a multilevel index; (v) the total number of blocks required by the multilevel index and the blocks used in the extra level of indirection; (vi) the approximate number of block accesses needed to search for and retrieve all records in the file that have a specific Department_code value, using the index. f. Suppose that the file is ordered by the nonkey field Department_code and we want to construct a clustering index on Department_code that uses block anchors (every new value of Department_code starts at the beginning of a new block). Assume there are 1,000 distinct values of Department_code and that the EMPLOYEE records are evenly distributed among these values. Calculate: (i) the index blocking factor bfri (which is also the index fan-out fo); (ii) the number of first-level index entries and the number of first-level index blocks; (iii) the number of levels needed if we make it into a multilevel index; (iv) the total number of blocks required by the multilevel index; (v) the number of block accesses needed to search for and retrieve all records in the file that have a specific Department_code value, using the clustering index (assume that multiple blocks in a cluster are contiguous). 2
