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

JD

{ { 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.