Consider the following functional dependencies over the attribute set ABCDEFGH :

Question


Find a minimal cover, then decompose into lossless 3NF. After that, check if all the resulting relations are in BCNF. If you ?nd a schema that is not, decompose it into a lossless BCNF. Explain all steps.

Answer 1

Minimal cover: First split AD ? BE into AD ? B and AD ? E. The next step is to reduce the left-hand sides of the FDs. Since A ? B and A ? E are implied by the given set of FDs, we can replace AD ? B, AD ? E ,and AC ? E in the original set with A ? B (A ? E already exists in the original set). Likewise E ? D and BD ? E can be derived from the original set so we can replace BE ? D and BDH ? E with E ? D and BD ? E.
In the next step, we remove redundant FDs, of which we ?nd only A ? B. The ?nal result is therefore


Lossless 3NF:
(AE; {A ? E}), (EBD; {BD ? E, E ? D, E ? B}),
(BGF ; {BG ? F}), (FA ; {F ? A}), (DH ; {D ? H }), (CDA; {CD ? A).

Since BGF is a superkey of the original schema, we do not need to add anything to this decomposition.
The schema (BGF ; {BG ? F}) is not in BCNF because F ? B is entailed by the original set of FDs and (CDA; {CD ? A) is not in BCNF because A ? D is entailed.
We decompose BGF with respect to F ? B into (BF ; {F ? B}) and (GF ; {}), thereby loosing the FD BG ? F .
Similarly CDA is decomposed using A ? D into (AD; {A ? D}) and (AC ; {}), loosing CD ? A.