<home

3NF vs BCNF

Boyce-Codd Normal Form (BCNF) is a "refinement" of Ted Codd's original 3rd Normal Form. The subtle difference between 3rd Normal Form (3NF) and BCNF is that 3NF makes a distinction between key and non-key attributes (also called non-prime attributes) whereas BCNF does not. This is best explained using Zaniolo's definition [1] of 3NF, which is equivalent to Codd's:


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

BCNF requires (a) but doesn't treat (b) as a special case of its own. In other words BCNF requires that every nontrivial determinant is a superkey even its dependent attributes happen to be part of a key.


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

BCNF is therefore more "strict" (a relation that satisfies 3NF does not necessarily satisfy BCNF) but BCNF is also simpler to define and explain, generally simpler to achieve and fundamentally more significant and useful than 3NF.

The difference is so subtle that what many people informally describe as 3NF is actually BCNF.


[1] Carlo Zaniolo. 1982. A new normal form for the design of relational database schemata. ACM Trans. Database Syst. 7, 3 (September 1982), 489-499. DOI=10.1145/319732.319749 http://doi.acm.org/10.1145/319732.319749

This 1982 paper by Carlo Zaniolo actually defines a new normal form called EKNF which sits "between" 3NF and BCNF. EKNF is significant because it is dependency-preserving whereas BCNF in some exceptional cases is not. Zaniolo's paper also includes the reformulated definitions of 3NF and BCNF referred to here.