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.
Search WWH ::

Custom Search