Databases Reference
In-Depth Information
distinction might help to illustrate the point (at least intuitively) that JDs and FDs really are different in kind, in a
sense.
Now, the JD in the foregoing example─
{ { SNO , SNAME , CITY } , { CITY , STATUS } }
─is binary: It has two components, and it corresponds to a nonloss decomposition into two projections. Here by
contrast is another JD that holds in relvar S:
{ { SNO , SNAME } , { SNO , CITY } , { CITY , STATUS } }
This one is ternary, but it's derived, in effect, by “cascading” two binary ones:
First, we already know the binary JD
{{SNO,SNAME,CITY},{CITY,STATUS} holds in S.
But the FD {SNO} → {SNAME} holds in the projection of S on {SNO,SNAME,CITY} (corresponding to
one of the components of that binary JD),
5
and so the binary JD
{{SNO,SNAME},{SNO,CITY}}, holds in
that projection.
It follows that the given ternary JD holds in the original relvar. By contrast, in the section immediately
following, I'll give an example of a ternary JD that's not derived by cascading binary ones, and hence an example of
a relvar that can be nonloss decomposed into three projections and not into two.
A RELVAR IN BCNF AND NOT 5NF
I'll start with a revised version─I'll call it SPJ─of our usual shipments relvar SP. The revisions consist of
(a) dropping attribute QTY and (b) introducing a new attribute JNO (“project number”). The predicate is
Supplier
SNO supplies part PNO to project JNO
, and a sample value is shown in Fig. 9.1. Note that the relvar is “all key”
and therefore certainly in BCNF.
6
┌─────┬─────┬─────┐
SPJ │ SNO │ PNO │ JNO │
├═════┼═════┼═════┤
│ S1 │ P1 │ J2 │
│ S1 │ P2 │ J1 │
│ S2 │ P1 │ J1 │
│ S1 │ P1 │ J1 │
└─────┴─────┴─────┘
Fig.9.1: Relvar SPJ—sample value
Now suppose the following business rule is in effect:
5
I'm appealing here to an easily proved theorem: viz., that the FD
X
→
Y
holds in some projection of relvar
R
if and only if it holds in
R
itself
(see Exercise 12.5 in Chapter 12).
6
As a matter of fact it's in 4NF, too; however, it's not in what Chapter 13 calls
redundancy free normal form
, RFNF, and thus not in 5NF either.
