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,
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.
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:
The JD { X1 ,..., Xn } is trivial if and only if at least one of X1 , ..., Xn is equal to the heading of R .
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).
Search WWH ::

Custom Search