Databases Reference

In-Depth Information

1NF

2NF

3NF

BCNF

4NF

5NF

BCNF and 5NF are the important ones

(at least until further notice)

Fig. 3.3: The normal form hierarchy (I)

All of the normal forms apart from 1NF are defined in terms of certain
dependencies
(in this context, just

another term for integrity constraints). The principal dependencies are
functional
dependencies (FDs) and

join
dependencies (JDs).
Note:
The terms
dependence
and
dependency
are used interchangeably in the

literature. I'll stick with
dependency
in this topic.

To elaborate briefly on the previous point: FDs are the basis for defining BCNF, and JDs are the basis for

defining 5NF. As the figure states, BCNF and 5NF are the most important normal forms (at least until

further notice).

It's possible for a relvar to be in
n
th normal form and not in (
n
+1)st (
n
= 1, …, 4).

If relvar
R
is in (
n
+1)st normal form, then it's certainly in
n
th (
n
= 1, …, 4). In other words, fifth normal

form (5NF) implies fourth normal form (4NF), and so on. It follows that to say that, e.g., relvar
R
is in

BCNF doesn't preclude the possibility that
R
is in 5NF as well. In practice, however, it's common for

statements to the effect that relvar
R
is in, say, BCNF to be taken to mean that
R
is in BCNF
and not in any

higher normal form
. Please note carefully, therefore, that I do
not
adopt this usage in this topic.

If relvar
R
is in
n
th normal form and not in (
n
+1)st (
n
= 1, …, 4), then it can always be decomposed via

projection, in a nonloss way, such that (a) the projections are, typically, in (
n
+1)st normal form and (b)
R
is

equal to the join of those projections.

Finally, it follows from the previous point that any given relvar
R
can always be decomposed into 5NF

projections in particular. In other words, 5NF is always achievable.

A note on the concept of redundancy:
In Chapter 1, I said design theory is largely about reducing

redundancy, and I've referred to the concept repeatedly in the present chapter; in particular, I've said the higher the

level of normalization, the more redundancy is prevented. But coming up with a precise definition of redundancy

seems to be quite difficult—much more so, in fact, than I think is appropriate for this early point in the topic. For