Keywords

“temporal” update; PORTION; multiple assignment; current relvar; historical relvar; automated view

Since Relvars Only

In this section we address the comparatively straightforward case of updates on a version of the database that contains just the “current” relvars S_SINCE and SP_SINCE. Fig. 16.1, a copy of Fig. 14.2 from Chapter 14, shows some sample values; we’ll base our examples on those specific values, where it makes any difference. We remind you that this database can’t represent information about the past at all, other than what might be conveyed by the “since” values; however, it can represent future information—certainly implicitly, and possibly explicitly as well. Refer to Fig. 14.3 in Chapter 14 if you need to remind yourself of the constraints that apply to this database.

One general remark that’s worth making right away is the following: Given that a database is essentially a collection of propositions (see Chapter 1)—more precisely, propositions we currently believe to be true ones—a good way to think about updating in general is to think in terms of adding, removing, and replacing such propositions, instead of thinking, as we more usually do, in terms of adding, removing, and replacing tuples. (This remark applies to the entire chapter, not just to the present section.) This shift in emphasis can be helpful in understanding what’s really going on, especially when we reach later sections of the chapter where, as already indicated, some of the examples unfortunately start to get quite complicated.

Given the foregoing, it might help to begin our discussions in this section by stating the predicates for relvars S_SINCE and SP_SINCE once again:

The propositions we’ll be considering in the rest of this section are all instantiations of one or other of these predicates. (Actually, since the examples in this section all have to with relvar S_SINCE specifically, they’ll all be instantiations of the first predicate specifically.)

Perhaps we should add that, like the queries in the section “Since Relvars Only” in the previous chapter (and for much the same reasons), the updates to be discussed in the present section aren’t particularly temporal in nature. In fact, it turns out there’s not much to say about those updates anyway; updating a database that’s merely semitemporal turns out not to differ in any major respect from updating a database that isn’t temporal at all, as we’ll see.

Update U1: Add a proposition to show that supplier S9 has just been placed under contract, starting today, with status 15.

INSERT S_SINCE

    RELATION { TUPLE { SNO SNO ( 'S9' ), SNO_SINCE TODAY ( ) , STATUS 15 , STATUS_SINCE TODAY ( ) } } ;

This INSERT statement effectively adds to the database the proposition “Supplier S9 has been under contract since day dc and has had status 15 since day ds,” where dc and ds are both whatever the date happens to be today. (As in previous chapters, we assume the availability of a niladic operator called TODAY that returns the date today.)

Update U2: Remove the proposition showing that supplier S5 is under contract.

DELETE S_SINCE WHERE SNO = SNO ( 'S5' ) ;

This DELETE statement effectively removes from the database the proposition “Supplier S5 has been under contract since day dc and has had status st since day ds,” where dc, st, and ds are whatever the SNO_SINCE, STATUS, and STATUS_SINCE values happen to be in the current tuple for supplier S5.

Update U3: Replace the proposition showing that supplier S1 was placed under contract on day 4 by one showing that the same supplier was placed under contract on day 3 instead.

UPDATE S_SINCE WHERE SNO = SNO ( 'S1' ) : { SNO_SINCE := d03 } ;

As explained in Chapter 3, this UPDATE statement is shorthand for:

S_SINCE := ( S_SINCE WHERE NOT ( SNO = SNO ( 'S1' ) ) )

      UNION

       ( EXTEND ( S_SINCE WHERE SNO = SNO ( 'S1' ) ) : { SNO_SINCE := d03 } ) ;

Thus, the effect is (a) to remove the proposition “Supplier S1 has been under contract since day 4 (and has had some specific status st since some specific day ds)” and then (b) to add the proposition “Supplier S1 has been under contract since day 3 (and has had that same status st since that same day ds).” The only difference between these two propositions is in the SNO_SINCE value, of course, and the desired result is thereby achieved.

During Relvars Only I: U_UPDATES

In this section and the next two, we consider updates on a version of the database consisting of just the “historical” relvars S_DURING, S_STATUS_DURING, and SP_DURING. Fig. 16.2, a copy of Fig. 14.5, shows some sample values for these relvars; we’ll base our examples on those specific values, where it makes any difference. The database is fully temporal, but horizontal decomposition hasn’t been done; thus, the database can represent information about the future as well as the past, but typically has to use artificial “end of time” values (d99 in Fig. 16.2) to mark the end of any interval whose true end point is currently unknown. Refer to Fig. 14.6 in Chapter 14 if you need to remind yourself of the constraints—specifically, the U_key and foreign U_key constraints—that apply to this database. Here are the applicable predicates:

For simplicity, however, we’ll focus in this section and the next (on PORTION specifications) on relvar SP_DURING almost exclusively.

U_INSERT

Update U4: Add the proposition “Supplier S2 was able to supply part P4 on day 2.”

The following INSERT is sufficient:

INSERT SP_DURING

    RELATION { TUPLE { SNO SNO ( 'S2' ), PNO PNO ( 'P4' ), DURING INTERVAL_DATE ( [ d02 : d02 ] ) } } ;

