Databases Reference
In-Depth Information
If (a) supplier s1 supplies part p1 to project j2 and (b) supplier s2 supplies part p1 to project j1 ( s1 s2 , j1
j2 ), then (c) no part, not even p1 , can be supplied by supplier s1 to project j1 .
If (a) supplier s1 supplies part p2 to project j1 and (b) supplier s2 supplies part p1 to project j1 ( s1 s2 , p1
p2 ), then (c) no project, not even j1 , can be supplied by supplier s1 with part p1 .
In fact, these three business rules can all be combined into one, as follows. Let's agree, just for the moment,
to say each tuple of relvar SPJ′ represents a shipment (by some supplier of some part to some project). Then there
cannot exist three distinct shipments x , y , and z such that x and y involve the same supplier, y and z involve the same
part, and z and x involve the same project.
There's still another point to be made in connection with the SPJ′ example. Refer again to the analysis that
led to the first of the foregoing three business rules. That analysis showed that tuple t3 can't appear together with
tuples t1 and t2 . It follows, therefore, that SPJ′ suffers from an insertion anomaly, despite the fact that it's in RFNF
(and the fact that no tuple forcing JD holds, therefore). By contrast, it doesn't suffer from a deletion
anomaly─assuming, that is, that the only constraints to which it's subject are the stated FD and JD (and logical
consequences thereof). So one difference between 5NF and RFNF is this: Even though both are redundancy free,
5NF guarantees “no insertion anomalies” while RFNF doesn't─assuming, again, that FDs and JDs are the only
constraints under consideration.
Of course, it's tempting to conclude from the SPJ′ example that relvars that are in RFNF and not 5NF are
likely to be rare in practice. Nevertheless, there's a clear logical difference between the two normal forms, and thus,
from the point of view of reducing redundancy at least, it's really RFNF and not 5NF that ought to be the target to
be aimed for. (As a bonus, I note that RFNF is a little easier to test for, too, than 5NF is.)
Note: As a matter of fact, the SPJ′ example reinforces the foregoing observation in another way also. Since
the relvar is subject to the JD {{SNO,PNO},{PNO,JNO},{JNO,SNO}}, it can be nonloss decomposed into its
projections on {SNO,PNO}, {PNO,JNO}, and {JNO,SNO}, respectively. Those projections are each “all key,” and
in fact in 5NF. However, that decomposition “loses” the FD {SNO,PNO} → {JNO}! As we saw in Chapter 6,
losing dependencies is generally not recommended. Hence relvar SPJ′ illustrates the point that not only is 5NF
sometimes too strong, but sometimes it might be positively contraindicated.
Domain-key normal form (DK/NF) differs from all of the normal forms discussed in this topic so far in that it's not
defined in terms of FDs, MVDs, and JDs, as such, at all. 15 DK/NF is really a kind of “ideal” normal form: It's
desirable because, by definition, a relvar in DK/NF is guaranteed to be free of certain update anomalies; sadly,
however, it's not always achievable, nor has the question “Exactly when can it be achieved?” been answered. Be
that as it may, let's investigate.
DK/NF is defined in terms of domain constraints and key constraints . Key constraints are already familiar,
of course (they were defined in Chapter 5). As for domain constraints, I remind you that domain is essentially just
another word for type (see the answer to Exercise 2.4 in Appendix D). It follows that a domain constraint ought
logically to be the same thing as a type constraint; in other words, it ought simply to be a specification of the set of
values that constitute the type in question (see SQL and Relational Theory for further explanation of this concept).
However, the term is being used in the present context in a slightly special sense. To be specific, a domain
constraint , as that term is used here, is a constraint to the effect that values of a given attribute are taken from some
15 Well … it's defined in terms of key constraints, as we'll see, and key constraints in turn are a special case of FDs, so this remark is perhaps a
little economical with the truth.
Search WWH ::

Custom Search