Keywords

“temporal” query; current relvar; historical relvar; automated view

The Twelve Queries

Of course, we’ve seen many examples of queries in this book already, especially in Chapters 7-11. However, most of those earlier examples were intended primarily to illustrate the functionality of some specific operator; also, they all involved during or “historical” relvars specifically, or at any rate relvars (or relations) with interval attributes. In this chapter, by contrast, we want to concentrate on how we might formulate a variety of arguably more realistic queries on a more complete database. For purposes of future reference, we list below the queries we’ll be considering.

Query Q1: Get the status of supplier S1 on day dn.

Query Q2: Get pairs of supplier numbers such that the indicated suppliers were assigned their current status on the same day.

Query Q3: Get supplier numbers for suppliers currently able to supply both part P1 and part P2.

Query Q4: Get supplier numbers for suppliers not currently able to supply both part P1 and part P2.

Query Q5: Get supplier numbers for suppliers currently able to supply some part who have changed their status since they most recently became able to supply some part.

Query Q6: Get intervals during which at least one supplier was under contract.

Query Q7: Suppose the result of Query Q6 is kept as a relvar BUSY. Use BUSY to get intervals during which no supplier was under contract at all.

Query Q8: Get supplier numbers for suppliers currently under contract who also had an earlier contract.

Query Q9: Get SNO-PARTS-DURING triples such that the indicated supplier was able to supply the indicated range of parts during the indicated interval.

Query Q10: Suppose the result of Query Q9 is kept as a relvar S_PARTS_DURING. Use S_PARTS_DURING to get SNO-PNO-DURING triples such that the indicated supplier was able to supply the indicated part during the indicated interval.

Query Q11: Given relvar TERM, with attributes DURING, PRESIDENT, and TERMNO, and both {DURING} and {PRESIDENT,TERMNO} as keys, get DURING-PRESIDENT pairs such that the indicated president held office throughout the indicated interval.

Query Q12: Given relvar TERM as in Query Q11, get pairs of presidents who held office in the same year (in the sense that, e.g., Ford and Carter held office in the same year, because Ford’s term ended when Carter’s began, on January 20th, 1977).

Incidentally, it’s worth noting that certain of these queries—Queries Q6, Q7, and Q9, for example—can be handled only rather clumsily (possibly not at all, in some cases) in certain of the other temporal database approaches described in the literature. The reason is that the approaches in question typically violate The Information Principle (see Chapter 4) by treating timestamps in general, and interval timestamps in particular, as special in some way, instead of representing them by regular relational attributes. See reference [29] for further discussion.

The structure of the chapter is as follows. Following these preliminary remarks, the next three sections show what the sample queries might look like on a database involving (a) since (“current”) relvars only, (b) during (“historical”) relvars only, or (c) a mixture of both. The final section then considers the possibility of providing a collection of predefined views in order to simplify the formulation of certain kinds of queries, thereby making the user’s life a little easier than it otherwise might be.

Since Relvars Only

As just indicated, in this section we limit our attention to a version of the database that contains since or “current” relvars only. Fig. 15.1, a copy of Fig. 14.3, shows the database definition (in outline), pertinent constraints included. We remind you that this database can’t represent information about the past at all, other than whatever might be conveyed by the “since” values; on the other hand, it can definitely represent future information (certainly implicitly, and possibly explicitly as well). Refer to Fig. 14.2 in Chapter 14 for some sample values.

Perhaps we should explain at the outset that some of the query formulations in this section aren’t really very temporal in nature (after all, the database itself is only semitemporal). Part of the point, though, is to pave the way for the discussion of fully temporal databases in later sections. Also, we ignore Queries Q11 and Q12, since they assume the existence of a fully temporal relvar (namely, TERM) and thus aren’t in the spirit of a merely semitemporal database like that of Fig. 15.1.

Query Q1: Get the status of supplier S1 on day dn.

First we remind you that if some S_SINCE tuple says supplier Sx has had (or will have) status st from day d onward, we interpret that tuple as meaning that supplier Sx had, has, or will have status st on every day from day d until “the last day.” It follows that Query Q1 can be answered from the database of Fig. 15.1 if and only if (a) S_SINCE contains a (necessarily unique) tuple for supplier S1 and (b) the STATUS_SINCE value in that tuple is less than or equal to dn. Here then is a suitable formulation:

( S_SINCE WHERE SNO = SNO ( 'S1' ) AND STATUS_SINCE ≤ dn ) { STATUS }

