Databases Reference
In-Depth Information
Chapter 9
J D s a n d 5 N F
( I n f o r m a l )
If you can't beat them, join them
─Anon.
Just as Boyce/Codd normal form is defined in terms of functional dependencies, so fifth normal form (5NF) is
defined in terms of join dependencies (JDs);
1
as noted in Chapter 4, in fact, 5NF is
the
normal form with respect to
JDs, just as BCNF is
the
normal form with respect to FDs. And the treatment of these ideas in this part of the topic
therefore parallels the treatment of BCNF and FDs in Part II. In other words, I plan to treat the material both
formally, in Chapter 10, and informally in the present chapter.
Let me immediately add that although 5NF is indeed “the” normal form with respect to JDs, this state of
affairs shouldn't be taken to mean that 5NF is the ultimate goal of the normalization process.
Au contraire
, in fact:
There are at least two other normal forms that have a better claim to that title, as we'll see in Chapter 13. From a
pedagogical point of view, however─as well as from a historical one─I think it's desirable to discuss 5NF in detail
first. (I mention this point simply in order to avoid giving a false impression; one of my reviewers felt I should have
presented the material in a different sequence, but I don't agree.)
Now, in previous writings I've tended to regard JDs as if they were just a generalized kind of FD. I now
think this perception is wrong, or at least misleading; I now think it's better to regard JDs as a completely different
phenomenon. Of course, FDs and JDs are both dependencies (i.e., constraints), and they do resemble each other in
certain respects; in particular, the fact that a certain JD holds in relvar
R
implies that
R
can be nonloss decomposed
in certain ways, just as the fact that a certain FD holds in relvar
R
also implies that
R
can be nonloss decomposed in
certain ways. It's also true that every FD implies a JD, so that if some FD
F
holds in relvar
R
, a certain JD
J
holds
in
R
also. But not all JDs are implied by FDs; in fact, to speak
very
loosely─but I must emphasize that what I'm
about to say is
extremely
imprecise─we might say that 5NF has to do with JDs that aren't implied by FDs. That is,
it's if some relvar
R
is in BCNF, but is subject to some JD that's not implied by FDs, that the notion of 5NF might
be relevant.
Now, a relvar is in BCNF if and only if all FDs to which it's subject are implied by keys. As you'd probably
expect, therefore, a relvar is in 5NF if and only if all JDs to which it's subject are implied by keys.
2
However, this
latter notion─i.e., the notion of JDs being implied by keys─is a bit trickier to pin down than its FD counterpart; in
fact, there's a very rich theory surrounding these ideas, as we'll soon see, and some of that theory can be a little
overwhelming (not to say confusing) at first. You need to keep a clear head! As someone much more
knowledgeable in these matters than I am once said to me:
JDs are very mysterious
.
1
So is 4NF, but I'm going to ignore 4NF (mostly) until Chapter 12.
2
This very informal definition is the only one I'll be giving for 5NF in the present chapter.