Databases Reference

In-Depth Information

adequate. What's more, to repeat something I said in Chapter 8, a design can be fully normalized (meaning the

relvars are all in 5NF, or even 6NF) and yet still be bad. For example, the projection of the suppliers relvar S on

{SNO,STATUS} is certainly in 6NF, but it's not a good design, as we saw in Chapter 6.

Another point to consider is that replacing a 5NF relvar by 6NF projections will probably lead to the need to

enforce certain equality dependencies (EQDs). As we saw in the previous section, an EQD is a constraint to the

effect that certain projections of certain relvars must be equal (speaking a trifle loosely). For example, if we

decompose relvar P as discussed above into its projections PN, PL, PW, and PC, then the following constraints will

probably apply:

CONSTRAINT ... PL { PNO } = PN { PNO } ;

CONSTRAINT ... PW { PNO } = PN { PNO } ;

CONSTRAINT ... PC { PNO } = PN { PNO } ;

On the other hand, as explained elsewhere,
6
decompositions like the one under discussion can be a good basis

for dealing with missing information. Suppose every part does always have a known name but doesn't necessarily

have a known color, weight, or city. Then a part with no known color will simply have no tuple in relvar PL (and

similarly for weights and cities and relvars PW and PC, respectively). Of course, the equality dependencies will

then become
inclusion
dependencies (actually foreign key constraints), from PL to PN, PW to PN, and PC to PN,

respectively.

The net of the foregoing discussion is as follows (I'll express it in terms of the parts example, just for

definiteness): If there are two or more properties that every part always has─say name and color─then separating

those two properties into distinct projections is probably a bad idea; but if some property is “optional” (in other

words, has the potential to be “missing” or unknown), then placing that property in a relvar of its own is probably a

good idea.

SUPERKEY NORMAL FORM

The next normal form I want to discuss, briefly, is
superkey normal form
, SKNF. Let me immediately say that

SKNF doesn't seem to be very important in its own right; the main reason I mention it at all is that many textbooks

give what's essentially a definition of it as an incorrect definition of
fifth
normal form. For example, the following

is a paraphrased extract from a textbook of my own
7
(
warning! untruths coming up!
):

Relvar
R
is in 5NF if and only if every nontrivial JD that holds in
R
is implied by the keys of
R
, where:

a.

The JD
{
X1
,...,
Xn
} is
trivial
if and only if at least one of
X1
, ...,
Xn
is equal to the heading of
R
.

b.

The JD
{
X1
,...,
Xn
} is
implied by the keys
of
R
if and only if each of
X1
, ...,
Xn
is a superkey for
R
.

Part a. of this definition is correct, of course, but part b. isn't. To see why not, consider the following

counterexample. Let relvar SNC be the projection of the suppliers relvar S on the attributes {SNO,SNAME,CITY}.

SNC is in 5NF. Yet the following JD─

6
See either
SQL and Relational Theory
or the topic
Database Explorations: Essays on The Third Manifesto and Related Topics
, by Hugh

Darwen and myself (Trafford, 2010).

7
An Introduction to Database Systems
(8th ed., Addison-Wesley, 2004).