3NF vs BCNF: Example

Question

Gday lads,

Ok, so I'm having a database class and I have this example that makes me think that I really didn't fully understand the difference between the two NFs..

I know that

A relation, R, is in 3NF iff for every nontrivial FD (X->A) satisfied by R at least ONE of the following conditions is true:

(a) X is a superkey for R, or

(b) A is a key attribute for R

and

A relation, R, is in BCNF iff for every nontrivial FD (X->A) satisfied by R the following condition is true:

(a) X is a superkey for R

Here's the Example:

R = (A, B, C, D) F = {AB->C, AB->D, C->A, D->B} 1. Is R in 3NF, why? If it is not, decompose it into 3NF
Sol:
Yes. Find all the Candidate Keys:
AB, BC, CD, AD
Check all FDs in F for 3NF condition

Well, Doesn't C->A cause a violation? C is not a super key and its not trivial, same goes for D->B?

But its not a BCNF:

Is R in BCNF, why? If it is not, decompose it into BCNF No. Because for C->A, C is not a superkey. Similar for D->B R1 = {C, D}, R2 = {A, C}, R3 = {B, D}

possible duplicate of [What is the difference between 3NF and BCNF?](http://stackoverflow.com/questions/19749913/what-is-the-difference-between-3nf-and-bcnf) — Mosty Mostacho, Dec 14 '13 at 05:35

score 4 · Accepted Answer · answered Dec 14 '13 at 21:03

Well, Doesn't C->A cause a violation? C is not a super key and its not trivial ...

and what about that second condition for 3NF ? Is A an attribute in some key in R ?

(Hint : the purpose of such exercises is partly also to show you why 3NF isn't a very useful normal form, and the cases where the distinction between 3NF and BCNF matters)

3NF vs BCNF: Example

1 Answers1