However, suppose the proposition to be added had specified part P5 instead of P4. Then we couldn’t just insert the corresponding tuple into SP_DURING (even though we’re indeed just trying to add a proposition to the database, logically speaking). Because if we did, that relvar would then contain the following two tuples—

—and thus would no longer be packed on DURING, and would thereby violate the PACKED ON constraint on that relvar.

In order to address this problem, we introduce a generalized form of INSERT—another shorthand, of course—which we call U_INSERT:

Definition: Let relation r and relvar R be of the same type T, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is one of type T’s component attributes and (b) is of some interval type. Then the update operator invocation

USING ( ACL ) : INSERT R r

denotes the U_INSERT (with respect to ACL) of r into R, and it’s defined to be shorthand for the following explicit assignment:

R := USING ( ACL ) : R UNION r

In other words, the update works by (a) unpacking (on ACL) both r and the relation that’s the current value of R, (b) forming the union of those two unpacked relations, (c) packing the result of the previous step on ACL, and finally (d) assigning that packed result to R. Points arising:

■ The qualification “with respect to ACL” (on the phrase “U_INSERT of r into R”) can be omitted if the commalist ACL is understood. Note: An analogous remark applies to all of the operators to be defined in this section; thus, we won’t bother to keep repeating the point but will instead let this one paragraph do duty for all.

■ If ACL is empty, then the USING specification and the colon separator can be omitted, and the U_INSERT reduces to a regular INSERT. Note: Again an analogous remark applies to all of the operators we’ll be defining in this section and we won’t keep repeating the point, again letting this one paragraph do duty for all.

■ We pointed out in Chapter 13 that PACKED ON constraints solve certain problems—basically, the problems of redundancy and circumlocution—by replacing them by another problem: namely, the problem of ensuring that updates don’t violate those constraints. We also said we’d be introducing some operators later that can help to simplify this latter task. Well, the operators in question are, of course, precisely U_INSERT and the other new update operators to be described in the present chapter.

■ With respect to U_INSERT specifically: As you can see, that operator is defined in terms of U_UNION, just as regular INSERT is defined in terms of regular UNION. As a consequence, U_INSERT can have the arguably counterintuitive effect of decreasing the cardinality of the target relvar. Developing an example to illustrate this point is left as an exercise.

■ Of course, we can and do additionally define a “disjoint” version of U_INSERT (“disjoint U_INSERT”). The syntax is as for U_INSERT, except that D_INSERT appears in place of INSERT; likewise, the expansion is as for U_INSERT, except that D_UNION appears in place of UNION. The semantics should thus be obvious, and we omit further details here.

To return to Update U4, the following formulation will suffice regardless of whether the specified part number Py is P4 or P5:¹

USING ( DURING ) :

INSERT SP_DURING

    RELATION { TUPLE { SNO SNO ( 'S2' ), PNO PNO ( 'Py' ), DURING INTERVAL_DATE ( [ d02 : d02 ] ) } } ;

Now, a regular INSERT can always fail on a key constraint violation. Analogously, of course, a U_INSERT can fail on a U_key violation.² Well … it can’t fail on a PACKED ON violation, thanks to the way it’s defined; however, it can certainly fail on a WHEN / THEN violation, though only if relvar R and relation r have at least one nonkey attribute. For example, given the sample values of Fig. 16.2, the following U_INSERT fails in just such a way—

USING ( DURING ) :

INSERT S_STATUS_DURING

    RELATION { TUPLE { SNO SNO ( 'S2' ), STATUS 20, DURING INTERVAL_DATE ( [ d04 : d06 ] ) } } ;

—because the unpacked form of S_STATUS_DURING on DURING already contains (among other things) a tuple saying supplier S2 has status 10 on day 4.

Of course, U_INSERTs can violate other kinds of constraints, too. In particular, they can fail on a foreign U_key (referential constraint) violation. For example, given the sample values of Fig. 16.2, the following U_INSERT fails in just such a way—

USING ( DURING ) :

INSERT SP_DURING

    RELATION { TUPLE { SNO SNO ( 'S2' ), PNO PNO ( 'P4' ), DURING INTERVAL_DATE ( [ d01 : d03 ] ) } } ;

—because supplier S2 wasn’t under contract on day 1.

Note: For the remainder of this chapter, we won’t bother to discuss possible integrity constraint violations unless there’s some special point to be made in connection with them.

U_DELETE

Update U5: Remove the proposition “Supplier S6 was able to supply part P3 from day 3 to day 5” (in other words, after the specified removal has been performed, the database shouldn’t show supplier S6 as supplying part P3 on any of days 3, 4, or 5).

Before we consider what’s involved in achieving the desired effect here, we should take a moment to think about why we might ever want to remove a proposition from a fully temporal database anyway. After all, the database is supposed to contain historical records—so once a given proposition p has been represented in the database, isn’t that proposition a historical record that should be kept in the database “forever”?

