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 Figure 9-1. Note that the relvar is “all key” and therefore certainly in BCNF.
Figure 9-1. Relvar SPJ—sample value
Now suppose the following business rule is in effect:
If (a) supplier s supplies part p and (b) part p is supplied to project j and (c) project j is supplied by supplier s, then (d) supplier s supplies part p to project j.
In slightly more concrete terms, this business rule says that if (for example) all three of the following are true propositions—
Smith supplies monkey wrenches to some project.
Somebody supplies monkey wrenches to the Manhattan project.
Smith supplies something to the Manhattan project.
—then the following is a true proposition as well:
Smith supplies monkey wrenches to the Manhattan project.
In other words, if relvar SPJ contains tuples representing propositions a., b., and c., it must also contain a tuple representing proposition d. Note that this requirement is met in Figure 9-1 (take S1 to be Smith, P1 to be monkey wrenches, and J1 to be the Manhattan project).
Now, propositions a., b., and c. would normally not imply proposition ...