Spring 2014: Database Midterm

• The weight for each question is given with the question.
• Use as much paper as you wish, but make your answers are legible.
• Please answer each question thoughtfully and carefully.

1. [4 points] A database is said to be self-describing. What does this mean? What is the name for the special data that such a system maintains called?

2. [4 points] What is SQL? How does SQL fit into a database system?

3. [4 points] What is a database schema? What information is presented in a schema and how is this information encoded?

4. [2 points] What is a database query? How are the results of a query returned?

5. [2 points each] Consider the following database schema:

    ANIMALS(AnimalName, SpeciesID, DisplayID, Keeper)
    SPECIES(SpeciesID, CommonName, ScientificName)
       

   1. Draw a diagram to represent this database.

      For each query, explain the effects. ("It will produce a table which ... ")

   2. SELECT DISTINCT Keeper 
      FROM ANIMALS;
      

   3. SELECT CommonName, ScientificName 
      FROM SPECIES
      WHERE CommonName LIKE "%cat%";
      	 

   4. SELECT *
      FROM ANIMALS
      ORDER BY Keeper DESC;
      	 

   5. SELECT AnimalName, ScientificName 
      FROM ANIMALS, SPECIES;
      	 

   6. SELECT AnimalName, ScientificName
      FROM ANIMALS, SPECIES
      WHERE ANIMAL.SpeciesID = SPECIES.SpeciesID;
      	 

    

    

    

   Over Please

6. Consider the following table

   KeeperID	KeeperName	KeeperAge	Shift	Paygrade	Title
   100	Bob	25	Day	K2	Junior Keeper
   100	Bob	25	Night	K2	Junior Keeper
   102	Sue	27	Day	K2	Junior Keeper
   103	Tim	31	Day	K4	Keeper Supervisor

   1. [4 points] State at least two functional dependencies (based upon the data). State any assumptions you made.

   2. [2 points] State a multivalued dependency (based upon the data). State any assumptions you made.

   3. [3 points] Provide a question to verify each dependency.

7. Consider the following database schema and functional dependencies

   MEMBER_LIST(VisitorName, VisitorPhone, VisitorAddress, MembershipType, AdmissionPrice)
   
   MemberType → AdmissionPrice
   VisitorName →  (VisitorAddress, MembershipType, AdmissionPrice)
   VisitorName →→ VisitorPhone
       

   1. [1 point each] Provide a statement for what each functional dependency means.

   2. [10 points] Provide the database schema for a new set of tables that represents MEMBER_LIST in 4NF.