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.