Database BCNF Violations

antikbd picture antikbd · Oct 23, 2014 · Viewed 8.8k times · Source

I am confused about a particular aspect of DB BCNF violation criteria. Here's an example:

R(ABCDEF)

The FDs are BC->D, C->AF, AB->CE.

I have derived the candidate keys to be AB and BC.

The relation is in BCNF right, given all of the FDs contain at least part of the candidate keys?

Thanks guys!

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Oct 23, 2014

You derived the right keys: AB and BC.

The relation is in BCNF right, given all of the FDs contain at least part of the candidate keys?

No. Informally, a relation is in BCNF if and only if the arrow in every FD is an arrow out of a candidate key. In other words, a relation is in BCNF if and only if the left-hand side of every functional dependency is a candidate key. The left-hand side of C->AF is C, but C is not a candidate key. So R is not in BCNF.

(From a comment by the OP . . .)

And one more thing, if I want to find out the violations in R, should I select one CK and compare all the FDs against that CK, or should I use all of the CKs when comparing the FDs?

Use all the candidate keys. Normalization always considers all the candidate keys.