2. (Multiattribute PACK): Let relation r have interval attributes A1, A2, ..., An (n > 1). Then (and only then) the expression PACK r ON (A1, A2, ..., An) denotes the packing of r on A1, A2, ..., An, in that order, and it’s equivalent to the following—

PACK ( ... ( PACK ( PACK r′ ON ( A1 ) ) ON ( A2 ) ) ... ) ON ( An )

—where r′ is the fully unpacked form of r (in other words, r′ is the relation denoted by the expression UNPACK r ON (A1, A2, ..., An)). 3. (Nullary PACK) Let r be a relation. Then (and only then) the expression PACK r ON ( ) denotes the packing of r on no attributes, and it returns relation r itself.

Examples: 1. Let relation r be as follows:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d02:d04] │
│ S2  │ [d03:d05] │
│ S4  │ [d02:d05] │
│ S4  │ [d04:d06] │
│ S4  │ [d09:d10] │
└─────┴───────────┘

Then packing r on DURING yields:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d02:d05] │
│ S4  │ [d02:d06] │
│ S4  │ [d09:d10] │
└─────┴───────────┘

2. Let relation r be as follows:

┌─────────┬───────────┐
│ A1      │ A2        │
├═════════┼═══════════┤
│ [P2:P4] │ [d01:d04] │
│ [P3:P5] │ [d01:d04] │
│ [P2:P4] │ [d05:d06] │
│ [P2:P4] │ [d06:d09] │
└─────────┴───────────┘

Then packing r on (A1,A2) yields:

┌─────────┬───────────┐
│ A1      │ A2        │
├═════════┼═══════════┤
│ [P2:P5] │ [d01:d04] │
│ [P2:P4] │ [d05:d09] │
└─────────┴───────────┘

By contrast, packing r on (A2,A1) yields:

┌─────────┬───────────┐
│ A1      │ A2        │
├═════════┼═══════════┤
│ [P2:P4] │ [d01:d09] │
│ [P5:P5] │ [d01:d04] │
└─────────┴───────────┘

Observe that these latter two results are logically distinct.

period SQL analog of an interval. Note, however, that SQL has no analog of the interval type generator, q.v. (i.e., there’s no period type generator); in fact, it doesn’t actually have any period types as such. Instead, SQL periods are represented by explicit (FROM,TO) pairs—specifically, pairs of column values, in the case of a period that happens to be part of an SQL table—and they’re always understood, implicitly, to be represented in closed:open style (despite the fact that they’re represented in concrete syntax with an opening parenthesis, not a bracket). Thus, e.g., the SQL period PERIOD (d04,d11), with FROM value d04 and TO value d11, consists precisely of the points d04, d05, d06, d07, d08, d09, and d10—in other words, it corresponds to the closed:closed interval [d04:d10]. Note too that (as indeed the term period tends to suggest) SQL’s periods are quite specifically temporal in nature; SQL has nothing corresponding to the general purpose interval abstraction as discussed elsewhere in this dictionary. Also, precisely because SQL periods are represented in closed:open style, there’s no way “the end of time” can actually be contained in such a period. In particular, if (as in several examples in Fig. 9) some period has a TO value of d99, then the last day that’s actually contained in the period in question is that day’s immediate predecessor—denoted symbolically d98—which of course isn’t “the end of time.”

Let p be the SQL period PERIOD (f,t), where f and t are SQL datetime values (see further discussion below). Then f and t are the boundary values for p. Further, if p happens to be part of an SQL table, then the columns of that table corresponding to f and t are the boundary columns for p.

Note: Actually, SQL supports two kinds of periods, representing application time (q.v.) and system time (q.v.), respectively. The remainder of this entry assumes, where it makes any difference, that the periods in question are application time periods specifically. The special considerations (such as they are) that apply to system time periods are discussed under system time, q.v.

Examples: Here first is a possible definition—i.e., a CREATE TABLE statement—for the base table S_FROM_TO as illustrated in Fig. 9 (irrelevant details omitted):

CREATE TABLE S_FROM_TO
     ( SNO   SNO  NOT NULL ,
       DFROM DATE NOT NULL ,
       DTO   DATE NOT NULL ,
       PERIOD FOR DPERIOD ( DFROM , DTO ) ,
       UNIQUE ( SNO , DPERIOD WITHOUT OVERLAPS ) ... ) ;

The specification PERIOD FOR DPERIOD (DFROM, DTO) means that columns DFROM and DTO taken together represent an application time period, called DPERIOD, for table S_FROM_TO. The UNIQUE specification means that (a) the combination (SNO,DPERIOD) constitutes a proper superkey for that table—note that it’s explicitly not a key as such because, as explained under foreign key (SQL), it’s not irreducible (see Part I of this dictionary)—and (b) if two rows of that table have the same SNO value, then their DPERIOD values can’t overlap, thanks to the qualification WITHOUT OVERLAPS. Note: WITHOUT OVERLAPS prevents the table from suffering from the redundancy and contradiction problems but not from the circumlocution problem (where the terms redundancy problem, contradiction problem, and circumlocution problem are each to be understood as defined elsewhere in this part of the dictionary, and where the problems in question are to be understood as with respect to DPERIOD in each case). Unfortunately, the fact that the table is subject to the circumlocution problem seems to mean the table isn’t properly relational, because it doesn’t seem to have anything corresponding to a proper relvar predicate. See table predicate (SQL) for further discussion of this point.

Here now are the CREATE TABLE statements for all three of the base tables illustrated in Fig. 9, now shown complete:

CREATE TABLE S_FROM_TO
     ( SNO   SNO  NOT NULL ,
       DFROM DATE NOT NULL ,
       DTO   DATE NOT NULL ,
       PERIOD FOR DPERIOD ( DFROM , DTO ) ,
       UNIQUE ( SNO , DPERIOD WITHOUT OVERLAPS ) ,
       FOREIGN KEY ( SNO , PERIOD DPERIOD )
               REFERENCES S_STATUS_FROM_TO ( SNO , PERIOD DPERIOD ) ) ;

CREATE TABLE S_STATUS_FROM_TO
     ( SNO    SNO     NOT NULL ,
       STATUS INTEGER NOT NULL ,
       DFROM  DATE    NOT NULL ,
       DTO    DATE    NOT NULL ,
       PERIOD FOR DPERIOD ( DFROM , DTO ) ,
       UNIQUE ( SNO , DPERIOD WITHOUT OVERLAPS ) ,
       FOREIGN KEY ( SNO , PERIOD DPERIOD )
               REFERENCES S_FROM_TO ( SNO , PERIOD DPERIOD ) ) ;

CREATE TABLE SP_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 S_FROM_TO ( SNO , PERIOD DPERIOD ) ) ;

Observe in particular that each of tables S_FROM_TO and S_STATUS_FROM_TO has a “foreign key” that references the other (and table SP_FROM_TO has a “foreign key” that references S_FROM_TO, but not the other way around). Note: Foreign key is indeed what SQL calls the constructs in question, but it would be closer to the truth, though still not entirely accurate, to refer to them as foreign U_keys, q.v. See foreign key (SQL) for further explanation. Note too that, precisely because each of the tables does have a “foreign key” that references the other, updates to either table will often need to be accompanied by updates to the other. Unfortunately, however, SQL lacks support for the multiple assignment operator that’s needed in order to perform such double updates properly (see Part I of this dictionary).

Now, since SQL has no period types, it also has no period variables. A fortiori, therefore, it has no period variable references, nor more generally does it have period expressions—i.e., expressions that return a period value—of any kind. But it does have a construct that might be thought of, informally, as a kind of “period selector,” and, as a special case of that construct, it does support a kind of “period literal.” However, these constructs can appear in just one context where it might have been expected that a column reference would be allowed: To be specific, they can appear in what SQL calls a period predicate, q.v., where they can be used to denote an operand, or both operands, to one of Allen’s operators. The syntax is PERIOD (f,t), where f and t are expressions both of the same SQL type, viz., either DATE or one of SQL’s TIMESTAMP types (these are the only point types that SQL supports).

Here now is a simple SQL query against table S_FROM_TO:

SELECT DISTINCT SNO
FROM   S_FROM_TO
WHERE  PERIOD ( DFROM , DTO ) OVERLAPS
       PERIOD ( DATE '2012-12-01' , DATE '2013-01-01' )

Observe that the OVERLAPS operands in this example are denoted by constructs—SQL calls them period predicands—that do look something like hypothetical “period selector” invocations, and the second in particular does look something like a hypothetical “period literal.”

Note: In the common special case where a period predicand denotes a period that’s explicitly defined to be part of some SQL table (necessarily a base table), the corresponding period name can be used in place of the corresponding “period selector invocation.” Thus, the WHERE clause in the foregoing example can be simplified slightly as indicated here:

SELECT DISTINCT SNO
FROM   S_FROM_TO
WHERE  DPERIOD OVERLAPS
       PERIOD ( DATE '2012-12-01' , DATE '2013-01-01' )

Note finally that periods don’t “carry through” operational expressions; thus, no SQL table other than a base table contains, or can contain, any periods at all. Thus, for example, the following attempted query—

SELECT *
FROM   S_FROM_TO NATURAL JOIN SP_FROM_TO
WHERE  DPERIOD OVERLAPS
       PERIOD ( DATE '2012-12-01' , DATE '2013-01-01' )

—fails on a syntax error, because the result of the join has no period called DPERIOD (in fact, it has no period at all). However, the desired effect can be obtained by replacing the reference to DPERIOD in the WHERE clause by an appropriate “period selector invocation,” thus:

SELECT *
FROM   S_FROM_TO NATURAL JOIN SP_FROM_TO
WHERE  PERIOD ( DFROM , DTO ) OVERLAPS
       PERIOD ( DATE '2012-12-01' , DATE '2013-01-01' )

PERIOD FOR (SQL) See period; see also system time.

period literal (SQL) See period.

period name (SQL) Periods in SQL are named if and only if the period in question is represented by a column pair in some SQL base table. Such names can be used (a) in a period predicate to denote a period predicand (see period); (b) in a FOR PORTION OF specification, q.v. (but only if the period name in question denotes an application time period, q.v., not a system time period); (c) in SQL “key” and “foreign key” specifications, q.v. (again, only if the period name in question denotes an application time period, q.v., not a system time period); (d) nowhere else.

period predicand (SQL) See period.

period predicate (SQL) A boolean expression, representing the SQL analog of an invocation of one of Allen’s operators, q.v.

period selector (SQL) See period.

point A value of some point type, q.v.; a point value.

point attribute An attribute of some point type, q.v.; a point valued attribute.

point extractor An operator for extracting the single point p from the unit interval [p:p].

