Developing Temporal Applications in SQL | Richard T. Snodgrass

#

# MAINTAINING STATE TABLES

## UniSQL Implementation Examples

#

## Contents at a Glance

> Defining State Tables

> > Initial Schema
> > Adding History
> > Temporal Keys
> > Keys, Reexamined
> > Referential Integrity

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Join
> > Nonsequenced Variants
> > Duplicate Elimination

> Modifying State Tables

> > Current Modifications
> > Sequenced Modifications
> > Nonsequenced Modifications
> > Modifications that Mention Other Tables

##

## Detailed Contents

This document outlines implementation experiments using the UniSQL DBMS. It follows the general outline of the TDB book and is arranged in the following manner.

> Defining State Tables

> > Initial Schema

> > > Simple Select
> > > Simple Join

> > Adding History
> > Temporal Keys
> > Keys, Reexamined

> > > Unique Constraints
> > > Other Constraints

> > Referential Integrity

> > > REFERENCES Keyword
> > > ASSERTIONs/TRIGGERs and Current Foreign Keys
> > > FOREIGN KEY Keyword
> > > ASSERTIONs/TRIGGERs and Sequenced Foreign Keys
> > > ASSERTIONs/TRIGGERs and Contiguous Histories

> Querying State Tables

> > Extracting the Current State

> > > Extracting Current Information Using The Future
> > > Extracting Current Information By Squeezing
> > > Matching and Squeezing

> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Join

> > > Union
> > > Union All
> > > Conclusion1

> > Nonsequenced Variants
> > Duplicate Elimination

> Modifying State Tables

> > Current Modifications

> > > Current Insertions

> > > > Simple Current Insertions
> > > > Current Insertions With Checks

> > > Current Deletions

> > > > Not Really Deleting
> > > > Deleting By Updating

> > > Current Updates

> > > > Nontemporal Updates
> > > > Updating Current Records

> > Sequenced Modifications

> > > Sequenced Insertions
> > > Sequenced Deletions
> > > Sequenced Updates

> > Nonsequenced Modifications
> > Modifications that Mention Other Tables

> > > Complex Current Modifications
> > > Complex Sequenced Modifications

##

##

## Initial Schema (UniSQL)

### Simple Select

This query is as simple as it gets. One column is fetched from one table based on one criterion.

> UniSQL SQL code (cf5-1.sql)
> Results (cf5-1.out)

### Simple Join

This query is slightly more complex; it joins four tables.

> UniSQL SQL code (cf5-2.sql)
> Results (cf5-2.out)

##

##

## Adding History (UniSQL)

### A Complex View

This View is rather complex, compared to the previous two examples. By using the view, we eliminate most of the columns and restrict the number of rows that are returned. based on one criterion.

> UniSQL SQL code (view.sql) (cf5-3.sql)
> Results (cf5-3.out)

##

##

## Temporal Keys (UniSQL)

### Incorporating Time Support into Keys

While UniSQL does not support ASSERTIONs, it does support TRIGGERs. Triggers are used here to perform the tasks that the assertions would do: verify the integrity of the primary key when periods are used. If the integrity is compromised, the Trigger will prevent the change from being made.

> UniSQL SQL code (cf5-4.sql) (cf5-5.sql)

Triggers are discussed in detail in the Audit Trail section covering UniSQL.

##

##

## Keys, Reexamined (UniSQL)

### Unique Constraints

UniSQL allows the programmer to set unique constraints on tables. These ensure that no two rows have the same values for those fields addressed in the unique constraint. More than one column can be specified in a unique constraint. Here, unique constraints are used to prevent value-equivalent rows in Incumbents (in the first case) and to prevent nonsequenced duplicates in Incumbents (in the second case).

> UniSQL SQL code (cf5-6.sql) (cf5-7.sql) (cf5-8.sql) (cf5-9.sql) (cf5-10.sql)

### Other Constraints

UniSQL allows the programmer to set other types of constraints as well.

##

##

## Referential Integrity (UniSQL)

### REFERENCES Keyword

UniSQL allows the programmer to denote that one column may reference a column of the same name in another table. In this case, we are using it with two nontemporal tables to preserve referential integrity.

> UniSQL SQL code (cf5-11.sql)

### ASSERTIONs/TRIGGERs and Current Foreign Keys

Once again, a Trigger is used instead of an Assertion. The Trigger rejects changes that would violate current referential integrity.

> UniSQL SQL code (cf5-12.sql) (cf5-17.sql)

### FOREIGN KEY Keyword

Another way UniSQL allows the programmer to denote that one column may reference a column is through the use of the FOREIGN KEY keywords.

> UniSQL SQL code (cf5-13.sql)

### ASSERTIONs/TRIGGERs and Sequenced Foreign Keys

Once again, a Trigger is used instead of an Assertion. The Trigger rejects changes that would violate sequenced referential integrity.

> UniSQL SQL code (cf5-14.sql) (cf5-16.sql)

### ASSERTIONs/TRIGGERs and Contiguous Histories

Once again, a Trigger is used instead of an Assertion. In contiguous histories, there should be no holes. The Trigger rejects changes that would introduce these holes.

> UniSQL SQL code (cf5-15.sql)

##

##

## Extracting the Current State (UniSQL)

### Extracting Current Information Using The Future

In temporal tables that use two columns to denote a period, one way to find the current data is to query based on the The Future. Specifically, a time in the the future is set aside as a marker to indicate an event that has not happened yet (an open interval).

> UniSQL SQL code (cf6-1.sql)
> Result (cf6-1.out)

### Extracting Current Information By Squeezing

