■ We’ve renamed the “from” and “to” columns DFROM and DTO, respectively (D for during). Note: FROM and TO as such are reserved words in SQL and so can’t be used as column names without being enclosed in what the standard calls double quotes (in other words, quotation marks). As for the tables themselves, we’ve named them S_FROM_TO and SP_FROM_TO as in Chapter 5, in order to stress the point that they do indeed have explicit “from” and “to” columns instead of a single “during” column.

■ We’ve replaced all of those d10 “end of time” markers by the slightly more reasonable value d99. Note: As far as SQL is concerned, the actual end of time is DATE ‘9999–12–31’ and the beginning of time is DATE ‘0001–01–01’ (both values given here in the form of DATE literals, which by definition are accurate to the day—though SQL does also support finer granularities, of course, as we’ll see later). Throughout this chapter, therefore, references to d99 should be taken as denoting “the last day” as understood by SQL (i.e., December 31st, 9999).

■ As we’ve said, an SQL period is denoted by an explicit pair of “from” and “to” values, not by a single interval value. As a consequence, SQL has to specify the intended interpretation for such value pairs, and it plumps for the closed:open interpretation; that is, the “from” value denotes the first point contained in the period, and the “to” value denotes the point immediately following the last point contained in the period. For this reason, we’ve increased all of the “to” values in the figure (other than those “end of time” markers) by one day.

■ Following on from the previous point, however, there’s a serious oddity here. Precisely because periods in SQL do use the closed:open interpretation, there’s no way “the end of time” can actually be contained in such a period! If, as in Fig. 19.1, we specify a “to” value of d99, then the last day that’s actually contained in the period in question is d98, which of course isn’t “the end of time.” On the other hand, we can’t specify a “to” value of d100, because d100 isn’t legal as a DATE value in SQL.² To put the point another way, no “from” value can ever be equal to d99, and SQL has no way of representing the fact that some state of affairs holds on day d99.

Many treatments of temporal databases introduce a period data type,³ defined as an ordered pair of two datetime values … SQL:2011 has not taken this route. Adding a new data type to the SQL standard (or to an SQL product) is a costly venture because of the need to support the new data type in the tools and other software packages that form the ecosystem surrounding SQL. For example, if a period type were added to SQL, then it would have to also be added to the stored procedure language, to all database APIs such as JDBC, ODBC, and .NET, as well as to the surrounding technology such as ETL products, replication solutions, and others. There must also be some means of communicating period values to host languages that do not support period as a native data type, such as C or Java. These factors can potentially slow down the adoption of a new type for a long time.

■ Table S_FROM_TO is, of course, a base table, in SQL terms. For simplicity, we’ll adopt the convention throughout this chapter that the term table, unqualified, means a base table specifically, unless the context demands otherwise.

■ The PERIOD FOR specification defines columns DFROM and DTO to be the boundary columns for a period called DPERIOD. Note very carefully that DPERIOD itself is not a column. However, period names are allowed to appear in just one context where column references are permitted: They can be used as period references within certain boolean expressions, typically within a WHERE clause (see the discussion of Allen’s operators, later in this section).⁵

■ The columns that are paired in a PERIOD FOR specification must both be of the same data type—either DATE, as in the example, or some SQL timestamp type such as TIMESTAMP(6).⁶ As already indicated, therefore, SQL’s periods are specifically temporal in nature; there’s no support for intervals (or periods, rather) involving integers, or heights, or money values, or indeed anything else at all.

■ It follows from the previous point that the only point types supported in SQL are dates and SQL-style timestamps. For simplicity, throughout what follows we’ll concentrate on type DATE only and ignore the other possibilities (i.e., we’ll assume from this point forward that periods are always measured in days specifically), until further notice. Now, we saw in Chapter 6 that a point type requires niladic first and last operators and monadic next and prior operators. For type DATE, invocations of the first and last operators can be represented by literals denoting “the first day” and “the last day,” respectively, as we’ve already seen. As for next (the successor function), the operator for adding one day suffices. Here’s a simple example (note the syntax):⁷

