CS 4604 Homework #3

1. (10 points; From the Spring 1999 midterm) An E/R diagram when converted to relations (using the mechanical construction that we now know so well) gives rise to the following relations (before normalization):

   
   R(a,b,c)
   S(a,d)
   T(a,d,f,g)
   

   You may assume that the same symbols refer to the same attribute and different symbols refer to different attributes (e.g., the a in R, S, and T are the same). Your task is to reverse-engineer the E/R diagram from these relations; in other words, what E/R diagram could have produced these relations?
   
   For full credit, give two different E/R diagrams that could have produced these (and only these) relations.
   
   
2. (10 points; From the Spring 2000 midterm) An ODL schema when converted to relations (using the mechanical construction that we now know so well) gives rise to the following relations (before normalization):

   
   R(a)
   S(b)
   T(c)
   U(d)
   V(a,b,c,d)
   

   You may assume that the same symbols refer to the same attribute and different symbols refer to different attributes (e.g., the a in R and V are the same). Your task is to reverse-engineer the ODL schema from these relations; in other words, what ODL listing could have produced these relations?
   
   For full credit, give an ODL schema and describe what kind of situation would have created this schema in the first place.
   
   
3. (5 points) Exercise 3.4.6 (b) of your textbook.
   
   
4. (5 points) Exercise 3.5.10 (b) of your textbook. Do not worry about how S was obtained from R. It is irrelevant for this question. It may have been obtained by a BCNF-type decomposition or it may not. Your task is to simply infer the FDs for S, given the FDs for R.
   
   
5. (10 points) Exercise 3.6.1 (e) of your textbook. All four parts.
   
   
6. (5 points) Exercise 3.6.4 of your textbook.
   
   
7. (5 points) Repeat the same exercise as above with one small twist: give an example of an instance of relation R whose projection onto S and T and subsequent rejoining actually does yield the same relation instance. All the assumptions of the previous question apply.