Another way to find the current data is to query in such a way that the current time falls within the period of some row. Note that UniSQL does not provide a "Current Time" macro; in order to avoid hardcoding a time, one should use the time functions available through other languages (e.g., C's time facilities).

> UniSQL SQL code (cf6-2.sql) (cf6-3.sql)
> Results (cf6-2.out) (cf6-3.out)

### Matching and Squeezing

Now we see two tables being matched together; one table is squeezed to determine whether the other table matches the criteria. In this case, we seek incumbents with no current job.

> UniSQL SQL code (cf6-4.sql)
> Results (cf6-4.out)

##

##

## Extracting Prior States (UniSQL)

Just as we can use the current time to seek current data, we can seek the value of data at some specific time.

> UniSQL SQL code (cf6-5.sql)
> Results (cf6-5.out)

##

##

## Sequenced Queries (UniSQL)

We provide sequenced variations of selection, projection, sorting and union.

> UniSQL SQL code (cf6-6.sql) (cf6-7.sql) (cf6-8.sql) (cf6-9.sql) (cf6-10.sql)
> Results (cf6-6.out) (cf6-7.out) (cf6-8.out) (cf6-9.out) (cf6-10.out)

##

##

## Sequenced Join

### Temporal Join

> #### Union

> This approach uses the basic UNION operator, standard in SQL. This approach is more general, but slower because it automatically eliminates duplicates and sorts the results.

> > UniSQL SQLPLUS c ode (unio.ec)
> > Performance Table
> > General Performance Comparisons, Index Performance Comparisons

> #### Union All

> Uses Union-All to bypass the duplicate elimination and sorting operations performed by the ordinary UniSQL UNION operator. Logically, sorting and duplicate elimination should not be a by-product of a join, so this approach seems more correct. However, without elimination of duplicates, a special case were there are tuples with exactly the same periods would need to be considered. See the last select statement, and pay close attention to the compound condition added to it.

> > UniSQL SQLPLUS c ode (unioall.ec)
> > Performance Table
> > General Performance Comparisons, Index Performance Comparisons

> #### Greatest/Least

> Cannot do in UniSQL

##

## Nonsequenced Variants

These operations ignore the time-varying nature of temporal tables and are straightforward.

> UniSQL SQL code (cf6-13.sql) (cf6-14.sql)
> Results (cf6-13.out) (cf6-14.out)

##

## Duplicate Elimination (UniSQL)

### Coalescing While Removing Duplicates

> #### Coalesce via a Cursor

> Uses cursors to go through every record, comparing period boundaries. Garnered the best results from the performance experiments made. Problem with this approach, though, is that it is not SQL at all and relies on a programming language extension for SQL from UniSQL (PLSQL).

> > UniSQL SQLPLUS code (cola.ec)

##

##

## Current Modifications (UniSQL)

### Current Insertions

> #### Simple Current Insertions

> These insertions use the current time as the starting time, and set the ending time to be the future.

> > UniSQL SQL code (cf7-1.sql)

> Current Insertions With Checks These insertions perform the same task as the earlier one, but verify the consistency and integrity of the database before actually modifying it.

> > UniSQL SQL code (cf7-1.sql) (cf7-2.sql) (cf7-3.sql) (cf7-4.sql) (cf7-5.sql) (cf7-6.sql)

### Current Deletions

> #### Not Really Deleting

> These deletions work by "closing" a record; the end marker is set to the current time, but the record is still kept around.

> > UniSQL SQL code (cf7-7.sql)

> #### Deleting By Updating

> These deletions actually delete records in addition to "closing" them. These are typically an update and a delete.

> > UniSQL SQL code (cf7-8.sql)

### Current Updates

> #### Nontemporal Updates

> These are the standard updates. They do not take into account the fact that tables might be temporal.

> > UniSQL SQL code (cf7-9.sql)

> #### Updating Current Records

> These updates modify current records. In addition to having to insert new records, the old current record must be decommissioned.

> > UniSQL SQL code (cf7-10.sql) (cf7-11.sql)

##

##

## Sequenced Modifications (UniSQL)

### Sequenced Insertions

In Sequenced Insertions, the period is provided by the application.

> UniSQL SQL code (cf7-12.sql) (cf7-13.sql) (cf7-14.sql)

### Sequenced Deletions

In Sequenced Deletions, there are four cases, depending on whether the initial and final markers have to be reset or not.

> UniSQL SQL code (cf7-15.sql)

### Sequenced Updates

A Sequenced Update is very similar to a nontemporal update, within a specific period.

> UniSQL SQL code (cf7-16.sql) (cf7-17.sql)

##

##

## Nonsequenced Modifications (UniSQL)

Nonsequenced Modifications do not use the timestamps as current and sequenced modifications do.

> UniSQL SQL code (cf7-18.sql)

Code Fragment 19 cannot be implemented in UniSQL's version of SQL, but the equivalent could be done if SQL were embedded in a C program.

##

##

## Modifications that Mention Other Tables (UniSQL)

### Complex Current Modifications

Complex Current Modifications can usually be converted to SQL by using an additional overlap.

> UniSQL SQL code (cf7-20.sql) (cf7-21.sql)

### Complex Sequenced Modifications

Complex Sequenced Modifications (those that involve more than one table) are very difficult to convert to SQL.

> UniSQL SQL code (cf7-22.sql) (cf7-23.sql)

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Carlos A. Ugarte, Department of Computer Science, University of Arizona (cau@cs.arizona.edu)
Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
February 7, 1999(Last update)

#### UniSQL Specific Code Credits:

Carlos A. Ugarte, Department of Computer Science, University of Arizona (cau@cs.arizona.edu)
Sridhar Devarapalli, Lin Zhang, Department of Computer Science, University of Arizona (sridhar@cs.arizona.edu).
December 17, 1997 (Last Update)

####