■ As already noted, SQL’s periods implicitly use the closed:open interpretation, as in, e.g., [d02,d05). The PERIOD FOR specification in the definition of table S_FROM_TO effectively reflects this state of affairs by guaranteeing that, within any row of that table, the value of DFROM will always be less than that of DTO. More generally, in fact, SQL will enforce the constraint for any period, no matter how or where it’s specified (in a table definition or elsewhere), that the “from” value is strictly less than the “to” value.⁸ Note: The expression [d02,d05) isn’t meant to be actual SQL syntax (we’ll explain that actual syntax when we get to “period selectors,” later in this section). However, it’s at least true, as the example suggests, that SQL uses a comma, not a colon, as a separator.

■ Period DPERIOD in the example represents what SQL calls application time, which is the SQL term for valid time. (Recall from Chapter 17 that our own preferred term for this concept is stated time. In this chapter, however, we’ll stay with the SQL term.)

■ No SQL table can ever have more than one application time period. Now, this rule is perhaps not too unreasonable, given that—as we effectively saw in Chapter 12, in the section “During Relvars Only”—it usually wouldn’t make sense for a table to have more than one, anyway (though it might).⁹ Observe, however, that the table in question had probably better be in 6NF—for otherwise the application time period will probably be “timestamping too much,” as we put it in that discussion in Chapter 12. Note, therefore, that converting a nontemporal design into a satisfactory temporal analog will typically involve rather more than just adding an application time period to each nontemporal table (as indeed we also saw in Chapter 12).

■ We defer detailed explanation of the UNIQUE specification to the next section (“Database Design”).¹⁰ However, we remark that—regardless of whether WITHOUT OVERLAPS is specified—UNIQUE (SNO,DPERIOD…) effectively defines, not a key as such, but rather a proper superkey (see the answer to Exercise 13.2a in Chapter 13).

(Expansion)¹¹ Let row r in table S_STATUS_FROM_TO have SNO, STATUS, DFROM, and DTO values sno, st, df, and dt, respectively. Derive from that row n distinct rows, one for each DATE value d such that df ≤ d < dt; each such derived row contains the SNO value sno, the STATUS value st, and one of those n DATE values, d. Let T be a table containing all such derived rows for all such rows r (and nothing else besides), and let D be the column of T that contains those DATE values d. Then no two rows of that table T have the same (SNO,D) value.

■ First, each of S_FROM_TO and S_STATUS_FROM_TO has a foreign key that references the other. As a consequence, the tables must be kept “in synch,” as it were. Now, the operator we used for such purposes in Chapter 16 was multiple assignment (multiple relvar assignment, to be specific). Unfortunately, however, SQL has no direct support for such an operator, and the task of keeping those SQL tables in synch is thus not entirely straightforward. See the discussion of Updates U12, U13, and U14 in the section “Updates,” later, for further explanation.

■ Second, SQL doesn’t support PACK and UNPACK, and so the user’s job of making sure the various KEY and FOREIGN KEY constraints aren’t violated is rather more difficult than it might otherwise be. In other words, keeping the tables in packed form, assuming that’s what’s wanted, is at least partly the user’s problem—a problem that isn’t made any easier by the fact that, as noted previously, SQL also fails to support the U_update operators (U_INSERT in particular).

1. Let table t1 contain just those rows of SP_FROM_TO that satisfy the boolean expression in the WHERE clause (i.e., the rows for supplier S7 and part P1).

2. Let table t2 contain just those rows of t1 that satisfy the boolean expression DPERIOD OVERLAPS [d04,d12).

3. Let table t3 contain just one row: namely, some row from t2.

4. Let table t4 be the result of unpacking t3 on DPERIOD.

5. Let table t5 be the result of removing from t4 all rows whose DPERIOD values (which are all “unit periods,” of course) are included in [d04,d12).

6. Let table t6 be the result of packing t5 on DPERIOD.

7. Let table t7 be the result of replacing t3, in SP_FROM_TO, by t6.

8. Repeat Steps 3-7 for each row of t2 in turn.

■ t1 consists of the following three rows (we’ve labeled them for future reference):

■ t2 is the same as t1.

■ Let t3 be the table containing just row x1. Then unpacking t3 produces a table t4 containing just two rows, for [d03,d04) and [d04,d05), and removing the rows whose DPERIOD values are included in [d04,d12) produces a table t5 containing just one row, for [d03,d04). Packing t5 then produces t6, which is identical to t5 in this case. Thus, the effect of this step is to replace x1 in SP_FROM_TO by a row for [d03,d04).

