Databases Reference
In-Depth Information
NLS XLP
┌─────┬───────┬────────┬────────┐ ┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │ │ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤ ├═════┼───────┼────────┼────────┤
│ S2 │ Jones │ 10 │ Paris │ │ S5 │ Adams │ 30 │ Athens │
│ 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. 9.1: Relvars NLS, NPS, and XLP—sample values
What constraints hold in this example? Well, first, each of the three relvars has {SNO} as
its sole key, as already indicated. Second, {SNO} in XLP is a foreign key, referencing both NLS
and NPS. Third, we also clearly have:
CONSTRAINT ... XLP = NLS INTERSECT NPS ;
CONSTRAINT ... IS_EMPTY ( NLS WHERE CITY = 'London' ) ;
CONSTRAINT ... IS_EMPTY ( NPS WHERE CITY = 'Paris' ) ;
CONSTRAINT ... ( NLS WHERE CITY
≠
'Paris' ) =
( NPS WHERE CITY
≠
'London' ) ;
Note the last of these in particular, which implies among other things that certain updates
on each of NLS and NPS must cascade appropriately to the other:
ON INSERT
i
INTO NLS : INSERT (
i
WHERE CITY
≠
'Paris' ) INTO NPS ;
ON INSERT
i
INTO NPS : INSERT (
i
WHERE CITY
≠
'London' ) INTO NLS ;
ON DELETE
d
FROM NLS : DELETE (
d
WHERE CITY
≠
'Paris' ) FROM NPS ;
ON DELETE
d
FROM NPS : DELETE (
d
WHERE CITY
≠
'London' ) FROM NLS ;
Points arising:
First of all, note that the rules just shown make no mention of relvar XLP—in fact, they
would be needed even if relvar XLP didn't exist.
In the delete rules, the restriction conditions CITY ≠ 'Paris' and CITY ≠ 'London' could be
dropped (or, more precisely, replaced by just TRUE) without significant loss. I've
included them for reasons of explicitness, also for symmetry with the insert rules.
As was previously mentioned in Chapter 4, the last of the foregoing constraints—the one
that implies the need for the cascade rules—also implies that relvars NLS and NPS together
