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).
