Databases Reference
In-Depth Information
it were, and (other things being equal) you should probably try to ensure that all of the relvars in your database are
in 5NF.
11
CONCLUDING REMARKS
I'll close this chapter with a few miscellaneous observations. First, note that I'm assuming throughout this part of
the topic (as indeed I did in the previous part as well) that the only dependencies we care about are ones that have to
do with projection as the decomposition operator and join as the corresponding recomposition operator. Under that
assumption, it's immediate from the definition of join dependency that JDs are, in a sense, the “ultimate” kind of
dependency; that is, there's no “higher” kind of dependency such that JDs are just a special case of that higher kind.
And it follows further that─though I haven't really defined it properly yet!─fifth normal form is the final normal
form
12
with respect to projection and join (which accounts for its alternative name,
projection-join
normal form).
Second, I've referred several times to relvars that are in BCNF and not 5NF; indeed, I've tacitly assumed that
if relvar
R
is in 5NF, then it's certainly in BCNF. In fact this assumption is correct. Let me also state explicitly for
the record that 5NF is always achievable; that is, any relvar not in 5NF can always be decomposed into a set of 5NF
projections─though not necessarily without losing dependencies, of course, since we already know from Chapter 7
that decomposition to
BCNF
and preserving dependencies can be conflicting objectives.
Third, it follows from the definition of 5NF that a relvar
R
that's in 5NF is guaranteed to be free of
redundancies that can be removed by taking projections. In other words, to say
R
is in 5NF is to say that further
nonloss decomposition of
R
into projections, while it might be possible, certainly won't remove any redundancies.
Note very carefully, however, that to say R is in 5NF is not to say R is free of redundancy.
(A belief to the contrary
is another popular misconception. See Exercise 1.11 in Chapter 1.) The fact is, there are many kinds of redundancy
that projection as such is powerless to remove─which is an illustration of the point I made in Chapter 1, in the
section “The Place of Design Theory,” to the effect that there are numerous issues that current design theory simply
doesn't address at all. By way of example, consider Fig. 9.3 below, which shows a sample value for a relvar,
CTXD, that's in 5NF and yet suffers from redundancy. The predicate is
Teacher TNO spends DAYS days with
textbook XNO on course CNO
. The sole key is {CNO,TNO,XNO}. As you can see, the fact that (e.g.) teacher T1
teaches course C1 appears twice, and so does the fact that course C1 uses textbook X1.
13
┌─────┬─────┬─────┬──────┐
CTXD │ CNO │ TNO │ XNO │ DAYS │
├═════┼═════┼═════┼──────┤
│ C1 │ T1 │ X1 │ 7 │
│ C1 │ T1 │ X2 │ 8 │
│ C1 │ T2 │ X1 │ 9 │
│ C1 │ T2 │ X2 │ 6 │
└─────┴─────┴─────┴──────┘
9.3: The 5NF relvar CTXD─sample value
Let's analyze this example a little more carefully:
11
Except as noted in Chapter 13.
12
Well ... except for 6NF (again, see Chapter 13).
13
One reviewer argued strongly that those repetitions didn't really constitute redundancy. Well, I don't want to argue the point here; I'll just
remind you that I'll be examining the whole issue of exactly what does constitute redundancy in detail in Chapter 15.
