Databases Reference
In-Depth Information
( s , p , j′ ) SPJ
AND ( s , p′ , j ) SPJ
AND ( s′ , p , j ) SPJ
Aside: I apologize for the tiny lack of symmetry in the foregoing, but it's unavoidable if we're to represent
tuples, which are unordered by definition, by ordered commalists of symbols on the page. End of aside.
But by the business rule,
IF ( s , p , j′ ) SPJ
AND ( s , p′ , j ) SPJ
AND ( s′ , p , j ) SPJ
then we necessarily have:
( s , p , j ) SPJ
So if ( s , p , j ) appears in the join of SP, PJ, and JS, it also appears in SPJ. But the converse is obviously true as
well─i.e., if ( s , p , j ) appears in SPJ, it certainly appears in the join of SP, PJ, and JS.
Thus ( s , p , j ) appears in SPJ if and only if it appears in the join of SP, PJ, and JS. It follows that every legal value of
relvar SPJ is equal to the join of its projections on {SNO,PNO}, {PNO,JNO}, and {JNO,SNO}, and hence that the
{ { SNO , PNO } , { PNO , JNO } , { JNO , SNO } }
certainly holds in relvar SPJ.
Observe now that the foregoing JD is ternary─it has three components. What's more, it isn't implied by
FDs. 9 Hence it certainly isn't implied by keys (recall from Chapter 5 that a key constraint is just a special case of an
FD). As a consequence, relvar SPJ, although it's in BCNF (because it's “all key”), isn't in 5NF.
In order to understand this state of affairs a little better, it's helpful to go back to the sample SPJ value shown
in Fig. 9.1. Fig. 9.2 shows (a) values of the projections SP, PJ, and JS corresponding to that sample value, (b) the
effect of joining the SP and PJ projections (on {PNO}), and (c) the effect of joining that result and the JS projection
(on {JNO,SNO}). As you can see, joining the first two projections produces a copy of the original SPJ relation plus
one additional (“spurious”) tuple; joining in the other projection then eliminates that additional tuple, thereby getting
us back to the original SPJ relation. Moreover, the net effect is the same whatever pair of projections we choose for
the first join, though the intermediate result is different in each case. Exercise: Check this claim.
To repeat, therefore, the JD {SP,PJ,JS}─if now you'll let me use the names SP, PJ, and SJ to refer not
to the projections as such but to the corresponding subsets of the heading─holds in relvar SPJ; in other words, that
JD captures the essence (as it were) of the original business rule. As a consequence, relvar SPJ can be nonloss
decomposed accordingly. What's more, it probably should be, because it suffers from redundancy; to be specific, in
terms of the sample value of Fig. 9.1, the proposition that supplier S1 supplies part P1 to project J1 is represented
9 Proof: The only FDs that hold in relvar SPJ are trivial ones, and it's certainly not the case that every relation satisfying those trivial FDs also
satisfies the JD. For example, the relation containing the first three but not the fourth of the tuples as shown in Fig. 9.1 doesn't.
Search WWH ::

Custom Search