Exercise 19.1 Briefly answer the following questions:

1. Define the term functional dependency.
2. Why are some functional dependencies called trivial?
3. Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which R is in 1NF but not in 2NF.
4. Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which R is in 2NF but not in 3NF.
5. Consider the relation schema R(A,B,C), which has the FD B → C. If A is a candidate key for R, is it possible for R to be in BCNF? If so, under what conditions?
If not, explain why not.
6. Suppose we have a relation schema R(A,B,C) representing a relationship between two entity sets with keys A and B, respectively, and suppose that R has (among others) the FDs A → B and B → A. Explain what such a pair of dependencies means (i.e., what they imply about the relationship that the relation models).

Answer 19.1
1. Let R be a relational schema and let X and Y be two subsets of the set of all attributes of R. We say Y is functionally dependent on X, written X → Y, if the
Y-values are determined by the X-values. More precisely, for any two tuples r1 and r2 in (any instance of) R πX(r1) = πX(r2) [pic]πY (r1) = πY (r2)

2. Some functional dependencies are considered trivial because they contain superfluous attributes that do not need to be listed. Consider the FD: A → AB. By reflexivity, A always implies A, so that the A on the right hand side is not necessary and can be dropped. The proper form, without the trivial dependency would then be A → B.

3. Consider the set of FD: AB → CD and B → C. AB is obviously a key for this relation since AB → CD implies AB → ABCD. It is a primary key since there are no smaller subsets of keys that hold over R(A,B,C,D). The FD: B → C violates
2NF since:

C [pic] B is false; that is, it is not a trivial FD
B is not a superkey
C