Let’s agree for simplicity to drop attribute SNAME from the suppliers relvar, so that throughout this chapter the name “S” refers to the following reduced form of that relvar:

S { SNO , STATUS , CITY } KEY { SNO }

(As you can see, I haven’t just dropped attribute SNAME, I’ve also abbreviated the relvar definition considerably. In particular, I’ve omitted the attribute type specifications, again in the interest of simplicity.)

Now let relvars ST and SC be defined as projections of this relvar. To be specific, let ST be the projection on SNO and STATUS and let SC be the projection on SNO and CITY:

ST { SNO , STATUS } KEY { SNO }
SC { SNO , CITY } KEY { SNO }

Observe that these two projections taken together correspond to what in normalization terms we’d call a nonloss decomposition of relvar S—at all times, (a) the current values of relvars ST and SC are equal to the pertinent projections of the current value of relvar S and (b) the current value of relvar S is equal to the join of the current values of relvars ST and SC. As a direct consequence, the two designs, S by itself and the combination of ST and SC, are information equivalent.^[68] Note that {SNO} is a key for each of the three relvars, as already indicated. Sample values are shown in Figure 5-1.

Figure 5-1. Relvars S, ST, and SC—sample values

Here now are the predicates for ST and SC:

As indicated in the introduction to the chapter, I’ll begin by considering what happens if all three relvars are base ones, living alongside one another as it were. Of course, if they are indeed all base relvars, we’re looking at a database that involves some redundancy once again, and so we’re going to want to control that redundancy accordingly. Here then are the integrity constraints that capture that redundancy (observe that they’re all EQDs—equality dependencies—and together they spell out the specifics of the redundancy and guarantee the information equivalence referred to above):

CONSTRAINT ... ST = S { SNO , STATUS } ;
CONSTRAINT ... SC = S { SNO , CITY } ;
CONSTRAINT ... S  = JOIN { ST , SC } ;

Note: In connection with the third constraint here (which is, of course, implied by the other two together with the fact that {SNO} is a key for S), I remind you from Chapter 4 that Tutorial D—at least, the version of Tutorial D I’m using in this book—supports both dyadic (infix) and n-adic (prefix) versions of certain relational operators, including join in particular. Thus, for example, the join of ST and SC can be expressed as either ST JOIN SC or (as above) JOIN {ST,SC}. As noted in that previous chapter, I’ll use both styles in this book, whichever best suits my purpose at the time.

The following constraint is also implied by the first two above:

CONSTRAINT ... IDENTICAL { S { SNO } , ST { SNO } , SC { SNO } } ;

And here I should explain that the Tutorial D expression IDENTICAL {r1,…,rn} returns TRUE if and only if the argument relations r1, …, rn are all equal (IDENTICAL can be thought of as a kind of “n-adic equals” operator). Thus, this particular constraint is really the logical AND of three separate equality dependencies:

S  { SNO } = ST { SNO } AND
ST { SNO } = SC { SNO } AND
SC { SNO } = S  { SNO }

What compensatory actions apply? Well, these ones are fairly obvious:

ON DELETE d FROM S : DELETE d { SNO , STATUS } FROM ST ,
                     DELETE d { SNO , CITY } FROM SC ;

ON INSERT i INTO S : INSERT i { SNO , STATUS } INTO ST ,
                     INSERT i { SNO , CITY } INTO SC ;

These rules take care of updates on S. What about updates on ST and SC? Let’s think about DELETE operations first. Now, the following “double DELETE” certainly seems reasonable:^[69]

DELETE ( S1 , 20 ) FROM ST ,
DELETE ( S1 , London ) FROM SC ;

The effect is to delete the specified tuples from ST and SC and—assuming, not unreasonably, that appropriate cascade delete rules are in effect—also the tuple (S1,20,London) from relvar S. But what about this “single DELETE”?—

DELETE ( S1 , 20 ) FROM ST ;