■ Next, let t3 be the table containing just row x2. Then unpacking t3 produces a table t4 containing just two rows, for [d06,d07) and [d07,d08), and removing the rows whose DPERIOD values are included in [d04,d12) produces an empty table t5. Packing t5 produces t6, which is also empty. Thus, the effect of this step is simply to remove x2 from SP_FROM_TO.

■ Next, let t3 be the table containing just row x3. Then unpacking t3 produces a table t4 containing 90 rows, for [d09,d10), [d10,d11), …, and [d98,d99). Removing the rows whose DPERIOD values are included in [d04,d12) produces a table t5 containing all except the first three of these 90 rows. Packing t5 produces t6, which contains just one row, for [d12,d99]. Thus, the effect of this step is to replace x3 in SP_FROM_TO by a row for [d12,d99).

■ Observe first that table XS_STATUS_FROM_TO has j ust two “regular” columns, SNO and STATUS. These are the only columns users can update directly (see the subsection “Updates on System-Versioned Tables” below).

■ The system time period—which, as usual in SQL, uses the closed:open interpretation—has the required name SYSTEM_TIME. The PERIOD FOR SYSTEM_TIME specification defines columns XFROM and XTO—which for the sake of the example we’ve defined to be of type TIMESTAMP(12), meaning times that are accurate to the picosecond—to be the boundary columns for that period.

■ The specifications GENERATED ALWAYS AS ROW START (on XFROM) and GENERATED ALWAYS AS ROW END (on XTO) are required. That’s because values of those columns aren’t assigned by explicit user updates in the usual way but are instead assigned automatically by the system (again, see the subsection “Updates on System-Versioned Tables” below).

■ The specification WITH SYSTEM VERSIONING is actually optional, but we’ll treat it as required until further notice. Note, however, that if it appears, then PERIOD FOR SYSTEM_TIME must be specified as well.

■ The remaining specifications are discussed in the next subsection but one, “UNIQUE and FOREIGN KEY Specifications for System-Versioned Tables.”

19.1 Given table XS_STATUS_FROM_TO as defined in the body of the chapter, what does the expression SELECT * FROM XS_STATUS_FROM_TO evaluate to?

19.2 What are the predicates for S_STATUS_FROM_TO and XS_STATUS_FROM_TO?

19.3 Here repeated from Exercise 2.2 in Chapter 2 are Tutorial D definitions for the three relvars in the original version of the courses-and-students database:
VAR COURSE BASE RELATION
 { COURSENO COURSENO , CNAME NAME , AVAILABLE DATE }
  KEY { COURSENO } ;
VAR STUDENT BASE RELATION
  { STUDENTNO STUDENTNO , SNAME NAME , REGISTERED DATE }
  KEY { STUDENTNO } ;
VAR ENROLLMENT BASE RELATION
  { COURSENO COURSENO , STUDENTNO STUDENTNO , ENROLLED DATE }
  KEY { COURSENO , STUDENTNO }
  FOREIGN KEY { COURSENO } REFERENCES COURSE
  FOREIGN KEY { STUDENTNO } REFERENCES STUDENT ;
Observe now that although we didn’t say as much in Chapter 2, this database is actually semitemporal. Give CREATE TABLE statements for a fully temporal SQL analog.

19.4 (Based on Exercises 15.2-15.5 in Chapter 15) Given your answer to Exercise 19.3, write SQL queries for the following:

a. Get student numbers for students currently enrolled on both course C1 and course C2.

b. Get student numbers for students not currently enrolled on both course C1 and course C2.

c. Get intervals during which at least one course was being offered.

d. Get intervals during which no course was being offered at all.

19.5 (Based on the answer to Exercise 16.2 in Chapter 16.) Given your answer to Exercise 19.3, write SQL updates for the following:

a. New courses C542 (“Tutorial D”) and C543 (“Temporal Databases”) became available on June 1st, 2013.

b. Currently available courses C193 (“SQL”) and C203 (“Object Oriented Databases”) were discontinued at the end of May this year (2013).

c. Student ST21, Hugh Manatee, not currently registered with the university, was registered from October 1st, 2011, to June 30th, 2012, but the database doesn’t show that fact.

d. Student ST19 changed her name to Anna Marino on November 18th, 2012.

19.6 Let BSP_FROM_TO be a system-versioned bitemporal analog of table SP_FROM_TO from the body of the chapter. Give a suitable CREATE TABLE statement.