Example: The following expression extracts the single DATE value (viz., d03) from the unit interval that’s the DURING value in the tuple for supplier S2 and part P2 in the relation that’s shown as the sample value for relvar SP_DURING in Fig. 6:

POINT FROM
    ( DURING FROM
           ( TUPLE FROM
                 ( SP_DURING WHERE SNO = SNO('S2')
                             AND   PNO = PNO('P2') ) ) )

A run-time error will occur if the interval expression that denotes the POINT FROM argument doesn’t evaluate to an interval of cardinality exactly one (i.e., a unit interval).

POINT FROM Tutorial D syntax for a point extractor, q.v. Note: SQL has no support for the POINT FROM operator as such. But if p denotes the SQL period PERIOD (f,t), where t is in fact the immediate successor of f, then p is effectively a “unit period,” and the SQL expression f is then effectively equivalent to the hypothetical SQL expression “POINT FROM (p).” However, it’s the user’s responsibility in such a situation to ensure that period p does indeed contain just one point—no exception will be raised if it doesn’t. See period for further explanation.

point type A type—usually assumed to be an ordinal type (see Part I of this dictionary)—over which an interval type, q.v, can be defined. In other words, let T be a type for which all of the following are defined: (a) a total ordering, according to which the operator “≤” is defined for every pair of values v1 and v2 of type T, such that if v1 and v2 are distinct, exactly one of the comparisons v1 < v2 and v2 < v1 returns TRUE; (b) niladic FIRST_T and LAST_T operators, which return the smallest (first) and largest (last) value of type T, respectively, according to the aforementioned ordering; and (c) monadic NEXT_T and PRIOR_T operators, which return the successor (if it exists) and predecessor (if it exists), respectively, of any given value of type T according to the aforementioned ordering. Then T is an ordinal type, and it’s usable as a point type. Note: NEXT_T and PRIOR_T are the successor function and predecessor function, respectively, for type T. The only value of type T for which NEXT_T is undefined is the value denoted by LAST_T ( ); similarly, the only value of type T for which PRIOR_T is undefined is the value denoted by FIRST_T ( ).

Examples: 1. Type INTEGER (the scale is unity, and the successor function is “add one”). 2. Type MONEY, which we assume for the sake of the example is a type that represents monetary amounts measured in dollars and cents (the scale is one cent, and the successor function is “add one cent”). 3. Type DATE (the scale is one day, and the successor function is “add one day”).

Note: The foregoing definition gives a set of conditions on type T that are certainly sufficient for that type to be usable as a point type. However, those conditions might not all be necessary. For example, it might be possible to drop conditions (b) and (c), which together constitute the property of ordinality, q.v. (see continuity assumption). Alternatively, it might be possible to replace the linear ordering required by condition (a) by a cyclic ordering, thereby making the type in question a cyclic point type. (An example of this possibility is provided by days of the week, where the available values can be thought of as being arranged around the circumference of a circle, such that every value has both a successor and a predecessor and there’s no first or last value.) Further details of such possibilities are beyond the scope of this dictionary.

Note finally that the definition implicitly requires the successor function for a given point type to be unique. So what about a point type such as DATE for which (it would appear) several different successor functions might make sense—for example, “next week,” “next business day,” “next month,” and so on? One possible approach to such questions consists in defining a series of proper subtypes of the type in question, each with its own unique successor function. See inheritance (point types) for further discussion of this possibility.

point type (SQL) See period.

point type inheritance See inheritance (point types).

point value A value of some point type; hence, a value that can be contained within some interval.

PORTION An auxiliary operator, available for use in conjunction with certain other operators on a relation or relvar having at least one interval attribute, that simplifies the process of accessing just a certain “portion” of the relation or relvar in question. The portion in question can be thought of as the result of (a) picking out those tuples of the relation or relvar in question for which the value of a specified interval attribute overlaps a specified interval; (b) unpacking the set of tuples so identified on the specified attribute; (c) picking out the tuples in the result of that unpacking whose interval value is included in the specified interval; and then (d) (re)packing this latter set of tuples on the specified attribute. Note: The foregoing definition is deliberately somewhat simplified, in that it assumes there’s just one interval attribute and just one specified interval. Details of the general case are beyond the scope of this dictionary.

Examples: First a retrieval example. Given the sample value shown for SP_DURING in Fig. 6, the expression

SP_DURING PORTION { DURING { INTERVAL_DATE ( [ d06 : d08 ] ) } }

yields:

┌─────┬─────┬───────────┐
│ SNO │ PNO │ DURING    │
├═════┼═════┼═══════════┤
│ S3  │ P5  │ [d06:d07] │
│ S4  │ P2  │ [d06:d08] │
│ S4  │ P4  │ [d06:d08] │
└─────┴─────┴───────────┘

To see how this example works, observe that the given expression, using PORTION, can be regarded as shorthand for the following:

WITH ( i0 := INTERVAL_DATE ( [ d06 : d08 ] ) ,
       t1 := SP_DURING WHERE DURING OVERLAPS i0 ,
       t2 := UNPACK t1 ON ( DURING ) ,
       t3 := t2 WHERE DURING ⊆ i0 ) :
PACK t3 ON ( DURING )

(The final PACK step has no effect in this example, but it’s necessary in the general case.)

Here now is a DELETE example:

DELETE SP_DURING WHERE SNO = SNO('S4') :
       PORTION { DURING { INTERVAL_DATE ( [ d08 : d13 ] ) } } ;

The effect of this DELETE, loosely speaking, is to remove from relvar SP_DURING any representation of the proposition “Supplier S4 was able to supply some part from day 8 to day 13.” Here’s the result, given the sample values shown in Fig. 8 (only tuples for supplier S4 shown):

┌─────┬─────┬───────────┐
│ SNO │ PNO │ DURING    │
├═════┼═════┼═══════════┤
│ ..  │ ..  │ ......... │
│ S4  │ P2  │ [d06:d07] │
│ S4  │ P4  │ [d04:d07] │
│ S4  │ P5  │ [d05:d07] │
│ S4  │ P5  │ [d14:d99] │
│ ..  │ ..  │ ......... │
└─────┴─────┴───────────┘

In general, the DELETE statement

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

can be regarded as shorthand for the following:

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 ) ;

(Once again, in the case of the particular DELETE example shown earlier, the final PACK step actually has no effect, but it’s necessary in the general case.)

Finally, here’s an UPDATE example (note that PORTION makes sense with DELETE and UPDATE but not with INSERT). The following statement has the effect of replacing the proposition “Supplier S2 was able to supply part P1 on day 3” by the proposition “Supplier S2 was able to supply part P1 on day 5”:

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

In this example, assuming the initial value of relvar SP_DURING is as shown in either Fig. 6 or Fig. 8, the final PACK step does have some effect. (Details of the expanded form of UPDATE with PORTION are omitted here for simplicity, but they follow the same general pattern as that already shown for DELETE with PORTION.)

Turning now to SQL: SQL does support PORTION, but (a) it does so only in conjunction with DELETE and UPDATE, not with retrieval; (b) that support is limited to operating in terms of just one period in the target table—necessarily so, given that SQL allows at most one application time period per table—and just one overlapping period; (c) as in fact the previous point suggests, the period in the target table must be an application time period, not a system time period, q.v.; and (d) in the case of UPDATE, the SET clause isn’t allowed to assign to the application time period boundary columns. Note: As a consequence of point (d) here, the UPDATE example shown above has no direct SQL counterpart. However, the earlier DELETE example does. Here it is:

DELETE
FROM   SP_FROM_TO FOR PORTION OF DPERIOD FROM d08 TO d14
WHERE  SNO = SNO('S4') ;

By the way, suppose we now execute the following SQL INSERT (which can be regarded, a trifle loosely, as a partial inverse of the foregoing SQL DELETE):

INSERT INTO SP_FROM_TO ( SNO , PNO , DFROM , DTO )
       VALUES ( SNO('S4') , PNO('P5') , d08 , d14 ) ;

Here’s the result (rows for S4 only):

┌─────┬─────┬───────┬─────┐
│ SNO │ PNO │ DFROM │ DTO │
├═════┼═════┼═══════┼─────┤
│ ..  │ ..  │ ...   │ ... │
│ S4  │ P2  │ d06   │ d08 │
│ S4  │ P4  │ d04   │ d08 │
│ S4  │ P5  │ d05   │ d08 │
│ S4  │ P5  │ d08   │ d14 │
│ S4  │ P5  │ d14   │ d99 │
│ ..  │ ..  │ ...   │ ... │
└─────┴─────┴───────┴─────┘

The point about this example, of course, is that the three rows for S4 and P5 in the result are not automatically packed together into one, in SQL. See table predicate (SQL); see also U_INSERT.

POST Let i be the interval [b:e]. Then if e is the last value of the point type underlying the type of interval i, POST (i) is undefined; otherwise it returns the immediate successor (informally denoted “e+1”) of e. Note: SQL has no support for the POST operator as such. Rather, if p denotes the SQL period PERIOD (f,t), then the SQL expression t is effectively equivalent to the hypothetical SQL expression “POST (p).” See period for further explanation.

PRE Let i be the interval [b:e]. Then if b is the first value of the point type underlying the type of interval i, PRE (i) is undefined; otherwise it returns the immediate predecessor (informally denoted “b−1”) of b. Note: SQL has no support for the PRE operator as such. Rather, if p denotes the SQL period PERIOD (f,t)—and if we assume for definiteness that f and t are of type DATE and the scale, q.v., is one day—then the SQL expression

f – INTERVAL '1' DAY

(which will fail, of course, if f is “the beginning of time”) is effectively equivalent to the hypothetical SQL expression “PRE (p).” For further explanation and discussion, see datetime arithmetic (SQL); period.

predecessor Let p be a value of point type T (p ≠ FIRST_T ( )); then the predecessor of p (informally denoted “p−1”) is the point that’s the immediate predecessor of p with respect to the ordering associated with T. Note: The term “predecessor of p” is always used to mean the immediate predecessor of p specifically, though the explicit qualifier immediate is sometimes used for emphasis.

predecessor function (SQL) See datetime arithmetic (SQL).

predecessor function / predecessor operator See point type.

PRIOR_T The predecessor function for point type T. See ordinality; PRIOR (in Part I of this dictionary); point type. Note: SQL has no support for this operator as such; instead, an expression involving explicit datetime arithmetic, such as DV − INTERVAL '1' DAY, has to be used. For further explanation and discussion, see datetime arithmetic (SQL); period.

proper interval inclusion One of Allen’s operators, q.v. Let i1 and i2 be intervals of the same type. Then i1 properly includes i2 (“i1 ⊃ i2”) if and only if i1 includes i2 and i1 ≠ i2. Also, i2 is properly included in i1 (“i2 ⊂ i1”) if and only if i1 ⊃ i2 is true.

