Databases Reference
In-Depth Information
Chapter 7
J o i n V i e w s I I :
M a n y t o M a n y J o i n s
Recent researches at Harvard
And further researches at Yale
Show that joins can all be updated
By removing the spikes from their tail
—Anon.:
Where Bugs Go
Now I want to examine the question of many to many joins (I'm deliberately leaving the one to
many case till last). As in Chapter 6, I think it might help to state right at the outset where our
investigations are going to take us. As you might intuitively expect, the many to many case is
going to turn out to involve certain complications, complications that didn't arise in the one to
one case; nevertheless, I claim we're still going to wind up with essentially the same rules as
before. That is, given a view
V
defined as
A
JOIN
B
—where the join is now a many to many
join specifically—the rules are still going to look like this (in outline):
ON INSERT INTO
V
: INSERT
A
(sub)tuples if they don't already exist,
INSERT
B
(sub)tuples if they don't already exist
ON DELETE FROM
V
: DELETE
A
(sub)tuples if they don't exist elsewhere,
DELETE
B
(sub)tuples if they don't exist elsewhere
EXAMPLE 1: INFORMATION EQUIVALENCE
Consider a simplified version of our usual suppliers and parts relvars in which suppliers have no
attributes except SNO and CITY and parts have no attributes except PNO and CITY, thus:
S { SNO , CITY } KEY { SNO }
P { PNO , CITY } KEY { PNO }