19.7 Using the bitemporal table BS_FROM_TO as defined in the body of the chapter, write SQL queries for the following:

a. When if ever did the database say that supplier S2 was under contract on day 4?

b. On day 8, what did the database say was supplier S2’s term of contract?

19.8 In the body of the chapter, we saw that current rows in tables with system time have a system time period in which the “to” value is represented by “the end of time” (t99 in examples). But system time is just SQL’s term for transaction time or—our preferred term—logged time, and we saw in Chapter 17 that, by definition, transaction times can never refer to the future. How do you reconcile this state of affairs?

19.9 Our explanation of system time (also known as transaction time or—our preferred term—logged time) in the body of the chapter referred to the system clock. However, we did also say, in a footnote, that the standard doesn’t actually mention this concept—it merely says there’s something called the transaction timestamp, which is required to remain constant throughout the life of the transaction in question and is used for the purpose of timestamping rows in tables with system time. How do you think transaction timestamps might be implemented in a real system?

19.1 It evaluates to that restriction of the table that contains current rows only. Note, therefore, that the usual definition, to the effect that SELECT * FROM T returns table T in its entirety, is no longer applicable. Perhaps even more counterintuitively, the expression TABLE T can no longer be said to denote table T(!). Note: In case you’re unfamiliar with this latter SQL construct—i.e., an expression of the form TABLE T—we should explain that it’s defined to be shorthand for SELECT * FROM T. (At least, that’s what the standard says; however, it would probably be more correct to say it’s shorthand for a parenthesized version of this latter expression, thus: (SELECT * FROM T).)

19.2 We don’t have a good answer to this exercise. In fact, we seriously doubt whether a good answer even exists. The essence of the problem is this: Although the tables in question are guaranteed to be free of redundancy (and of course contradiction), they’re not guaranteed to be free of circumlocution. To illustrate the point, let’s focus on table S_STATUS_FROM_TO, for definiteness. Here then is the obvious first attempt at a predicate—let’s call it P—for that table:

Note that we can’t extend this predicate by adding and not throughout any period that properly includes period p, precisely because the table isn’t guaranteed to be kept packed on DPERIOD.
Now, Fig. 19.3 shows S_STATUS_FROM_TO as containing a row—let’s call it row r—indicating that supplier S7 had status 15 throughout the period [d03,d09). However, there are numerous ways of splitting up that period [d03,d09) into smaller, nonoverlapping periods. Here are just a few of them:

■ [d03,d04) [d04,d05) [d05,d06) [d06,d07) [d07,d08) [d08,d09)

■ [d03,d05) [d05,d06) [d06,d07) [d07,d08) [d08,d09)

■ [d03,d04) [d04,d06) [d06,d07) [d07,d08) [d08,d09)

■ [d03,d06) [d06,d08) [d08,d09)

■ [d03,d07) [d07,d09)