Examples: Let i1 and i2 be [d02:d10] and [d04:d08], respectively; then i1 ⊃ i2 is true. By contrast, if i1 and i2 are [d02:d07] and [d04:d08], respectively, then i1 ⊃ i2 is false. Note that no interval is properly included in itself. Note too that the term proper interval inclusion is usually taken, a trifle arbitrarily, to refer to the operator “⊂” specifically, not the operator “⊃”.

Note: SQL has no direct support for proper interval inclusion. However, if p1 and p2 denote the SQL periods PERIOD (f1,t1) and PERIOD (f2,t2), respectively, then (e.g.) the SQL expression ( f1 ≥ f2 AND t1 < t2 ) OR ( f1 > f2 AND t1 ≤ t2 ) is effectively equivalent to the hypothetical SQL expression “p1 ⊂ p2.” See period for further discussion.

redundancy problem A problem that can arise in connection with relations with interval attributes, absent suitable controls: specifically, the problem that two tuples appearing in such a relvar at the same time might effectively imply the same proposition. For example, suppose with reference to either Fig. 6 or Fig. 8 that the following tuples were both to appear in relvar S_STATUS_DURING at the same time:

┌─────┬────────┬───────────┐          ┌─────┬────────┬───────────┐
│ SNO │ STATUS │ DURING    │          │ SNO │ STATUS │ DURING    │
├─────┼────────┼───────────┤          ├─────┼────────┼───────────┤
│ S4  │     25 │ [d05:d06] │          │ S4  │     25 │ [d06:d07] │
└─────┴────────┴───────────┘          └─────┴────────┴───────────┘

These two tuples both imply among other things the proposition “Supplier S4 had status 25 on day 6.” Clearly, it would be better if the two tuples were replaced by the following single tuple:

┌─────┬────────┬───────────┐
│ SNO │ STATUS │ DURING    │
├─────┼────────┼───────────┤
│ S4  │     25 │ [d05:d07] │
└─────┴────────┴───────────┘

Formally, the problem illustrated by this example is that the two original tuples (a) agree on their SNO and STATUS values and (b) have DURING values i1 and i2 such that i1 OVERLAPS i2 is true (see OVERLAPS). Note that if those original tuples were indeed both allowed to appear, the relvar would be in violation of its own predicate, because neither [d05:d06] nor [d06:d07] would in fact be a maximal interval, q.v., of days during which supplier S4 had status 25. Note too that enforcing a constraint to the effect that {SNO,DURING} is a key for S_STATUS_DURING—which it is—isn’t sufficient to prevent the foregoing problem from occurring. See PACKED ON for further discussion.

scale (Of a point type) Same as granularity. The term scale is preferred, in part because it has a precise definition (see Part I of this dictionary), which granularity seems not to have.

Note: Scales aren’t necessarily uniform. For example, let type MDATE represent calendar dates measured in months—see inheritance (point types). Then the associated successor function NEXT_MDATE will add 28, 29, 30, or 31 days to its MDATE argument, depending on which particular month of which particular year that argument happens to denote. (Observe, incidentally, that—as pointed out under datetime arithmetic (SQL)—the same is not true of the SQL expression DV + INTERVAL '1' MONTH, where DV is of type DATE. For example, if the current value of DV happens to be August 31st, then an error will occur, because September 31st isn’t a legitimate date.)

semitemporal Term used informally to characterize a since relvar, q.v., in contrast to a during relvar, q.v. Contrast fully temporal.

since Term much used in connection with temporal data; if some specified condition c holds “since” some specified time point p, it means condition c holds throughout (i.e., at every time point within) the interval from p to “the end of time” inclusive. See until further notice. Note: The term is often used—in this dictionary in particular—in a more restrictive sense, according to which the condition in question holds throughout and not immediately before the interval in question, in which case the interval in question is said to be maximal. See maximal interval for further discussion.

since attribute Term used informally to refer to an attribute of some temporal point type.

Examples: Attributes SNO_SINCE and STATUS_SINCE in relvar S_SINCE, and attribute SINCE in relvar SP_SINCE, in the suppliers-and-shipments database of Fig. 6.

since relation Term used informally to refer to a relation one of whose attributes is of some temporal point type (especially a relation that’s the current value of some since relvar, q.v.).

Examples: The current values of relvars S_SINCE and SP_SINCE in the suppliers-and-shipments database of Fig. 6.

since relvar Term used informally to denote a relvar that (a) isn’t a during relvar, q.v., and (b) has a predicate that can reasonably be formulated in such a way as to include one or more qualifications of the form “since time t” (and thus has one or more attributes of some temporal point type); very loosely, a relvar that contains current information.

Examples: Relvars S_SINCE and SP_SINCE in the suppliers-and-shipments database of Fig. 6.

sixth normal form (Expanded definition) Relvar R is in sixth normal form, 6NF, if and only if it can’t be nonloss decomposed at all, other than trivially—i.e., if and only if the only JDs to which it’s subject are trivial ones. Note: The foregoing definition of what it means for a given relvar to be in 6NF is identical to the one given in Part I of this dictionary. However, (a) the term JD must now be understood to include U_JDs, q.v., in particular, and (b) the term nonloss decomposed must now be understood in terms of U_projection, q.v. (the decomposition operator) and U_join, q.v. (the corresponding recomposition operator), rather than just regular projection and regular join. See vertical decomposition.

Examples: In Figs. 6 and 8, relvars S_DURING, S_STATUS_DURING, and SP_DURING are all in 6NF. By contrast, in Fig. 6, relvar S_SINCE isn’t (though SP_SINCE is).

Note, incidentally, that the SQL analog of relvar S_STATUS_DURING—viz., base table S_STATUS_FROM_TO (see Fig. 9)—isn’t in 6NF, because the following join dependencies—

{ { SNO , DFROM , DTO } , { SNO , DFROM , STATUS } }
{ { SNO , DTO , DFROM } , { SNO , DTO , STATUS } }

—both hold in that table. These JDs are clearly nontrivial, and the table is thus not in 6NF (though it is in 5NF).

snapshot database Deprecated term for a nontemporal database, q.v.

snapshot of the database Deprecated term for a database value—especially a value of some temporal database.

snapshot query Informal and somewhat deprecated term for a query on a temporal database whose result represents the state of affairs as it was at some specified time.

Example: Consider the query “Get (SNO, STATUS, PNO, DURING) tuples such that supplier SNO (a) had status STATUS, and (b) was able to supply part PNO, throughout interval DURING, where DURING contains day 4.” Here’s a possible formulation of this query against the database of Fig. 8:

( USING ( DURING ) : S_STATUS_DURING JOIN SP_DURING ) WHERE d04 ∊ DURING

Let’s agree to refer to this expression as exp (note, incidentally, that exp involves a U_join followed by a regular restriction, not a U_restriction). Then the expression

( exp ) { ALL BUT DURING }

is a snapshot query—it returns a “snapshot” of a certain portion of the database as of a certain point in time (day 4, in the case at hand).

stated time (Of a proposition) The time or times, represented as a set of temporal intervals (preferably in packed form), when, according to what the database currently says (which is to say, according to what we currently believe), the proposition in question is, was, or will be true. Note: Other terms that might be used for this concept include user stated time (to emphasize the point that it’s some user, not the system, that did the stating); user time; asserted time; user asserted time; application time, q.v. (this is the term used in the SQL standard); business time, q.v.; and valid time, q.v. (this is the term most often encountered in the literature).

Example: In Fig. 6, the stated time for the proposition Supplier S2 is, was, or will be under contract is {[d02:d04],[d07:d99]}. Note carefully that the proposition in this example—the proposition, that is, to which the specified stated time applies—isn’t currently represented in the database; rather, the proposition that is represented in the database is the proposition Supplier S2 is, was, or will be under contract during certain intervals (where the “certain intervals” in question are [d02:d04] and [d07:d99], of course). Note, however, that those “certain intervals” aren’t mentioned in the proposition to which the specified stated time applies; rather, they are the stated time for that proposition. And an analogous remark applies to the stated time concept in general; that is, stated times in general apply to some proposition that’s not actually represented in the database.

Note: In the relational approach to temporal data espoused in this dictionary, stated times are represented by regular relational attributes in the usual way. Precisely for that reason, there’s very little need to use a special term (“stated times”) for them at all. In nonrelational approaches, by contrast (see, e.g., the approach adopted in the SQL standard), stated times are treated as special—in particular, they’re typically not represented by regular relational attributes in the usual way—and the need for some kind of special term to refer to them thus becomes somewhat more pressing. Note, incidentally, that not representing stated times by regular relational attributes in the usual way means the approach in question is indeed nonrelational; in fact, such an approach is in clear violation of The Information Principle (see Part I of this dictionary).

successor Let p be a value of point type T (p ≠ LAST_T ( )); then the successor of p (informally denoted “p+1”) is the point that’s the immediate successor of p with respect to the ordering associated with T. Note: The term “successor of p” is always used to mean the immediate successor of p specifically, though the explicit qualifier immediate is sometimes used for emphasis.

successor function (SQL) See datetime arithmetic (SQL).

successor function / successor operator See point type.

system clock See logged time; system time.

system time SQL term for logged time, q.v. Note, however, that system times in SQL are kept as part of the table to which they pertain, whereas—at least in the approach advocated in Time and Relational Theory: Temporal Data in the Relational Model and SQL, by Date, Darwen, and Lorentzos—logged times are kept in distinct logged time relvars, q.v. An SQL base table can have at most one system time period (see period). However, such periods don’t “carry through” operational expressions; thus, no SQL table other than a base table has, or can have, any system time period at all (see period for further discussion and explanation).

Example: Suppose we want to keep system time information, but (for simplicity) not application time information, for suppliers and their status values. Then instead of table S_STATUS_FROM_TO as defined in the examples under period (and as illustrated in Fig. 9), we might define a table XS_STATUS_FROM_TO that looks like this:

CREATE TABLE XS_STATUS_FROM_TO
     ( SNO    SNO  NOT NULL ,
       STATUS INTEGER NOT NULL ,
       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 ) ,
       FOREIGN KEY ( SNO ) REFERENCES XS_FROM_TO ( SNO ) )
       WITH SYSTEM VERSIONING ;

Points arising:

• Table XS_STATUS_FROM_TO has just two “regular” columns, SNO and STATUS. These are the only columns the user can update directly (see below).

• The system time period, which (as usual in SQL) is implicitly represented in closed:open style, has the required name SYSTEM_TIME. The PERIOD FOR SYSTEM_TIME specification defines columns XFROM and XTO to be the boundary columns for that period. Those columns must both be of the same type (either DATE—which is unlikely—or some specific TIMESTAMP type). Purely for definiteness, the example shows them as being of type TIMESTAMP(12), meaning times that are accurate to the picosecond (one picosecond = 10^-12 seconds).

