Databases Reference
In-Depth Information
The predicate for this relvar is:
ULN:
Supplier SNO is under contract, is named SNAME, has status STATUS, and is
located in city CITY (and CITY is either London or not London).
(Of course, if LS and NLS are indeed views of relvar S, then ULN is identical to that relvar S.)
Sample values are shown in Fig. 10.1 below.
LS ULN
┌─────┬───────┬────────┬────────┐ ┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │ │ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤ ├═════┼───────┼────────┼────────┤
│ S1 │ Smith │ 20 │ London │ │ S1 │ Smith │ 20 │ London │
│ S4 │ Clark │ 20 │ London │ │ S2 │ Jones │ 10 │ Paris │
└─────┴───────┴────────┴────────┘ │ S3 │ Blake │ 30 │ Paris │
NLS │ S4 │ Clark │ 20 │ London │
┌─────┬───────┬────────┬────────┐ │ S5 │ Adams │ 30 │ Athens │
│ SNO │ SNAME │ STATUS │ CITY │ └─────┴───────┴────────┴────────┘
├═════┼───────┼────────┼────────┤
│ S2 │ Jones │ 10 │ Paris │
│ S3 │ Blake │ 30 │ Paris │
│ S5 │ Adams │ 30 │ Athens │
└─────┴───────┴────────┴────────┘
Fig. 10.1: Relvars LS, NLS, and ULN—sample values
The following constraints, edited versions of the ones from the discussion of the
corresponding example in Chapter 4, clearly apply:
CONSTRAINT ... ULN = LS UNION NLS ;
CONSTRAINT ... DISJOINT { LS { SNO } , NLS { SNO } } ;
CONSTRAINT ... IS_EMPTY ( LS WHERE CITY
≠
'London' ) ;
CONSTRAINT ...
IS_EMPTY ( NLS WHERE CITY = 'London' ) ;
CONSTRAINT ... LS = ( ULN WHERE CITY = 'London' ) ;
CONSTRAINT
... NLS = ( ULN WHERE CITY
≠
'London' ) ;
Also, each of the three relvars has {SNO} as its sole key, as already indicated, and {SNO}
is a foreign key, referencing ULN, in each of LS and NLS. Note that (in contrast to the
intersection examples discussed in the previous chapter) here we do have information
equivalence—the design consisting of ULN by itself is information equivalent to the design
consisting of LS and NLS taken in combination.
The following compensatory actions are also taken, lightly edited, from Chapter 4:
ON DELETE
d
FROM LS : DELETE
d
FROM ULN ;
ON DELETE
d
FROM NLS : DELETE
d
FROM ULN ;
ON INSERT
i
INTO LS : INSERT
i
INTO ULN ;
ON INSERT
i
INTO NLS : INSERT
i
INTO ULN ;
