Databases Reference
In-Depth Information
THIRD NORMAL FORM
Definition:
Relvar
R
is in
third normal form
(3NF) if and only if, for every nontrivial FD
X
→
Y
that holds
in
R
, either (a)
X
is a superkey or (b)
Y
is a subkey.
Points arising:
To repeat something I said in the previous section (and contrary to popular opinion, perhaps), 3NF is mainly
of historical interest—it should be regarded at best as no more than a stepping stone on the way to BCNF.
Note:
The reason I say
contrary to popular opinion, perhaps
is that many of the “definitions” of 3NF
commonly found (at least those in the popular literature) are actually definitions of BCNF—and BCNF, as
I've already indicated,
is
important.
Caveat lector.
Definitions of 3NF in the literature often take the form “
R
is in 3NF if and only if it's in 2NF and ...”; I prefer
a definition that makes no mention of 2NF. Note, however, that my definition of 3NF can in fact be derived
from the second of the definitions I gave for 2NF by dropping condition (c) (“
X
is not a subkey”). It follows
that 3NF implies 2NF—that is, if a relvar is in 3NF, then it's certainly in 2NF.
We've already seen an example of a relvar that's in 2NF but not 3NF: namely, the suppliers relvar S (see
Fig. 3.1 in Chapter 3). To elaborate: The nontrivial FD {CITY} → {STATUS} holds in that relvar, as we know;
moreover, {CITY} isn't a superkey and {STATUS} isn't a subkey, and so the relvar isn't in 3NF. (It's certainly in
2NF, however.
Exercise:
Confirm this claim!)
BOYCE/CODD NORMAL FORM
As I said earlier, Boyce/Codd normal form (BCNF) is
the
normal form with respect to FDs—but now I can define it
precisely:
Definition:
Relvar
R
is in
Boyce/Codd normal form
(BCNF) if and only if, for every nontrivial FD
X
→
Y
that holds in
R
,
X
is a superkey.
Points arising:
It follows from the definition that the only FDs that hold in a BCNF relvar are either trivial ones (we can't
get rid of those, obviously) or arrows out of superkeys (we can't get rid of those, either). Or as some people
like to say:
Every fact is a fact about the key, the whole key, and nothing but the key
—though I must
immediately add that this informal characterization, intuitively attractive though it is, isn't really accurate,
because it assumes among other things that there's just one key.
The definition makes no reference to 2NF or 3NF. Note, however, that the definition can be derived from the
3NF definition by dropping condition (b) (“
Y
is a subkey”). It follows that BCNF implies 3NF—that is, if a
relvar is in BCNF, then it's certainly in 3NF.