• The specifications GENERATED ALWAYS AS ROW START (on XFROM) and GENERATED ALWAYS AS ROW END (on XTO) are required.

• The UNIQUE and FOREIGN KEY specifications effectively assume the table contains the two regular columns SNO and STATUS only. Note: The FOREIGN KEY specification in particular assumes the existence of an analogous table called XS_FROM_TO, with the obvious definition and semantics. For simplicity, however, foreign keys are ignored in the discussion of updates below. Note, however, that while SQL does indeed call the constructs in question “foreign keys,” it would be closer to the truth, though still not entirely accurate, to refer to them as foreign U_keys, q.v. See foreign key (SQL) for further explanation.

• The specification WITH SYSTEM VERSIONING is optional, but it seems unlikely that it would ever be omitted in practice (and details of what happens if it’s omitted are therefore omitted from this dictionary). If it’s not omitted, the table is said to be a system versioned table.

Table XS_STATUS_FROM_TO is initially empty, of course. Suppose we now execute the following INSERT:

INSERT INTO XS_STATUS_FROM_TO ( SNO , STATUS )
       VALUES ( SNO('S1') , 20 ) ;

Further, suppose this INSERT statement is executed at time t02 by the system clock. Then the row that’s actually inserted looks like this:

┌─────┬────────┬───────┬─────┐
│ SNO │ STATUS │ XFROM │ XTO │
├─────┼────────┼───────┼─────┤
│ S1  │     20 │ t02   │ t99 │
└─────┴────────┴───────┴─────┘

In other words, the system automatically inserts the timestamp t02 in the XFROM position and “the end of time” timestamp t99 in the XTO position. (Of course, system times are supposed always to be times in the past—see logged time—but that “end of time” timestamp t99 doesn’t really mean the end of time as such, it means “until further notice,” q.v. Though it should be noted that the concept of “until further notice” doesn’t really make much sense in this context either, given the intended semantics for the concept of logged time. Perhaps it would be better to say of such appearances of t99 that they denote, not “until further notice,” but rather “the time right now,” meaning the time when the update occurred.)

Note: The foregoing explanation is somewhat simplified. To be more specific, the SQL standard doesn’t actually mention the system clock as such; instead, it says there’s something called the transaction timestamp, which (a) is required to remain constant throughout the life of the transaction in question, (b) is used as the source for system time values in general, and (c) is presumably distinct for distinct transactions, though the standard doesn’t actually seem to come out and say as much. This state of affairs notwithstanding, the remainder of this entry continues to talk in terms of the system clock as such, for reasons of definiteness and simplicity.

Now suppose we execute the following UPDATE statement:

UPDATE XS_STATUS_FROM_TO
SET    STATUS = 25
WHERE  SNO = SNO('S1') ;

Further, suppose this UPDATE statement is executed at time t06 by the system clock. After the UPDATE, then, the table looks like this:

┌─────┬────────┬───────┬─────┐
│ SNO │ STATUS │ XFROM │ XTO │
├═════┼────────┼═══════┼─────┤
│ S1  │     25 │ t06   │ t99 │
│ S1  │     20 │ t02   │ t06 │
└─────┴────────┴───────┴─────┘

In other words, the UPDATE (a) inserts a new row for supplier S1 with STATUS value 25, XFROM value t06, and XTO value t99, and (b) replaces the old row for supplier S1 by a row that’s identical to that old row except that the XTO value is t06 instead of t99.

Finally, suppose we subsequently execute the following DELETE statement:

DELETE
FROM   XS_STATUS_FROM_TO
WHERE  SNO = SNO('S1') ;

Further, suppose this DELETE statement is executed at time t45 by the system clock. After the DELETE, then, the table looks like this:

┌─────┬────────┬───────┬─────┐
│ SNO │ STATUS │ XFROM │ XTO │
├═════┼────────┼═══════┼─────┤
│ S1  │     25 │ t06   │ t45 │
│ S1  │     20 │ t02   │ t06 │
└─────┴────────┴───────┴─────┘

In other words, the DELETE doesn’t actually delete anything; instead, it simply replaces the XTO value in the “current row” for supplier S1 by t45. Note: The current row for supplier S1 is, of course, the row for supplier S1 in which the XTO value is t99. After the DELETE, there’s no current row for supplier S1 at all. More generally, current rows are the only ones that can be updated—once a “historical” row gets into the table, it’s there forever, and it never changes.

Turning now to queries: By default, queries on a system versioned table apply only to the current rows. Thus, if table XS_STATUS_FROM_TO currently looks like this—

┌─────┬────────┬───────┬─────┐
│ SNO │ STATUS │ XFROM │ XTO │
├═════┼────────┼═══════┼─────┤
│ S1  │     25 │ t06   │ t99 │
│ S1  │     20 │ t02   │ t06 │
└─────┴────────┴───────┴─────┘

—then the query

SELECT STATUS
FROM   XS_STATUS_FROM_TO
WHERE  SNO = SNO('S1')

returns the following result:

┌────────┐
│ STATUS │
├────────┤
│     25 │
└────────┘

To query historical rows, or more generally to query both current and historical rows, we can qualify the pertinent table reference (in the FROM clause) by a FOR SYSTEM_TIME specification, as in this example:

SELECT STATUS , XFROM , XTO
FROM   XS_STATUS_FROM_TO FOR SYSTEM_TIME AS OF t04
WHERE  SNO = SNO('S1')

Here’s the result:

┌────────┬───────┬─────┐
│ STATUS │ XFROM │ XTO │
├────────┼───────┼─────┤
│     20 │ t02   │ t06 │
└────────┴───────┴─────┘

Note that, although this result does have XFROM and XTO columns, it doesn’t have a system time period as such—like application time periods, q.v., system time periods don’t “carry through” operational expressions. See period for further discussion of this and related matters.

The following FOR SYSTEM_TIME options are supported (t, t1, and t2 are expressions denoting values of the same type, either type DATE—which is unlikely—or some specific TIMESTAMP type):

• FOR SYSTEM_TIME AS OF t

  Selects rows whose system time period contains t.

• FOR SYSTEM_TIME FROM t1 TO t2

  Selects rows whose system time period overlaps the closed:open period [t1:t2).

• span class="literal">FOR SYSTEM_TIME BETWEEN t1 AND t2

  Selects rows whose system time period overlaps the closed:closed period [t1:t2]. Note that here for once SQL does make use of the closed:closed style, albeit implicitly.

system time period (SQL) See system time.

system versioned table (SQL) See system time.

table predicate (SQL) As noted elsewhere (see period), the fact that SQL has no direct way of preventing tables from being subject to the circumlocution problem, q.v., appears to mean that such tables aren’t properly relational, because they don’t seem to have anything corresponding to a proper relvar predicate. To illustrate the point, consider table SP_FROM_TO (see Fig. 9). Note in particular that this table isn’t guaranteed to be kept packed on DPERIOD. So here’s the obvious first attempt at a predicate—call it P—for this table:

If DTO is “the end of time,” then supplier SNO has been able to supply part PNO ever since day DFROM (and not the day immediately before day DFROM) and will continue to be so until further notice; otherwise supplier SNO was able to supply part PNO throughout the period (“period p”) from day DFROM to the day that’s the immediate predecessor of day DTO, inclusive.

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. (Though it is at least true that there can’t be more than one row for any given combination of a supplier number, a part number, and some specific day, thanks to the applicable WITHOUT OVERLAPS constraint, q.v.)

Now, Fig. 9 shows SP_FROM_TO as containing a row—call it r—indicating that supplier S4 was able to supply part P4 throughout the interval [d04:d08] (or PERIOD (d04,d09), in SQL notation). However, there are numerous ways of splitting that interval [d04,d08] up into smaller, nonoverlapping intervals. Here are just a few of them:

• [d04:d04]   [d05:d05]   [d06:d06]   [d07:d07]   [d08:d08]

• [d04:d04]   [d05:d05]   [d06:d06]   [d07:d08]

• [d04:d05]   [d06:d06]   [d07:d07]   [d08:d08]

• [d04:d05]   [d06:d07]   [d08:d08]

• [d04:d06]   [d07:d08]

And so on. It follows that it would be possible, without violating the WITHOUT OVERLAPS constraint, to replace row r by several different rows, and to do so, moreover, in several different ways. And every such possible replacement row—call it r′—would represent a true instantiation of predicate P. By The Closed World Assumption (q.v.), therefore, predicate P can’t possibly be right—because 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 (boldface for emphasis). In the case at hand, however, table SP_FROM_TO clearly isn’t going to contain all of those possible rows r′ at the same time—in fact it can’t possibly do so, thanks to the WITHOUT OVERLAPS constraint—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′):

If DTO is “the end of time,” then supplier SNO has been able to supply part PNO ever since day DFROM (and not the day immediately before day DFROM) and will continue to be so until further notice; otherwise supplier SNO was able to supply part PNO throughout the period (“period p”) from day DFROM to the day that’s the immediate predecessor of day DTO, inclusive, and hence—but only implicitly—throughout every period properly included in period 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; by contrast, however, it isn’t true that if row r satisfies this predicate, then row r necessarily appears in the table.

So it seems to be quite difficult—in fact, it seems to be impossible (?)—to come up with a predicate that exactly characterizes table SP_FROM_TO. If such is indeed the case, then (to generalize from the example) it seems that certain SQL tables fail to correspond to any well defined predicate; equivalently, certain real world situations seem to be representable by a given SQL table in many different ways. This state of affairs would appear to constitute a rather serious departure from relational principles. (Of course, the same criticism would apply to relvars in Tutorial D as well, if they permitted the same kind of circumlocution.)

temporal Many concepts from conventional database theory have, or can be given, extended interpretations in the context of relations and relvars with interval attributes. Thus, the terminology used to refer to the concepts in question needs some corresponding extension as well; for example, the familiar term join is extended to U_join, q.v. Unfortunately, much of the literature uses the qualifier temporal for this purpose, as in (for example) temporal join; temporal restriction; temporal projection; temporal FD; temporal MVD; temporal key; temporal superkey; temporal BCNF; temporal 3NF; temporal 4NF; and so on. But the concepts denoted by these terms are by no means limited to applying only to temporal data as such, and use of the qualifier temporal in such contexts is therefore deprecated. See also temporal operator.

temporal database A database containing at least one temporal relvar, q.v.

Example: The suppliers-and-shipments database of either Fig. 6 or Fig. 8.

temporal interval type An interval type defined over some temporal point type, q.v.

temporal operator Deprecated term sometimes used to refer to any of the U_ operators (U_JOIN, U_MINUS, etc.), q.v. Such terminology is deprecated because the operators aren’t limited to operating on temporal data as such. See temporal for further discussion.

