Interval Types

Our discussion of intervals in the previous section was mostly intuitive in nature; now we need to approach the issue more formally. First of all, observe that the granularity of the interval [d04,d10] is “days.” More precisely, we could say it istype DATE, by which term we mean that member of the usual family of “datetime” data types whose precision is “day” (as opposed to,

Table 5.4

The Suppliers and Parts Database (Sample Values)—Final Fully Temporal Version, Using Intervals

S_DURING S# SNAME STATUS CITY DURING

S1 Smith 20 London [d04,d10] S2 Jones 10 Paris [d07,d10] S2 Jones 10 Paris [d02,d04] S3 Blake 30 Paris [d03,d10] S4 Clark 20 London [d04,d10] S5 Adams 30 Athens [d02,d10] SP_DURING S# P# DURING S1 P1 [d04,d10] S1 P2 [d05,d10] S1 P3 [d09,d10] S1 P4 [d05,d10] S1 P5 [d04,d10] S1 P6 [d06,d10] S2 P1 [d02,d04] S2 P2 [d03,d03] S2 P1 [d08,d10] S2 P2 [d09,d10] S3 P2 [d08,d10] S4 P2 [d06,d09] S4 P4 [d04,d08] S4 P5 [d05,d10]

say, “hour” or “millisecond” or “month”). This observation allows us to pin down the exact type of the interval in question, as follows:

• First and foremost, of course, it is someinterval type; this fact by itself is sufficient to determine theoperatorsthat are applicable to the interval value in question (just as to say that, for example, a value

r is of some relation type is sufficient to determine the opera- tors—JOIN, etc.—that are applicable to that valuer).

• Second, the interval in question is, very specifically, an interval from onedateto another, andthisfact is sufficient to determine the set of

interval valuesthat constitute the interval type in question.

The specific type of [d04,d10] is thus INTERVAL(DATE), where:

a. INTERVAL is atype generator(like RELATION inTutorial D,

or “array” in conventional programming languages) that allows us to define a variety of specific interval types (see further discussion below);

b. DATE is thepoint typeof this specific interval type.

It is important to note that, in general, point typePTdetermines both the type and the precision of the start and end points—and all points in between—of values of type INTERVAL(PT). (In the case of type DATE, of course, the precision is implicit.)

Note: Normally, we do not regard precision as part of the applicable type but, rather, as an integrity constraint. Given the declarations DECLARE X TIMESTAMP(3) and DECLARE Y TIMESTAMP(6), for example, X and Y are of the same type but are subject to different constraints (X is constrained to hold millisecond values and Y is constrained to hold microsecond values). Strictly speaking, therefore, to say that, for example, TIMESTAMP(3)—or DATE—is a legal point type is to bundle together two concepts that should really be kept separate. Instead, it would be better to define two types T1 and T2, both with a TIMESTAMP possible represen- tation but with different “precision constraints,” and then say that T1 and T2 (not, for example, TIMESTAMP(3) and TIMESTAMP(6)) are legal point types. For simplicity, however, we follow conventional usage in this chapter and pretend that precision is part of the type.

What properties must a type possess if it is to be legal as a point type? Well, we have seen that an interval is denoted by its start and end points; we

have also seen that (at least informally) an interval consists of a set of points. If we are to be able to determine the complete set of points, given just the start pointsand the end pointe, we must first be able to determine the point that immediately follows (in some agreed ordering) the points. We call that immediately following point thesuccessorofs; for simplicity, let us agree to refer to it ass+1. Then the function by whichs+1 is determined fromsis thesuccessor function for the point type (and precision) in question. That successor function must be defined for every value of the point type, except the one designated as “last.” (There will also be one point designated as “first,” which is not the successor of anything.)

Having determined thats+1 is the successor ofs, we must next deter- mine whether or nots+1 comes aftere, according to the same agreed order- ing for the point type in question. If it does not, thens+1 is indeed a point in [s,e], and we must now consider the next point, s + 2. Continuing this process until we come to the first points+nthat comes aftere(that is, the successor ofe), we will discover every point of [s,e].

Noting thats+nis in fact the successor ofe(that is, it actually comes immediately aftere), we can now safely say that the only property a typePT

must have to be legal as a point type is that a successor function must be defined for it. The existence of such a function implies that there must be a

total orderingfor the values inPT(and we can therefore assume the usual comparison operators—“<,” “≥,” etc.—are available and defined for all pairs ofPTvalues).

By the way, you will surely have noticed by now that we are no longer talking about temporal data specifically. Indeed, most of the rest of this chap- ter is about intervals in general rather than time intervals in particular, though we will consider certain specifically temporal issues in Section 5.11.

Here then (at last) is a precise definition: LetPTbe a point type. Then aninterval(orinterval value)iof type INTERVAL(PT) is a scalar value for which two monadic scalar operators (START and END) and one dyadic operator (IN) are defined, such that:

a. START(i) and END(i) each return a value of typePT. b. START(i)≤END(i).

c. Let p be a value of type PT. Then p IN i is true if and only if START(i)≤pandp≤END(i) are both true.

Note the appeals in this definition to the defined successor function for typePT. Note also that, by definition, intervals are always nonempty (that is, there is always at least one point “IN” any given interval).

Observe very carefully that a value of type INTERVAL(PT) is ascalar

value—that is, it has no user-visible components. It is true that it does have a possible representation—in fact, several possible representations, as we saw in the previous section—and those possible representations in turn do have user-visible components, but the interval valueper sedoes not. Another way of saying the same thing is to say that intervals areencapsulated.