Databases Reference
In-Depth Information
Suppose also for the sake of the example that every supplier city is required to be a part city and
vice versa—in other words, there's a constraint in effect (actually an equality dependency once
again) that looks like this:
CONSTRAINT ... S { CITY } = P { CITY } ;
In order to conform to this new requirement, let's agree until further notice to drop the tuple for
supplier S5 (city Athens) from our usual suppliers relation and the tuple for part P3 (city Oslo)
from our usual parts relation.
Now let's define the join of relvars S and P as a view SCP:
SCP { SNO , CITY , PNO } KEY { SNO , PNO }
Observe that the join here is indeed many to many, in the sense that (in general) every tuple in S
joins to many tuples in P and vice versa. Nevertheless, we do have information equivalence,
precisely because every tuple in either relvar does join to at least one tuple in the other. Sample
values are shown in Fig. 7.1.
S P SCP
┌─────┬────────┐ ┌─────┬────────┐ ┌─────┬────────┬─────┐
│ SNO │ CITY │ │ PNO │ CITY │ │ SNO │ CITY │ PNO │
├═════┼────────┤ ├═════┼────────┤ ├═════┼────────┼═════┤
│ S1 │ London │ │ P1 │ London │ │ S1 │ London │ P1 │
│ S2 │ Paris │ │ P2 │ Paris │ │ S1 │ London │ P4 │
│ S3 │ Paris │ │ P4 │ London │ │ S1 │ London │ P6 │
│ S4 │ London │ │ P5 │ Paris │ │ S2 │ Paris │ P2 │
└─────┴────────┘ │ P6 │ London │ │ S2 │ Paris │ P5 │
└─────┴────────┘ │ S3 │ Paris │ P2 │
│ S3 │ Paris │ P5 │
│ S4 │ London │ P1 │
│ S4 │ London │ P4 │
│ S4 │ London │ P6 │
└─────┴────────┴─────┘
Fig. 7.1: Relvars S, P, and SCP—sample values
Here now are the predicates:
1
S:
Supplier SNO has city CITY.
P:
Part PNO has city CITY.
1
I've simplified these predicates slightly (a) to drop “is under contract” (for suppliers) and “is used in the enterprise” (for parts),
and (b) to say that suppliers and parts both “have” a city (instead of saying suppliers are “located in,” and parts are “stored in,” a
city). These changes are purely cosmetic, of course; I make the first merely for simplicity and the second in order to stress the
parallel nature of the roles being played by suppliers and parts in this example.
