Databases Reference
In-Depth Information
Back to Example 2. Let me now stress the fact that, precisely because the join view S loses
information, a database containing just that view isn't information equivalent to one containing
base relvars ST and SC. (An example of a query on the latter that has no counterpart on the
former is “Get supplier numbers for suppliers who have a city but no status.”) More precisely,
any information that can be represented by S alone can certainly be represented by the
combination of ST and SC, but the converse is false. As a consequence, it's obvious that there'll
be updates on ST and/or SC that have no counterpart on S. An example is “Insert a tuple into SC
for a supplier with supplier number S9 and city London,” without simultaneously inserting a
tuple for that same supplier S9 into ST.
So what compensatory actions apply? Well, since there's no longer a strict one to one
relationship between relvars ST and SC, it's now possible as just indicated to insert into, or
delete from, just one of the two without simultaneously inserting into or deleting from the other.
Thus, since S is required to be at all times equal to the join of ST and SC, we clearly have:
ON INSERT
it
INTO ST , INSERT
ic
INTO SC :
INSERT (
it
JOIN SC ) INTO S ,
INSERT (
ic
JOIN ST ) INTO S ;
ON DELETE
dt
FROM ST , DELETE
dc
FROM SC :
DELETE ( S MATCHING
dt
) FROM S ,
DELETE ( S MATCHING
dc
) FROM S ;
Now, I've shown these rules separately for reasons of clarity, but of course they can be
combined into one:
ON DELETE
dt
FROM ST , DELETE
dc
FROM SC ,
INSERT
it
INTO ST , INSERT
ic
INTO SC :
DELETE ( S MATCHING
dt
) FROM S ,
DELETE ( S MATCHING
dc
) FROM S ,
INSERT (
it
JOIN SC ) INTO S ,
INSERT (
ic
JOIN ST ) INTO S ;
And if you compare this rule with the corresponding rule for Example 1 from the previous
section—the strict one to one case—you'll see it's essentially identical (except that I used some
introduced names in that previous section, for clarity). Here in order to make it easier to do the
comparison is that previous rule:
ON DELETE
dt
FROM ST , DELETE
dc
FROM SC ,
INSERT
it
INTO ST , INSERT
ic
INTO SC :
WITH (
t1
:=
it
JOIN SC ,
t2
:=
ic
JOIN ST ,
t3
:= S MATCHING
dt
,
t4
:= S MATCHING
dc
) :
INSERT
t1
INTO S ,
INSERT
t2
INTO S ,
DELETE
t3
FROM S ,
DELETE
t4
FROM S ;
