Some sample exam 1 questions:

1. Briefly define the following terms:


   Integrity Constraint

   Candidate Key

   Primary Key

   Foreign Key

   System Catalog


   Entity, Entity set (in an ER model)

   Relationship, Relationship set (in an ER model)

   Key constraint (in an ER model)

   Participation constraint (ER)

   Weak entity (ER)

   ISA hierarchy (ER)


   Natural Join

2. A music database contains information about Songs (identified by a unique
   song id# and the title of the song), Bands (identified by a unique band name
   and the number of members in the band), and Albums (identified by a unique
   album id#, the title of the album, the number of songs on the album).  Bands
   sing Songs and produce Albums.  Songs are parts of an Album.  Draw an ER
   diagram for each of the situations below:

   - A Song may be part of more than one Album.
   - A Song must be part of at least one Album.
   - A Song appears on one and only one Album.
   - Each Album must have at least one Song.
   - A Band may sing more than one version of a Song, but each version must
     appear on a different Album.

3. 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.

4. Answer question 3 using Domain Relational Calculus.

5. Answer question 3 using SQL.

    For added practice, produce queries for exercises 5.1, 5.3 (answers can
    be found on textbook web page).

6. Answer question 9 using QBE and skeletons as shown in class.

    For added practice, produce queries for exercises 6.1.

7. Define the following operations in relational algebra:

    Selection, Projection, Cross Product, Intersection, Union, Natural Join,

    How would you perform operations similar to these in Domain Relational

8. A SQL query may have five different clauses, a SELECT, FROM, WHERE,
   GROUP BY and HAVING clause.  Explain how each of these clauses works
   and how they are evaluated in an SQL query.  Give an example using
   all five clauses.

9. How is a view defined in SQL?  How are queries on views resolved?  What
   are some of the difficulties introduced with views?

10. 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.

11. 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.

12. 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

13. 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.

14. 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.

15. Define the following properties of indexes:
     clustered versus clustered
     dense versus sparse
     primary versus secondary