Now, what can’t be allowed to happen here is for the tuple (S1,20) to be deleted from relvar ST, exactly as requested, while everything else remains unchanged (because such an outcome would clearly violate certain of the constraints specified earlier). So one possibility would be simply to reject the specified DELETE on a Golden Rule violation. On the grounds that it’s surely desirable in general for user requests to succeed if they sensibly can, however, it seems preferable to accept the DELETE and let it cascade appropriately.^[70] In other words, I propose the following as an appropriate rule for deletes on ST:

ON DELETE d FROM ST : DELETE ( S MATCHING d ) FROM S ;

Note that cascading a delete on the projection relvar ST to relvar S will cause the rule previously defined for deletes on S to come into play; the net effect will thus be that the original DELETE cascades to the other projection relvar SC as well.

ON DELETE d FROM SC : DELETE ( S MATCHING d ) FROM S ;

Now what about INSERT operations on those projection relvars? Well, the following “double INSERT” does seem reasonable:

INSERT ( S9 , 20 ) INTO ST ,
INSERT ( S9 , London ) INTO SC ;

The effect is to insert the specified tuples into ST and SC and—again assuming, not unreasonably, that an appropriate cascade rule is in effect (see below)—also the tuple (S9,20,London) into relvar S. But what about this “single INSERT”?—

INSERT ( S9 , 20 ) INTO ST ;

Now, what can’t be allowed to happen here is for the tuple (S9,20) to be inserted into relvar ST, exactly as requested, while everything else remains unchanged, because such an outcome would clearly violate certain of the constraints specified earlier. What’s more, no cascading makes sense here (contrast the situation with DELETE operations as discussed above)—we can’t make the insertion of tuple (S9,20) into relvar ST cascade to insert some tuple (S9,20,c) into relvar S, precisely because the user who requested the original INSERT hasn’t provided us with that value c that we need to “complete the tuple,” as it were.^[71] (By the same token, no cascading of the original INSERT to relvar SC makes sense, either.) So the original INSERT has to fail on a Golden Rule violation, and the only insert rule that seems to make any sense is as follows:

ON INSERT it INTO ST , INSERT ic INTO SC :
   INSERT ( it JOIN ic ) INTO S ;

Note in connection with this rule that if the projections of it and ic on SNO aren’t equal, the INSERT overall will fail on, again, a violation of The Golden Rule. As a consequence, the fact that INSERT operations must (among other things) be “double INSERTs” if they’re to succeed is automatically taken care of.

In the interest of clarity, let me now restate the foregoing rule in the following expanded but logically equivalent form (note in particular the introduced names t1 and t2):

ON INSERT it INTO ST , INSERT ic INTO SC :
   WITH ( t1 := it JOIN SC , t2 := ic JOIN ST ) :
   INSERT t1 INTO S , INSERT t2 INTO S ;

Note carefully here that (as explained in Chapter 4) the original INSERTs into ST and SC are done before the specified compensatory actions—the two INSERTs into S—come into play. Thus, the symbols “ST” and “SC” in those compensatory actions (in the expressions ic JOIN ST and it JOIN SC, respectively) refer to the values of those relvars after the original INSERTs into those relvars have been done.

INSERT UNION { t1 , t2 } INTO S ;

For convenience, let me now summarize the rules as defined so far for INSERTs and DELETEs on ST and SC as a single combined rule (again I use introduced names for clarity):

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 ;

Let me also restate the rules for INSERTs and DELETEs on S as a single combined rule:

ON DELETE d FROM S , INSERT i INTO S :
   DELETE d { SNO , STATUS } FROM ST , DELETE d { SNO , CITY } FROM SC ,
   INSERT i { SNO , STATUS } INTO ST , INSERT i { SNO , CITY } INTO SC ;

Now let’s think about explicit UPDATE operations. First, I’ll leave it as an exercise to show that explicit UPDATEs on relvar S all work as you would intuitively expect. (To convince yourself of this fact, you might like to consider the following examples:

UPDATE S WHERE SNO = 'S1' : { CITY := 'Paris' } ;

UPDATE S WHERE SNO = 'S1' : { SNO := 'S9' } ;

Note in particular that the second of these examples is a “key UPDATE.”)