If this expression yields an empty result, it means that either (a) S_SINCE contains no tuple for S1 or (b) S_SINCE does contain a tuple for S1 but the STATUS_SINCE value in that tuple is greater than dn. Both of these possibilities mean the query can’t be answered, because the information isn’t in the database.

Note: We’re assuming in this example—and we’ll continue to assume throughout the rest of the chapter—that results are always required in relational form, even when (as in the present case) a simple scalar result might serve the purpose.

Query Q2: Get pairs of supplier numbers such that the indicated suppliers were assigned their current status on the same day.

The first point to make here is that the natural language version of this query ought really to be stated in terms of suppliers who were or will be assigned their current status on the same day; also, the qualifier “current” has to be understood as meaning current at the time in question. For simplicity, however, we’ll ignore such refinements for the most part, both in this example and throughout the remainder of this chapter (in our natural language statements, that is, but not, of course, in their formal counterparts).

Observe next that a given supplier has a current status if and only if there exists a tuple for that supplier in S_SINCE—and if such a tuple does exist, then the STATUS_SINCE value in that tuple gives the date when that current status was assigned to that supplier. Hence:

WITH ( t1 := S_SINCE { SNO , STATUS_SINCE } ,

    t2 := t1 RENAME { SNO AS XNO } ,

    t3 := t1 RENAME { SNO AS YNO } ,

    t4 := t2 JOIN t3 ,

    t5 := t4 WHERE XNO < YNO ) :

t5 { XNO, YNO }

Note: Line 5 here requests a restriction of t4 to just those tuples where supplier number XNO is less than supplier number YNO. Well, we’ve seen this trick before, in Chapter 2 and elsewhere; the idea is to eliminate pairs of supplier numbers of the form (x,x) and to guarantee that the pairs (x,y) and (y,x) don’t both appear. Of course, the operator “<” must be defined for type SNO in order for this trick to work.

Query Q3: Get supplier numbers for suppliers currently able to supply both part P1 and part P2.

A formulation of this query is straightforward:

WITH ( t1 := ( SP_SINCE WHERE PNO = PNO ( 'P1' ) ) { SNO } ,

    t2 := ( SP_SINCE WHERE PNO = PNO ( 'P2' ) ) { SNO } ) :

t1 JOIN t2

We could replace JOIN in the last line here by INTERSECT if we liked.

Query Q4: Get supplier numbers for suppliers not currently able to supply both part P1 and part P2.

A formulation of this query is easily obtained by extending the formulation for Query Q3 slightly:

WITH ( t1 := ( SP_SINCE WHERE PNO = PNO ( 'P1' ) ) { SNO } ,

    t2 := ( SP_SINCE WHERE PNO = PNO ( 'P2' ) ) { SNO } ,

    t3 := t1 JOIN t2 ) :

S_SINCE { SNO } MINUS t3

Query Q5: Get supplier numbers for suppliers currently able to supply some part who have changed their status since they most recently became able to supply some part.

Suppliers who are currently able to supply some part are represented in SP_SINCE, and the date when they most recently became able to do so can also be obtained from that relvar. Furthermore, the date when they acquired their current status—i.e., the date of their most recent status change, in effect—is given in S_SINCE. Hence:

WITH ( t1 := EXTEND SP_SINCE { SNO } : { MRC := MAX ( !!SP_SINCE , SINCE ) } ,

    t2 : S_SINCE JOIN t1 ,

    t3 : t2 WHERE STATUS_SINCE > MRC ) :

t3 { SNO }

Query Q6: Get intervals during which at least one supplier was under contract.

Given only the semitemporal database of Fig. 15.1, the best attempt we can make at answering this query is just to say that if the earliest SNO_SINCE date in relvar S_SINCE is d, then at least one supplier is under contract on every day from d to “the last day” (inclusive):

RELATION { TUPLE { DURING INTERVAL_DATE

    ( [ MIN ( S_SINCE , SNO_SINCE ) : LAST_DATE ( ) ] ) } }

Explanation: The expression overall here is a relation selector invocation of the form RELATION {TUPLE {DURING INTERVAL_DATE ([b:e])}}; it returns a relation with just one attribute, called DURING, and just one tuple. The expression within the outermost set of braces is a tuple selector invocation; it denotes the sole tuple in that single-tuple relation. That tuple in turn has just one component, called DURING. The DURING value in that tuple is specified by means of the interval selector invocation INTERVAL_DATE ([b:e]), where the b value is obtained by means of an invocation of the aggregate operator MIN—returning the minimum value of attribute SNO_SINCE within relvar S_SINCE—and the e value is obtained by means of an invocation of LAST_DATE.