And so on. It follows that it would be possible, without violating the WITHOUT OVERLAPS constraint on S_STATUS_FROM_TO, to replace row r by several distinct rows, and to do so, moreover, in several different ways. And every such replacement row—call it r’—would represent a true instantiation of predicate P! So predicate P can’t possibly be right. Why not? Well, recall The Closed World Assumption from Chapter 3. That assumption, translated into SQL terms, says that row r appears in table T at time t if and only if r satisfies the predicate for T at time t (emphasis added). In the case at hand, however, table S_STATUS_FROM_TO clearly isn’t going to contain all of those possible rows r’at the same time, and so predicate P clearly isn’t sufficient, in and of itself, to pin down just which rows do or don’t appear in that table at any given time.
Here’s another predicate we might consider (let’s call it P'):

But predicate P' doesn’t do the job either. To be specific, it’s true—as it was with the previous attempt, predicate P—that if row r appears in the table, then row r necessarily satisfies this predicate; conversely, however, it isn’t true that if row r satisfies this predicate, then row r necessarily appears in the table.
To sum up: It seems to be the case that certain real world situations can be represented by the same SQL table—table S_STATUS_FROM_TO, in the case at hand—in more than one way. This state of affairs begins to look like a rather serious violation of relational principles. To put it another way, avoiding circumlocution seems to be even more important than we originally thought, and SQL tables, precisely because they don’t avoid it, seem to be less than fully respectable, relationally speaking.³²

19.3 In contrast to earlier exercises and answers having to do with this database (see, e.g., Exercise 12.8c in Chapter 12), we choose to go for a pure 6NF design here.
CREATE TABLE COURSE_FROM_TO
   ( COURSENO COURSENO NOT NULL ,
    DFROM DATE NOT NULL ,
    DTO DATE NOT NULL ,
    PERIOD FOR AVAILABLE ( DFROM , DTO ) ,
    UNIQUE ( COURSENO, AVAILABLE WITHOUT OVERLAPS ) ,
    FOREIGN KEY ( COURSENO, PERIOD AVAILABLE )
      REFERENCES COURSE_NAME_FROM_TO ( COURSENO , PERIOD NAMED ) ) ;
CREATE TABLE COURSE_NAME_FROM_TO
   ( COURSENO COURSENO NOT NULL ,
    CNAME NAME NOT NULL ,
    DFROM DATE NOT NULL ,
    DTO DATE NOT NULL ,
    PERIOD FOR NAMED ( DFROM , DTO ) ,
    UNIQUE ( COURSENO , NAMED WITHOUT OVERLAPS ) ,
    FOREIGN KEY ( COURSENO , PERIOD NAMED )
      REFERENCES COURSE_FROM_TO ( COURSENO , PERIOD AVAILABLE ) ) ;
CREATE TABLE STUDENT_FROM_TO
   ( STUDENTNO STUDENTNO NOT NULL ,
    DFROM DATE NOT NULL ,
    DTO DATE NOT NULL ,
    PERIOD FOR REGISTERED ( DFROM , DTO ) ,
    UNIQUE ( STUDENTNO , REGISTERED WITHOUT OVERLAPS ) ,
    FOREIGN KEY ( STUDENTNO , PERIOD REGISTERED )
      REFERENCES STUDENT_NAME_FROM_TO ( STUDENTNO , PERIOD NAMED ) ) ;
CREATE TABLE STUDENT_NAME_FROM_TO
   ( STUDENTNO STUDENTNO NOT NULL ,
    SNAME NAME NOT NULL ,
    DFROM DATE NOT NULL ,
    DTO DATE NOT NULL ,
    PERIOD FOR NAMED ( DFROM , DTO ) ,
    UNIQUE ( STUDENTNO , NAMED WITHOUT OVERLAPS ) ,
    FOREIGN KEY ( STUDENTNO , PERIOD NAMED )
      REFERENCES STUDENT_FROM_TO ( STUDENTNO , PERIOD REGISTERED ) ) ;
CREATE TABLE ENROLLMENT_FROM_TO
   ( COURSENO COURSENO NOT NULL ,
    STUDENTNO STUDENTNO NOT NULL ,
    DFROM DATE NOT NULL ,
    DTO DATE NOT NULL ,
    PERIOD FOR ENROLLMENT ( DFROM , DTO ) ,
    UNIQUE ( COURSENO , STUDENTNO , ENROLLMENT WITHOUT OVERLAPS ) ,
    FOREIGN KEY ( COURSENO , PERIOD ENROLLMENT )
      REFERENCES COURSE_FROM_TO ( COURSENO , PERIOD AVAILABLE ) ,
    FOREIGN KEY ( STUDENTNO , PERIOD ENROLLMENT )
      REFERENCES STUDENT_FROM_TO ( STUDENTNO , PERIOD REGISTERED ) ) ;

19.4 

a. WITH t1 AS ( SELECT STUDENTNO FROM ENROLLMENT_FROM_TO WHERE COURSENO = COURSENO ( 'C1' ) AND DTO = d99 ),
  t2 AS ( SELECT STUDENTNO FROM ENROLLMENT_FROM_TO WHERE COURSENO = COURSENO ( 'C2' ) AND DTO = d99 )
SELECT STUDENTNO FROM t1 NATURAL JOIN t2
Note: The expression DTO = d99 in the definitions of t1 and t2 here could be replaced by ENROLLMENT CONTAINS CURRENT_DATE if desired.

b. The problem statement is ambiguous! We interpret the requirement to be “Get student numbers for students currently enrolled on at least one course, but not currently enrolled on both course C1 and course C2.” Let the result of evaluating the expression given as the answer to part a. above be assigned to table Ta. Then the following will suffice:
SELECT STUDENTNO FROM ENROLLMENT_FROM_TO WHERE DTO = d99
EXCEPT
SELECT STUDENTNO FROM Ta
Note that there won’t be any duplicate student numbers in the result here (why not?).

c. SELECT DISTINCT DFROM , DTO
FROM COURSE_FROM_TO
But the result here almost certainly won’t be in packed form. Do you have a good way to fix this problem? If so, we’d like to hear about it. You can contact us via the publisher.

d. This one is even worse, and would remain so even if we could figure out a way to get the result in part c. into packed form. No solution provided.

19.5 

a. INSERT INTO COURSE_FROM_TO ( COURSENO , DFROM , DTO )
    VALUES ( COURSENO ( 'C542' ) , DATE '2013-06-01' , DATE '9999-12-31' )
      ( COURSENO ( 'C543' ) , DATE '2013-06-01' , DATE '9999-12-31' )
INSERT INTO COURSE_NAME_FROM_TO ( COURSENO , CNAME , DFROM , DTO )
    VALUES ( COURSENO ( 'C542' ), NAME ( 'Tutorial D' ), DATE '2013-06-01' , DATE '9999-12-31' ) ,
      ( COURSENO ( 'C543' ) , NAME ( 'Temporal Databases' ), DATE '2013-06-01' , DATE '9999-12-31' ) ;
Of course, these two INSERTs really need to be done as part of the same transaction. An analogous remark applies to the answers to parts b.-d. also.

b. UPDATE COURSE_FROM_TO
SET DTO = DATE '2013-05-31'
WHERE DTO = DATE '9999-12-31'
AND COURSENO IN ( COURSENO ( 'C193' ), COURSENO ( 'C203' ) ) ;
UPDATE COURSE_NAME_FROM_TO
SET DTO = DATE '2013-05-31'
WHERE DTO = DATE '9999-12-31'
AND COURSENO IN ( COURSENO ( 'C193' ), COURSENO ( 'C203' ) ) ;
We assume for simplicity here that table ENROLLMENT_FROM_TO doesn’t show any students as currently enrolled on either of these courses.

c. INSERT INTO STUDENT_FROM_TO ( STUDENTNO , DFROM , DTO )
    VALUES ( STUDENTNO ( 'ST21' ) , DATE '2011-10-01' , DATE '2012-06-30' ) ;
INSERT INTO STUDENT_NAME_FROM_TO ( STUDENTNO , SNAME , DFROM , DTO )
    VALUES ( STUDENTNO ( 'ST21' ) , NAME ( 'Hugh Manatee' ) , DATE '2011-10-01' , DATE '2012-06-30' ) ;

d. UPDATE STUDENT_NAME_FROM_TO
SET DTO = DATE '2012-11-18'
WHERE STUDENTNO = STUDENTNO ( 'ST19' )
AND NAMED CONTAINS DATE '2012-11-18' ;
INSERT STUDENT_NAME_FROM_TO ( STUDENTNO , SNAME , DFROM , DTO )
VALUES ( STUDENTNO ( 'ST19' ) , NAME ( 'Anna Marino' ) , DATE '2012-11-18' ,
     ( SELECT MIN ( DTO ) FROM STUDENT_NAME_FROM_TO WHERE STUDENTNO = STUDENTNO ( 'ST19' ) AND NAMED CONTAINS DATE '2012-11-18' ) ;
Note: This solution assumes the database currently shows student ST19 as having some name on November 18th, 2012.

19.6 CREATE TABLE BSP_FROM_TO
      ( SNO SNO NOT NULL ,
       PNO PNO NOT NULL ,
       DFROM DATE NOT NULL ,
       DTO DATE NOT NULL ,
       PERIOD FOR DPERIOD ( DFROM , DTO ) ,
       UNIQUE ( SNO , PNO , DPERIOD WITHOUT OVERLAPS ) ,
       FOREIGN KEY ( SNO , PERIOD DPERIOD ) REFERENCES BS_FROM_TO ( SNO , PERIOD DPERIOD ) ,
       XFROM TIMESTAMP(12) GENERATED ALWAYS AS ROW START NOT NULL ,
       XTO TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL ,
       PERIOD FOR SYSTEM_TIME ( XFROM , XTO ) ,
       UNIQUE ( SNO , PNO ) )
       WITH SYSTEM VERSIONING ;

19.7 

a. SELECT XFROM, XTO
FROM BS_FROM_TO
FOR SYSTEM_TIME BETWEEN t01 AND t99
WHERE SNO = SNO ( 'S2' )
AND DPERIOD CONTAINS d04
For simplicity we use t01 and t99 here to denote the first and last value, respectively, of type TIMESTAMP(12). The result is guaranteed to be free of redundancy but not necessarily free of circumlocution.

b. SELECT DFROM , DTO
FROM BS_FROM_TO
FOR SYSTEM_TIME BETWEEN t01 AND t99
WHERE SNO = SNO ( 'S2' )
AND SYSTEM_TIME CONTAINS d08

19.8 The situation here is analogous to that described in Chapter 12 in connection with during relvars only and “the moving point now.” The point is, those appearances of t99 in current rows don’t really mean the end of time as such—rather, they stand for until further notice. However, SQL has no direct way of saying something is true “until further notice”; thus, it has to resort to that “end of time” trick,³³ much as we had to do for “valid” or stated time in a design consisting of during relvars only. As noted in Chapter 12, however, such tricks effectively mean we’re recording in the database something we know is false—a practice that’s logically incorrect, and hence somewhat hard to condone.

19.9 There are several interconnected issues involved here, and a good way to explain them, or at least air them, is by means of a series of questions and answers—a kind of dialog between a hypothetical student and teacher, as follows.
Student: First of all, why do we need this transaction timestamp notion at all? Why can’t we just timestamp updates with the system clock reading, as suggested in the body of the chapter? Surely that would be the obvious approach. Is there something wrong with it?
Teacher: Well, imagine the following scenario. You start a transaction on Monday. Your transaction updates some row r on Wednesday,³⁴ so—assuming we do use the system clock—the pertinent timestamp is Wednesday. On Friday, your transaction successfully completes (i.e., commits). Meanwhile, I submit a query on row r on Thursday, and I see the version of r that was current before your transaction ran. (I can’t see your updated version, because your transaction hasn’t committed yet.) Then I submit the same query on Saturday—and now I do see your updated version of r. What’s more, I see now that your updated version of r came into being on Wednesday, before I ran my query on Thursday! So why didn’t I see that version on Thursday?
Student: But wait a minute. Surely, when my transaction updates r on Wednesday, I get an exclusive lock on r. So when you try to access r on Thursday, you’re trying to get at least a shared lock on r, and so you’ll have to wait. And when my transaction commits, you’ll come out of the wait state, but then you’ll see my updated version of r. Isn’t that right?
Teacher: Yes, it’s right in principle, so long as the DBMS we’re talking about is one that uses locking for concurrency control and abides by all of the protocols normally associated with locking. But it might not be! The SQL standard nowhere mentions locking as such, and indeed there are concurrency control schemes that don’t use it. For example, there’s a scheme called multiversion read (MVR). Under MVR, if I ask to see a row r that you already have update access to, then I’m given access to a previously committed version of that row (in our example, that would be the version of r as it was last Sunday). In other words, the system makes it look as if my transaction actually ran before yours.
Student: But isn’t that approach a little suspect? It looks to me as if there’s something inconsistent going on. On Thursday, you’re going to make some business decision on the basis of incorrect information—to be specific, on the basis of row r having the value on Thursday that you saw on Thursday. What are you going to do on Saturday when you discover that row r actually had a different value on Thursday? I mean, the ATM machine might have dispensed hard cash on Thursday … and you can’t call it back on Saturday, can you?
Teacher: I can’t answer that.
Student: All right, let’s go back to locking. It seems to me there are other problems too, even with locking. Suppose we don’t use the system clock; suppose instead that, since updates don’t “really” happen until they’re committed, we use commit time as the transaction timestamp. One obvious difficulty springs to mind immediately: namely, at the time when an update is requested inside the transaction, we don’t know what the commit time is going to be. So what’s the transaction timestamp, within a given transaction, for a row that has been updated in that transaction and not yet committed, because the transaction hasn’t yet finished?³⁵
Teacher: Good point! Perhaps we might use the transaction start time, instead of commit time, for the transaction timestamp?
Student: All right … So suppose I update the same row twice in the same transaction. If we use the same timestamp for both updates, how can I see, within that same transaction, the update history for that row? I mean an update history that does reflect both of those updates?
Teacher: I give up.