temporal point type A type such as DATE or TIME or TIMESTAMP, whose values represent points in time (“time points”) as such.

temporal relation A relation whose heading contains at least one attribute of some temporal type; in particular, the value of a given temporal relvar at a given time.

Examples: With reference to Fig. 6, the relations that are the values of relvars S_SINCE and S_DURING at any given time.

temporal relvar A relvar whose heading contains at least one attribute of some temporal type (implying that the corresponding predicate has at least one parameter of some temporal type); a since relvar or a during relvar.

temporal type Either a temporal point type or a temporal interval type.

temporal upward compatibility The idea that it should be possible to convert a nontemporal database into a temporal one by just “adding temporal support,” while allowing existing nontemporal applications to run unchanged against the now temporal database. In other words, suppose we’re given some nontemporal database DB, together with a set of applications that run successfully on that database, and suppose we now want DB to evolve to include some temporal features. Then it would be nice if we could add those features in such a way that those existing applications can continue to run unchanged, and produce correct results, on that temporal version of DB. If this goal is met, then temporal upward compatibility has been achieved.

Unfortunately, it seems impossible to achieve such a goal without doing serious violence to the relational model. What’s more—and what might be more important in practice—it’s easy to see that such a goal is quite unrealistic (see “An Overview and Analysis of Proposals Based on the TSQL2 Approach,” by Hugh Darwen and C. J. Date, in Date on Database: Writings 2000-2006, Apress, 2006). The whole idea is thus now somewhat discredited.

TIME A point type, assumed for the purposes of this dictionary to be system defined and to have values that represent times of the day on a 24 hour clock, accurate to the second. In other words, the scale, q.v., is one second, and the successor function is basically “next second,” meaning “add one second to the given time” (i.e., it’s a function that, given a TIME value t, returns the TIME value that’s the immediate successor of t according to conventional clock ordering). Note: Type TIME might very reasonably be not just a regular or “linear” point type but a cyclic one (see cyclic point type), though it isn’t in SQL. Further details (of cyclic point types of any kind) are beyond the scope of this dictionary.

Examples: Here’s an example of a TIME literal in Tutorial D:

TIME ( '18:33:45' )

(“33 minutes and 45 seconds past 6:00 pm”). As noted in the introduction to this part of the dictionary, symbols such as t01, t02, etc. used in examples elsewhere can be thought of as shorthand for such literals (or—more likely, perhaps—possibly for literals of type some TIMESTAMP type, q.v.). Note: An SQL version of the literal shown above would look like this:

TIME '18:33:45'

time point A value of some temporal point type, such as DATE or TIME or TIMESTAMP; a granule, q.v. The smallest possible time point is the chronon, q.v.

time quantum A chronon, q.v.

timeline Let T be a temporal point type. Then the set of all values of type T, in sequence according to the ordering associated with that type, can be regarded as the timeline corresponding to type T (equivalently, as the timeline whose scale is the scale associated with type T). The values returned by FIRST_T ( ) and LAST_T ( ) can be regarded as “the beginning of time” and “the end of time,” respectively, with respect to type T, or equivalently with respect to that particular timeline or that particular scale.

Examples: 1. Let T be type DATE, q.v. Then the corresponding timeline is measured in days (i.e., the scale is one day); the beginning of time with respect to that timeline is “the first day,” which is returned by FIRST_DATE ( ), and the end of time with respect to that timeline is “the last day,” which is returned by LAST_DATE ( ). Note: As far as the SQL type DATE is concerned, the beginning of time is DATE '0001−01−01' and the end of time is DATE '9999−12−31' (both values given here in the form of SQL DATE literals, which by definition are accurate to the day). Note, however, that SQL does also support finer granularities with its various TIMESTAMP types. 2. Let T be type TIMESTAMP, q.v. Then the corresponding timeline—the chronon timeline—is measured in chronons (i.e., the scale is one chronon); the beginning of time with respect to that timeline is “the first chronon” (returned by FIRST_TIMESTAMP ( )), and the end of time with respect to that timeline is “the last chronon” (returned by LAST_TIMESTAMP ( )). See TIMESTAMP for further discussion.

TIMESTAMP A point type, assumed for the purposes of this dictionary to be system defined and to have values that represent points on the chronon timeline. In other words, the scale, q.v., is one chronon, and the successor function is basically “next chronon,” meaning “add one chronon to the given timestamp” (i.e., it’s a function that, given a TIMESTAMP value ts, returns the TIMESTAMP value that’s the immediate successor of ts on the chronon timeline). Note: If timestamps of some coarser scale are needed (say milliseconds), the mechanism sketched under inheritance (point types) can be used to achieve the desired effect.

Examples: Here’s an example of a TIMESTAMP literal in Tutorial D:

TIMESTAMP ( '2014/8/25 18:33:45' )

(“33 minutes and 45 seconds past 6:00 pm, August 25th, 2014”; we’re assuming for simplicity in this example that the digits representing fractional parts of a second are all zeros and can therefore be omitted). As noted in the introduction to this part of the dictionary, symbols such as t01, t02, etc. used in examples elsewhere can be thought of as shorthand for such literals (or—less likely, perhaps—possibly for literals of type TIME, q.v.). Note: An SQL version of the literal shown above would look like this:

TIMESTAMP '2014-8-25 18:33:45'

Be aware, however, that SQL’s TIMESTAMP “type” isn’t really a type at all (in particular, it’s not the specific point type that’s defined in the present entry); rather, it’s a type generator.

TO value (SQL) See period.

transaction time The original term, much used in the literature, for logged time, q.v. Note, however, that transaction times are usually assumed in the literature to be part of the table (or relvar) to which they apply, and further that the table (or relvar) in question is usually assumed to be a base one specifically; in other words, there’s usually no distinct “transaction time table” (or relvar), and transaction times are usually assumed to be associated with base tables (or relvars) specifically. Contrast logged time relvar.

transaction timestamp (SQL) See system time.

TUC Temporal upward compatibility.

U_ A prefix (short for USING), used generically to refer to a variety of operators and other constructs that are useful in connection with relations (and/or relvars) with interval attributes. By way of example, here’s a slightly simplified and abbreviated definition for the operator U_MINUS, q.v.: Let ACL be a commalist of attribute names in which every attribute mentioned (a) is common to relations r1 and r2 and (b) is of some interval type. Then (and only then) USING (ACL) : r1 MINUS r2 denotes the U_difference with respect to ACL between r1 and r2 (in that order), and it’s shorthand for the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 MINUS t2 ) :
PACK t3 ON ( ACL )

Points arising from this definition (but note that the following points apply to U_ operators and U_ constructs in general, mutatis mutandis, not just to U_MINUS as such, and hence should be considered an implicit part of every “U_...” entry in this dictionary):

• The qualification “with respect to ACL” can be omitted from the definition if the commalist ACL is understood.

• Suppose ACL contains the names of all of the interval valued attributes that are common to r1 and r2. In general, then, there’ll be a distinct U_difference between r1 and r2 (in that order) for each distinct permutation of the attributes in each distinct subset of ACL.

• Suppose ACL is empty. Then (a) the prefix “USING (ACL)” reduces to just “USING ( )” and can be omitted from the concrete syntax (together with the colon separator), and (b) the U_MINUS operation reduces to its regular, or conventional, “non U_” counterpart. In other words, regular MINUS is a special case of U_MINUS.

Note: SQL has no direct support for U_MINUS, nor indeed for any U_ operators or other U_ constructs.

U_operator See U_.

U_assignment Let ACL be a commalist of attribute names such that every attribute mentioned (a) is interval valued and (b) is common to relvar R and relation r. Then (and only then) USING (ACL) : R := r denotes the U_assignment of relation r to relvar R with respect to ACL, and it’s equivalent to the following:

R := PACK r ON ( ACL )

Note: Explicit U_assignment is defined mainly for completeness; in practice, updates are much more likely to be done by means of U_INSERT and/or U_DELETE and/or U_UPDATE, q.v. See also PORTION.

U_comparison Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 theta r2 (where theta is any of the regular relational comparison operators “=”, “≠”, “⊆”, “⊂”, “⊇”, or “⊃”) denotes a U_comparison with respect to ACL between r1 and r2, and it’s equivalent to the following:

( UNPACK r1 ON ( ACL ) ) theta ( UNPACK r2 ON ( ACL ) )

Example: Let r1 and r2 be as follows:

 r1                      r2
┌───────────┐           ┌───────────┐
│ A         │           │ A         │
├═══════════┤           ├═══════════┤
│ [d01:d03] │           │ [d01:d02] │
│ [d02:d05] │           │ [d03:d05] │
│ [d04:d04] │           └───────────┘
└───────────┘

Then r1 = r2 is obviously false, but USING (A) : r1 = r2 is true. Note: When (as in this example) theta is “=”, the U_comparison reduces to U_equality, q.v.

U_COMPOSE See U_composition.

U_composition 1. (Dyadic case) Let relations r1 and r2 be joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of both r1 and r2 and (b) is of some interval type. Then (and only then) the expression USING (ACL) : r1 COMPOSE r2 denotes the U_composition with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 COMPOSE t2 ) :
PACK t3 ON ( ACL )

2. (N-adic case) Let relations r1, r2, ..., rn (n ≥ 0) be n-way joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of each of r1, r2, ..., rn, and (b) is of some interval type. Then (and only then) the expression USING (ACL) : COMPOSE {r1,r2,...,rn} denotes the U_composition with respect to ACL of r1, r2, ..., rn, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
        ........................  ,
       tn := UNPACK rn ON ( ACL ) ,
       tz := COMPOSE { t1 , t2 , ... , tn } ) :
PACK tz ON ( ACL )

U_DELETE 1. 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 (and only then) USING (ACL) : DELETE R WHERE bx denotes a U_DELETE WHERE (with respect to ACL and bx) on R, and it’s equivalent to the following:

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

2. Let relvar R and relation 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 (and only then) USING (ACL) : DELETE R r denotes the U_DELETE (with respect to ACL) of r from R, and it’s equivalent to the following:

R := USING ( ACL ) : R MINUS r

Note: Because operations of the form USING (ACL) : DELETE R WHERE bx are so much more common in practice than ones of the form USING (ACL) : DELETE R r, the unqualified name “U_DELETE” is usually taken to refer to a U_DELETE WHERE operation rather than a U_DELETE as such. Caveat lector. Note too that an “included” version of U_DELETE (“included U_DELETE”) is also defined (see included DELETE in Part I of this dictionary). The syntax is as for U_DELETE—not U_DELETE WHERE—except that I_DELETE appears in place of DELETE; likewise, the expansion is as for U_DELETE, except that I_MINUS appears in place of MINUS.

