7.30

Consider the following set $F$ of functional dependencies on the relation schema $(A,B,C,D,E,G)$:

$$\begin{gathered} A\to BCD \\ BC\to DE \\ B\to D \\ D\to A \end{gathered}$$

a. Compute $B^{+}$.

b. Prove (using Armstrong’s axioms) that $AG$ is a superkey.

c. Compute a canonical cover for this set of functional dependencies $F$; give each step of your derivation with an explanation.

d. Give a 3NF decomposition of the given schema based on a canonical cover.

e. Give a BCNF decomposition of the given schema using the original set $F$ of functional dependencies.

---

a. Compute $B^{+}$.

$B^{+}=\{A,B,C,D,E\}$

b. Prove (using Armstrong’s axioms) that $AG$ is a superkey.

$A\to BCD$ holds (given).

By Decomposition rule (I know that Decomposition rule is not one of Armstrong’s axioms, but since I have proved it in Exercise 7.27 using Armstrong’s axioms I think it is okay to use it here.)

$A\to BC$ holds (Decomposition rule).

$BC\to DE$ holds (given).

By Transitivity rule $A\to DE$ holds.

Thus, $A\to BCDE$ holds by Union rule (see Exercise 7.4).

By Augmentation rule $AG\to ABCDEG$.

This proves that $AG$ is a superkey.

c. Compute a canonical cover for this set of functional dependencies $F$; give each step of your derivation with an explanation.

Apply algorithm given in Figure 7.9.

$D$ is extraneous in $A\to BCD$ so, remove it.

$D$ is also extraneous in $BC\to DE$ so, remove it.

Thus the following is a canonical cover of $F$.

$$\begin{gathered} A\to BC \\ BC\to E \\ B\to D \\ D\to A \end{gathered}$$

d. Give a 3NF decomposition of the given schema based on a canonical cover.

The following is a 3NF decomposition of the given schema based on a canonical cover given above.

$$\{\{A,B,C\},\{B,C,E\},\{B,D\},\{D,A\},\{A,G\}\}$$

e. Give a BCNF decomposition of the given schema using the original set $F$ of functional dependencies.

$$\{\{A,B,C\},\{B,D\},\{A,E\},\{A,G\}\}$$