Saturday, 11 February 2017

Find the functional dependencies that are violating BCNF

Find the functional dependencies that are violating BCNF, Find the FDs that are not violating the BCNF rules, Find FD for BCNF decomposition


Question:
Consider a relation schema R with attributes ABCDEFGH with functional dependencies S:
S = {B CD; BF H; C AG; CEH F; CH B}
Which of these functional dependencies violate BCNF (Boyce-Codd Normal Form)?

Solution:

BCNF requires that the LHS of an FD be a super key. Hence, we would find the closure for all the Left Hand Side attributes/sets of attributes to check whether the LHS forms the key or not. [Note: B+ means the closure of the attribute B]
LHS
Result
Decision
B+
= BCD from B CD
= BCDAG from C A
Result does not include attributes E, F and H. Hence, B is not a super key and B CD violates BCNF.
BF+
= BFH from BF H
= BFHCD from B CD
= BFHCDAG from C AG
Result does not include E. Hence, (BF) is not a super key and BF H violates BCNF.
C+
= CAG from C AG
Result does not include B, D, E, F, and H. Hence C is not a super key and C AG violates BCNF.
CEH+
= CEHF from CEH F
= CEHFB from CH B
= CEHFBAG from C AG
= CEHFBAGD from B CD
Result includes all the attributes of relation R. Hence, (CEH) is the super key of R and CEH F does not violate BCNF.
CH+
= CHB from CH B
= CHBD from B CD
= CHBDAG from C AG
Result does not include E and F. Hence, (CH) is not a super key and CH B violates BCNF.

 *************************



Go to - 1NF,    2NF,    3NF,    BCNF









No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...