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!
