Databases Reference
In-Depth Information
prescribed set of values: for example, a constraint on the suppliers relvar S to the effect that STATUS values (which
are integers, i.e., are of type INTEGER) must be in the range one to a hundred, inclusive.
Here then is a definition:
Definition:
Relvar
R
is in
domain-key normal form
(DK/NF) if and only if every relvar constraint that
holds in
R
is implied by the domain constraints and key constraints that hold in
R
.
16
Enforcing constraints on a DK/NF relvar is thus conceptually simple, since it is sufficient to enforce just the
pertinent domain and key constraints, and all constraints─not just FDs, MVDs, and JDs, but
all
relvar constraints
that apply to the relvar in question─on the relvar will then be enforced automatically.
DK/NF was first defined by Fagin in 1981, and it was the DK/NF paper that first gave precise definitions of
the terms
insertion anomaly
and
deletion anomaly
. I defined these notions in Chapter 10, but there the definitions
were framed in terms of JDs specifically. Here for the record are the general definitions (note that they refer to
constraints in general, not just ones that happen to be FDs or MVDs or JDs):
17
Definition:
Relvar
R
suffers from an
insertion anomaly
if and only if there exists a legal value
r
for
R
and a
tuple
t
with the same heading as
R
such that the relation obtained by appending
t
to
r
satisfies
R
's key
constraints but is not a legal value for
R
(i.e., it violates some relvar constraint on
R
).
Definition:
Relvar
R
suffers from a
deletion anomaly
if and only if there exists a legal value
r
for
R
and a
tuple
t
of
r
such that the relation obtained by removing
t
from
r
is not a legal value for
R
(i.e., it violates some
relvar constraint on
R
).
Finally, we have the following theorem:
Theorem:
So long as every pertinent attribute can take at least two distinct values, DK/NF implies 5NF.
That is (speaking a trifle loosely), every DK/NF relvar is in 5NF, and therefore in RFNF (etc.) as
well─though it's not necessarily in 6NF, of course. In fact, DK/NF and 5NF coincide in the (probably unlikely)
special case where the only constraints that hold are FDs and JDs specifically.
CONCLUDING REMARKS
What a long strange trip it's been ... In previous chapters, I've described 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF (the
last three at some length), and now we've met four more normal forms: RFNF, SKNF, 6NF, and DK/NF (this last
one being something of an odd one out). But even that's not the end of the story. In this concluding section, just for
completeness, I briefly mention a few other normal forms that have been defined in the literature at one time or
another.
16
A relvar constraint is any constraint that can be tested by examining the pertinent relvar in isolation. For further discussion, see
SQL and
Relational Theory
.
17
These definitions, like the ones in Chapter 10, are slightly suspect, inasmuch as they talk about inserting or deleting individual tuples.
