As you know, there are many different normal forms. Figure 3-3 is our first take on the normal form hierarchy (but please note immediately that I’ll be expanding the hierarchy later in this book—in Chapter 13, to be specific). Note: sYou might think the hierarchy is upside down, since it shows the highest normal form at the bottom and the lowest at the top. I don’t want to argue the point; let me just say that showing it the way the figure does fits better (in my view) with the fact that, e.g., all 2NF relvars are in 1NF but some 1NF relvars aren’t in 2NF. To elaborate on the figure:
There are several different normal forms: first, second, third, and so on. The figure shows six such, but as you can see they aren’t labeled first, ..., sixth (not quite)—there’s an interloper, BCNF, between third and fourth.[30] I’ll explain the reason for this terminological oddity in Chapter 4; for now, let me just say that the name BCNF is short for Boyce/Codd normal form. Note: Despite the BCNF exception, it’s convenient to use the term nth normal form to refer generically to the different levels of normalization, and I’ll adopt that usage from time to time in what follows.
In general, the higher the level of normalization the better, from a design point of view—because the higher the level of normalization, the more redundancies are prevented and the fewer update anomalies can occur.
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 book.
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 nth 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 nth (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 book.
If relvar R is in nth 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 book. For that reason, I’m not even going to try to define it here; I’m just going to assume until further notice that we can at least recognize it when we see it (though even that’s a pretty big assumption, actually). Chapter 15 examines the concept in depth.