Consider a schema S with functional dependencies:

Question

{A->BC, C->FG, E->HG, G->A}.

(a) Compute the attribute closure of A with respect to S.
(b) Suppose we decompose S so that one of the subrelations, say called R, contains attributes AFE only. Find a projection of the functional dependencies in S on R (i.e., find the functional dependencies between attributes AFE).
(c) Is R in BCNF? Explain. If not, use one cycle of the BCNF decomposition algorithm to
obtain two sub relations, and answer whether they are in BCNF.
(d) Show that your decomposition is lossless.
(e) Is your decomposition dependency preserving? Explain.
(f) Is R in 3NF? Explain. If not, use 3NF decomposition algorithm to obtain sub relations,
and answer whether they are in 3NF.

Answer 1

(a) ABCFG
(b) {A->F, E->AF} or {A->F, E->A, E->F} or {A->F, E->A}
(c) No, A is not a superkey. R1={AF,A->F} and R2={EA,E->A}. They are in BCNF.
(d) fAFg intersection {EA} is {A}, which is a key of R1.
(e) Yes, because each of the FDs of R is entailed by FDs of R1 and R2.
(f) No, F is not part of a key. Obtain same R1 and R2. They are in 3NF.