However, there’s a problem with the foregoing formulation. In fact, that formulation will suffice, just so long as we can be sure that S_SINCE is nonempty. If it is empty, however, then (as explained in reference [45]) the MIN invocation will raise an exception—or, at least, so we assume for present purposes.¹ So if we can’t assume S_SINCE is nonempty, a more complicated formulation becomes necessary:

WITH ( t1 := EXTEND S_SINCE { } : { EARLIEST := MIN ( S_SINCE , SNO_SINCE ) } ,

    t2 := EXTEND t1 : { DURING := INTERVAL_DATE ( [ EARLIEST : LAST_DATE ( ) ] ) } ) :

t2 { DURING }

Explanation: The first step here extends the projection of S_SINCE on no attributes. That projection is either TABLE_DUM or TABLE_DEE—TABLE_DUM if S_SINCE is empty, TABLE_DEE otherwise [25]. If it’s TABLE_DUM, then t1 is empty too (and the MIN operator is never invoked);² otherwise, t1 contains just one tuple. And if t1 is empty, so is t2, and so is the overall result; otherwise, t2 (like t1) contains just one tuple, and so does the overall result.

Query Q7: Suppose the result of Query Q6 is kept as a relvar BUSY. Use BUSY to get intervals during which no supplier was under contract at all.

If we can assume BUSY is nonempty, then it’ll actually contain just one tuple, and the following formulation will suffice:

RELATION { TUPLE { DURING INTERVAL_DATE

  ( [ FIRST_DATE ( ) : PRE ( DURING FROM ( TUPLE FROM BUSY ) ) ] ) } }

Explanation: Again the overall expression here is a relation selector invocation of the form RELATION {TUPLE {DURING INTERVAL_DATE ([b:e])}}; thus, it returns a relation with one attribute, called DURING, and one tuple. The sole DURING value within that relation is specified by means of an interval selector invocation of the form INTERVAL_DATE ([b:e]), where the b value is obtained by means of an invocation of FIRST_DATE and the e value is the date immediately preceding the begin point of the sole DURING value in BUSY. Note: See Chapter 2 if you need to refresh your memory regarding the meaning of expressions like DURING FROM (TUPLE FROM BUSY).

This time, however, there are two problems with the formulation as shown. In fact, that formulation will suffice so long as we can be sure that (a) BUSY is nonempty and (b) the sole interval it contains doesn’t have “the beginning of time” as its begin point (it will, of course, have “the end of time” as its end point). Here by contrast is a more complicated formulation that works correctly in all cases:

USING ( DURING ) : RELATION { TUPLE { DURING INTERVAL_DATE ( [ FIRST_DATE ( ) : LAST_DATE ( ) ] ) } }

        MINUS BUSY

This expression makes use of a U_MINUS operation. However, we would certainly prefer that the implementation not physically materialize the results of the two UNPACKs implied by that operation—especially in the case of the left operand, since the unpacked form of that operand contains a tuple for every day from the beginning of time to the end of time inclusive! (See Appendix E for a discussion of implementation matters in general.) An alternative but ugly formulation that explicitly avoids such possible inefficiencies is:

CASE

  WHEN IS_EMPTY ( BUSY ) THEN

     RELATION { TUPLE { DURING INTERVAL_DATE ( [ FIRST_DATE ( ) : LAST_DATE ( ) ] ) } }

  WHEN IS_EMPTY ( BUSY WHERE BEGIN ( DURING ) > FIRST_DATE ( ) ) THEN

     RELATION { DURING INTERVAL_DATE } { }

  ELSE RELATION { TUPLE { DURING INTERVAL_DATE ( [ FIRST_DATE ( ) : PRE ( DURING FROM ( TUPLE FROM BUSY ) ) ] ) } }

END CASE

Query Q8: Get supplier numbers for suppliers currently under contract who also had an earlier contract.

Given only the semitemporal database of Fig. 15.1, this query can’t be answered (in fact, it can’t even be formulated), because the information isn’t in the database.

Query Q9: Get SNO-PARTS-DURING triples such that the indicated supplier was able to supply the indicated range of parts during the indicated interval.

The result of this query will look like—more precisely, it’ll be of the same type as—relvar S_PARTS_DURING from the section “A More Searching Example” in Chapter 6 (see Fig. 6.2). Here’s a possible formulation:

WITH ( t0 := EXTEND SP_SINCE :

      { PARTS := INTERVAL_PNO ( [ PNO : PNO ] ) , DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } ) :

USING ( PARTS , DURING ) : t0 { SNO , PARTS , DURING }

Note the use of (a) multiple EXTEND and (b) U_projection in this example.

Query Q10: Suppose the result of Query Q9 is kept as a relvar S_PARTS_DURING. Use S_PARTS_DURING to get SNO-PNO-DURING triples such that the indicated supplier was able to supply the indicated part during the indicated interval.

WITH ( t1 := UNPACK S_PARTS_DURING ON ( PARTS ) ,

    t2 := EXTEND t1 : { PNO := POINT FROM PARTS } ) :

USING ( DURING ) : t2 { ALL BUT PARTS }

Note the use of POINT FROM here to extract the single point from a unit interval.

During Relvars Only

Now we turn our attention to a version of the database that contains during or “historical” relvars only. Fig. 15.2, a copy of Fig. 14.6, shows the database definition. 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 to mark the end of any interval whose true end point is currently unknown. Refer to Fig. 14.5 in Chapter 14 for some sample values.

Query Q1: Get the status of supplier S1 on day dn.

This one is straightforward:

( S_STATUS_DURING WHERE SNO = SNO ( 'S1' ) AND dn ∈ DURING ) { STATUS }

Note that this formulation works even if dn is a date in the future. Of course, the result will be empty if supplier S1 isn’t represented in the database at all.

Query Q2: Get pairs of supplier numbers such that the indicated suppliers were assigned their current status on the same day.

A supplier will have some current status if and only if there’s a tuple for that supplier in S_STATUS_DURING with a DURING value that encompasses the date today, in which case the STATUS and BEGIN (DURING) values in that tuple will give that supplier’s current status and the date when that current status was assigned. So:

WITH ( t1 := S_STATUS_DURING WHERE TODAY ( ) ∈ DURING ,

    t2 := ( EXTEND t1 : { D := BEGIN ( DURING ) } ) { SNO , D } ,

    t3 := t2 RENAME { SNO AS XNO } ,

    t4 := t2 RENAME { SNO AS YNO } ,

    t5 := t3 JOIN t4 ,

    t6 := t5 WHERE XNO < YNO ) :

t6 { XNO , YNO }

As in Chapter 14, we’re assuming the availability here of a niladic operator called TODAY that returns today’s date.

Query Q3: Get supplier numbers for suppliers who were able to supply both part P1 and part P2 at the same time.

Here we’ve revised the query slightly to make it a little more typical of a fully temporal database as such; to be specific, we’ve replaced “suppliers currently able” by “suppliers who were able [i.e., to supply both parts] at the same time.” Here’s a suitable formulation:

WITH ( t1 : = ( SP_DURING WHERE PNO = PNO ( 'P1' ) ) { SNO , DURING } ,

    t2 := ( SP_DURING WHERE PNO = PNO ( 'P2' ) ) { SNO , DURING } ,

    t3 := USING ( DURING ) : t1 JOIN t2 ) :

t3 { SNO }

Query Q4: Get supplier numbers for suppliers who were never able to supply both part P1 and part P2 at the same time.

Again we’ve revised the query slightly. Here’s a suitable formulation:

WITH ( t1 := ( SP_DURING WHERE PNO = PNO ( ’P1’ ) ) { SNO , DURING } ,

    t2 := ( SP_DURING WHERE PNO = PNO ( 'P2' ) ) { SNO , DURING } ,

    t3 := USING ( DURING ) : t1 JOIN t2 ) :

S_DURING { SNO } MINUS t3 { SNO }

Query Q5: Get supplier numbers for suppliers who, while they were under some specific contract, changed their status since they most recently became able to supply some part under that contract.

Once again we’ve revised the original query; what’s more, the revised version is now quite complicated. In particular, note the requirement that both specified events (changing status, becoming able to supply some part) must have occurred while the same contract was in effect. This requirement accounts for the restriction operations in lines 3 and 6 below.

WITH ( t1 := ( S_STATUS_DURING RENAME { DURING AS X } ) { SNO , X } ,

    t2 := t1 JOIN S_DURING ,

    t3 := t2 WHERE X ⊆ DURING ,

    t4 := ( SP_DURING RENAME { DURING AS Y } ) { SNO , Y } ,

    t5 := t4 JOIN t3 ,

    t6 := t5 WHERE Y ⊆ DURING ,

    t7 := EXTEND t6 { SNO , DURING } :

         { BXMAX := MAX ( !!t6 , BEGIN ( X ) ) , BYMAX := MAX ( !!t6 , BEGIN ( Y ) ) } ,

    t8 := t7 WHERE BXMAX > BYMAX ) :