Examples: Consider a request to remove from the database of Fig. 8 the proposition “Supplier S4 was able to supply part P4 on days 5, 6, and 7.” Here’s a formulation using U_DELETE WHERE:

USING ( DURING ) :
DELETE SP_DURING WHERE SNO = SNO('S4') AND PNO = PNO('P4')
                 AND DURING ⊆ INTERVAL_DATE ( [ d05 : d07 ] )

Here by contrast is a formulation using U_DELETE without a WHERE:

USING ( DURING ) :
DELETE SP_DURING
       RELATION { TUPLE { SNO SNO('S4') , PNO PNO('P4') ,
                          DURING INTERVAL_DATE ( [ d05 : d07 ] } }

Note: In fact, however, this particular update can alternatively be achieved using a regular DELETE with a PORTION specification (i.e., without using U_DELETE at all):

DELETE SP_DURING WHERE SNO = SNO('S4') AND PNO = PNO('P4') :
       PORTION { DURING { INTERVAL_DATE ( [ d05 : d07 ] ) } } ;

U_DELETE WHERE See U_DELETE.

U_difference Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 MINUS r2 denotes the U_difference with respect to ACL between r1 and r2 (in that order), and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 MINUS t2 ) :
PACK t3 ON ( ACL )

Example: Given the sample values shown for relvars S_DURING and SP_DURING in Fig. 8, the expression

USING ( DURING ) : S_DURING MINUS SP_DURING { SNO , DURING }

yields:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d07:d07] │
│ S3  │ [d03:d04] │
│ S5  │ [d02:d99] │
│ S6  │ [d04:d04] │
└─────┴───────────┘

U_disjoint union Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 D_UNION r2 denotes the U_disjoint union with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 D_UNION t2 ) :
PACK t3 ON ( ACL )

Note: An n-adic version of this operator could also be defined if desired.

U_EQD U_equality dependency.

U_equality See U_comparison.

U_equality dependency An expression of the form USING (ACL) : rx = ry, where rx and ry are relational expressions of the same type and ACL is a commalist of attribute names such that every attribute mentioned (a) is interval valued and (b) is common to the relations denoted by rx and ry. It can be read as “The relations obtained by unpacking rx and ry on ACL are equal.” An important special case is as follows: Let R1 and R2 be relvars, not necessarily distinct. Let X1 and X2 be subsets of the heading of R1 and the heading of R2, respectively, such that there exists a possibly empty set of attribute renamings on R1 that maps X1 into X1′, say, where X1′ and X2 contain exactly the same attributes (in other words, X1′ and X2 are in fact one and the same). Further, let R1 and R2 be subject to the constraint that, at all times, (a) every tuple t1 in the result of unpacking R1 on ACL has an X1′ value that’s the X2 value for at least one tuple t2 in the result of unpacking R2 on ACL at the time in question, and (b) every tuple t2 in the result of unpacking R2 on ACL has an X2 value that’s the X1′ value for at least one tuple t1 in the result of unpacking R1 on ACL at the time in question. Then that constraint is a U_equality dependency (U_EQD for short)—very loosely, a U_EQD “on” relvars R1 and R2.

Example: The suppliers-and-shipments database (either the Fig. 6 or the Fig. 8 version) is subject to the constraint that whenever a supplier is under contract, that supplier must have some status and vice versa. Here’s a formulation of that constraint for the database of Fig. 8:

CONSTRAINT U_EQDX USING ( DURING ) :
                  S_DURING = S_STATUS_DURING { SNO , DURING } ;

This constraint is a U_EQD “on” S_DURING and S_STATUS_DURING; in effect, it says that each of S_DURING and S_STATUS_DURING has a foreign U_key, q.v., that references the other, where the foreign U_keys in question are both defined with respect to DURING.

U_exclusive union 1. (Dyadic case) Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 XUNION r2 denotes the U_exclusive union with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 XUNION t2 ) :
PACK t3 ON ( ACL )

2. (N-adic case) Let relations r1, r2, ..., rn (n ≥ 0) be all 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 (and only then) the expression USING (ACL) : XUNION {r1,r2,...,rn} denotes the U_exclusive union with respect to ACL of r1, r2, ..., rn, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
        ........................  ,
       tn := UNPACK rn ON ( ACL ) ,
       tz := XUNION { t1 , t2 , ... , tn } ) :
PACK tz ON ( ACL )

U_EXTEND See U_extension.

U_extension Let r be a relation, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type. Then (and only then) the expression USING (ACL) : EXTEND r : {A := exp} denotes a U_extension with respect to ACL of r, and it’s equivalent to the following:

WITH ( t1 := UNPACK r ON ( ACL ) ,
       t2 := EXTEND t1 : { A := exp } ) :
PACK t2 ON ( ACL )

Examples: Let relation r contain just two tuples, as follows (note, incidentally, that this relation is neither packed nor unpacked on DURING):

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d01:d05] │
│ S2  │ [d03:d04] │
└─────┴───────────┘

Then the following expression returns a relation of cardinality five:

USING ( DURING ) : EXTEND r : { X := POINT FROM DURING }

Here’s the result:

┌─────┬───────────┬─────┐
│ SNO │ DURING    │ X   │
├═════┼═══════════┼─────┤
│ S2  │ [d01:d01] │ d01 │
│ S2  │ [d02:d02] │ d02 │
│ S2  │ [d03:d03] │ d03 │
│ S2  │ [d04:d04] │ d04 │
│ S2  │ [d05:d05] │ d05 │
└─────┴───────────┴─────┘

By contrast, the following expression returns a relation of cardinality one:

USING ( DURING ) : EXTEND r : { Y := COUNT ( DURING ) }

Here’s the result:

┌─────┬───────────┬───┐
│ SNO │ DURING    │ Y │
├═════┼═══════════┼───┤
│ S2  │ [d01:d05] │ 1 │
└─────┴───────────┴───┘

U_GROUP See U_grouping.

U_grouping Let r be a relation, let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type, let BCL be a commalist of attribute names in which every attribute mentioned is an attribute of r not mentioned in ACL, and let X be an attribute name that’s distinct from that of every attribute of r apart possibly from those attributes mentioned in BCL. Then (and only then) the expression USING (ACL) : r GROUP {BCL} AS X denotes a U_grouping with respect to ACL of r on BCL, and it’s equivalent to the following:

WITH ( t1 := UNPACK r ON ( ACL ) ,
       t2 := t1 GROUP { BCL } AS X ) :
PACK t2 ON ( ACL )

Example: Let relation r be as follows:

┌─────┬─────┬───────────┐
│ SNO │ PNO │ DURING    │
├═════┼═════┼═══════════┤
│ S2  │ P1  │ [d08:d10] │
│ S2  │ P2  │ [d09:d10] │
│ S4  │ P2  │ [d07:d09] │
│ S4  │ P4  │ [d07:d08] │
└─────┴─────┴───────────┘

Then the expression

USING ( DURING ) : r GROUP { PNO } AS PNO_REL

yields the following result:

┌─────┬───────────┬─────────┐
│ SNO │ DURING    │ PNO_REL │
├═════┼═══════════┼─────────┤
│     │           │ ┌─────┐ │
│ S2  │ [d08:d08] │ │ PNO │ │
│     │           │ ├═════┤ │
│     │           │ │ P1  │ │
│     │           │ └─────┘ │
│     │           │ ┌─────┐ │
│ S2  │ [d09:d10] │ │ PNO │ │
│     │           │ ├═════┤ │
│     │           │ │ P1  │ │
│     │           │ │ P2  │ │
│     │           │ └─────┘ │
│     │           │ ┌─────┐ │
│ S4  │ [d07:d08] │ │ PNO │ │
│     │           │ ├═════┤ │
│     │           │ │ P2  │ │
│     │           │ │ P4  │ │
│     │           │ └─────┘ │
│     │           │ ┌─────┐ │
│ S4  │ [d09:d09] │ │ PNO │ │
│     │           │ ├═════┤ │
│     │           │ │ P2  │ │
│     │           │ └─────┘ │
└─────┴───────────┴─────────┘

U_included difference Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 I_MINUS r2 denotes the U_included difference with respect to ACL between r1 and r2 (in that order), and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 I_MINUS t2 ) :
PACK t3 ON ( ACL )

U_INSERT Let relvar R and relation 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 (and only then) USING (ACL) : INSERT R r denotes the U_INSERT (with respect to ACL) of r into R, and it’s equivalent to the following:

R := USING ( ACL ) : R UNION r

Note: A “disjoint” version of U_INSERT (“disjoint U_INSERT”) is also defined (see disjoint INSERT in Part I of this dictionary). 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.

Example: Let relvar SP_DURING be as shown in Fig. 6. Then the following U_INSERT—

USING ( DURING ) :
INSERT SP_DURING
       RELATION { TUPLE { SNO  SNO('S2') ,
                          PNO  PNO('P1') ,
                          DURING INTERVAL_DATE ( [ d03 : d06 ] ) } } ;

—will yield the following result (the only change is in the tuple for S2 and P1):

SP_DURING
┌─────┬─────┬───────────┐
│ SNO │ PNO │ DURING    │
├═════┼═════┼═══════════┤
│ S2  │ P1  │ [d02:d06] │
│ S2  │ P2  │ [d03:d03] │
│ S3  │ P5  │ [d05:d07] │
│ S4  │ P2  │ [d06:d09] │
│ S4  │ P4  │ [d04:d08] │
│ S6  │ P3  │ [d03:d03] │
│ S6  │ P3  │ [d05:d05] │
└─────┴─────┴───────────┘

U_INTERSECT See U_intersection.

U_intersection 1. (Dyadic case) Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 INTERSECT r2 denotes the U_intersection with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 INTERSECT t2 ) :
PACK t3 ON ( ACL )

2. (N-adic case) Let relations r1, r2, ..., rn (n ≥ 0) be all 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 (and only then) the expression USING (ACL) : INTERSECT {r1,r2,...,rn} denotes the U_intersection with respect to ACL of r1, r2, ..., rn, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
        ........................  ,
       tn := UNPACK rn ON ( ACL ) ,
       tz := INTERSECT { t1 , t2 , ... , tn } ) :
PACK tz ON ( ACL )

Example (dyadic case): Given the sample values shown in Fig. 6, the expression

USING ( DURING ) : SP_DURING { SNO , DURING } INTERSECT
                             S_STATUS_DURING { SNO , DURING }

yields:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d02:d04] │
│ S4  │ [d04:d07] │
│ S6  │ [d03:d03] │
│ S6  │ [d05:d05] │
└─────┴───────────┘

Note: U_intersection is a special case of U_join, q.v.

U_JD U_join dependency.

U_JOIN See U_join.

