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.