Comp 419
Database Design
Final Exam Study Questions

Note: these study questions pertain only to material covered since the midterm. The final is comprehensive! See the midterm and study questions for the midterm for more study questions.

1. Consider a relational database containing relations with the following schemes:

       Customer-scheme = (name, address, cust-num)
       Flyrod-scheme = (manufacture, length, line-weight, flyrod-stock-num)
       Purchased-scheme = (cust-num, flyrod-stock-num, date)

   1. One of the referential integrity constraints associated with this flyshop database is:

      

      1. (5 pts) When a tuple is inserted into purchased, what test needs to be performed to insure that this referential integrity constraint is maintained?
      2. (5 pts) When a tuple is inserted into customer, what test needs to be performed to insure that this referential integrity constraint is maintained?
   2. (5 pts) Give an SQL-92 query that returns just the length of every flyrod that someone has purchased. You are not allowed to use a where clause in your query.
2. A company that manufactures lava lamps uses a relational database to keep track of what parts it has in stock, suppliers of parts, and which suppliers supply what parts. The schemes of these relations are:

     Part-scheme = (part-num, description, groovyness, quantity)
     Supplier-scheme = (supplier-name, address, phone-number)
     Supplies-scheme = (part-num, supplier-name, address, cost)

   The primary key for relations on Part-scheme is part-num, and the primary key for relations on Supplier-scheme is {supplier-name, address}.
   1. (5 pts) What referential integrity constraint should hold between the supplier and supplies relations?
   2. (5 pts) Is there any referential integrity constraint between the part and supplies relations? Why or why not?
   3. (5 pts) Give a functional dependency expressing the fact that two different addresses do not share the same phone-number.
3. Now assume that the management of the lava lamp company decides that each supplier can supply at most one kind of part - i.e. that the company will buy at most one kind of part from each supplier, even if that supplier has other kinds of parts. Thus, the company will not have two different kinds of parts in stock that were supplied by the same supplier, and will not even store the information that a supplier has other kinds of parts and could potentially supply these parts.
   1. (5 pts) Under this assumption, if we view supplies as a relationship set from supplier to part (E-R model), what is the mapping cardinality of this relationship set?
   2. (5 pts) Express your mapping cardinality from the first part of this question as one or more functional dependencies that relations on Supplies-scheme must satisfy.
4. (5 pts) Consider a scheme:

    Allparts-scheme = (part-num, description, groovyness, quantity, 
                       supplier-name, address, phone-number, cost)

   and set of functional dependencies on that scheme:

    F =   part-num   {description, groovyness, quantity},
          {supplier-name, address}    phone-number   
   

   Is the decomposition of Allparts-scheme into Part-scheme, Supplier-scheme, and Supplies-scheme a lossless-join decomposition? Why or why not?
5. Consider the relation scheme and set of functional dependencies:

   

   that relations on scheme R must satisfy.

   1. (2 pts) Which rule or axiom allows us to conclude from ?
   2. (2 pts) Which rule or axiom allows us to conclude from and ?
   3. (2 pts) Which rule or axiom allows us to conclude from and ?
   4. (4 pts) Is G a superkey for R? Why or why not?
6. Consider the relation scheme and set of functional dependencies:

   

   that relations on scheme R must satisfy.

   1. (10 pts) Find a lossless join decomposition of R into BCNF.
   2. (7 pts) Compute , a canonical cover for F.
   3. (5 pts) Find a lossless join, dependency preserving decomposition of R into 3NF.
7. Consider the relation scheme R = {A, B, C, G} and set of dependencies:

   

   that relations on scheme R must satisfy.

   1. (5 pts) Prove that is in .
   2. (5 pts) Prove that the decomposition {ABC, AG} is a lossless join decomposition.
   3. (10 pts) Find a lossless join decomposition of R into 4NF.
8. (10 pts) What kinds of problems associated with relational database design can decomposition help solve? Why does decomposition help?
9. (5 pts) Are arbitrary decompositions helpful in designing relation schemes? Why or why not?
10. (5 pts) Is it possible in general to have two primary indices on the same relation for different search keys? Justify your answer.
11. (15 pts) Construct a B tree for the following key values: (2, 3, 4, 7, 11, 17, 19, 23, 29, 31). Assume that insertions happen in the order that the keys are listed in. You do not need to show the file that the leaves are pointing into - just the B tree.

• About this document ...