Developing Time-Oriented Applications in SQL | Richard T. Snodgrass

# BITEMPORAL TABLES

## Contents

> Implementation Details

> Bitemporal Tables

> Modifications

> > Current Modification
> > Sequenced Modification
> > Nonsequenced Modification

> Queries

> > Timeslice Querie
> > The Spectrum of Bitemporal Queries
> > Multi-table queries involving bitemporal tables

> Integrity Constraints

> Temporal Partitioning

> > VT Current/TT Current + Bitemporal State
> > VT State/TT Current + State/State Archive
> > VT State/TT Current + State/Event Archive

> Vacuuming

#

# Implementation Details (Oracle8 Server)

Oracle's syntax contains a few small deviations from the standard. Most significantly, the ASSERT mechanism in standard SQL is absent and must be simulared with triggers. Also, all SQL statements must be terminated with a semicolon. This includes the individual statements nested in larger DDL statements like CREATE TRIGGER. Secondly, the current date is stored in the ghost column SYSDATE, which is silently considered a column of each table in the system. Oracle also does not support date literals, so dates must be created using the TO_DATE function. TO_DATE takes two string parameters: the string of characters representing the date value and a pattern string. In all examples with TO_DATE in this chapter the pattern string 'MM/DD/YYYY' is used, indicating a two digit month, a two digit day, and a four digit year delimited by forward slashes. Since triggers are used extensively in this chapter, a few points should be clarified regarding the trigger syntax in Oracle. An example of a CREATE TRIGGER statement in Oracle is provided below.

CREATE TRIGGER Update_PROJECTIONS
AFTER UPDATE ON PROJECTIONS
FOR EACH ROW
BEGIN
INSERT INTO P_Audit VALUES (:old.PROJECTION_ID,
:old.PROJECTION_NAME,
:old.PROJECTION_TYPE,
:old.SPHEROID_CODE,
:old.PROJECTION_UOM,
:old.ZONE_CODE,
SYSDATE);
END;
/

Notice the trailing slash. Without the trailing slash Oracle will refuse to create the trigger. This requirement does not appear in any documentation I've seen and learning of the requirement by trial and error is nearly impossible. Also notice the two semicolons as required by Oracle. In trigger definitions in Oracle, the keyword :old refers to the triggering row of the trigger's table as it appeared before the triggering event. :new refers to the modified row. As mentioned above, Oracle lacks standard SQL's notion of an ASSERT. Accordingly, whenever an ASSERT is needed, it must be implemented with a trigger that raises an error with the ASSERT condition is violated. Unfortunately this mechanism is clumsier than ASSERT's because the trigger must be defined independently on each of the tables in the ASSERT condition. Also, for complete generality the trigger must be defined to fire on INSERT, UPDATE, or DELETE in the general case. As an optimization, trigger events may be restricted if particular types of events cannot introduce an inconsistency into a valid table. Here is the Oracle syntax for a trigger that implements an ASSERT on table INCUMBENTS:

CREATE OR REPLACE TRIGGER ASSERT_Simulation
AFTER INSERT OR UPDATE OR DELETE ON INCUMBENTS
DECLARE
valid INTEGER;
BEGIN
SELECT 1 INTO valid FROM DUAL
WHERE
-- Insert condition ASSERT'ed to be true;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20000, 'error message' )
END;
/

A final note regarding triggers is that a trigger may be dropped using the following statement:

DROP TRIGGER trigger_name;

#### Note About Code Fragments

In the course of this chapter's text are a number of code fragments that taken together build up a a useful example table. Many of these fragments depend upon the value of the system date to create valid data. Where needed queries of this sort have been included twice... once in the original form depending upon the system date and once with the assumed system date in place of the system date. Running the fragments named 10_xxt.sql in order will build the table. The fragments named 10_xxnt.sql with matching fragment numbers will be the original unmodified form. Fragments named 10_xxnt.sql without a matching 10_xxt.sql form are not used in creating the example table, but if run would wreck it. All fragments labeled 10_xx.sql do not modify the example table.

#

# Bitemporal Tables (Oracle8 Server)

Bitemporal tables store data which may change over time, while simultaniously recording the history of transactions on these data. The majority of this chapter deals with the Prop_Owner table, which implements an incomplete many to one relationship with bitemporal semantics between the Customer and Property tables. The data in Prop_Owner are pairs of foreign keys from Customer and Property. Prop_Owner has four columns supporting temporal operations: VT_Begin, VT_End, TT_Start, and TT_Stop. [VT_Begin, VT_End) represents the time interval during which the row's data are valid in the modeled universe. [TT_Start, TT_Stop) represents the time interval during which the row's data (including the valid time interval) were/are considered accurate. Examples in this chapter take the convention that the date 12/31/9999 represents "forever". If this restriction of the date-space is unacceptable to your application... well... I'm impressed.