t8 { SNO }

Query Q6: Get intervals during which at least one supplier was under contract.

USING ( DURING ) : S_DURING { DURING }

Or, more straightforwardly, just:

PACK S_DURING { DURING } ON ( DURING )

Query Q7: Suppose the result of Query Q6 is kept as a relvar BUSY. Use BUSY to get intervals during which no supplier was under contract at all.

USING ( DURING ) : RELATION { TUPLE { DURING INTERVAL_DATE ( [ FIRST_DATE ( ) : LAST_DATE ( ) ] ) } }

        MINUS BUSY

This formulation is identical to the “best” formulation given for this query in the previous section.

Query Q8: Get supplier numbers for suppliers currently under contract who also had an earlier contract.

WITH ( t1 := ( S_DURING WHERE TODAY ( ) ∈ DURING ) { SNO } ,

    t2 := ( S_DURING WHERE TODAY ( ) > END ( DURING ) ) { SNO } ) :

t1 JOIN t2

Query Q9: Get SNO-PARTS-DURING triples such that the indicated supplier was able to supply the indicated range of parts during the indicated interval.

WITH ( t0 := EXTEND SP_DURING : { PARTS := INTERVAL_PNO ( [ PNO : PNO ] ) } ) :

USING ( PARTS, DURING ) : t0 { SNO , PARTS , DURING }

Query Q10: Suppose the result of Query Q9 is kept as a relvar S_PARTS_DURING. Use S_PARTS_DURING to get SNO-PNO-DURING triples such that the indicated supplier was able to supply the indicated part during the indicated interval.

WITH ( t1 := UNPACK S_PARTS_DURING ON ( PARTS ) ,

    t2 := EXTEND t1 : { PNO := POINT FROM PARTS } ) :

USING ( DURING ) : t2 { ALL BUT PARTS }

This expression is identical to its counterpart in the previous section.

Query Q11: Given relvar TERM, with attributes DURING, PRESIDENT, and TERMNO, and both {DURING} and {PRESIDENT,TERMNO} as keys, get DURING-PRESIDENT pairs such that the indicated president held office throughout the indicated interval.

PACK TERM { DURING, PRESIDENT } ON ( DURING )

Query Q12: Given relvar TERM as in Query Q11, get pairs of presidents who held office in the same year (in the sense that, e.g., Ford and Carter held office in the same year, because Ford’s term ended when Carter’s began—namely, on January 20th, 1977).

WITH ( t1 := TERM RENAME { DURING AS D1 , PRESIDENT AS P1 } ,

    t2 := TERM RENAME { DURING AS D2 , PRESIDENT AS P2 } ,

    t3 := ( t1 JOIN t2 ) WHERE P1 ≠ P2 AND END ( D1 ) = PRE ( D2 ) ) :

t3 { P1 , P2 }

Both Since and During Relvars

In this section, we consider a version of the database containing both 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. 15.3, an edited version of Fig. 14.7, shows the database definition, including some but not all of the pertinent constraints. Refer to Fig. 14.8 in Chapter 14 for some sample values.

In contrast to the version of the database discussed in the previous section (“During Relvars Only”), it’s important to understand that:

The queries that follow are identical in their natural language form to their counterparts from the previous section. Because of the foregoing bullet points, however, the corresponding formal expressions are sometimes different. Also, we ignore Queries Q7 and Q10-Q12, because the formal versions of those queries are identical to their counterparts in the previous section.

Query Q1: Get the status of supplier S1 on day dn.

The difficulty here is that we don’t know, in general, whether the answer to the query is to be found in S_SINCE or S_STATUS_DURING. Hence:

WITH ( t1 := S_SINCE WHERE SNO = SNO ( 'S1' ) ) ,

    t2 := EXTEND t1 : { DURING := INTERVAL_DATE ( [ STATUS_SINCE : LAST_DATE ( ) ] } ,

    t3 := t2 { STATUS , DURING } ,

    t4 := S_STATUS_DURING WHERE SNO = SNO ( 'S1' ) ,

    t5 := t4 { STATUS , DURING } ,

    t6 := t3 UNION t5 ) :

( t6 WHERE dn ∈ DURING ) { STATUS }

An alternative formulation, involving in effect an explicit test to see which relvar contains the desired information, might look like this:

