View Updating and Relational Theory

Chapter 8. Join Views III: One to Many Joins

What Codd hath joined

Update such without blunder

— Anon.:

Chapters Chapter 6 and Chapter 7 discussed one to one and many to many joins, respectively; this chapter is concerned with the sole remaining case, one to many joins. But of course you know by now where our investigations are going to take us—we’re going to wind up with the same general rules as we did in the previous two chapters:

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

Once again I’ll start with our usual suppliers-and-parts database, but I want to focus in this chapter on relvars S and SP and ignore relvar P (I’ll also continue to ignore attribute SNAME and, for simplicity, attribute STATUS as well). So we have two base relvars looking like this:

S  { SNO , CITY } KEY { SNO }
SP { SNO , PNO , QTY } KEY { SNO , PNO }

Moreover, suppose for the sake of this first example that every supplier has to supply at least one part. In other words, suppose there’s a constraint in effect (actually an equality dependency once again) that looks like this:

CONSTRAINT ... S { SNO } = SP { SNO } ;

In order to conform to this requirement, let’s also agree for the sake of the example to drop the tuple for supplier S5 from our usual suppliers relation.

Now let’s define the join ...