> Create the Prop_Owner table 10_1t.sql

Unfortunately for bitemporal tables, SQL's PRIMARY KEY constraint isn't useful. To implement the notion of a PRIMARY KEY over bitemporal data an ASSERT is required. Further complicating life is Oracle's lack of a simple ASSERT mechanism. In Oracle the action of an ASSERT must be simulated using a TRIGGER. In order to be completely general the simulating TRIGGER must be defined on all tables referenced in the ASSERT predicate. Fortunately here the only table referenced is Prop_Owner, so only one TRIGGER is needed.

> Simulate ASSERT enforcing PRIMARY KEY constraint on Prop_Owner 10_2nt.sql

Another useful constraint on this bitemporal table is requiring that the valid time history be contiguous.

> Add to PRIMARY KEY trigger logic enforcing valid time to be contiguous 10_3nt.sql

#

# Modifications (Oracle8 Server)

This section defines the modifications possible on bitemporal tables and discusses implementation of the often complex operations.

> ## Current Modifications

> Current modifications change the state of the modeled data "as best known".

> > Perform simple insertion of a purchase today 10_4nt.sql
> > Perform the insertion on the date indicated in the chapter 10_4t.sql

> Insertions with new non-temporal primary keys are easy. Updates are not: they must preserve the existing state, possibly modifying its transaction time range, and add a new row with the updated information. The next three fragments implement an update to the bitemporal information.

> > Non temporal notion of the update 10_5nt.sql
> > Partial temporal version considering only valid time 10_6nt.sql
> > Full bitemporal update today 10_7nt.sql
> > Full bitemporal update on the indicated date 10_7t.sql

> Deletions are also much more complex on bitemporal data.

> > Non temporal notion of the deletion 10_8nt.sql
> > Partial temporal delete considering only valid time 10_9nt.sql
> > Full bitemporal delete today 10_10nt.sql
> > Full bitemporal delete on indicated date 10_10t.sql
> > Optimized full bitemporal delete today 10_11nt.sql

> ## Sequenced Modifications

> Sequenced modifications do not simply modify "now", but a specific interval of valid-time.

> > Sequenced insert via valid-time splitting today 10_12nt.sql
> > Sequenced insert via valid-time splitting on indicated date 10_12t.sql
> > Sequenced insert via trasaction splitting today 10_13nt.sql
> > Sequenced delete today 10_14nt.sql
> > Sequenced delete on indicated date 10_14t.sql
> > Sequenced update today 10_15nt.sql
> > Sequenced update on indicated date 10_15t.sql

> ## Nonsequenced modifications

> Nonsequenced modifications are the most general, they treat valid time as just another aspect of the captured data. Again transaction time semantics must be followed.

> > Bitemporal delete of all records with a valid time duration of exactly one week today 10_16nt.sql
> > Bitemporal delete of all records with a valid time duration of exactly one week on indicated date 10_16t.sql

#

# Queries (Oracle8 Server)

> ## Timeslice Queries

> Timeslice queries capture the state of the modeled data over a range of one temporal dimension while the other is held constant.

> > Give history of flat over valid time as of transaction time 1/1/1998 10_17.sql
> > Give history of flat over valid time as currently recorded 10_18.sql
> > Give history of table changes effecting state on 1/4/1998 valid time 10_19.sql
> > Give owner of flat at 1/13/1998 valid time as recorded on 1/18/1998 10_20.sql
> > Give owner of flat as best known today 10_21.sql

> ## The Spectrum of Bitemporal Queries

> As with valid time state tables, current ("now"), sequenced ("history"), and nonsequenced ("at some time") queries are all defined and useful over the valid time dimension. Also, the same three queries: current ("as best known"), sequenced ("when was it recorded"), and nonsequenced ("when was ... erroneously changed") exist for transaction time. For bitemporal tables all nine pairings are considered.

> > Add more data to the example table today 10_22nt.sql
> > Add more data to the example table at the indicated date 10_22t.sql
> > Nontemporal query, used as base for temporal queries 10_23.sql
> > VT current/TT current: What properties are owned by the current owner of 7797 as best known 10_24.sql
> > VT sequened/TT current: What properties are or were owned by the customer who owned 7797 at the same time 10_25.sql
> > VT nonsequenced/TT current: What properties are owned by customers who have owned 7797 at any time as best known 10_26.sql
> > VT current/TT sequenced: What properties have we recorded as owned by the current owner of 7797 10_27.sql
> > VT sequenced/TT sequenced: When did we think that some property, at some time was owned by the customer who owned at the same time property 7797 10_28.sql
> > VT nonsequenced/TT sequenced: Whend did we think that some property, at some time was owned by the customers who owned at any time property 7797 10_29.sql
> > VT current/TT nonsequenced: When was it recorded that a property is owned by the customer who owns property 7797, as best known 10_3.sql
> > VT sequenced/TT nonsequenced: When was it recorded that a property is or was owned by the customer who owned at the same time property 7797 10_31.sql
> > VT nonsequenced/TT nonsequenced: When was it recored that a property was owned by the customer who owned at some time property 7797 10_32.sql

