Databases Reference
In-Depth Information
Aside:
Since relvar SP isn't actually defined in terms of relvar S—in particular, SP isn't a
view of S—there's no question of the DBMS determining, from the relvar definitions
alone, just what rule(s) if any should apply in this situation. Rather, the DBA, or some
suitably authorized user, will have to specify those rules explicitly, somehow.
End of
aside.
Now consider the following UPDATE operation on relvar S:
UPDATE S WHERE SNO = 'S1' : { STATUS := 10 } ;
Assume for simplicity that we know the status for supplier S1 isn't already 10. What happens,
then, is this:
1. The supplier tuple for supplier S1 is deleted from relvar S and a new tuple for that supplier,
with STATUS value 10, is inserted into that same relvar.
2. The shipment tuples for supplier S1 are deleted from relvar SP as well, thanks to the
cascade delete rule from S to SP. However, since there's no cascade insert rule from S to
SP, no shipment tuples are inserted into relvar SP corresponding to the new tuple in S for
supplier S1.
Net effect:
The shipment tuples for supplier S1 are lost!
Note:
It's important to understand in this example that not only is there no cascade insert rule
from S to SP, but there
can't
be—because no such rule makes sense (right?).
But the foregoing state of affairs is surely unacceptable; I mean, we must surely be able to
do things like changing the status of a supplier without losing all of that supplier's shipments.
Clearly, therefore, we need to do something about UPDATEs like the one in the example. But
what?
The key to this problem, I think, lies in the recognition that, in general, updates involve
both
a delete set and an insert set, and hence that, in general, compensatory actions need to apply
not just to delete or insert operations taken separately, but rather to such operations taken in
combination. Here for example is an appropriate combined rule for the case of suppliers and
shipments in particular (note the introduced names
t1
and
t2
):
ON DELETE
d
FROM S , INSERT
i
INTO S :
WITH (
t1
:= SP MATCHING
d
,
t2
:= SP MATCHING
i
) :
DELETE
t1
FROM SP , INSERT
t2
INTO SP ;