U_join 1. (Dyadic case) Let relations r1 and r2 be joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of both r1 and r2 and (b) is of some interval type. Then (and only then) the expression USING (ACL) : r1 JOIN r2 denotes the U_join with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 JOIN t2 ) :
PACK t3 ON ( ACL )

2. (N-adic case) Let relations r1, r2, ..., rn (n ≥ 0) be n-way joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of each of r1, r2, ..., rn, and (b) is of some interval type. Then (and only then) the expression USING (ACL) : JOIN {r1,r2,...,rn} denotes the U_join with respect to ACL of r1, r2, ..., rn, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
        ........................  ,
       tn := UNPACK rn ON ( ACL ) ,
       tz := JOIN { t1 , t2 , ... , tn } ) :
PACK tz ON ( ACL )

Example (dyadic case): With reference to Fig. 8, the expression

USING ( DURING ) : S_STATUS_DURING JOIN SP_DURING

is a possible formulation of the query “Get (SNO, STATUS, PNO, DURING) tuples such that DURING denotes a maximal interval of days throughout which supplier SNO (a) had status STATUS and (b) was able to supply part PNO.”

Note: If r1 and r2 are of the same type, then U_join degenerates to U_intersection, q.v. See also U_TIMES.

U_join dependency Let H be a heading, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is one of the attributes in H and (b) is of some interval type. Then a U_join dependency (U_JD) with respect to ACL and H is an expression of the form USING (ACL) : {X1,X2,...,Xn}, such that the set theory union of X1, X2, ..., Xn is equal to H. Note: The phrase U_JD with respect to ACL and H can be abbreviated to U_JD with respect to ACL if H is understood; to U_JD with respect to H if ACL is understood; and to just U_JD if ACL and H are both understood.

Let relation r have heading H and let USING (ACL) : {X1,X2,...,Xn} be a U_JD, UJ say, with respect to ACL and H. If r is U_equal to the U_join of its U_projections on X1, X2, …, Xn, then r satisfies UJ; otherwise r violates UJ. Note: The U_equality comparison, the U_join, and the U_projections mentioned in this definition must all be with respect to ACL (i.e., they must all have a prefix of the form “USING (ACL) :”).

Now let relvar R have heading H. Then R is subject to the U_JD UJ—equivalently, the U_JD UJ holds in R—if and only if every relation r that can ever be assigned to R satisfies that U_JD UJ. The U_JDs that hold in relvar R are the U_JDs of R, and they serve as constraints on R.

Note that U_JDs are defined with respect to some heading, not with respect to some relation or some relvar. Note too that from a formal point of view, a U_JD is just an expression: an expression that, when interpreted with respect to some specific relation, becomes a proposition that, by definition, evaluates to either TRUE or FALSE. Now, it’s common informally to define USING (ACL) : {X1,X2,...,Xn} to be a U_JD only if it actually holds in the pertinent relvar—but that definition leaves no way of saying a given U_JD fails to hold in some relvar, because, by that definition, a U_JD that fails to hold isn’t a U_JD in the first place. Note finally that it’s immediate from the definition that relvar R can be nonloss decomposed into its U_projections (using ACL) on X1, X2, ..., and Xn if and only if the U_JD USING (ACL) : {X1,X2,...,Xn} holds in R.

U_key Let ACL and K be commalists of attribute names of relvar R, such that every attribute mentioned in ACL is also mentioned in K. Then (and only then) the specification

USING ( ACL ) : KEY { K }

—part of the definition of R—defines {K} to be a U_key for relvar R, and it’s shorthand for all three of the following in combination:

PACKED ON ( ACL )
WHEN UNPACKED ON ( ACL ) THEN KEY { K }
KEY { K }

See PACKED ON; WHEN / THEN; see also foreign U_key.

Examples: See the definitions of relvars S_DURING, S_STATUS_DURING, and SP_DURING in Fig. 7.

U_key constraint A generalized form of key constraint (see Part I of this dictionary) in which the role usually played by a key as such is played by a U_key (q.v.) instead.

U_MATCHING See U_semijoin.

U_MINUS See U_difference.

U_operator Same as U_ operator.

U_product Let relations r1 and r2 have no attribute names in common; then (and only then) the expression USING ( ) : r1 TIMES r2 denotes the U_product of r1 and r2. Note, however, that this expression simply and necessarily reduces to the regular cartesian product r1 TIMES r2. Note: An n-adic version of this operator could also be defined if desired.

U_projection Let r be a relation, let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type, and let BCL be a commalist of attribute names such that every attribute mentioned in ACL is also mentioned in BCL. Then (and only then) the expression USING (ACL) : r {BCL} denotes the U_projection with respect to ACL of r on BCL, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := r2 { BCL } ) :
PACK t2 ON ( ACL )

Example: With reference to Fig. 8, the expression

USING ( DURING ) : SP_DURING { SNO , DURING }

is a possible formulation of the query “Get (SNO,DURING) pairs such that DURING designates a maximal interval of days during which supplier SNO was able to supply at least one part.”

U_RENAME See U_renaming.

U_renaming Let r be a relation, let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type, and let r have an attribute called A, not mentioned in ACL, and no attribute called B. Then (and only then) the expression USING (ACL) : r RENAME {A AS B} denotes an (attribute) U_renaming of r, and it’s equivalent to the following:

WITH ( t1 := UNPACK r ON ( ACL ) ,
       t2 := t1 RENAME { A AS B } ) :
PACK t2 ON ( ACL )

Note: In fact, the foregoing expression reduces to just:

PACK ( r RENAME { A AS B } ) ON ( ACL )

U_restriction Let r be a relation, let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type, and let bx be a restriction condition on r. Then (and only then) the expression USING (ACL) : r WHERE bx denotes the U_restriction with respect to ACL of r according to bx, and it’s equivalent to the following:

WITH ( t1 := UNPACK r ON ( ACL ) ,
       t2 := r WHERE bx ) :
PACK t2 ON ( ACL )

Examples: Suppose relvar S_DURING contains just two tuples, as follows:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d01:d03] │
│ S2  │ [d05:d09] │
└─────┴───────────┘

Then the following expression—

USING ( DURING ) :
      S_DURING WHERE DURING ⊆ INTERVAL_DATE ( [ d03 : d07 ] )

—returns this result:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d03:d03] │
│ S2  │ [d05:d07] │
└─────┴───────────┘

U_semidifference Let relations r1 and r2 be joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of both r1 and r2 and (b) is of some interval type. Then (and only then) the expression USING (ACL) : r1 NOT MATCHING r2 denotes the U_semidifference with respect to ACL between r1 and r2 (in that order), and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 NOT MATCHING t2 ) :
PACK t3 ON ( ACL )

In other words, it’s shorthand for:

USING ( ACL ) : ( r1 MINUS ( USING ( ACL ) : r1 MATCHING r2 ) )

(a U_difference in which the second operand is a U_semijoin).

U_semijoin Let relations r1 and r2 be joinable, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of both r1 and r2 and (b) is of some interval type. Then (and only then) the expression USING (ACL) : r1 MATCHING r2 denotes the U_semijoin with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 MATCHING t2 ) :
PACK t3 ON ( ACL )

In other words, it’s shorthand for:

USING ( ACL ) : ( ( USING ( ACL ) : r1 JOIN r2 ) { ACL } )

(a U_projection of a U_join).

U_summarization Let relations r1 and r2 be such that the heading of r2 is some subset of that of r1, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r2 (and therefore of r1 as well) and (b) is of some interval type. Then (and only then) the expression USING (ACL) : SUMMARIZE r1 PER {r2} : { B := exp } denotes a U_summarization of r1 (with respect to ACL) according to r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := SUMMARIZE t1 PER ( t2 ) : { B := exp } ) :
PACK t3 ON ( ACL )

Example: With reference to Fig. 8, consider the following query: At any given time, if there are any shipments at all at that time, then there’s some part number pmax such that, at that time, (a) at least one supplier is able to supply part pmax, but (b) no supplier is able to supply any part with a part number greater than pmax. So, for each part number that has ever been such a pmax value, get that part number together with the maximal interval(s) of days during which it actually was that pmax value. Here’s a possible formulation:

USING ( DURING ) : SUMMARIZE SP_DURING PER ( SP_DURING { DURING } ) :
                                              { PMAX := MAX ( PNO ) }

Explanation: Relations r1 and r2 here are the current value of SP_DURING and the current value of the projection of SP_DURING on {DURING}, respectively. These relations are each unpacked on DURING. Then, each DURING value—by definition a unit interval—in the unpacked form of r2 has appended to it the corresponding PMAX value, which is computed by examining all tuples with that DURING value in the unpacked form of r1; the result of this step is a relation with attributes DURING and PMAX. That relation is then packed on DURING.

U_SUMMARIZE See U_summarization.

U_TIMES See U_product.

U_UNGROUP See U_ungrouping.

U_ungrouping Let r be a relation, let r have a relation valued attribute B, and let ACL be a commalist of attribute names in which every attribute mentioned (a) is an attribute of r and (b) is of some interval type. Then (and only then) the expression USING (ACL) : r UNGROUP B denotes the U_ungrouping with respect to ACL of r on B, and it’s equivalent to the following:

WITH ( t1 := UNPACK r ON ( ACL ) ,
       t2 := t1 UNGROUP B ) :
PACK t2 ON ( ACL )

Example: Let r be the relation shown as the result in the example under grouping. Then the expression

USING ( DURING ) : r UNGROUP PNO_REL

returns the relation used as input in that example.

U_UNION See U_union.

U_union 1. (Dyadic case) Let relations r1 and r2 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 (and only then) the expression USING (ACL) : r1 UNION r2 denotes the U_union with respect to ACL of r1 and r2, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
       t3 := t1 UNION t2 ) :
PACK t3 ON ( ACL )

Note: In fact, the foregoing expression reduces to just:

PACK ( r1 UNION r2 ) ON ( ACL )

2. (N-adic case) Let relations r1, r2, ..., rn (n ≥ 0) be all 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 (and only then) the expression USING (ACL) : UNION {r1,r2,...,rn} denotes the U_union with respect to ACL of r1, r2, ..., rn, and it’s equivalent to the following:

WITH ( t1 := UNPACK r1 ON ( ACL ) ,
       t2 := UNPACK r2 ON ( ACL ) ,
        ........................  ,
       tn := UNPACK rn ON ( ACL ) ,
       tz := UNION { t1 , t2 , ... , tn } ) :
PACK tz ON ( ACL )

Note: In fact, the foregoing expression reduces to just:

PACK ( UNION { r1 , r2 , ... , rn } ) ON ( ACL )

Example (dyadic case): Given the sample values shown in Fig. 6, the expression