Well, this discussion takes us straight into the realm of valid time vs. transaction time (see Chapter 4). If the database did once say some proposition p was true, then that state of affairs—i.e., the fact that it said so—is certainly a matter of historical record. What’s more, that historical record should indeed be kept “forever,” with a transaction time to indicate just when the database did say so (i.e., that p was true). However, proposition p as such is not a matter of history; if we subsequently find it’s false—perhaps we made a mistake originally and it shouldn’t have been represented in the database in the first place—then we should definitely remove it.

We’ll return to such matters and discuss them in much more depth in Chapter 17. For now, let’s just consider how to achieve the desired effect in the particular case at hand. In fact, the following DELETE will suffice:

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND DURING = INTERVAL_DATE ( [ d03 : d05 ] ) ;

But suppose the proposition to be removed had specified the interval from day 4 (instead of day 3) to day 5. Then the corresponding DELETE—

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND DURING = INTERVAL_DATE ( [ d04 : d05 ] ) ;

—will have no effect (it certainly won’t delete anything), because there is no tuple in relvar SP_DURING for supplier S6 and part P3 with DURING = [d04:d05]. Clearly, therefore, we need another shorthand:

Definition: Let R be a relvar and let ACL be a commalist of attribute names in which every attribute mentioned (a) is one of R’s component attributes and (b) is of some interval type. Then the update operator invocation

USING ( ACL ) : DELETE R WHERE bx

denotes a U_DELETE WHERE (with respect to ACL and bx) on R, and it’s defined to be shorthand for the following explicit assignment:

R := USING ( ACL ) : R WHERE NOT ( bx )

As you can see, this operator is defined in terms of U_restriction, just as—as we saw in Chapter 3—regular DELETE WHERE is defined in terms of regular restriction.³ In other words, the update works by (a) unpacking the current value of R on ACL, (b) forming the relation with body consisting of those tuples of that unpacked relation that satisfy the condition NOT (bx), (c) packing the result of the previous step on ACL, and finally (d) assigning that packed result to R. In the example, therefore, the desired effect can be achieved as follows:

USING ( DURING ) :

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND DURING ⊆ INTERVAL_DATE ( [ d04 : d05 ] ) ;

Note that the “=” comparison on DURING in the original (incorrect) formulation has become a “⊆” comparison in this latter formulation. That’s because after SP_DURING has been unpacked on DURING, the DURING reference in the WHERE condition is a reference to a unit interval specifically. The following alternative formulation might make this point a little more clearly (the difference is in the last line):

USING ( DURING ) :

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND POINT FROM DURING ∈ INTERVAL_DATE ( [ d04 : d05 ] ) ;

Either way, the effect on SP_DURING, given the sample values of Fig. 16.2, is as shown here (the change is in the highlighted tuple):

Note: As you can see, U_DELETE WHERE is really a kind of UPDATE (in the example, it “updates” just one tuple)—but calling it a DELETE seems preferable, intuitively speaking. Also, as you might expect, such an operation can have the arguably counterintuitive effect of increasing the cardinality of the target relvar. Developing an example to illustrate this point is left as an exercise.

By the way, you might be thinking that removing propositions can never cause a PACKED ON constraint to be violated, and hence that the final PACK step in the U_DELETE WHERE definition (caused by the USING (ACL) specification) is unnecessary. In fact, this conjecture is correct in the specific example just discussed, because the initial unpacking and subsequent repacking are both done on the basis of a single attribute; but it’s not correct, in general, if the unpacking and repacking are done on the basis of two attributes or more. By way of example, consider relvar S_PARTS_DURING once again (see the section “A More Searching Example” in Chapter 6). Suppose that relvar has the following relation as its current value:⁴

Note that this relation is packed on (DURING,PARTS), in that order, as you can easily confirm.⁵ Now consider the following U_DELETE WHERE:

USING ( PARTS, DURING ) :

DELETE S_PARTS_DURING WHERE DURING ⊆ INTERVAL_DATE ( [ d01 : d02 ] ) ;

Let’s work through this update in detail. First of all, the preliminary UNPACK on (PARTS,DURING) gives:

Removing the tuples whose DURING value is included in the interval [d01:d02] gives:

And this relation certainly does violate the constraint PACKED ON (DURING,PARTS) (and the constraint PACKED ON (PARTS,DURING) too, come to that). So it needs to be packed to just:

USING ( DURING ) :

