I've been reading many different sources on how to differentiate relations that are in 3NF/BCNF. And I've so far this is my understanding...
I will use this relation as an example...
R = {A, B, C, D, E}
and
F = {A -> B, B C - > E, E D -> A}
.
Firstly we must find the keys of the relation. I used this video to help me do that. And I got
Keys = {ACD, BCD, CDE}
Now to make sure R
is in BCNF, we must make sure that the left hand side of every functional dependency in F
is one of the Keys
. We instantly know this is not the case, because the first FD is A -> B
and A
is not one of the keys. So it is not in BCNF.
Now to make sure R
is in 3NF, we must make sure that the left hand side of every functional dependency in F
is one of the Keys
OR the right hand side of every functional dependency in F
is a subset of one of the Keys
. If you look at the right hand side of every FD, they are B
, E
and A
. These are each a subset of a Key
, so this means that it is in 3NF.
So this is one of the rare cases (according to wiki) where a relation is in 3NF
but not in BCNF
. Is this method correct? Is it reliable? Am I missing anything?
First you need to learn superkeys, candidate keys, and primary attributes.
However, this rule of thumb helps:
A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.
In other words, if the candidate keys in a 3NF relation are
it is guaranteed that the relation is in BCNF.
The simplest relation which violates BCNF but meets 3NF has the following functional dependencies:
A,B -> C C -> B
In this case, candidate keys are (A,B)
and (A,C)
.
It meets 3NF because
It violates BCNF because
C -> B
, but the left-hand-side is not a superkey.