7.31

Consider the schema $R=(A,B,C,D,E,G)$ and the set $F$ of functional dependencies:

$$\begin{gathered} AB\to CD \\ B\to D \\ DE\to B \\ DEG\to AB \\ AC\to DE \end{gathered}$$

$R$ is not in BCNF for many reasons, one of which arises from the functional dependency $AB\to CD$. Explain why $AB\to CD$ shows that $R$ is not in BCNF and then use the BCNF decomposition algorithm starting with $AB\to CD$ to generate a BCNF decomposition of $R$. Once that is done, determine whether your result is or is not dependency preserving, and explain your reasoning.

---

$AB\to CD$ is NOT a trivial functional dependency.

Also, $(AB{)}^{+}=\{A,B,C,D,E\}$. That is, $AB$ is NOT a superkey.

Thus the relation $R$ is NOT in BCNF.

BCNF decomposition of $R$:-

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

Clearly the above decomposition is NOT dependency preserving since the functional dependency $DE\to B$ cannot be tested by using only one relation.