Databases Reference
In-Depth Information
DLP { SNO , SNAME , STATUS , CITY } KEY { SNO }
The predicate for this relvar is:
DLP:
Supplier SNO is under contract, is named SNAME, has status STATUS, and is
located in city CITY (and CITY is Paris).
6
Sample values are shown in Fig. 11.4.
NLS DLP
┌─────┬───────┬────────┬────────┐ ┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │ │ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤ ├═════┼───────┼────────┼────────┤
│ S2 │ Jones │ 10 │ Paris │ │ S2 │ Jones │ 10 │ Paris │
│ S3 │ Blake │ 30 │ Paris │ │ S3 │ Blake │ 30 │ Paris │
│ S5 │ Adams │ 30 │ Athens │ └─────┴───────┴────────┴────────┘
└─────┴───────┴────────┴────────┘
NPS
┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤
│ S1 │ Smith │ 20 │ London │
│ S4 │ Clark │ 20 │ London │
│ S5 │ Adams │ 30 │ Athens │
└─────┴───────┴────────┴────────┘
Fig. 11.4: Relvars NLS, NPS, and DLP—sample values
The following constraints clearly apply:
CONSTRAINT ... DLP = NLS MINUS NPS ;
CONSTRAINT ... IS_EMPTY ( NLS WHERE CITY = 'London' ) ;
CONSTRAINT ... IS_EMPTY ( NPS WHERE CITY = 'Paris' ) ;
CONSTRAINT ... IS_EMPTY ( DLP WHERE CITY
≠
'Paris' ) ;
CONSTRAINT ... ( NLS WHERE CITY
≠
'Paris' ) =
( NPS WHERE CITY
≠
'London' ) ;
Also, each of the three relvars has {SNO} as its sole key, as already indicated, and {SNO}
in DLP is a foreign key referencing NLS.
Note:
As usual, we additionally need a constraint to
the effect that {SNO} is a key for the union of NLS and NPS, in order to guarantee that if some
supplier is represented in both of those relvars, then the two tuples representing that supplier are
in fact one and the same:
6
Strictly speaking, the portion of this predicate in parentheses should read “and CITY is not London and is not (not Paris).” But
it's easy to see that this latter simplifies to just “and CITY is Paris.” It follows that the update behavior of DLP and NPS with
respect to each other should be identical to that of LS and NLS with respect to each other (see Chapter 4), except of course for the
fact that references to London must all be replaced by references to Paris.
Note:
By contrast, the predicate for NPS MINUS NLS
is
Supplier SNO is under contract, is named SNAME, has status STATUS, and is located in city CITY (and CITY is London).
