CS 5614 Homework #4

1. (5+5 = 10 points) Suppose we have a relational schema R(A,B,C) with FD A->B. Suppose also that we decide to decompose this schema into S(A,B) and T(B,C).
   
   • (a) Give an example of an instance of R for which the above decomposition is lossless. Also explain which normal form is violated in R.
     
   • (b) Give an example of an instance of R for which the above decomposition is lossy. Show how the projection of your instance on S and T, when subsequently rejoined do not yield the original relation instance.
     
   
   
   
2. (12 points) You are given the relational schema R(A,B,C,D,E) with FDs AB->C, DE->C and B->D.
   1. Indicate all BCNF violations. Do not forget to consider FDs that are not in the given set, but follow from them. However, it is not necessary to give violations that have more than one attribute on the right hand side.
      
   2. Decompose the relations, as necessary, into collections of relations that are in BCNF.
   3. Indicate all 3NF violations.
   4. Decompose the relations, as necessary, into collections of relations that are in 3NF.
   
   
   
3. (8 points) Suppose you decompose R(A,B,C,D,E) into relation S(A,B,C) and some other relation(s). Give the FDs that hold in S if the FDs for R are: {A->D, BD->E, AC->E, DE->B}.
   
   
4. (5+5=10 points) You are given the relational schema R(A,B,C,D) with the MD AB->->C and the FD B->D. Identify all 4NF violations. Decompose the relations into a collection of relation schemas in 4NF.
   
   
5. (20 points) Prove that if X,Y, Z are sets of attributes, and if X->->Y and X->->Z, then X->->(Y-Z).
   
   
6. (10 points) Prove that if R is in 3NF and every key is simple (which means it is a single attribute), then R is in BCNF. Is the reverse true? :-) Why/why not?
   
   
7. (10 points) Suppose we have a relation R(A,B,C) with MD A->->B. If we know that tuples (a,b1,c1), (a,b2,c2), (a,b2,c3), (a,b3,c3) are in R, what other tuples do we know must also be in R?
   
   
8. (10 points) List all (we need all, even those that derive from others) the MDs satisfied by a relation R(A,B,C) that has the following tuples: (a1,b1,c1), (a1,b1,c2), (a2,b1,c1), (a2,b1,c3).
   
   
9. (10 points) Since we have reached the end of Module 1, it is a good point to reflect and think on what we have learnt so far. Read Codd's original paper on relational database systems (it is available here) and comment on how the relational model has "evolved" since the time this paper was published (this was in mid-1970). Organize your answer according to the following topics: (a) Data Modeling, (b) Constraints, Dependencies etc. and (c) Normal Forms. Arrange your answer in the form of bullets under each of these topics. Please do not write stories. Your answer should not exceed more than 3/2 (1.5) pages.