Homework 4 -- Design Theory (Chapter 7)

Due on May 1 (beginning of class). All homework must be done independently.

1. Exercise 7.2 List all functional dependencies satisfied by the relation shown below
(same as in Figure 7.18 in the 5th edition)

        A    B     C
        a1    b1    c1
        a1    b1    c2
        a2    b1    c2
        a2    b1    c3

2. Exrercise 7.6 Compute the closure of the following set F of functional dependencies for relation schema R = (A,B,C,D,E).

Note: For computing F+, no need to list all the trivial FDs. List one or two and indicate
others like them are also in F+. Apply Armstrong's axioms to get the non-trivial
FDs and list those.

    A -> BC
    CD -> E
    B -> D
    E -> A
List the candidate keys for R.

3. Exercise 7.18 Why are certain functional dependencies called trivial functional dependencies?

4. Exercise 7.17 Explain what is meant by repetition of information and inability to represent information. Exaplain why each of these properties may indicate a bad relational database design.

5. Consider a relation R(A,B,C,D,E) with the following dependencies:
{AB-> C, CD -> E, DE -> B}
Is AB a candidate key of this relation? If not, is ABD? Explain your answer.

6. Consider a relation with schema R(A,B,C,D) and FDs {AB -> C, C -> D, D -> A}.
a. What are some of the nontrivial FDs that can be inferred from the given FDs?
b. What are all candidate keys of R?
c. Indicate all BCNF violations for R.
d. Decompose the relations into collections of relations that are in BCNF.
e. Indicate which dependencies if any are not preserved by the BCNF decomposition.

7. Consider a relation R(A,B,C,D,E) with FDs {AB -> C, DE ->C, and B -> D}
a. Indicate all BCNF violations for R.
b. Decompose the relations into collections of relations that are in BCNF.
c. Indicate which dependencies if any are not preserved by the BCNF decomposition.

8. Prove or disprove the following inference rules for functional dependencies.
Note: Read "|=" as implies
a. {X->Y, Z->W} |= XZ ->YW
b. {X->Y, XY -> Z} |= X -> Z
c. {XY -> Z, Y->W} |= XW->Z

Use Armstrong's Axioms or Attribute closure to prove or disprove.

9. Consider a relation R(A,B,C,D) with FDs {A ->B, B ->C, C-> D}
a. Indicate all BCNF violations for R.
b. Decompose the relations into collections of relations that are in BCNF.
c. Indicate which dependencies if any are not preserved by the BCNF decomposition.

Lossless join property notes:

Consider a relation R(A,B,C) and the only FD is A -> B.
Suppose R contains two tuples (1, 2, 3) and (2, 2, 4)
If R is decomposed into two relations, R1(A,B) and R2(B,C).
R1 will have 2 tuples (1,2), (2,2)
and R2 will have tuples (2,3), and (2,4)
Now try and do natural join between R1 and R2.

Say R" is the result of R1(A,B) natural join R2(B,C); it contains 4 tuples
(1,2,3), (1,2,4), (2,2,3), (2,2,4).
Compare R with R"; we have generated two spurious tuples (1,2,4), (2,2,3) in R" that
were not in R. A lossy join decomposition as the one shown in this example illustrates the problem of recovering the original relation R from its decomposed relations R1 and R2 if the decomposition is not done right. We prefer the decompositions to be lossless join decompositions.

10. For the same example relation R with the two tuples as in the notes above, decompose it as R1(A,B) and R2(A,C). Try and merge them back using natural join and see if the resulting relation is the same as R. Do you think this decomposition is a lossless join decomposition?