2

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!

AntikM
  • 636
  • 4
  • 13
  • 28
  • 1
    *"I have derived the candidate keys to be `AB->CE or BC->D`."* Those are functional dependencies, not candidate keys. There might be more than one candidate key. You don't choose from them; you identify *all* of them. – Mike Sherrill 'Cat Recall' Oct 23 '14 at 01:33
  • 1
    *" . . . the relation is still in BCNF right, given all of the FDs contain at least part of the LHS of the candidate key?"* No, it's in BCNF if the arrow in every FD is an arrow out of a candidate key. In this case R is not in BCNF, because the arrow in `C->AF` is not an arrow out of a candidate key. (C is not a candidate key in R.) – Mike Sherrill 'Cat Recall' Oct 23 '14 at 01:36
  • Hi. Thanks for the correction. I have edited the question as such, but the CKs are still AB and BC. So, in order for R to be in BCNF, the LHS of all FDs must contain at least one of the FULL candidate keys (in this case, either AB or BC)? – AntikM Oct 23 '14 at 01:39
  • 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? Thanks a lot! – AntikM Oct 23 '14 at 01:42

1 Answers1

6

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • If a relation is in BCNF, there exists a functional dependency X -> Y for all X belonging to the superkey (not only a candidate key). – Sankalp Oct 18 '22 at 04:30