Databases Reference
In-Depth Information
Theorem:
5NF implies SKNF; SKNF implies RFNF; and RFNF implies 4NF. The reverse implications do
not
hold.
To recap, then: RFNF is strictly weaker than 5NF, though it does just as much as 5NF to eliminate redundancy.
Here now are two more theorems that provide simple, useful, and practical tests:
Theorem:
Let
R
be a 3NF relvar and let
R
have no composite key; then
R
is in RFNF. (Recall that a
composite key is one consisting of two or more attributes.)
Theorem:
Let
R
be a BCNF relvar and let
R
have a noncomposite key; then
R
is in RFNF.
Each of these theorems provides a sufficient condition, though not a necessary one, for a relvar to be in
RFNF. Observe that the conditions in question have the attractive property that they refer to FDs only, not JDs.
Note:
As a matter of fact the first of these theorems should come as no surprise, because we already know from
Chapter 10 (section “A Useful Theorem”) that a 3NF relvar with no composite keys is in 5NF. A fortiori, therefore,
such a relvar is also in RFNF. As for the second theorem, it should be clear that if
R
is in BCNF and has a
noncomposite key
K
, then
K
must necessarily be included in at least one component of every JD that holds in
R
,
whence the stated result follows immediately.
This brings us to the end of what might be called the formal part of the RFNF discussion. However, I want
to take a closer look at the motivating example (relvar SPJ′), because there's more that can usefully be said about
that example. Recall that the FD {SNO,PNO} → {JNO} and the JD
{{SNO,PNO},{PNO,JNO},{JNO,SNO}}
both hold in that relvar. But what do these facts mean from an intuitive point of view? Well, suppose the relvar
contains these three tuples:
t1 = s1 p1 j2
t2 = s1 p2 j1
t3 = s2 p1 j1
Suppose also that
s1
≠
s2
,
p1
≠
p2
, and
j1
≠
j2
. Because of the JD, then, the following tuple must also appear:
t4 = s1 p1 j1
But {SNO,PNO} is a key; so tuples
t1
and
t4
must be one and the same and
j1
must be equal to
j2
, contradicting our
original assumption. Thus, if the relvar were to contain just tuples
t1
and
t2
, an attempt to insert tuple
t3
must fail,
precisely because it leads to that contradiction. Thus we see the following (somewhat bizarre) business rule must be
in effect:
If (a) supplier
s1
supplies part
p1
to project
j2
and (b) supplier
s1
also supplies part
p2
to project
j1
(
p1
≠
p2
,
j1
≠
j2
), then (c) no supplier,
not even
s1
, can supply part
p1
to project
j1
.
14
What's more, it should be clear that the following equally bizarre rules must be in effect as well (note the
symmetry):
14
By the same token, no supplier, not even
s1
, can supply part
p2
to project
j2,
either
. Note:
A similar remark applies also to the “equally
bizarre” rules to be discussed in just a moment, of course.
