Databases Reference
In-Depth Information
{ { SNO , SNAME } , { SNO , CITY } , { SNAME , CITY } }
─holds in relvar SNC, and the {SNAME,CITY} component isn't a superkey for that relvar.
Observe now that the foregoing “definition” refers explicitly to
nontrivial
JDs. Thus, you might be thinking
that what we need to do, in order to correct it, is to replace
nontrivial
by
irreducible
(notice that the JD just shown,
the one that holds in SNC, is reducible─the {SNAME,CITY} component could be dropped without loss). However,
such is not the case. Here's a more complicated counterexample:
Let relvar
R
have attributes
A
,
B
, and
C
(only); let
AB
,
BC
, and
CA
each be keys of
R
; and let the JD
{
AB
,
BC
,
CA
}─call it
J
─hold in
R
.
Then (a) no additional dependencies are implied by
J
and those keys, apart from trivial ones; (b)
J
is
irreducible with respect to
R
. (These two points might not be obvious, but they are in fact correct.)
It follows that
R
isn't in 5NF (the membership algorithm fails on
J
), and yet each component of
J
is a superkey.
Note:
If you'd prefer a slightly more concrete example, take
A
,
B
, and
C
to be “favorite color,” “favorite
food,” and “favorite composer,” respectively, and let the predicate be
There exists a person whose favorite color is
A, favorite food is B, and favorite composer is C
. Further, let there be business rules to the effect that:
No two distinct persons have more than one favorite in common.
No three distinct persons are such that, for each favorite, two of those three have it in common.
Exercise:
Invent some sample data for this relvar. If you try this exercise, I think you'll see why the specified key
constraints and JD make sense.
With the foregoing by way of motivation, then, let's define another normal form:
Definition:
Relvar
R
is in
superkey normal form
(SKNF) if and only if, for every irreducible JD
{
X1
,...,
Xn
} that holds in
R
, each of
X1
, ...,
Xn
is a superkey for
R
.
Now, I've said that SKNF isn't really very interesting. That's true─but there's at least one theorem that
concerns it:
Theorem:
5NF implies SKNF, but the reverse is not true; also, SKNF implies 4NF, but the reverse is not
true.
In other words, SKNF falls strictly between 4NF and 5NF (i.e., it's stronger than 4NF and weaker than 5NF).
That said, however, I should add that SKNF and 5NF coincide in the common special case in which the pertinent
relvar
R
has just one key (as indeed is obvious from the definitions)─another reason, perhaps, for thinking SKNF
isn't all that interesting in its right. Nevertheless, the fact remains that there's a logical difference between SKNF
and 5NF, which is why I include it in this chapter.
REDUNDANCY FREE NORMAL FORM
In Chapter 9 I discussed what I there referred to as a surprising fact: namely, the fact that there exist relvars that
can't be nonloss decomposed into two projections but can be nonloss decomposed into more than two. Well, now I
have another surprise for you: namely, that 5NF, though sufficient, isn't actually necessary in order to eliminate the