DELETE SP_DURING

    RELATION { TUPLE { SNO SNO ( 'S6' ), PNO PNO ( 'P3', DURING INTERVAL_DATE ( [ d04 : d05 ] } } ;

USING ( DURING ) :

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND POINT FROM DURING ∈ INTERVAL_DATE ( [ d04 : d05 ] ) ;

UPDATE SP_DURING WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P5' ) AND DURING = INTERVAL_DATE ( [ d03 : d03 ] ) : { DURING := INTERVAL_DATE ( [ d02 : d02 ] ) } ;

USING ( DURING ) :

UPDATE SP_DURING WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P5' ) AND DURING = INTERVAL_DATE ( [ d03 : d03 ] ) : { DURING := INTERVAL_DATE ( [ d02 : d02 ] ) } ;

During Relvars Only II: Portion Specifications

The alternative solution to the problem discussed at the end of the previous section involves a PORTION specification on UPDATE. But PORTION can be used with DELETE, too, and it suits our purposes better to discuss that case first.

PORTION DELETE

Update U7 (modified version of Update U5): Remove the proposition “Supplier S6 was able to supply part P3 from day 4 to day 5.”

In the previous section we gave the following formulation of this update:

USING ( DURING ) :

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) AND DURING ⊆ INTERVAL_DATE ( [ d04 : d05 ] ) ;

But here’s another formulation that works just as well (note the highlighted text, and note in particular that this statement isn’t a U_DELETE as such):⁷

DELETE SP_DURING WHERE SNO = SNO ( 'S6' ) AND PNO = PNO ( 'P3' ) : PORTION { DURING { INTERVAL_DATE ( [ d04 : d05 ] ) } } ;

In order to explain how this statement works, we offer the following preliminary definition:

Definition (first version): Let R be a relvar, let A be an attribute of R of some interval type T, and let ix be an expression denoting an interval of type T. Then the update operator invocation

DELETE R WHERE bx : PORTION { A { ix } }

denotes a PORTION DELETE on R, and it’s defined to be shorthand for the following explicit assignment:

WITH ( t1 := R WHERE ( bx ) AND A OVERLAPS ( ix ),

    t2 := R MINUS t1,

    t3 := UNPACK t1 ON ( A ) ,

    t4 := t3 WHERE NOT ( A ⊆ ( ix ) ) ,

    t5 := t2 UNION t4 ) :

R := PACK t5 ON ( A )

We leave it as an exercise to check the example given above against this definition. By the way, observe that in this definition we have an example—the first we’ve seen since Chapter 2—of WITH being used on a statement as opposed to an expression.

Now, relvars can have any number of interval attributes, of course—they aren’t limited to just one. So it seems reasonable, within the PORTION portion of a PORTION DELETE on relvar R, to allow interval expressions to be specified for any subset of relvar R’s interval attributes. Here’s an example, expressed in terms of relvar S_PARTS_DURING once again (again, see the section “A More Searching Example” in Chapter 6 if you need to refresh your memory):

DELETE S_PARTS_DURING :

  PORTION { PARTS { INTERVAL_PNO ( [ PNO ( 'P2' ) : PNO ( 'P4' ) ] ) }, DURING { INTERVAL_DATE ( [ d03 : d05 ] ) } } ;

(Examples like this one show why those outer braces are necessary in a PORTION specification.) The intent here,loosely, is to delete tuples from the unpacked form of S_PARTS_DURING whose PARTS value is in the range [P2:P4] and whose DURING value is in the range [d03:d05] (and then to pack the result, of course). Here’s the definition:

Definition (second version): Let R be a relvar, let A1, A2, …, An be interval attributes of R, of types T1, T2, …, Tn, respectively, and let ix1, ix2, …, ixn be expression denoting intervals of types T1, T2, …, Tn, respectively. Then the update operator invocation

DELETE R WHERE bx :

    PORTION { A1 { ix1 } , A2 { ix2 } , … , An { ixn } } ;

denotes a PORTION DELETE on R, and it’s defined to be shorthand for the following explicit assignment:

WITH ( t1 := R WHERE ( bx ) AND A1 OVERLAPS ( ix1 ) AND A2 OVERLAPS ( ix2 ) ……………… AND An OVERLAPS ( ixn ) ,

    t2 := R MINUS t1 ,

    t3 := UNPACK t1 ON ( A1 , A2 , … , An ) ,

    t4 := t3 WHERE NOT ( A1 ⊆ ( ix1 ) OR A2 ⊆ ( ix2 ) OR ………… An ⊆ ( ixn ) ) ,

    t5 := t2 UNION t4 ) :

R := PACK t5 ON ( A1 , A2 , … , An )

As an exercise, try working through the PORTION DELETE on S_PARTS_DURING shown above, using some sample data of your own invention.

Next, it also seems reasonable, within the PORTION portion of a PORTION DELETE on relvar R, to allow two or more interval expressions to be specified for each interval attribute.⁸

Here’s a simple example, this time reverting to our familiar relvar SP_DURING:

DELETE SP_DURING WHERE SNO = SNO ( 'S7' ) : PORTION { DURING { INTERVAL_DATE ( [ d04 : d06 ] ), INTERVAL_DATE ( [ d12 : d99 ] ) } } ;

(Examples like this one show why those inner braces are necessary in a PORTION specification.) The intent here, loosely, is to delete tuples for supplier S7 from the unpacked form of SP_DURING whose DURING value is in the range [d04:d06] or in the range [d12:d99] (and then to pack the result, of course). Here’s the result, given the sample values of Fig. 16.2:

And here’s the definition, expressed for simplicity in terms of just one interval attribute:

Definition (third version): Let R be a relvar, let A be an attribute of R of some interval type T, and let each of ix1, ix2, …, ixn be an expression denoting an interval of type T. Then the update operator invocation

DELETE R WHERE bx : PORTION { A { ix1, ix2, …, ixn } }

denotes a PORTION DELETE on R, and it’s defined to be shorthand for the following explicit assignment:

WITH ( t1 := R WHERE ( bx ) AND ( A OVERLAPS ( ix1 ) OR A OVERLAPS ( ix2 ) OR ……………… A OVERLAPS ( ixn ) ) ,

    t2 := R MINUS t1 ,

    t3 := UNPACK t1 ON ( A ) ,

    t4 := t3 WHERE NOT ( A ⊆ ( ix1 ) AND A ⊆ ( ix2 ) AND ………… A ⊆ ( ixn ) ) ,

    t5 := t2 UNION t4 ) :

R := PACK t5 ON ( A )

Extending this definition to cover the case of more than one interval attribute—in effect, combining the second and third definitions above—is left as another exercise. Note, however, that (in general, and speaking somewhat loosely) the commas within the inner braces represent OR, while the other commas represent AND.

PORTION UPDATE

Update U8 (modified version of Update U6): Replace the proposition “Supplier S2 was able to supply part P5 on day 3” by the proposition “Supplier S2 was able to supply part P5 on day 2.”

UPDATE SP_DURING WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P5' ) :

    PORTION { DURING { INTERVAL_DATE ( [ d03 : d03 ] ) } } : { DURING := INTERVAL_DATE ( [ d02 : d02 ] ) } ;

How this example works should be intuitively obvious, but spelling out the semantics of the general case (i.e., UPDATE with a PORTION specification in general) we leave as another exercise for the reader. We content ourselves here with offering some further examples.

Update U9 (modified version of Update U8): Replace the proposition “Supplier S2 was able to supply part P5 on day 3” by the proposition “Supplier S2 was able to supply part P5 on day 2”—but do so only if the interval containing day 3 during which supplier S2 was able to supply part P5 is at least seven days long.

This example is plainly somewhat contrived, but it serves our purpose here. The point about it is this: Clearly, we need to check whether the specified interval is indeed at least seven days long before we do any unpacking (after unpacking, of course, every interval will be just one day long). And that’s what this formulation does:

UPDATE SP_DURING WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P5' ) AND d03 ∈ DURING AND COUNT ( DURING ) ≥ 7 :

    PORTION { DURING { INTERVAL_DATE ( [ d03 : d03 ] ) } } : { DURING := INTERVAL_DATE ( [ d02 : d02 ] ) } ;

Update U10: Replace every proposition of the form “Supplier Sx was able to supply part P8 during interval i” by one of the form “Supplier Sx was able to supply part P9 during interval i”—but only if interval i overlaps [d05:d08], and then perform the replacement only for that part of interval i that overlaps [d02:d06].

Again this example is highly contrived, of course, but here’s a suitable formulation:

UPDATE SP_DURING WHERE PNO = PNO ( 'P8' ) AND DURING OVERLAPS INTERVAL_DATE ( [ d05 : d08 ] ) :

    PORTION { DURING { INTERVAL_DATE ( [ d02 : d06 ] ) } } : { PNO := PNO ( 'P9' ) } ;

Observe in this example that it’s not the interval as such that’s being updated.

Update U11: Replace the proposition “Supplier S1 was under contract from day 4 to day 8 inclusive” by the proposition “Supplier S2 was under contract from day 6 to day 7 inclusive.”

Now, it might well be argued that this example is not merely contrived but goes beyond the bounds of reasonableness, since no term in the new proposition is the same as its counterpart in the old one!⁹ (After all, the idea of updating a tuple in such a way that every component is changed is usually looked at a trifle askance, even in a nontemporal database.) Be that as it may, here’s a possible formulation:

UPDATE S_DURING WHERE SNO = SNO ( 'S1' ) : PORTION { DURING { INTERVAL_DATE ( [ d04 : d08 ] ) } } :

    { SNO := SNO ( 'S2' ), DURING := INTERVAL_DATE ( [ d06 : d07 ] ) } ;

If before this UPDATE relvar S_DURING looks like this—

—then after the UPDATE it looks like this:

Two final points to close this section:

■ First, we’ve considered PORTION in connection with the regular DELETE and UPDATE operators but not with their “U_” counterparts U_DELETE and U_UPDATE. Whether this latter possibility makes sense or could be useful is a matter that requires further investigation.

■ Second, we’ve considered PORTION in connection with update operators, but a PORTION version of restrict could be useful too (more useful, possibly, than U_restrict, as a matter of fact). E.g., given the sample value shown for S_DURING in Fig. 16.2, the expression

S_DURING PORTION { DURING { INTERVAL_DATE ( [ d05 : d08 ] ) } }

would yield:

During Relvars Only III: Multiple Assignment

Now we turn our attention to the question of updating S_DURING and S_STATUS_DURING (as noted earlier, examples in the previous two sections mostly dealt just with SP_DURING). First we remind you of the design of the relevant portions of the database:

S_DURING { SNO, DURING }

      USING ( DURING ) : KEY { SNO, DURING }

      USING ( DURING ) : FOREIGN KEY { SNO, DURING } REFERENCES S_STATUS_DURING

S_STATUS_DURING { SNO, STATUS, DURING }

      USING ( DURING ) : KEY { SNO, DURING }

      USING ( DURING ) : FOREIGN KEY { SNO, DURING } REFERENCES S_DURING

Note in particular that each of these relvars has a foreign U_key that references the other: The one in S_STATUS_DURING reflects the fact that any supplier that has some status at some time must be under contract at that time, and the one in S_DURING reflects the fact that any supplier under contract at some time must have some status at that time (recall the discussion of denseness constraints in Chapter 14).

Update U12 (same as Update U1): Add proposition(s) to show that supplier S9 has just been placed under contract, starting today, with status 15.

The following code will suffice:

INSERT S_DURING RELATION { TUPLE

    { SNO SNO ( 'S9' ), DURING INTERVAL_DATE ( [ TODAY ( ) : LAST_DATE ( ) ] ) } },

INSERT S_STATUS_DURING RELATION { TUPLE

    { SNO SNO ( 'S9' ), STATUS 15, DURING INTERVAL_DATE ( [ TODAY ( ) : LAST_DATE ( ) ] ) } } ;

Explanation: Clearly, we need two INSERTs here; the target relvars are S_DURING and S_STATUS_DURING, respectively, and the set of tuples to be inserted—actually a singleton set—is denoted by a simple relation selector invocation in each case. Note carefully, however, that the two INSERTs are separated by a comma, not a semicolon, and are thereby bundled into a single statement. That statement in turn is essentially just syntactic shorthand for an explicit multiple assignment that looks like this (again note the comma separator):

S_DURING := S_SINCE UNION RELATION { TUPLE

    { SNO SNO ( 'S9' ) , DURING INTERVAL_DATE ( [ TODAY ( ) : LAST_DATE ( ) ] ) } } ,

S_STATUS_DURING := S_SINCE UNION RELATION { TUPLE

    { SNO SNO ( 'S9' ) , STATUS 15 , DURING INTERVAL_DATE ( [ TODAY ( ) : LAST_DATE ( ) ] ) } } ;

As we saw in Chapter 3, the semantics are as follows:

Observe in particular that, precisely because the source expressions are all evaluated before any of the individual assignments are executed, none of those individual assignments can depend on the result of any other, and so the sequence in which they’re executed is irrelevant.¹⁰ In the example, the net effect is thus that S_DURING and S_STATUS_DURING are updated “simultaneously”—i.e., both relvars are updated as part of the same statement execution. Note that indeed both updates must be part of the same statement execution, for at least the following two reasons:

Update U13: Remove all proposition(s) showing supplier S7 as being under contract.

Again a multiple assignment is needed:

DELETE SP_DURING WHERE SNO = SNO ( 'S7' ) ,

DELETE S_STATUS_DURING WHERE SNO = SNO ( 'S7' ) ,

DELETE S_DURING WHERE SNO = SNO ( 'S7' ) ;

(The first comma here could be replaced by a semicolon, but the second mustn’t be.) Here’s the expansion in terms of explicit assignment:

SP_DURING := SP_DURING WHERE NOT ( SNO = SNO ( 'S7' ) ) ,

S_STATUS_DURING := S_STATUS_DURING WHERE NOT ( SNO = SNO ( 'S7' ) ) ,

S_DURING := S_DURING WHERE NOT ( SNO = SNO ( 'S7' ) ) ;

Update U14 (similar to Update U2, but note the different wording): Supplier S7’s current contract has just been terminated. Update the database accordingly.

UPDATE S_DURING WHERE SNO = SNO ( 'S7' ) AND TODAY ( ) ∈ DURING :

    { END ( DURING ) := TODAY ( ) } ,

UPDATE S_STATUS_DURING WHERE SNO = SNO ( 'S7' ) AND TODAY ( ) ∈ DURING :

    { END ( DURING ) := TODAY ( ) } ,

UPDATE SP_DURING WHERE SNO = SNO ( 'S7' ) AND TODAY ( ) ∈ DURING :

    { END ( DURING ) := TODAY ( ) } ;

Note the use of syntax that assigns directly to END (DURING) in this example (we explained this syntax in an aside in the discussion of Update U6 in the section “During Relvars I: U_updates,” earlier in the chapter). More important, note that the “removal” of certain propositions here is being done by means of UPDATE operations! (The propositions in question all have to do with the notion that supplier S7 is under contract until “the last day.”) Indeed, it’s often the case in a fully temporal database that there’s no simple correspondence between (a) adding or removing or replacing propositions and (b) adding or removing or replacing tuples. Several examples in the next section will illustrate this same point.

Here for interest is an explicit assignment—in fact, a multiple assignment—that’s equivalent to the first (only) of the three foregoing UPDATE operations:

WITH ( t := S_DURING WHERE SNO = SNO ( 'S7' ) AND TODAY ( ) ∈ DURING ) :

DELETE S_DURING t,

INSERT S_DURING ( EXTEND t : { END ( DURING ) := TODAY ( ) } ) ;

We remark in passing that this latter multiple assignment shows very clearly that an UPDATE in general can be thought of as a DELETE followed by an INSERT on the same target. We’ll make use of this fact several times in the section immediately following.

Both Since and During Relvars

Now we turn to our preferred version of the database, which contains a mixture of since and during relvars. This version, which we refer to as the combination design, keeps “current” information in the since relvars and “historical” information in the during relvars. Fig. 16.3, a copy of Fig. 14.8, shows some sample values; we’ll base our examples on those specific values, where it makes any difference. Refer to Fig. 14.9 in Chapter 14 if you need to remind yourself of the constraints that apply to this database. Here are the applicable predicates (in simplified form once again):

Update U15: Add the proposition “Supplier S4 has been able to supply part P4 since day 10.”

The following INSERT will suffice:

INSERT SP_SINCE RELATION { TUPLE { SNO SNO ( 'S4' ) , PNO PNO ( 'P4' ) , SINCE d10 } } ;

But suppose the proposition to be added had specified day 9 instead of day 10; given the sample values shown in Fig. 16.3, the corresponding INSERT would then have failed on a violation of Constraint BR78 (actually on the “BR8” portion of that constraint—see Fig. 14.9 in Chapter 14). That constraint, to remind you, is intended to enforce the requirement that if the database shows supplier Sx as able to supply the same part Py on days d and d+1, it must contain exactly one tuple that shows that fact. In the case at hand, if the INSERT were permitted, relvars SP_SINCE and SP_DURING would then contain the following tuples, respectively, and would thereby violate the constraint:

To add that revised proposition, therefore, what we need to do is the following:

Also, of course, these two updates need to be performed “at the same time.” The following code (a multiple assignment) will thus suffice:

DELETE SP_DURING WHERE SNO = SNO ( 'S4' ) AND PNO = PNO ( 'P4' ) AND DURING = [d04:d08] ,

INSERT SP_SINCE RELATION { TUPLE { SNO SNO ( 'S4' ) , PNO PNO ( 'P4' ) , SINCE d04 } } ;

Observe now that the code just shown is, of course, specific to a certain specific update and a certain specific set of existing values in the database. Here by contrast is generic code that will effectively add the proposition “Supplier Sx has been able to supply part Py since day d” to the database, regardless of the specific values of Sx and Py and d and regardless of the specific values currently existing in the database:

WITH ( temp := SP_DURING WHERE SNO = Sx AND PNO = Py AND d ≤ POST ( DURING ) ) :

CASE

  WHEN IS_EMPTY ( temp )

    THEN INSERT SP_SINCE RELATION { TUPLE { SNO Sx , PNO Py , SINCE d } } ;

  ELSE

    DELETE SP_DURING temp ,

    INSERT SP_SINCE RELATION { TUPLE { SNO Sx , PNO Py , SINCE MIN ( temp , BEGIN ( DURING ) ) } } ;

END CASE ;

Points arising:

Update U16: After today, supplier S1 will no longer be able to supply part P1. Update the database accordingly.

The value shown for SP_SINCE in Fig. 16.3 shows that supplier S1 has been able to supply part P1 since day 4. Given this state of affairs, the following code will suffice:

INSERT SP_DURING RELATION { TUPLE { SNO SNO ( 'S1' ) , PNO PNO ( 'P1' ) , DURING INTERVAL_DATE ( [ d04 : TODAY ( ) ] ) } } ,

DELETE SP_SINCE WHERE SNO = SNO ( 'S1' ) AND PNO = PNO ( 'P1' ) ;

Here’s code for the general case—i.e., generic code to update the database to reflect the fact that supplier Sx has been able to supply part Py since day d but after today will no longer be able to do so:

WITH ( temp : = SP_SINCE WHERE SNO = Sx AND PNO = Py ) :

IF IS_NOT_EMPTY ( temp ) THEN

    INSERT SP_DURING RELATION { TUPLE { SNO Sx , PNO Py , DURING INTERVAL_DATE ( [ SINCE FROM ( TUPLE FROM temp ) : TODAY ( ) ] ) } } ,

    DELETE SP_SINCE temp ;

END IF ;

Note: To return to Update U16 per se, we were making a tacit assumption in connection with that example that the original proposition—“Supplier S1 was able to supply part P1 since day 4”—was in fact true (or, rather, we believed it was true). But suppose we find we made a mistake, and the SP_SINCE tuple for S1 and P1 with SINCE = d04 should never have been in that relvar in the first place. Then we would need to perform an appropriate DELETE on SP_SINCE without performing a corresponding INSERT on SP_DURING:

DELETE SP_SINCE WHERE SNO = SNO ( 'S1' ) AND PNO = PNO ( 'P1' ) ;

Analogous remarks apply to other updates also: If the reason for the update is simply to correct an earlier mistake, then it might be inappropriate, or incorrect, to perform “simultaneous updates” on other relvars. For definiteness, we assume in our examples that such simultaneous updates are both appropriate and necessary—but exactly what’s appropriate and necessary in any given situation will, of course, depend on circumstances.

Update U17: Replace the proposition “Supplier S2 has been able to supply part P1 since day 8” by the proposition “Supplier S2 has been able to supply part P1 since day 7.”

Given the sample values of Fig. 16.3, the following UPDATE will suffice:

UPDATE SP_SINCE WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P1' ) : { SINCE := d07 } ;

But suppose the new SINCE value had been day 5, not day 7. Given those same sample values (i.e., as shown in Fig. 16.3), then the corresponding UPDATE would have failed on a violation of Constraint BR78 (actually on the “BR8” portion of that constraint—see Fig. 14.9 in Chapter 14). For if the UPDATE were permitted, relvars SP_SINCE and SP_DURING would then contain the following tuples, respectively, and would thereby violate the constraint:

Thus, what we need to do is as follows:

DELETE SP_DURING WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P1' ) AND END ( DURING ) = d04,

UPDATE SP_SINCE WHERE SNO = SNO ( 'S2' ) AND PNO = PNO ( 'P1' ) : { SINCE := d02 } ;

More generally, here’s code that will effectively replace the proposition “Supplier Sx has been able to supply part Py since day d” by the proposition “Supplier Sx has been able to supply part Py since day d’”:

WITH ( temp := SP_DURING WHERE SNO = Sx AND PNO = Py AND d ≤ POST ( DURING ) ) :

CASE

  WHEN IS_EMPTY ( temp )

    THEN UPDATE SP_SINCE WHERE SNO = Sx AND PNO = Py : { SINCE := d' } ;

  ELSE

    DELETE SP_DURING temp ,

    UPDATE SP_SINCE WHERE SNO = Sx AND PNO = Py : { SINCE := MIN (temp , BEGIN ( DURING ) ) } ;

END CASE ;

As you can see, this code is very similar to the generic “insert” code shown earlier under Update U10, except that the two INSERTs in that code have been replaced by UPDATEs. Note, however, that the code does assume a tuple for supplier Sx and part Py currently exists in relvar SP_SINCE. Extending the code appropriately to avoid reliance on such an assumption is left as an exercise.

We conclude this section with the following observations. First, we’ve considered attempts to change the SINCE component of an SP_SINCE tuple (loosely speaking), but not attempts to change either the SNO or the PNO component. We leave it as a further exercise to determine that considerations essentially similar to those already discussed apply in these cases too. If you want to try this exercise, we suggest you consider the following examples (expressed as usual in terms of the sample values shown in Fig. 16.3):

Second, we’ve considered updates affecting relvars SP_SINCE and SP_DURING but not ones affecting S_SINCE, S_DURING, or S_STATUS_DURING. However, the behavior of these latter relvars with respect to updating in general doesn’t differ significantly from that of relvars SP_SINCE and SP_DURING. The details are left as another exercise.

Views can Help

It’s clear from the discussions in the foregoing sections that updating a temporal database has the potential to be a seriously complicated matter. Among other things, we’ve seen that it rarely seems to be possible to talk straightforwardly in terms of adding, removing, or replacing tuples, as we usually (albeit informally) do; rather, it seems to make more sense to talk in terms of adding, removing, or replacing propositions. And we’ve also seen in particular that if the database involves a mixture of since and during relvars, then:

So can we provide some syntactic shorthands to make life a little less daunting for the user in this potentially complex area? We believe the answer to this question is yes. To be specific, we believe the automatic provision of views as discussed in the section “Views Can Help” in Chapter 15 can help with updates, just as it can with queries.¹¹ We content ourselves with a single example, making use of view S_STATUS_DURING' (see Chapter 15 for a definition of that view).

Update U18: Supplier S1’s status has just changed to 25. Update the database accordingly.

UPDATE S_STATUS_DURING’ WHERE SNO = SNO ( 'S1' ) :

PORTION { DURING { INTERVAL_DATE ( [ TODAY ( ) : LAST_DATE ( ) ] ) } } : { STATUS := 25 } ;

Suppose that, prior to the foregoing update, view S_STATUS_DURING’ contains the following “current” tuple (possibly other tuples as well) for supplier S1:

Suppose too that today is day 10. After the update, then, the relvar—that is, the view—will contain the following two tuples (possibly others as well) for supplier S1:

(More precisely, the underlying base relvars, S_SINCE and S_STATUS_DURING, will have been updated in such a way as to cause these two tuples both to appear in the view.)

We conjecture that a mechanism along the lines of that sketched above should be sufficient for the system to be able to conceal from the user much of the complexity described in earlier sections (in the section “Both Since and During Relvars” in particular).

Exercises

Answers