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