Rules for Decomposition to 2 NF

database-designnormalization

I am struggling with the concept of 2NF form. Lets say I have a set of functional dependencies for R1(A,B,C,D,E,H,M,K)
Where :

B  -> M,C
AB -> D
DH -> E
H  -> K
A  -> H

Whenever I try to decompose it, I directly get 3NF tables. What are the rules to get something in 2NF, with minimal set of tables ?

Now how do I take this further to 3NF or BCNF?

Best Answer

To simplify, say we have 

R(A,B,C,D)

AB -> C
A  -> D

Relation R is NOT in 2NF -- the KEY is {AB}, but {D} is dependent only on part of the key.

Now decomposition

  • Step 1. R1 {ABC} R2 {AD}

  • Done.

So, both R1 and R2 are in 6NF and therefore in 5,4,3,2,1. There is no in-between -- it may happen that in some specific example you may see "progress" 1, 2, 3, BCNF; but that is an exception, not the rule.

... looking at a systematic approach to move from 1 NF to 2 NF to 3 NF to BCNF ...

is a common misunderstanding.

Related Question