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.