Give an example of relation which is in 3 normal form but not in BCNF
if the candidate keys in a 3NF relation are
all atomic, or
non-atomic but non-overlapping,
it is guaranteed that the relation is in BCNF.
The simplest relation which violates BCNF but meets 3NF can be defined as below
A,B -> C C -> B
In this case, candidate keys are (A,B) and (A,C).
It meets 3NF because
the right-hand-side of all functional dependencies is a primary attribute.
It violates BCNF because
C -> B, but the left-hand-side is not a superkey.