Databases Reference

In-Depth Information

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

In slightly more concrete terms, this business rule says that if (for example) all three of the following are true

propositions─

a.

Smith supplies monkey wrenches to some project.

b.

Somebody supplies monkey wrenches to the Manhattan project.

c.

Smith supplies something to the Manhattan project.

─then the following is a true proposition as well:

d.

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.
8
Note that this requirement is met in Fig. 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 d. To elaborate, if we know only that

propositions a., b., and c. are true, then we know that Smith supplies monkey wrenches to some project
j
; we know

that some supplier
s
supplies monkey wrenches to the Manhattan project; and we know that Smith supplies some

part
p
to the Manhattan project─but we can't validly infer that
s
is Smith, we can't validly infer that
p
is monkey

wrenches, and we can't validly infer that
j
is the Manhattan project. False inferences such as these are examples of

what's sometimes called
the connection trap
. In the case at hand, however, the business rule tells us
there is no

trap
; that is, we
can
validly infer proposition d. from propositions a., b., and c. in this particular case.

Now let's consider the example more carefully. Let me use SP, PJ, and JS, just for the moment, to denote the

projections of SPJ on {SNO,PNO}, {PNO,JNO}, and {JNO,SNO}, respectively. Then we have the following:

By the definitions of projection and join,

IF (
s
,
p
,
j
)
∈
JOIN { SP , PJ , JS }

THEN (
s
,
p
)
∈
SP

AND (
p
,
j
)
∈
PJ

AND (
j
,
s
)
∈
JS

and therefore there exist
s′
,
p′
, and
j′
such that

7
It could be argued that, strictly speaking, “
supplier s supplies part p
” here should really be “
supplier s supplies part p
to some project
” (and

similarly for “
part p is supplied to project j
” and “
project j is supplied by supplier s
”). Whether such is in fact the case depends on the full

semantics of the situation, which I've deliberately left a little underspecified in the interest of intuitive simplicity. I'll come back to this issue in

Chapter 15.

8
I'm being a little sloppy once again. For example, consider proposition a. (“Smith supplies monkey wrenches to some project”). If “some

project” here means “some
unspecified
project”─i.e., there exists such a project, but we don't know what it is─then the proposition isn't an

instantiation of the predicate for SPJ, and no SPJ tuple can possibly represent it. But an SPJ tuple certainly can represent the proposition “Smith

supplies monkey wrenches to some
specific
project”; what's more, the proposition so represented then implies the proposition “Smith supplies

monkey wrenches to
some
project” (i.e., “there exists a project
j
such that Smith supplies monkey wrenches to
j
”). I hope that's clear!