WITH ( t := TUPLE FROM ( S_SINCE WHERE SNO = SNO ( 'S1' ) ) ,

    s := STATUS FROM t ,

    d := STATUS_SINCE FROM t ) :

IF dn ≥ d THEN RELATION { TUPLE { STATUS s } }

     ELSE ( S_STATUS_DURING WHERE SNO = SNO ( 'S1' ) AND dn ∈ DURING ) { STATUS }

END IF

Note: This is the first time we’ve seen code in this book in which the assignments in a WITH specification have been anything other than relational assignments as such (to be specific, the assignment to t here is a tuple assignment and the assignments to s and d are scalar assignments). More to the point, note that—unlike the first formulation above—this alternative formulation of the query relies on the strong, and in general unwarranted, assumption that there’s a tuple for supplier S1 in relvar S_SINCE. The first formulation is to be preferred.

For interest, we show yet another possible formulation of this query, one that (like the one we showed first) doesn’t rely on the existence of a tuple for supplier S1 in relvar S_SINCE:

WITH ( t0 := S_SINCE WHERE SNO = SNO ( 'S1' ) ) :

IF IS_NOT_EMPTY ( t0 )

  THEN t0 { STATUS }

  ELSE ( S_STATUS_DURING WHERE SNO = SNO ( 'S1' ) AND dn ∈ DURING ) { STATUS }

END IF

Query Q2: Get pairs of supplier numbers such that the indicated suppliers were assigned some status on the same day.

Here we’ve revised the query slightly in order to illustrate another complication that arises from the combination design. The complication is this: S_SINCE might show some supplier Sx as having been assigned some current status on some given day d, while S_STATUS_DURING shows some distinct supplier Sy as having been assigned some historical status on that same day d. (Analogous remarks apply to Queries Q3-Q6 as well, as we’ll see.) Hence:

WITH ( t1 := ( EXTEND S_STATUS_DURING : { STATUS_SINCE := BEGIN ( DURING ) } ) { SNO , STATUS_SINCE } ,

    t2 := t1 UNION S_SINCE { SNO , STATUS_SINCE } ,

    t3 := t2 RENAME { SNO AS XNO } ,

    t4 := t2 RENAME { SNO AS YNO } ,

    t5 := t3 JOIN t4 ,

    t6 := t5 WHERE XNO < YNO ) :

t6 { XNO, YNO }

Query Q3: Get supplier numbers for suppliers who were able to supply both part P1 and part P2 at the same time. (This isn’t the original version of this query but the revised version from the section “During Relvars Only.”)

WITH ( t1 := ( EXTEND SP_SINCE : { DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } ) { SNO , PNO , DURING } ,

    t2 := SP_DURING UNION t1 ,

    t3 := ( t2 WHERE PNO = PNO ( 'P1' ) ) { SNO , DURING } ,

    t4 := ( t2 WHERE PNO = PNO ( 'P2' ) ) { SNO , DURING } ,

    t5 := USING ( DURING ) : t3 JOIN t4 ) :

t5 { SNO }

Query Q4: Get supplier numbers for suppliers who were never able to supply both part P1 and part P2 at the same time. (This isn’t the original version of this query but the revised version from the section “During Relvars Only.”)

WITH ( t1 := ( EXTEND SP_SINCE : { DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } ) { SNO , PNO , DURING } ,

    t2 := SP_DURING UNION t1 ,

    t3 := ( t2 WHERE PNO = PNO ( 'P1' ) ) { SNO, DURING } ,

    t4 := ( t2 WHERE PNO = PNO ( 'P2' ) ) { SNO, DURING } ,

    t5 := USING ( DURING ) : t3 JOIN t4 ) :

t2 { SNO } MINUS t5 { SNO }

Query Q5: Get supplier numbers for suppliers who, while they were under some specific contract, changed their status since they most recently became able to supply some part under that contract. (This isn’t the original version of this query but the revised version from the section “During Relvars Only.”)

