Databases Reference
In-Depth Information
PROJECTION VIEWS REVISITED
I've discussed the case in which S and P are base relvars and SCP is a view. To complete my
examination of Example 1, I ought really to discuss the inverse situation also, in which SCP is a
base relvar and S and P are views of that relvar. Of course, S and P would then be projection
views specifically, and such a discussion thus logically belongs in Chapter 5. Rightly or
wrongly, however, I felt Chapter 5 was complicated enough already, and so I decided to defer
that discussion to the present chapter.
Actually I don't think there's all that much to be said. Recall from Chapter 5 that if
DB1
contains just a single relvar
R
and
DB2
contains just projections of
R
, then
DB1
and
DB2
are
information equivalent only if those projections are obtained by decomposing
R
in accordance
with some join dependency (JD) that holds in
R.
Well, the following JD does hold in SCP:
{ { SNO , CITY } , { PNO , CITY } }
As a direct consequence, SCP can be nonloss decomposed into its projections on
{SNO,CITY} and {PNO,CITY}—in other words, into relvars S and P—and those relvars are
necessarily updatable (just so long as SCP itself is updatable in the first place, which of course it
is). The various compensatory actions are as previously discussed:
ON INSERT
i
INTO SCP :
INSERT
i
{ SNO , CITY } INTO S ,
INSERT
i
{ PNO , CITY } INTO P ;
ON DELETE
d
FROM SCP :
DELETE ( ( S MATCHING
d
) NOT MATCHING SCP ) FROM S ,
DELETE ( ( P MATCHING
d
) NOT MATCHING SCP ) FROM P ;
ON INSERT
is
INTO S , INSERT
ip
INTO P :
INSERT ( P JOIN
is
) INTO SCP ,
INSERT ( S JOIN
ip
) INTO SCP ;
ON DELETE
ds
FROM S , DELETE
dp
FROM P :
DELETE ( SCP JOIN
ds
) FROM SCP ,
DELETE ( SCP JOIN
dp
) FROM SCP ;
Let me now add that (of course) the relationship between views S and P is many to many.
And although I didn't discuss this specific example in Chapter 5, I did in fact discuss another
example in that chapter involving projection views in a many to many relationship—namely,
Example 3, which involved views ST (the projection of S on SNO and STATUS) and TC (the
projection of S on STATUS and CITY). To spell the point out, the relationship in that example
was indeed many to many, because the same STATUS value could be associated with many
suppliers and many cities (STATUS value 30 is a case in point, given our usual sample values).
However, that example involved a lossy decomposition, not a nonloss one. As a consequence,
updates on the projection relvars didn't always work terribly well.
