7.30
Consider the following set \(F\) of functional dependencies on the relation schema \((A, B, C, D, E, G)\):
\[ A \rightarrow BCD \\ BC \rightarrow DE \\ B \rightarrow D \\ D \rightarrow A \\ \]
Compute \(B^+\).
Prove (using Armstrong’s axioms) that \(AG\) is a superkey.
Compute a canonical cover for this set of functional dependencies \(F\); give each step of your derivation with an explanation.
Give a 3NF decomposition of the given schema based on a canonical cover.
Give a BCNF decomposition of the given schema using the original set \(F\) of functional dependencies.
- Compute \(B^+\).
\(B^+ = \{A, B, C, D, E\}\)
- Prove (using Armstrong’s axioms) that \(AG\) is a superkey.
\(A \rightarrow 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 \rightarrow BC\) holds (Decomposition rule).
\(BC \rightarrow DE\) holds (given).
By Transitivity rule \(A \rightarrow DE\) holds.
Thus, \(A \rightarrow BCDE\) holds by Union rule (see Exercise 7.4).
By Augmentation rule \(AG \rightarrow ABCDEG\).
This proves that \(AG\) is a superkey.
- 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 \rightarrow BCD\) so, remove it.
\(D\) is also extraneous in \(BC \rightarrow DE\) so, remove it.
Thus the following is a canonical cover of \(F\).
\[ A \rightarrow BC \\ BC \rightarrow E \\ B \rightarrow D \\ D \rightarrow A \\ \]
- 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\}\} \]
- 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\}\} \]