Some sample exam 1 questions: 1. Briefly define the following terms: Relation Tuple Integrity Constraint Candidate Key Primary Key Foreign Key System Catalog Bulk Loading of a B+ Tree Equi-Join Natural Join 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. Define the following properties of indexes: clustered versus clustered dense versus sparse primary versus secondary 8. 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 Lineary Hashing 9. Give an algorithm for a two-way external merge sort. How many passes would your algorithm need for a file consisting of 87 pages? 10. Give an algorithm for a n-way external merge sort. How many passes would your algorithm need for a file consisting of 87 pages when you have 15 buffers? 11. Define the following operations in relational algebra: Selection, Projection, Cross Product, Intersection, Union, Natural Join, Division How would you perform operations similar to these in Domain Relational Calculus? 12. For the following relations (keys are shown with _s over the field name): Student Class_Grade Class ___ ___ ___ ___ sname sid age gpa sid cid grade cid cdept cnum -------------------- ------------- -------------- Ann 1 21 3.5 1 1 A- 1 CS 1621 Bob 2 19 3.4 1 2 A 2 BIOL 1011 Cal 3 20 2.6 2 1 B 3 ECE 1315 Dee 4 22 4.0 2 3 C 3 1 A 3 2 C 3 3 F 4 2 A How would you design the following queries in Relational Algebra: - The id#s of students who are 19 or 20. - The student names (sname) of those students who have taken CS 1621. - Students (names) who have taken a biology course or ECE course. - Students (names) who have taken every course in Class. - Students (names) who have received an A. 13. Answer the same question using Domain Relational Calculus.