But what about UPDATEs on ST and SC? By way of example, consider the following UPDATE request on ST:

UPDATE ST WHERE SNO = 'S1' : { STATUS := 10 } ;

Given our usual sample values, what happens here is as follows:

UPDATE ST WHERE SNO = 'S1' : { SNO := 'S9' } ;

Well, it’s easy to see here that this UPDATE unfortunately fails (to be specific, it fails on a Golden Rule violation). As in the case of the suppliers and shipments example in the previous chapter, in fact, it seems to me that here there’s no alternative to writing out the necessary multiple assignment explicitly, thus:

UPDATE ST WHERE SNO = 'S1' : { SNO := 'S9' } ,
UPDATE SC WHERE SNO = 'S1' : { SNO := 'S9' } ;

This “double UPDATE” succeeds, as you can easily confirm.

So: Despite this latter minor annoyance, it’s still the case that (a) the two designs, S by itself and the combination of ST and SC, are information equivalent, and hence that (b) for every update on one, there’s a logically equivalent update on the other that has the same overall effect. So far, so good.

Let’s continue to assume that relvars S, ST, and SC are all base relvars (still no views yet). Now consider a user who sees just the projection relvars ST and SC. That user:

Given all of the above, I’ll leave it as an exercise to show that, from this user’s perspective, all updates on ST and/or SC work exactly as described in the previous section.

In Chapter 4, I showed it made essentially no difference if some or all of the relvars under discussion were views instead of base relvars, just so long as information equivalence was preserved. And as I’m sure you’ve been expecting, the message here is the same, viz.: Everything I’ve said in this chapter so far applies pretty much unchanged if some or all of the relvars concerned are views. To be specific, suppose now that S is a base relvar and ST and SC are views. Then:

Now I want to consider a revised version—in a way, a generalized version—of Example 1. Again I’ll start with relvar S. This time, however, I’ll add a constraint to the effect that the functional dependency (FD)

{ CITY } → { STATUS }

holds in that relvar; in other words, whenever two suppliers have the same city, they also have the same status.^[73] (In order to bring our usual sample values into line with this new constraint, let’s temporarily change the status of supplier S2 from 10 to 30.) Now suppose we define two projection relvars—SC, the projection of S on SNO and CITY (as in Example 1), and CT, the projection of S on CITY and STATUS:

SC { SNO , CITY } KEY { SNO }
CT { CITY , STATUS } KEY { CITY }

Observe that, as with Example 1, these two projections taken together correspond to a nonloss decomposition of relvar S—at all times, (a) the values of SC and CT are equal to the pertinent projections of the value of S and (b) the value of S is equal to the value of the expression SC JOIN CT. Thus, the two designs, S by itself and the combination of SC and CT, are information equivalent.^[74] The sole keys for these relvars are {SNO} for SC and {CITY} for CT. Sample values are shown in Figure 5-2.

Figure 5-2. Relvars S, SC, and CT, with {CITY} → {STATUS}—sample values

Here now are the predicates for SC and CT:

As usual, first let’s see what happens if all three relvars are base ones. The following constraints clearly hold:

CONSTRAINT ... SC = S { SNO , CITY } ;
CONSTRAINT ... CT = S { CITY , STATUS } ;
CONSTRAINT ... S  = JOIN { SC , CT } ;
CONSTRAINT ... IDENTICAL { S { SNO } , SC { SNO } } ;
CONSTRAINT ... IDENTICAL { S { CITY } , SC { CITY } , CT { CITY } } ;

As for updates, INSERTs and DELETEs on S certainly cascade to SC appropriately:

ON INSERT i INTO S : INSERT i { SNO , CITY } INTO SC ;
ON DELETE d FROM S : DELETE d { SNO , CITY } FROM SC ;

