This assignment is not a programming assignment. In this assignment you will use the definitions and algorithms from this explanation to solve some normalization problems. This assignment is due Thursday, November 20.

Problem 1: Let R(S,F)=R({A,B,C,D,E}, {A->BC, CD->E, B->D, E->A}).

• Part (a): Show that S1={A,B,C} and S2={A,D,E} is a lossless decomposition.
• Part (b): Show that S1={A,B,C} and S2={C,D,E} is not a lossless decomposition. Explain why it is not lossless and then give a specific example that demonstrates a lossy join.
• Part (c): Find all the keys for R(S,F). Show your work.
• Part (d): For the decomposition in Part (a), compute the projection dependencies F1 and F2 and then show that R1(S1,F1) and R2(S2,F2) is not a dependency-preserving decomposition by showing specific tables T1 and T2 that when joined violate a constraint of R(S,F).
• Part (e): Compute a lossless BCNF decomposition of R(S,F). Show all your work.

Problem 2: Let R(S,F)=R({A,B,C,D}, {A->B, C->D, D->B}).

• Part (a): Find all the keys for R(S,F). Show your work.
• Part (b): Starting with the FD A->B, compute a lossless BCNF decomposition of R(S,F). Show your work.
• Part (c): Starting with the FD C->D, compute two distinct lossless BCNF decompositions of R(S,F). Show your work. (Hint: Be very careful about how you compute F1 and F2.)
• Part (d): Starting with the FD D->B, compute a lossless BCNF decomposition of R(S,F). Show your work.
• Part (e): Give this database some semantics. Let A be SupplierID, let B be ZipCode, let C be PartNo, and let D be Color. It is not hard to show that instances of
  R(S,F)=R({SupplierID,ZipCode,PartNo,Color}, {SupplierID->ZipCode, PartNo->Color, Color->ZipCode})
  can contain a lot of redundant information. Each of the decompositions in parts (b), (c) and (d) eliminates redundancies, but they all lose one of the constraints. For each decomposition, use the semantics to describe which constraint is lost (you don't need to give specific examples using tables).
• Part (f): How would you design a set of relations to replace R(S,F) so as to eliminate as much redundancy as possible but not lose any constraint information? Use as many relations as you think is needed. Can you eliminate all the redundant information?

Turn in a (electronic) document containing your solutions. You can write up your solutions in a Word document, a plain text file, or, if you really want to impress me, a LaTeX document. This assignment is due Thursday, November 20.