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.