databasedatabase-normalizationfunctional-dependencies3nfbcnf

# Finding a relation in 3NF but not in BCNF

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?

Solution

• 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

• 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 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

• 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.