Join Views III: One to Many Joins - View Updating and Relational Theory - page 134

3. Suppose we insert the tuple (S2,Paris,P3,500) into relvar SSP. The effect will be to insert
the tuple (S2,P3,500) into relvar SP and to leave relvar S unchanged.
4. Suppose we insert the tuple (S5,Athens,P3,500) into relvar SSP. The effect will be to insert
the tuple (S5,P3,500) into relvar SP and also to insert the tuple (S5,Athens) into relvar S.
Without going into details, I claim also that explicit UPDATEs on SSP (a) to change the
supplier number, part number, or quantity for some shipment, or (b) to change the city for some
supplier, or even (c) to change the supplier number for some supplier, all work as intuitively
expected.
Turning now to updates on relvars S and SP:
1. Suppose we delete the tuple (S1,P1,300) from relvar SP. The effect will be to delete the
tuple (S1,London,P1,300) from relvar SSP and (because relvar SSP still has some tuples
for supplier S1 remaining after the original delete) to leave relvar S unchanged.
2. Suppose we delete all tuples for supplier S1 from relvar SP. The effect will be to delete all
tuples for supplier S1 from relvar SSP and also to delete the tuple (S1,20) from relvar S.
3. Suppose we insert the tuple (S2,P3,500) into relvar SP. The effect will be to insert the
tuple (S2,Paris,P3,500) into relvar SSP.
4. Suppose we attempt to insert the tuple (S5,P3,500) into relvar SP. Absent an appropriate
simultaneous insert into relvar S, this insert will fail.
5. Suppose we attempt to insert the tuple (S5,Athens) into relvar S. Absent an appropriate
simultaneous insert into relvar SP, this insert will fail.
6. Suppose we delete the tuple (S1,20) from relvar S. The effect will be to delete all tuples
for supplier S1 from relvar SSP. Of course, the rule governing deletes on SSP will now
come into play, which will (not unreasonably) have the effect of deleting all tuples for
supplier S1 from relvar SP as well.
As for explicit UPDATEs, I'll leave it as an exercise to show that (a) as certain discussions
in Chapter 4 should have led us to expect, if we want to change the supplier number of some
supplier, then there's no real alternative to writing out the necessary double UPDATE explicitly,
as in this example—
UPDATE S WHERE SNO = 'S1' : { SNO := 'S9' } ,
UPDATE SP WHERE SNO = 'S1' : { SNO := 'S9' } ;
—but (b) other explicit UPDATEs all work satisfactorily.