USING ( DURING ) :
   ( S_STATUS_DURING WHERE SNO = SNO('S4') ) { SNO , DURING }
     UNION
   ( SP_DURING WHERE SNO = SNO('S4') ) { SNO , DURING }

(a U_union of two regular projections) yields:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S4  │ [d04:d09] │
└─────┴───────────┘

U_UPDATE 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 (and only then)

USING ( ACL ) : UPDATE R WHERE bx : { attribute assignments }

denotes a U_UPDATE (with respect to ACL and bx) on R, and it’s equivalent to the following:

WITH ( t1 := UNPACK R ON ( ACL ) ,
       t2 := t1 WHERE NOT ( bx ) ,
       t3 := t1 MINUS t2 ,
       t4 := EXTEND t3 : { attribute assignments } ,
       t5 := t2 UNION t4 ) :
R := PACK t5 ON ( ACL )

Example: Consider a request to update the database of Fig. 8 to replace the proposition “Supplier S4 was able to supply part P2 on day 9” by the proposition “Supplier S4 was able to supply part P2 on day 10”: Here’s a formulation using U_UPDATE:

USING ( DURING ) :
UPDATE SP_DURING WHERE SNO = SNO('S4') AND PNO = PNO('P2')
                 AND DURING = INTERVAL_DATE ( [ d09 : d09 ] ) :
               { DURING := INTERVAL_DATE ( [ d10 : d10 ] ) } ;

Note: In fact, however, this particular update can alternatively be achieved using a regular UPDATE with a PORTION specification (i.e., without using U_UPDATE at all):

UPDATE SP_DURING WHERE SNO = SNO('S4') AND PNO = PNO('P2') :
       PORTION { DURING { INTERVAL_DATE ( [ d09 : d09 ] ) } } :
     { DURING := INTERVAL_DATE ( [ d10 : d10 ] ) } ;

U_update A U_assignment, U_INSERT, disjoint U_INSERT, U_DELETE, included U_DELETE, or U_UPDATE operation, q.v.

U_XUNION See U_exclusive union.

unfolding Term sometimes used as a synonym for unpacking, q.v. (or for an operation of the same general nature as unpacking).

union (interval theory) Let i1 = [b1:e1] and i2 = [b2:e2] be intervals of the same type. Then:

• If i1 MERGES i2 is true, then (and only then) the expression i1 UNION i2 denotes the union of i1 and i2, and it returns [MIN{b1,b2}:MAX{e1,e2}].

• Otherwise i1 UNION i2 is undefined.

Observe that the foregoing definition guarantees that the result (when it’s defined) isn’t just some set of points but is, rather, an interval specifically. Note: SQL has no direct support for the interval union operator.

Example: Let i1 and i2 be [d02:d07] and [d04:d10], respectively. Then i1 UNION i2 is [d02:d10]. By contrast, let i1 and i2 be [d02:d04] and [d07:d10], respectively; then i1 UNION i2 is undefined. Incidentally, note that interval union, unlike set theory union (and unlike the union operator of the relational algebra also, come to that) has no corresponding identity value. (If it had one, it would be the empty interval of the applicable type, and intervals are never empty.) However, the operator is both commutative and associative.

unit interval An interval containing exactly one point. In other words, the interval [b:e] is a unit interval if and only if b = e. See also point extractor.

unitemporal See bitemporal.

universal interval The interval containing all of the points of the pertinent point type; in other words, the interval

[ FIRST_T ( ) : LAST_T ( ) ]

(where T is the point type in question). Note: This definition needs some slight refinement in the case where the point type in question is cyclic. Further details are beyond the scope of this dictionary.

UNPACK See unpacking. Note: SQL has no direct support for the UNPACK operator.

unpacked constraint Term that might be used (but usually isn’t) to mean a WHEN / THEN constraint, q.v.

Examples: See the example under WHEN / THEN.

unpacked form 1. Let relation r have interval attributes A1, A2, ..., An (n ≥ 0). Then r is in unpacked form with respect to A1, A2, ..., An if and only if r is equal to the result of evaluating the expression UNPACK r ON (A1,A2,...,An). 2. Let relvar R have interval attributes A1, A2, ..., An (n ≥ 0). Then R is in unpacked form with respect to A1, A2, ..., An if and only if every relation r that can ever be assigned to R is in unpacked form with respect to A1, A2, ..., An. Note: The phrase unpacked form with respect to A1, A2, ..., An can be abbreviated to just unpacked form if the attributes A1, A2, ..., An are understood. See unpacking for examples and further discussion.

unpacking 1. (Single-attribute UNPACK) Let relation r have an interval attribute A. Then (and only then) the expression UNPACK r ON (A) denotes the unpacking of r on A, and it’s equivalent to the following:

WITH ( r1 := r GROUP { A } AS X ,
       r2 := EXTEND r1 : { X := EXPAND ( X ) } ) :
r2 UNGROUP X

2. (Multiattribute UNPACK): Let relation r have interval attributes A1, A2, ..., An (n > 1). Then (and only then) the expression UNPACK r ON (A1, A2, ..., An) denotes the unpacking of r on A1, A2, ..., An, and it’s equivalent to the following—

UNPACK ( ... ( UNPACK ( UNPACK r ON ( B1 ) ) ON ( B2 ) ) ... ) ON ( Bn )

—where the sequence of attribute names B1, B2, ..., Bn consists of some arbitrary permutation of the specified sequence of attribute names A1, A2, ..., An.

Examples: 1. Let relation r be as follows:

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d02:d04] │
│ S2  │ [d03:d05] │
│ S4  │ [d02:d05] │
│ S4  │ [d04:d06] │
│ S4  │ [d09:d10] │
└─────┴───────────┘

Then the unpacked form of r looks like this (note that every DURING value in that unpacked form is a unit interval specifically):

┌─────┬───────────┐
│ SNO │ DURING    │
├═════┼═══════════┤
│ S2  │ [d02:d02] │
│ S2  │ [d03:d03] │
│ S2  │ [d04:d04] │
│ S2  │ [d05:d05] │
│ S4  │ [d02:d02] │
│ S4  │ [d03:d03] │
│ S4  │ [d04:d04] │
│ S4  │ [d05:d05] │
│ S4  │ [d06:d06] │
│ S4  │ [d09:d09] │
│ S4  │ [d10:d10] │
└─────┴───────────┘

2. Let relation r be as follows:

┌─────────┬───────────┐
│ A1      │ A2        │
├═════════┼═══════════┤
│ [P1:P1] │ [d08:d09] │
│ [P1:P2] │ [d08:d08] │
│ [P3:P4] │ [d07:d08] │
└─────────┴───────────┘

Then unpacking r on A1 and A2 (in either order) yields:

┌─────────┬───────────┐
│ A1      │ A2        │
├═════════┼═══════════┤
│ [P1:P1] │ [d08:d08] │
│ [P1:P1] │ [d09:d09] │
│ [P2:P2] │ [d08:d08] │
│ [P3:P3] │ [d07:d07] │
│ [P3:P3] │ [d08:d08] │
│ [P4:P4] │ [d07:d07] │
│ [P4:P4] │ [d08:d08] │
└─────────┴───────────┘

until further notice States of affairs that hold at the present time are often open ended; for example, a given supplier might be under contract and the date of termination of that contract might not currently be known. Such a state of affairs can thus be said to hold, or to be in effect, until further notice. Unfortunately, “until further notice,” whatever else it might be, is most certainly not a value; as a consequence, it can’t be explicitly recorded as such in a relation (nor in a relational database, a fortiori). In fact, it’s precisely for this reason that horizontal decomposition, q.v., is recommended as the best way to do temporal database design. By contrast, in a design that consists of during relvars only, some artificial value—typically “the end of time,” q.v.—will have to be used as the end point for any temporal interval for which the true end point is unknown.

valid time The original term, much used in the literature, for stated time, q.v.

vertical decomposition (Of temporal relvars) Informal term used to refer to the decomposition (via U_projection, q.v.) of a during relvar that’s not in sixth normal form, q.v., into a set of during relvars that are.

Example: Relvars CS_DURING, CS_STATUS_DURING, and CS_STATUS_DURING, discussed under COMBINED_IN, might be regarded as the result of applying vertical decomposition to the relvar CS (not explicitly shown in that discussion) that’s the U_join of those three relvars on DURING. That relvar CS isn’t in sixth normal form, as the corresponding predicate makes clear:

If END (DURING) is “the end of time,” then supplier SNO (a) has been under contract, (b) has had status STATUS, and (c) has been located in city CITY, ever since day BEGIN (DURING) (and not the day immediately before day BEGIN (DURING)) and will continue to be or do so until further notice; otherwise DURING denotes a maximal interval of days throughout which supplier SNO (a) was under contract, (b) had status STATUS, and (c) was located in city CITY.

WHEN / THEN A specification used in Tutorial D as part of a relvar definition to impose a constraint to the effect that if the pertinent relvar were to be kept in a certain unpacked form, then a certain attribute combination would constitute a key for the relvar in question (a regular key, that is, not a U_key, q.v.). Let ACL and K be commalists of attribute names of relvar R, such that every attribute mentioned in ACL is also mentioned in K, and let X be a commalist of all attribute names of R apart from those in K. Then the specification WHEN UNPACKED ON (ACL) THEN KEY {K}—part of the definition of relvar R—ensures that any attempt to update R will fail if the unpacked form of the result on ACL violates the functional dependency {K} → {X}, and thereby further ensures that R won’t suffer from the contradiction problem (as defined elsewhere in this part of the dictionary) with respect to ACL. Note: In practice, WHEN / THEN specifications will usually be implicit (see U_key).

Example: Consider relvar S_STATUS_DURING (either the Fig. 6 version or the Fig. 8 version, it makes no difference). Here’s a possible definition for that relvar (irrelevant details omitted):

VAR S_STATUS_DURING BASE RELATION
  { SNO SNO , STATUS INTEGER , DURING INTERVAL_DATE }
    WHEN UNPACKED ON ( DURING ) THEN KEY { SNO , DURING } ... ;

The effect of the WHEN / THEN specification here is to ensure that any attempt to update S_STATUS_DURING in such a way as to cause the functional dependency {SNO,DURING} → {STATUS} to be violated in the unpacked form of that relvar on DURING will fail. Note: A variety of U_update operators, q.v., are provided in order to assist with the process of updating a relvar to which a WHEN / THEN constraint applies.

The specification WHEN UNPACKED ON (ACL) THEN KEY {K} on relvar R is trivial—i.e., has no effect—if ACL is empty or if K is the entire heading of R.

when / then constraint A constraint—see WHEN / THEN—that prevents the contradiction problem (as defined elsewhere in this part of the dictionary) from occurring. See also U_key.

WITHOUT OVERLAPS (SQL) See period.