Databases Reference
In-Depth Information
These constraints are all equality dependencies (EQDs), and together they guarantee the
information equivalence referred to above. In fact, we can regard the combination of S and SP
as a nonloss decomposition of SSP, because the following functional dependency (FD)—
{ SNO }
→
{ CITY }
—holds in that latter relvar (i.e., SSP).
1
It follows that the example overall is essentially similar
to Example 2 in Chapter 5. For that reason, I won't go through a detailed analysis in order to
come up with the pertinent compensatory actions. Instead, I'll simply present them here as a
fait
accompli
, as it were:
ON INSERT
is
INTO S , INSERT
isp
INTO SP :
INSERT (
is
JOIN SP ) INTO SSP ,
INSERT ( S JOIN
isp
) INTO SSP ;
ON DELETE
ds
FROM S , DELETE
dsp
FROM SP :
DELETE ( SSP MATCHING
ds
) FROM SSP ,
DELETE ( SSP MATCHING
dsp
) FROM SSP ;
ON INSERT
i
INTO SSP :
INSERT
i
{ SNO , PNO , QTY } INTO SP ,
INSERT
i
{ SNO , CITY } INTO S ;
ON DELETE
d
FROM SSP :
DELETE ( SP MATCHING
d
) FROM SP ,
DELETE ( ( S MATCHING
d
) NOT MATCHING SSP ) FROM S ;
Let's consider some examples, using the sample values from Fig. 8.1.
Note:
Given that
our ultimate objective is to study the case in which relvars S and SP are base ones and relvar SSP
is a view, I'll concentrate first on updates on relvar SSP specifically.
1.
Suppose we delete the tuple (S1,London,P1,300) from relvar SSP. The effect will be to
delete the tuple (S1,P1,300) from relvar SP 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 the tuple (S3,Paris,P2,400) from relvar SSP. The effect will be to delete
the tuple (S3,P2,400) from relvar SP and (because relvar SSP has no tuples for supplier S3
remaining after the original delete) also to delete the tuple (S3,Paris) from relvar S.
Note:
I remind you that we're assuming for the sake of the example that every supplier does
supply at least one part.
1
I'm appealing here to Heath's Theorem, which was mentioned a couple of times in Chapter 5. For further explanation, see
Database Design and Relational Theory.
