Databases Reference
In-Depth Information
PL PK DLK
┌─────┐ ┌─────┐ ┌─────┐
│ PNO │ │ PNO │ │ PNO │
├═════┤ ├═════┤ ├═════┤
│ P1 │ │ P2 │ │ P1 │
│ P2 │ │ P5 │ │ P3 │
│ P3 │ └─────┘ │ P6 │
│ P6 │ └─────┘
└─────┘
Fig. 11.1: Relvars PL, PK, and DLK—sample values
As for constraints, {PNO} is obviously the sole key for each of the three relvars, and of
course DLK is equal to the difference—or, rather, one of the two possible differences—between
the other two:
CONSTRAINT ... DLK = PL MINUS PK ;
Turning now to compensatory actions,
1
here first is the rule for deletes on relvar PL:
ON DELETE
d
FROM PL : DELETE
d
FROM DLK ;
Imagine deleting tuple
t
from PL. Now,
t
will appear in DLK only if it appears in PL and
not in PK. And this state of affairs certainly won't exist after the delete on PL, regardless of
whether it did so before; hence the specified rule.
Next, here's the rule for inserts on relvar PL:
ON INSERT
i
INTO PL : INSERT (
i
MINUS PK ) INTO DLK ;
Imagine inserting tuple
t
into PL. Again,
t
will appear in DLK only if it appears in PL and
not in PK. And this state of affairs will exist after the insert on PL only if
t
didn't appear in PK
before the delete, and still doesn't do so afterward; hence the specified rule.
What about relvar PK? Well, first let me point out that PL and PK play asymmetric roles
in the definition of DLK, and it's therefore only to be expected that the corresponding
compensatory actions will be asymmetric too—and so indeed they turn out to be. Let's think
about inserts first. Again imagine inserting tuple
t
(into PK this time). Now, either
t
already
existed in PK or it did not. If it did,
2
then it certainly didn't appear in DLK before the insert, and
it'll continue not to do so afterward. Conversely, if it didn't already exist in PK, then after the
insert it certainly won't appear in DLK, even if it did so before. All of which leads to the
following rule:
1
I suggest using Venn diagrams to help in following the ensuing discussion.
2
Actually we could ignore this possibility, since we saw in Chapter 4 that no rule ever requests insertion of a tuple that's already
present, even if its formulation in concrete syntax appears to do so.
