Databases Reference
In-Depth Information
The only component of this JD that's a superkey is {SNO,PNO}; the union of all superkey components is certainly
not equal to the heading, therefore, and the relvar is thus not in Vincent's RFNF.
So SPJ′ is an example of a relvar that's in our RFNF and not in Vincent's. But can we find an example of a
relvar that's in Vincent's RFNF and not in SKNF? Note that such relvars must exist, given that Vincent's RFNF lies
strictly between our RFNF and SKNF (that is, SKNF implies Vincent's RFNF and Vincent's RFNF implies our
RFNF). Well, we can easily construct an example of such a relvar by taking relvar SPJ′ with its two dependencies
as discussed previously─
{ SNO , PNO }
→
{ JNO }
{ { SNO , PNO } , { PNO , JNO } , { JNO , SNO } }
─and adding another:
{ PNO , JNO }
→
{ SNO }
This additional dependency (which implies, of course, that {PNO,JNO} is another key) corresponds to an additional
business rule:
Any given part
p
is supplied to a given project
j
by at most one supplier
s
.
Observe now that this revised version of relvar SPJ′ satisfies the conditions of Vincent's Theorem: To be
specific, the superkey components of the JD
{{SNO,PNO},{PNO,JNO},{JNO,SNO}} are {SNO,PNO} and
{PNO,JNO}; their union is equal to the entire heading; and so the relvar is in Vincent's RFNF.
5
At the same time, it
isn't in SKNF, because that same JD contains a component, {JNO,SNO}, that isn't a superkey. So here we have an
example of a relvar that's in Vincent's RFNF and not in SKNF.
So now we've seen examples of relvars that are (a) in Vincent's RFNF and not in SKNF and (b) in our RFNF
and not in Vincent's. But the “syntactic” differences between these various normal forms can be a little hard to
remember, and it might be helpful to summarize them here:
Relvar
R
is in our RFNF if and only if it's in BCNF and, for every JD
J
that holds in
R
, some component of
J
is a superkey for
R
.
Relvar
R
is in Vincent's RFNF if and only if it's in BCNF and, for every JD
J
that holds in
R
, the union of
those components of
J
that are superkeys for
R
is equal to the heading of
R.
Relvar
R
is in SKNF if and only if, for every irreducible JD
J
that holds in
R
, every component of
J
is a
superkey for
R
.
Now let me remind you of the following definition of redundancy from Chapter 15:
Definition:
Let
D
be a database design; let
DB
be a database value (i.e., a set of values for the relvars
mentioned in
D
) that conforms to
D
; and let
p
be a proposition not involving any existential quantification. If
DB
contains two or more distinct representations of
p
, then
DB
contains, and
D
permits,
redundancy
.
5
I'm relying here on the fact that this particular JD is the only nontrivial one to hold.
