CSCC43 Winter 2009 Data Management Systems Assignment 1

Document Sample
CSCC43 Winter 2009 Data Management Systems Assignment 1 Powered By Docstoc
					CSCC43 Winter 2009                                                          Data Management Systems


                                               Assignment 1
                                             Selected solutions




Question 3

Let R(A1, A2, …, An) be a relation with n attributes. Assume that the only keys are {A1, A2,
A3} and {A1, A3, A4}. Express as a function of n the total number of superkeys in R.

Solution:
All superkeys contain either {A1, A2, A3} and {A1, A3, A4} or both. The total number of
superkeys that contain {A1, A2, A3} but not {A1, A3, A4}, because they do not contain A4, is
 n−4            n−4
        + ... +      . Similarly, the total number of superkeys that contain {A1, A3, A4} but
 1              n−4
                                                                  n−4         n−4
not {A1, A3, A4}, because they do not contain A2, is                  + ... +     . The number of of
                                                                  1           n−4
                                                                           n−4         n−4
superkeys that contain both {A1, A3, A4} and {A1, A2, A3} is                   + ... +     + 1. The
                                                                           1           n−4
total number is, thus, 3 * (2 n − 4 − 1) + 1 .


Question 4.

Prove or disprove that
   1. If A B, then B C.
   2. If AB C, then A C and B                     C

Solution:

     1. The following table is a counterexample:

A                                       B                                  C
A1                                      B1                                 C1
A1                                      B1                                 C2


   2. The following table is a counterexample
A                               B                                          C
A1                              B1                                         C1
A2                              B1                                         C2
A1                              B2                                         C3
Question 5

Consider the set of functional dependencies F= {AB        E, BE   I, E   G, GI    H}

B. Can the functional dependencies AB        GH and EI     H be derived from F.

Solution:

AB    GH

AB    E      => AB     G (transitivity)
 E    G
                                                          => AB    H
AB    E => AB        BE (augmentation)       AB       H
           BE        H




EI   H

E    G => EI    GI (augmentation)         => EI   H
          GI    H