## Find the dependency preserving BCNF decomposition of the given relation schema / Is the given decomposition is dependency preserving? / How to decompose a relation into BCNF with dependency preservation? / How to perform a dependency preserving decomposition? / Is the table in BCNF?

####
**Question:**

**7. Assume a relation R(A, B, C, D, E) with the set of functional dependencies F = { A → B, BC → D } and the candidate key (ACE). The relation is not in BCNF. Which of the following is the dependency preserving decomposition for R into BCNF relations?**

(a) R

_{1}(B, C, D), R

_{2}(A, B), R

_{3}(A, C, E)

(b) R

_{1}(A, B), R_{2}(A, C, D), R_{3}(A, C, E)
(c) R

_{1}(A, B), R_{2}(A, C, D, E)
(d) All of the above

####
**Answer:**

**(a) R**

_{1}(B, C, D), R_{2}(A, B), R_{3}(A, C, E)**BCNF**– In simpler terms, the Left Hand Side (LHS) of all the functional dependencies should be the key.

**Dependency preserving decomposition**– If a relation R with set F of functional dependencies is decomposed into relations R_{1}, R_{2}, R_{3}, …, R_{i}then the closure of set of functional dependencies for these relations should satisfy the following;- (F
_{1}U F_{2}U F_{3}U … U F_{i})^{+}= F^{+} - That is the closure of union of set of
functional dependencies of relations R
_{1}, R_{2}, …, R_{i}should be equal to the closure of set of functional dependencies F of R. In other words, all the functional dependencies in (F_{1}U F_{2}U F_{3}U … U F_{i})^{+}should be in F^{+}also.

**Given:**F = { A → B, BC → D }

**Step 1: What is the candidate key for R?**

A

^{+}= AB
(BC)

^{+}= BCD
E is not included in any of the functional
dependencies, hence E should be in the left hand side and forms one of the key attributes.

(AE)

^{+}= ABE
(BCE)

^{+}= BCDE
(ACE)

^{+}= ABCDE = R.
Hence, (ACE) is the candidate key for R.

**Step 2: Which of the functional dependencies violate BCNF?**

For the given table R, the following are
the dependencies that violate the conditions for BCNF;

{ A → B, BC → D
}

Because, the LHS of both FDs are not
candidate keys.

Step 3: Break
the relation using the violating FDs

Let us consider the FD BC → D
and break (decompose) R using this functional dependency;

We create relation R

_{1}with the schema R_{1}(B, C, D). [all the attributes of functional dependency BC → D]
We create relation R

_{2}with the schema R_{2}(A, B, C, E). [the attributes for R_{2}are derived by performing R – D, ie., {A, B, C, D, E} – {D}]**Step 4: R**

_{1}is in BCNF?
The candidate key for R

_{1}is BC and the FD that holds in R_{1}is BC → D. The LHS of the FD is the candidate key. Hence, R_{1}is in BCNF.**Step 5: R**

_{2}is in BCNF?
The candidate key for R

_{2}is ACE and the FD that holds in R_{2}is A → B. The LHS of the FD is not the candidate key. Hence A → B violates the rule for BCNF.
Step
5.1: Break R

_{2}on FD A → B.
We create relation R

_{21}(A, B) using A → B, the violating FD.
We create relation R

_{22}(A, C, E) using R_{2}– B.**Step 6: Is R**

_{21}and R_{22}are in BCNF?
The candidate key for R

_{21}is A and the LHS of A → B is A. Hence, R_{21}is in BCNF.
The candidate key for R

_{22}is ACD and the FD holds in R_{22}is trivial FD. Hence, R_{22}is in BCNF.**Step 7: R into R**

_{1}, R_{21}, and R_{22}is dependency preserving decomposition?
Let us consider the set of FDs of
R, R

_{1}, R_{21}, and R_{22}as F, F_{1}, F_{21}, and F_{22}.
Apply the rule (F

_{1}U F_{2}U F_{3}U … U F_{i})^{+}= F^{+}.
(F

_{1}U F_{21}U F_{22})^{+}= F^{+}
({BC → D} U {A → B}
U {ACE →
ACE})

^{+}= ({ A → B, BC → D})^{+}
All the FDs are found in
decomposed relations. Hence,

**.**__R into R___{1}(B, C, D), R_{21}(A, B), and R_{22}(A, C, E) is dependency preserving BCNF decompositions