But what about cascades to CT? Well, INSERTs are straightforward:

ON INSERT i INTO S : INSERT i { CITY , STATUS } INTO CT ;

(I remind you once again that an attempt to insert a tuple that already exists is effectively a “no op.”) However, DELETEs are a little more complicated:

ON DELETE d FROM S :
   DELETE ( ( CT MATCHING d ) NOT MATCHING S ) FROM CT ;

Explanation: Let r denote the relation represented by the subexpression CT MATCHING d. Then, assuming sample values as given in Figure 5-2:

Also, I observe without detailed discussion that given the foregoing compensatory actions, explicit UPDATEs on S all work exactly as expected, as you might like to confirm for yourself. Note in particular that updates that attempt to infringe the FD {CITY} → {STATUS} will fail on a Golden Rule violation.

Now what about updates on SC and CT? Well, again let’s consider some examples (all based, of course, on the sample values in Figure 5-2). First relvar SC:

Turning now to relvar CT:

Note: This latter rule could in fact be simplified to the following without loss (why, exactly?):

ON INSERT i INTO SC :
   INSERT ( i JOIN CT ) INTO S ;

Given these rules, the foregoing INSERT and DELETE examples all work as described, and as intuitively expected. Also, I observe without detailed discussion that explicit UPDATEs work as expected as well (exercise for the reader). As with Example 1, therefore, the situation is that (a) the two designs, S by itself and the combination of SC and CT, are information equivalent, and hence that (b) for every update on one, there’s a logically equivalent update on the other that has the same overall effect.

Now, for completeness I do plan to round out my discussion of Example 2, as I did with Example 1, by (a) considering relvars SC and CT independently of relvar S; (b) considering what happens if the user sees just one of those two relvars (i.e., if part of the total picture is hidden); and (c) considering what happens if SC and CT are actually views. However, I think it’s only fair to say up front that there aren’t going to be any surprises in those discussions; indeed, I think it would be surprising if there were any surprises. In fact, let me draw your attention to a more important point: namely that, as you might have already realized, the update rules I’ve given for Example 2 are essentially identical to their counterparts for Example 1—or, to state the matter a little more precisely, the rules for Example 1 are a degenerate special case of those for Example 2. What’s more, this state of affairs is only to be expected, given that the situation illustrated by Example 1 is a degenerate special case of that illustrated by Example 2. (Recall that Example 1 also involved a nonloss decomposition of relvar S, albeit into different projections.)

Now I want to change the example one final time (this is the last example I’ll be discussing in this chapter). As usual I’ll start with relvar S, but now I’ll assume (in contrast to Example 2) that the functional dependency {CITY} → {STATUS} does not hold. Now let’s define two projection relvars—ST, the projection on SNO and STATUS, and TC, the projection on STATUS and CITY:

ST { SNO , STATUS } KEY { SNO }
TC { STATUS , CITY } KEY { STATUS , CITY }

Note: Relvar ST is exactly as it was in Example 1. Relvar TC, by contrast, though it has the same heading as relvar CT in Example 2, has different semantics (as is reflected by the fact that it has a different key); for that reason, I’ve given it a different name in order—I hope—to reduce confusion. Sample values are shown in Figure 5-3. Observe that I’ve changed the status of supplier S2 back to 10, since the FD {CITY} → {STATUS} no longer holds.

Figure 5-3. Relvars S, ST, and TC—sample values

As indicated, the sole keys for these relvars are {SNO} for ST and {STATUS,CITY} (the entire heading) for TC. Note that, in contrast to Examples 1 and 2, relvars ST and TC together correspond to a lossy, not a nonloss, decomposition of relvar S. In other words, S isn’t equal to the join of ST and TC,^[79] and the two designs—S by itself and the combination of ST and TC—are thus not information equivalent (to be specific, information regarding which suppliers are in which cities is lost in the latter design).

Be that as it may, let’s begin as usual by assuming that all three relvars are base ones. Here then are the predicates for ST and TC:

