View Updating and Relational Theory

Chapter 7. Join Views II: Many to Many Joins

Recent researches at Harvard

And further researches at Yale

Show that joins can all be updated

By removing the spikes from their tail

— Anon.:

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 }

Suppose also for the sake of the example that every supplier city is required to be a part city and vice versa—in other words, there’s a constraint in effect ...