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

# MANIPULATING STATE TABLES

## Contents :

There are three kinds of constraints one can specify for a temporal table: current, sequenced and nonsequenced. The nonsequenced constraints are the easiest to specify yet the least useful. A similar situation exists for queries and modifications applied to temporal tables.

Here we will examine how to setup temporal tables, setup constraints and some basic referential integrity for the temporal tables, then execute common queries over the temporal tables, from extracting the current state, to extracting previous states, to evaluating several kinds of sequenced queries. The modifications are also dealt similarly.

    Generic Implementation Details - Schemas, and other concepts that are similar from one DBMS to another
      Schemas for the Book Examples.

    Defining State Tables

      Initial Schema - Queries that extract the current state of non-temporal tables.
      Adding History - Descriptions of how and why history is added to INCUMBENTS table.
      Temporal Keys - Creating assertions so primary keys now include valid-time support
      Handling Now - Why Jan-1-3000 should be the largest value in the timestamp domain.
      Keys Reexamined - Creating assertions on primary keys to prevent value-equivalent, nonsequenced duplicates, current duplicates, and sequenced duplicates.
      Referential Integrity - Creating Referential Integrity on temporal and non-temporal tables.

    Querying State Tables

      Extracting the Current State - Queries that extract the current state of the valid-time table.
      Extracting Prior States - Timeslice queries which are not restricted to the current state.
      Sequenced Queries - Queries taking time-varying tables and extracting a state at a particular point of time.
      Sequenced Join - The Join operation in a temporal fashion.
      Nonsequenced Variants - Nonsequenced versions of the operators on temporal tables.
      Duplicate Elimination - Temporal duplicates and its forms..

    Modifying State Tables

      Current Modifications - Modifications that happen now, and applies to the future
      Sequenced Modifications - Modifications that only affect a specific period.
      Nonsequenced Modifications - Modifications that only affect multiple periods.
      Modifications that Mention Other Tables - Modifications that require information from other tables.

#

# Generic Implementation Details

## Small Example Tables

> Snapshot tables: EMPLOYEES, INCUMBENTS, POSITIONS, JOB_TITLES
> Temporal tables: INCUMBENTS, POSITIONS, SAL_HISTORY

The INCUMBENTS table has no current duplicates, no sequenced duplicates, and no nonsequenced duplicates. It does contain a pair of value-equivalent rows. All histories are contiguous. It is not coalesced.

The SAL_HISTORY table contains contiguous histories, and should exercise all four cases for the temporal join with INCUMBENTS.

## Schema for the Examples

> Coalesce with Duplicates (Employee Table)

SSN Char(11) | StartDate DATE | EndDate DATE

> Coalesce without Duplicates, TJOIN(Employee Table)

Name Char(30) | Salary Integer | Title Char(30) | DateOfBirth DATE | StartDate DATE | Stop DATE

> TJOIN (EmpSalary and EmpTitle Tables)

Name Char(30) | Salary Char(30) | StartDate DATE | Stop DATE

Name Char(30) | Title Char(30) | StartDate DATE | Stop DATE

#

#

# Initial Schema

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Temporal Keys

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Keys Reexamined

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Referential Integrity

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Extracting the Current State

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

# Extracting Prior States

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

# Sequenced Queries

> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

# Sequenced Join

> Temporal Join

> > Union - Use of UNION to extract all employees who have salaries greater than their manager.

> > > Microsoft Access 2000
> > > IBM DB2 Universal Server
> > > Microsoft SQL Server
> > > Oracle8 Server
> > > Sybase SQL Server
> > > UniSQL

> > Union All - Use of UNIONAll to extract all employees who have salaries greater than their manager.

> > > Microsoft Access 2000
> > > IBM DB2 Universal Server
> > > Microsoft SQL Server
> > > Oracle8 Server
> > > Sybase SQL Server
> > > UniSQL

> > Greatest/Least - Use of the Greatest and Least SQL auxilliary functions to extract all employees who have salaries greater than their manager.

> > > Microsoft Access 2000
> > > IBM DB2 Universal Server
> > > Microsoft SQL Server
> > > Oracle8 Server
> > > Sybase SQL Server
> > > UniSQL

#

# Nonsequenced Variants

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

# Duplicate Elimination

> Easy Duplicate Elimination

> > Microsoft Access 2000
> > IBM DB2 Universal Server
> > Microsoft SQL Server
> > Oracle8 Server
> > Sybase SQL Server
> > UniSQL

> Coalescing (General Discussion)

> > Microsoft Access 2000
> > IBM DB2 Universal Server
> > Microsoft SQL Server
> > Oracle8 Server
> > Sybase SQL Server
> > UniSQL

> Coalescing While Removing Duplicates

> > Coalescing with Duplicate Elimination

> > > Looping SQL Version (with Repeat-Until construct) - Update periods in table which can be extended, and loop until no more extend-able periods are found.

> > > > Microsoft Access 2000
> > > > IBM DB2 Universal Server
> > > > Microsoft SQL Server
> > > > Oracle8 Server
> > > > Sybase SQL Server
> > > > UniSQL

> > > Pure SQL Version - Use a complicated series (nifty) of "Not Exists" clauses to come up with a coalesced table.

> > > > Microsoft Access 2000
> > > > IBM DB2 Universal Server
> > > > Microsoft SQL Server
> > > > Oracle8 Server
> > > > Sybase SQL Server
> > > > UniSQL

> > > Coalesce via a Cursor - Straightforward iteration on the database, inserting into result table whenever a gap is found.

> > > > Microsoft Access 2000
> > > > IBM DB2 Universal Server
> > > > Microsoft SQL Server
> > > > Oracle8 Server
> > > > Sybase SQL Server
> > > > UniSQL

> Coalescing Without Removing Duplicates

> > Coalescing With Duplicate Retention

> > > Microsoft Access 2000
> > > IBM DB2 Universal Server
> > > Microsoft SQL Server
> > > Oracle8 Server
> > > Sybase SQL Server
> > > UniSQL

#

#

# Current Modifications

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Sequenced Modifications

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Nonsequenced Modifications

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

#

#

# Modifications that Mention Other Tables

> Microsoft Access 2000
> IBM DB2 Universal Server
> Microsoft SQL Server
> Oracle8 Server
> Sybase SQL Server
> UniSQL

HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Bruce C. Huang, Department of Computer Science, University of Arizona (bchuang@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 28, 1999 (Last Update)