Some sample exam 2 questions: 1. Briefly define the following terms: Clustered Index Unclustered Index Primary Index Secondary Index Database Workload Index-only plan System Catalog Double Buffering Reduction Factor Bulk Loading of a B+ Tree 2. What is meant by the term Buffer Replacement Policy? Give an example of a buffer replacement policy and explain how this policy affects a DB. Make sure to give advantages and disadvantages of your sample policy. 3. What are the advantages of using a variable length format for records in a file. Give an example of how you might represent a record with four fields, each of varying lengths. 4. Describe how each of the following page formats represents records: * Variable length records with a slot directory * Packed fixed length records * Unpacked fixed length records with a bitmap 5. Three file storage mechanisms are heap files, sorted files and hashed files. Give advantages and disadvantages of each. Make your answers specific with respect to normal DB operations such a searching for a particular record, or scanning all of the records, etc. 6. We discussed three representations for data entries in an index file. Define these three possible representations and discuss advantages and disadvantages of each representation and when they would be appropriate. 7. For each of the following index methods describe (1) How the index is represented (2) Where data is stored in the index (3) How the following operations are executed searching the index for a key value insert a key value in the index deleting a key value from the index Also discuss the advantages and disadvantages of each method. The methods: ISAM B+ Trees Static Hashing Extendible Hashing Linear Hashing 8. Give the algorithm for each of the following external sorting methods. How many I/O operations would be needed for each assuming a file of 87 pages and 15 available buffers? 2 Way External Merge Sort N Way External Merge Sort 9. Why is heapsort often used as your sorting mechanism for the internal sort during an external sort? What is the average, best and worst case performance of this sort and under what conditions do these occur? 10. If you have a selection operator SELECT equality condition ( relation ) how would you perform the operation under the following conditions, and what would be the estimated cost of your approach in disk I/Os. - There is no index, data is unsorted - There is a clustered tree index - There is a hash index How would your answer change if the condition were not an equality condition? If there is more than one index available, how do you choose which mechanism(s) to use? 11. How is the projection operator implemented with sorting? How about hashing? What are the expected costs for these operations in terms of disk I/Os? 12. Give pseudo-code for the following join operations (you may assume the join is performed on a single attribute with an equality condition). Also give the expected cost for each operation. Simple Nested Loops Join Index Nested Loops Join Block Nested Loops Join Sort-Merge Join Hash-Join 13. What is meant by the term reduction factor? How does it apply in query evaluation? Give at least two examples of reduction factors and how they might affect a particular query. 14. System R only considers left deep plans. Why is this? Give an example of a plan that is left deep and one that is not.