> ## Multi-table queries involving bitemporal tables

> The following queries show examples of multi-table queries involving bitemporal tables. The Property and Customer tables in these queries aren't created as part of the chapter.

> > What is the estimated value of the property at Bygaden 4 10_33.sql
> > Three way equijoin: Who owns the property at Bygaden 4 10_34.sql
> > VT sequenced/TT current: How has the estimated value of the property at Bygaden 4 varied over time 10_35.sql
> > VT sequenced/TT current equijoin: Who has owned the property at Bygaden 4 10_36.sql
> > VT current/TT nonsequenced: When was the estimated valud for theproperty at Bygaden 4 stored 10_37.sql
> > VT sequenced/TT nonsequenced equijoin: Who has owned the property at Bygaden 4 and when was this information recorded 10_38.sql
> > VT nonsequenced/TT nonsequenced: List all retroactive changed made to the Prop_Owner table 10_39.sql

#

# Integrity Constraints (Oracle8 Server)

All of the queries in 9.3 can be patterned into integrity constraints. 9.4 explores these queries and constraints.

> Nontemporaly query for customers who own property 7797 and another 10_40.sql
> Nontemporaly prevent anyone who owns 7797 from owning any others 10_41nt.sql
> VT current/TT current: The owner of 7797 can own no other properties 10_42nt.sql
> VT sequenced/TT current: No one can concurrently own 7797 and any other property 10_43nt.sql
> VT sequenced/TT current: No one who has owned 7797 can ever own any others 10_44nt.sql
> Nontemporaly enforce Prop_Owner.customer_number to be a foreign key referencing Customer 10_45nt.sql
> Nontemporaly enforce Prop_Owner.customer_number to be a foreign key referencing Customer using EXCECPT 10_46nt.sql
> VT sequenced/TT current: Prop_Owner.customer_number is a foreign key referencing Customer 10_47nt.sql
> VT sequenced/TT current: Prop_Owner.customer_number is a foreign key referencing Customer optimized 10_48nt.sql

#

# Temporal Partitioning (Oracle8 Server)

Bitemporal tables are substantially more complex and, depending upon the application, much more space intensive, than their nontemporal cousins. If the data represented are large or complex, the cost of maintaining a bitemporal table can be prohibitive. Temporal partitioning offers several compromises that can reduce the cost of maintaining bitemporal data.

> ## VT Current/TT Current + Bitemporal State

> This scheme isn't technically a partitioning as it duplicates data, but it does offer an optimization in the cost of the most common query: VT current/TT current. The idea is to maintain two tables: PO_Current contains only current/current rows while PO_Bitemp is a full bitemporal table. On systems with materialized views the DBMS will do the housekeeping. Otherwise the application must synchronize PO_Current and PO_Bitemp.

> > Bring PO_Current table up to date 10_49nt.sql

> The big disadvantage of this scheme is that the simple passage of time, without any transactions with the database, can cause PO_Current to change.

> ## VT State/TT Current + State/State Archive

> In this scheme, the transaction current rows are separated out into PO_History. PO_Archive functions as an audit log on PO_History. This usage simplifies applications since 1) PO_History doesn't change with time and 2) the complex logic outlined in this chapter isn't needed on modifications since PO_Archive can be maintained with TRIGGERs.

> > Create triggers for maintaining the PO_Archive table 10_50nt.sql

> Within this scheme, the full bitemporal state can be accessed through a view.

> > Create a view reinstating the bitemporal state as a view 10_51nt.sql

> ## VT State/TT Current + State/Event Archive

> This scheme is identical to the one in 9.5.2, except for the elimination of the TT_Stop column from the archive table. This information is redundant since the transaction history must be contiguous. Therefore each archive row's transaction stop time must be the transaction start time of another row in either the archive or the history table.

> > Reconstitute the bitemporal state table as a view 10_52nt.sql

> The unfortunate flaw in this scheme lies in the fact that when the entire history of a sequenced/sequenced key is deleted, there is no place to store the date of that delete. One solution is to build a PO_Deleted table specifically for this purpose.

> > Reconstitute the bitemporal state table when history deletions are allowed 10_53.nt.sql

#

# Vacuuming (Oracle8 Server)

Bitemporal tables save every state they have ever known. When this state changes frequently the result can be similar to never taking out the trash. This section introduces bitemporal tables to the garbage-person.

> Temporally vacuum old unused entities from the archival store for which no recent history exists 10_54nt.sql

#### Code Credits

Scott I Calvert, Department of Computer Science, University of Arizona (calverts@CS.Arizona.EDU)

#### Html Credits

Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 27, 1999 (Last Update)