3NF vs BCNF: Example

5.7k views Asked by At

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:

  1. 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}
1

There are 1 answers

0
Erwin Smout On BEST ANSWER

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)