Paper.dvi

TIP: A Temporal Extension to Informix∗
Computer Science Department, Stanford University Our research in temporal data warehouses has led us to with the following datatypes related to time: (1) Chronon require a relational database system with full SQL as well as represents a specific point in time, like SQL’s DATE type.
rich temporal support. Most commercial relational database (2) Span is a duration of time, which can be positive or systems today support only a DATE type (or its variants).
negative. (3) Instant is either a Chronon or a NOW-relative A DATE attribute can be used to timestamp a tuple with a time. A NOW-relative Instant is represented by an offset specific point in time (e.g., “this patient was taking Prozac of type Span from the symbol NOW, whose interpretation as of September 1”). But for general temporal modeling, a timestamp should be able to consist of multiple time periods (e.g., “this patient was prescribed Prozac from January to Instant’s. The first one marks the start of the period and the April, and then from July to October”). Furthermore, the second one marks the end. (5) Element is a set of Period’s.
DATE type does not include NOW, a special symbol interpreted These datatypes allow complex temporal data to be as the current (transaction) time with many uses in temporal Consider a table that records the prescription modeling. Although it is possible to encode NOW and sets history for patients. We store a patient’s date of birth as of time periods using DATE as the only type of timestamp, a Chronon, dosage frequency as a Span, and prescription doing so can lead to cumbersome queries and inefficient periods as an Element. The following SQL creates the table: implementations. An alternative is to use one of the available temporal database prototypes, but none of them fully meets (doc CHAR(20),patient CHAR(20),patientDOB Chronon, the needs of our temporal data warehousing application, as drug CHAR(20),dosage INT,freq Span,valid Element) we discuss in the extended version of this description.1 TIP also defines an extensive collection of support rou- Recently the major relational database vendors have intro- tines for its datatypes, including casts, arithmetic opera- duced technologies that allow their systems to be extended tors (+, -, *, /) and comparison operators (=, <, >, etc.), with software plugins developed by users or third-party ven- TIP-defined routines (union, intersect, difference, dors. Examples include Informix DataBlades, DB2 Exten- overlaps, contains, length, etc.), and aggregate func- ders, and Oracle Cartridges. Some time-related extensions tions (group union, group intersect, etc.). For more have been developed, such as time-series, spatiotemporal, details, please refer to the extended version.1 With these sup- and temporal index extensions. Unfortunately, they do not port routines, we are able to express many temporal opera- provide the general-purpose temporal support we need.
tions without adding new language constructs to SQL.
Hence, we built TIP (Temporal Information Processor), For example, the following query finds all patients who an extension to the Informix database system based on its were prescribed Tylenol when they were less than w weeks DataBlade technology. The TIP DataBlade implements a old, for input parameter w. In the query, start is a TIP rich set of datatypes and routines that support modeling routine that returns the start time of the first period in an and querying of temporal data. Once the TIP DataBlade Element. The “::” is Informix’s notation for explicit cast, is installed, TIP datatypes and routines become available needed here to convert the string into a Span.
to users as if they were built into the DBMS. Client applications connect directly to a TIP-enabled database through a standard API such as ODBC or JDBC, and they AND start(valid)-patientDOB < ’7days’::Span * :w manipulate TIP datatypes using C and Java libraries provided For another example, suppose we want to know who has by TIP. We have developed a Java client with a powerful taken Diabeta and Aspirin simultaneously, and exactly when.
graphical user interface for querying and browsing temporal This temporal self-join query can be expressed as follows: data. The architecture and implementation of TIP are further SELECT p1.*, p2.*, intersect(p1.valid, p2.valid)FROM Prescription p1, Prescription p2 described in the extended version,1 which also includes WHERE p1.drug=’Diabeta’ AND p2.drug=’Aspirin’ screen shots from our temporal browsing interface.
The TIP DataBlade extends the Informix type system Finally, we compute how long each patient has been on med- This work was supported by the National Science Foundation under grant IIS-9811947 and by an Informix Software Grant.
ication using the aggregate function group union, which re- 1http://www-db.stanford.edu/pub/papers/yyw-tipdemo.ps turns the union of a collection of Element’s.
SELECT patient, length(group union(valid))FROM Prescription GROUP BY patient Note that TIP does not introduce a new temporal query language, such as TSQL2. Instead, TIP enables temporalqueries expressed in SQL by providing an extensive set ofbuilt-in datatypes and routines. See the extended version1for more discussion on related and future work.

Source: ftp://ftp.cse.buffalo.edu/users/azhang/disc/disc01/cd1/out/demos/sigmod/tipatemporalextjuhuj/yyw-tipdemo.pdf