WITH ( t1 := ( EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ SNO_SINCE : LAST_DATE ( ) ] } ) { SNO , DURING } ,

    t2 := t1 UNION S_DURING ,

    t3 := ( EXTEND S_SINCE : { DURING := INTERVAL_DATE ( [ STATUS_SINCE : LAST_DATE ( ) ] } ) { SNO , STATUS , DURING } ,

    t4 := t3 UNION S_STATUS_DURING ,

    t5 := ( EXTEND SP_SINCE : { DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] } ) { SNO , PNO , DURING } ,

    t6 := t5 UNION SP_DURING ,

    t7 := ( t4 RENAME { DURING AS X } ) { SNO , X } ,

    t8 := t7 JOIN t2 ,

    t9 := t8 WHERE X ⊆ DURING ,

    t10 := ( t6 RENAME { DURING AS Y } ) { SNO , Y } ,

    t11 := t10 JOIN t9 ,

    t12 := t11 WHERE Y ⊆ DURING,

    t13 := EXTEND t12 { SNO , DURING } : { BXMAX := MAX ( !!t12, BEGIN ( X ) ) , BYMAX := MAX ( !!t12 , BEGIN ( Y ) ) } ,

    t14 := t13 WHERE BXMAX > BYMAX ) :

t14 { SNO }

Query Q6: Get intervals during which at least one supplier was under contract.

WITH ( t1 := EXTEND S_SINCE : { DURING := INTERVAL ( [ SNO_SINCE : LAST_DATE ( ) ] ) } ,

    t2 := t1 { SNO, DURING } UNION S_DURING ) :

USING ( DURING ) : t2 { DURING }

Query Q8: Get supplier numbers for suppliers currently under contract who also had an earlier contract.

( S_SINCE JOIN S_DURING ) { SNO }

Query Q9: Get SNO-PARTS-DURING triples such that the indicated supplier was able to supply the indicated range of parts during the indicated interval.

WITH ( t1 := EXTEND SP_SINCE : { PARTS := INTERVAL_PNO ( [ PNO : PNO ] ) , DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } ,

    t2 := t1 { SNO , PARTS , DURING } ,

    t3 := EXTEND SP_DURING : { PARTS := INTERVAL_PNO ( [ PNO : PNO ] ) } ,

    t4 := t3 { SNO, PARTS, DURING } ) :

USING ( PARTS, DURING ) : t2 UNION t4

Views Can Help

It’s undeniable that several of the query formulations shown in the previous section were fairly complicated: more complicated, in all likelihood, than some users will be prepared to deal with. Yet the database we were discussing in that section made use of what we repeatedly said in previous chapters was our preferred design! In this section, we explore the possibility of making life a little easier for the user by predefining a suitable collection of views (also known as virtual relvars) for such a database.

Broadly speaking, the views we have in mind have the effect of conceptually undoing the horizontal and vertical decompositions described in Chapter 12. Of course, it’s important to understand that the decompositions are indeed only conceptually undone; the relvars that result do have the effect of making certain queries easier to state, but they don’t contravene the design recommendations of Chapter 12. Indeed, what we’re proposing here is a perfectly standard technique. For example, given the original nontemporal version of the suppliers-and-shipments database from Chapter 1, we might well define the join of suppliers and shipments as a view in order to simplify the formulation of certain queries, even though such a view would violate well known principles of normalization.³

Unfortunately, our running example is a little too simple to illustrate the foregoing ideas properly, so we need to extend that example slightly. Let’s therefore bring supplier city information back into the picture by (a) reinstating attributes CITY and CITY_SINCE in relvar S_SINCE and (b) reinstating relvar S_CITY_DURING, with its attributes SNO, CITY, and DURING, thus:⁴

S_SINCE { SNO , SNO_SINCE , STATUS , STATUS_SINCE , CITY , CITY_SINCE }

    KEY { SNO }

S_CITY_DURING { SNO , CITY , DURING } USING ( DURING ) : KEY { SNO , DURING }

Providing sample data values for these two relvars is left as an exercise.

Now we can explain the views we have in mind. First, we define four views S_DURING’, S_STATUS_DURING’, S_CITY_DURING’, and SP_DURING’ that effectively combine current and historical information, thereby undoing the original horizontal decompositions:⁵

VAR S_DURING' VIRTUAL

 ( S_DURING UNION

  ( EXTEND S_SINCE :

   { DURING := INTERVAL_DATE ( [ SNO_SINCE : LAST_DATE ( ) ] ) } ) { SNO , DURING } ) … ;

VAR S_STATUS_DURING’ VIRTUAL

 ( S_STATUS_DURING UNION

  ( EXTEND S_SINCE :

   { DURING := INTERVAL_DATE ( [ STATUS_SINCE : LAST_DATE ( ) ] ) } ) { SNO , STATUS , DURING } ) … ;

VAR S_CITY_DURING’ VIRTUAL

 ( S_CITY_DURING UNION

  ( EXTEND S_SINCE :

   { DURING := INTERVAL_DATE ( [ CITY_SINCE : LAST_DATE ( ) ] ) } ) { SNO , CITY , DURING } ) … ;

