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.