The following constraints clearly hold:

CONSTRAINT ... ST = S { SNO , STATUS } ;
CONSTRAINT ... TC = S { STATUS , CITY } ;
CONSTRAINT ... S { SNO } = ST { SNO } ;
CONSTRAINT ... ST { STATUS } = TC { STATUS } ;

Next, an analysis essentially similar to the one we went through for Example 2—I’ll leave the details to you—shows that the rules for updates on S are as follows:

ON INSERT i INTO S :
   INSERT i { SNO , STATUS } INTO ST ,
   INSERT i { STATUS , CITY } INTO TC ;

ON DELETE d FROM S :
   DELETE d { SNO , STATUS } FROM ST ,
   DELETE ( ( TC MATCHING d ) NOT MATCHING S ) FROM TC ;

But what about updates on ST and TC? Well, again let’s consider some examples:

ON DELETE d FROM ST :
   DELETE ( S MATCHING d ) FROM S ;

ON INSERT i INTO ST :
   INSERT ( i JOIN TC ) INTO S ;

Given these rules, the foregoing INSERT and DELETE examples all work as described. Explicit UPDATE operations are another matter, however. In fact, it seems that most such operations, on either ST or TC, will either fail or have undesirable side effects. By way of example, you might like to consider what happens if we try to update ST, changing the status for supplier S2 to (a) 40, (b) 20, (c) 30, or changing the supplier number for supplier S1 to S9. (Analogs of all of these updates can certainly be done on relvar S, of course.) And the reason for this state of affairs is, of course, that the two designs—S by itself and the combination of ST and TC—aren’t information equivalent; to be specific (and to repeat), the fact that a given supplier is in a given city can be represented in the former design but not, in general, in the latter. Thus, while such explicit UPDATE operations can certainly be requested, they probably shouldn’t be.

Now, once again I’ll complete my investigation into the example overall by (a) considering the projection relvars independently of relvar S, (b) considering what happens if the user sees just one of those projection relvars, and (c) considering what happens if those relvars are views—but again I don’t think you’ll find any surprises in those discussions. What you might find a little surprising, however, is that the update rules I’ve given for the example overall are extremely similar, if not quite identical, to the rules I gave earlier for Examples 1 and 2. (To repeat, they’re similar, but—speaking very loosely—updates are more likely to fail in the case of Example 3 than they are in the case of Examples 1 and 2.) It seems to me, therefore, that examples like the ones discussed in this chapter should suffice as a basis on which to define a set of rules for updating projections in general.

ON DELETE d FROM ST :
   DELETE ( ( TC MATCHING d ) NOT MATCHING ST ) FROM TC ;

There are two final remarks I want to make. First, I haven’t said quite all I want to say about updating projection views as such—I’ll come back to the topic briefly in Chapter 7, in the section Projection Views Revisited Second, I’d like to clarify something I ought perhaps to have clarified much earlier … In Chapter 1, I claimed that all views are updatable; in this chapter, by contrast, I’ve said that certain updates on certain views don’t work after all. As an extreme case in point, view TC—defined as the projection of relvar S on STATUS and CITY, in the case where the functional dependency {CITY} → {STATUS} doesn’t hold—can’t sensibly be updated at all. So am I talking out of both sides of my mouth here? What exactly is going on?

Well, I did also say in Chapter 1 that I would elaborate later on “this very strong claim” (the claim, that is, that all views are updatable). The point is this: It’s not that certain views are intrinsically nonupdatable. Rather, it’s that certain updates on certain views fail because, if they—the updates, that is—were accepted, they would cause some integrity constraint to be violated (and no compensatory actions are in place to prevent such violations from occurring.) Note that this state of affairs exactly parallels the situation with base relvars: Certain updates on certain base relvars fail too, not because the relvar in question is intrinsically nonupdatable, but because some integrity constraint would otherwise be violated. Inserting a tuple for a nonexistent supplier into the shipments relvar SP is a case in point.