VAR SP_DURING’ VIRTUAL

 ( SP_DURING UNION

  ( EXTEND SP_SINCE :

   { DURING := INTERVAL_DATE ( [ SINCE : LAST_DATE ( ) ] ) } ) { SNO , PNO , DURING } ) … ;

Next, we define a view S” that effectively combines supplier contract, status, and city information, thereby undoing the original vertical decomposition:

VAR S’’ VIRTUAL

 ( USING ( DURING ) : S_STATUS_DURING’ JOIN S_CITY_DURING’ ) … ;

Note: There’s no need to include S_DURING’ in the U_join here, because the unpacked form of S_DURING’ on DURING is equal to both:

Thus, no information would be added—or lost—if S_DURING’ were included.

For completeness, let’s also define a view SP’’ that effectively does for shipments what S” does for suppliers:

VAR SP’’ VIRTUAL ( SP_DURING’ ) … ;

(Since shipments were in fact never vertically decomposed in the first place, views SP’’ and SP_DURING’ are identical, of course.)

Fig. 15.4 shows the structure, in outline, of all of these views. Note the U_key specifications in particular.⁶ As an exercise, you might like to try stating the corresponding predicates (for all of the views in the figure). You might also like to think about any foreign U_key relationships that might exist among those views.

We now proceed to reconsider Queries Q1-Q10 in terms of these views (Queries Q11 and Q12 remain unchanged, of course).

Query Q1: Get the status of supplier S1 on day dn.

This one is now very easy:

( S’’ WHERE SNO = SNO ( 'S1' ) AND dn ∈ DURING ) { STATUS }

Note: We could have specified relvar S_STATUS_DURING’ in place of relvar S’’—it would make no difference to the result.

Query Q2: Get pairs of supplier numbers such that the indicated suppliers were assigned some status on the same day. (This is the version of the query we discussed in connection with the combination design.)

WITH ( t1 := ( EXTEND S_STATUS_DURING' : { D := BEGIN ( DURING ) } ) { SNO, D } ,

    t2 := t1 RENAME { SNO AS XNO } ,

    t3 := t1 RENAME { SNO AS YNO } ,

    t4 := t2 JOIN t3 ,

    t5 := t4 WHERE XNO < YNO ) :

t6 { XNO , YNO }

This expression is very similar to the one shown for this query in the section “Both Since and During Relvars,” except that (a) the reference to relvar S_STATUS_DURING has been replaced by one to relvar S_STATUS_DURING’ and (b) there’s now no explicit reference to relvar S_SINCE at all. Observe in particular that, by contrast with what we had to do in that previous section, there’s now no need to give special attention to current information.

Query Q3: Get supplier numbers for suppliers who were able to supply both part P1 and part P2 at the same time. (This the revised version of the query we discussed in the sections “During Relvars Only” and “Both Since and During Relvars.”)

WITH ( t1 := ( SP" WHERE PNO = PNO ( 'P1' ) ) { SNO , DURING } ,

    t2 := ( SP" WHERE PNO = PNO ( 'P2' ) ) { SNO , DURING } ,

    t3 := USING ( DURING ) : t1 JOIN t2 ) :

t3 { SNO }

This expression is considerably simpler than the one shown in the previous section; in fact, it’s essentially identical—as is only to be expected—to the one shown in the section “During Relvars Only.” A similar remark applies to Queries Q4-Q6 (also to Query Q9), and we therefore omit detailed consideration of those cases. So let’s move on to Query Q7.

Query Q7: Suppose the result of Query Q6 is kept as a relvar BUSY. Use BUSY to get intervals during which no supplier was under contract at all.

The views obviously don’t help with this query (the formulation remains as it was before, in the sections “During Relvars Only” and “Both Since and During Relvars”).

Query Q8: Get supplier numbers for suppliers currently under contract who also had an earlier contract.

This query is actually harder to express using the views than it was before (as a comparison of the formulations in the previous two sections should lead you to expect).

Query Q10: Suppose the result of Query Q9 is kept as a relvar S_PARTS_DURING. Use S_PARTS_DURING to get SNO-PNO-DURING triples such that the indicated supplier was able to supply the indicated part during the indicated interval.

As with Query Q7, the views don’t help with this query (the formulation remains as it was in the section “During Relvars Only”).

Exercises

Write Tutorial D expressions for the following queries on the version of the courses-and-students database from Exercise 13.7 in Chapter 13.

Answers