Databases Reference
In-Depth Information
( S9 , Madrid , P8 )
From these examples and others like them, I hope it's clear that the following is an
appropriate rule for inserts on S and/or P:
ON INSERT
is
INTO S , INSERT
ip
INTO P :
INSERT ( P JOIN
is
) INTO SCP ,
INSERT ( S JOIN
ip
) INTO SCP ;
Turning now to deletes: Suppose just for the moment that we reinstate the tuples for
supplier S5 and part P3, where the city for S5 is Athens as usual, but we make the city for P3
Athens as well instead of Oslo. Thus, relvar SCP additionally contains the tuple
(S5,Athens,P3)—but note that this tuple is the only one for Athens in that relvar. Now the
following double DELETE clearly makes sense:
DELETE ( S5 , Athens ) FROM S ,
DELETE ( P3 , Athens ) FROM P ;
The effect is to delete the specified tuples from S and P and—assuming an appropriate cascade
delete rule is in effect—also the tuple (S5,Athens,P3) from relvar SCP. But what about this
single DELETE?—
DELETE ( S5 , Athens ) FROM S ;
Now, what can't be allowed to happen here is for the tuple (S5,Athens) to be deleted from
relvar S, exactly as requested, while everything else remains unchanged. So one possibility
would be simply to reject the specified DELETE on a violation of
The Golden Rule
. On the
grounds once again that it's surely desirable in general for user requests to succeed if they
sensibly can, however, it seems preferable to accept the DELETE and let it cascade
appropriately. In other words, I propose the following as a suitable rule for deletes on relvars S
and/or P:
ON DELETE
ds
FROM S , DELETE
dp
FROM P :
DELETE ( SCP JOIN
ds
) FROM SCP ,
DELETE ( SCP JOIN
dp
) FROM SCP ;
Given the foregoing insert and delete rules, I'll leave it as an exercise to show that explicit
UPDATEs on S and/or P all work as expected, too.
I turn now to updates on the join SCP. The insert rule is fairly obvious:
ON INSERT
i
INTO SCP :
INSERT
i
{ SNO , CITY } INTO S ,
INSERT
i
{ PNO , CITY } INTO P ;
