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

# MAINTAINING STATE TABLES

## Microsoft SQL Server Implementation Examples

## Contents at a Glance

> Defining State Tables

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

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Joins
> > Nonsequenced Variants
> > Eliminating Duplicates

> Modifying State Tables

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

## Detailed Contents

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

> Defining State Tables

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

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Joins
> > Nonsequenced Variants
> > Eliminating Duplicates

> Modifying State Tables

> > Current Modifications

> > > Current Insertions
> > > Current Deletions
> > > Current Updates

> > Sequenced Modifications

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

> > Nonsequenced Modifications
> > Modifications that Mention Other Tables

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

##

##

## Defining State Tables

### Initial Schema

The following code fragments are Microsoft SQL Server examples for SQL queries, showing that SQL is adequate to handle nontemporal queries or queries on isolated temporal columns.

> Examples

> > Code Fragment 5.1 What is Bob's salary ?
> > Code Fragment 5.2 What is Bob's position ?
> > Code Fragment 5.3 What is Bob's date of birth ?

##

## Adding History

A valid-time table records the history of the modeled reality. The history can be retained by adding timestamp column(s). For a table describing facts that are valid over time, the timestamp is a period.

> Examples

> > Code Fragment5.4 Extracting the relevant information.

##

## Temporal Keys

> Examples

> The primary key is not, by itself, a primary key of the temporal table.

> > Code Fragment5.5 The primary key of INCUMBENTS is (SSN, PCN).

> > Adding the start time of the timestamp of the row, or the end time, or both, does not serve to convert a nontemporal key to a temporal key.

> > Code Fragment5.6 Attempting to specify a primary key at any point in time.

> > A sequenced constraint is one that is applied independently at each point in time.

> > Code Fragment5.7 (SSN, PCN) is a sequenced primary key for INCUMBENTS.

> > A sequenced primary key can be expressed as a SQL Server trigger.

> > Code Fragment5.8 (SSN, PCN) is a sequenced primary key for INCUMBENTS, assuming a closed-closed timestamp representation.

> > Microsoft SQL Server does not support assertions. Check constraints can be defined over one column or multiple columns of a table. However, they do not support subqueries as a part of the constraint. Hence, sequenced primary keys must be defined as triggers on Microsoft SQL Server.

##

## Handling Now

No Microsoft SQL Server examples for this section.

##

## Keys, Reexamined

> Examples

> > Code Fragment5.9 Prevent value-equivalent rows in INCUMBENTS.
> > Code Fragment5.10 Prevent nonsequenced duplicates in INCUMBENTS.
> > Code Fragment5.11 Prevent current duplicates in INCUMBENTS.
> > Code Fragment5.12 Prevent current duplicates in INCUMBENTS, assuming no future data.
> > Code Fragment5.13 Prevent sequenced duplicates in INCUMBENTS.
> > Code Fragment5.14 Prevent sequenced duplicates in INCUMBENTS, assuming only current modifications.

##

## Referential Integrity

SQL Server does not support assertions. Therefore, triggers have to be used to ensure referential integrity.

> Examples

> > Code Fragment5.15 INCUMBENTS.PCN is a foreign key for POSITIONS.PCN (neither table is temporal).

> > Referential integrity (RI) constraints also apply without change if the referencing table is temporal but the referenced table is nontemporal.

> > Current referential integrity requires a Microsoft SQL Server trigger.

> > Code Fragment5.16 INCUMBENTS.PCN is a current foreign key for POSITIONS.PCN (both tables are temporal).

> > Sequenced referential integrity is the natural extension to time varying tables, but requires a complex SQL Server trigger.

> > Code Fragment5.17 INCUMBENTS.PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal).

> > Code Fragment5.18 POSITIONS.PCN defines a contiguous history.

> > Exploiting contiguous histories in the referenced table simplifies sequenced referential integrity when both tables are temporal.

> > Code Fragment5.19 INCUMBENTS.PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal, version 2).

> > The case where the referencing table is nontemporal but the referenced table is temporal reduces to the other cases just described.

> > Code Fragment5.20 INCUMBENTS.PCN is a current foreign key for POSITIONS.PCN (only POSITIONS is temporal).

##

## Querying State Tables

Here we examine common queries over temporal tables, from extracting the current state, to extracting previous states, to evaluating several kinds of sequenced queries.

### Extracting the Current State

> Examples

> Executing a query on the current state of a temporal table requires an additional predicate.

> > Code Fragment 5.1 What is Bob's current position ?
> > Code Fragment 5.2 What is Bob's current position ?

> > Current joins over two temporal tables are not too difficult.

> > Code Fragment 5.3 What is Bob's current position and salary ?

> > Executing a query on the current state requires an additional predicate for each temporal table.

> > Code Fragment 5.4 What employees currently have not position ?

##

## Extracting Prior States

> Examples

> Timeslice queries, over a previous state, requires an additional predicate for each temporal table.

> > Code Fragment 5.5 What was Bob's position at the beginning of 1997 ?

##

## Sequenced Queries

> Examples

> A selection (a predicate over a non-timestamp column) is a sequenced selection on a temporal table.

> > Code Fragment 5.6 Who makes or has made more than $50,000 annually?

> > A sequenced projection of specified columns in the select clause can be effected by including the timestamp columns.

> > Code Fragment 5.7 List the social security numbers of current and past employees.
> > Code Fragment 5.8 Sequenced sort INCUMBENTS on the position code (first version).

> > A query using ORDER BY is automatically sequenced, if the timestamp columns are retained.

> > Code Fragment 5.9 Sequenced sort INCUMBENTS on the position code (second version).

> > A UNION ALL over temporal tables is automatically sequenced if the timestamp columns are retained.

> > Code Fragment 5.10 Who makes or has made more than $50,000 annually or less than $10,000?

##

## Sequenced Joins

> Examples

> A sequenced join requires four select statements and complex inequality predicates.

> > Code Fragment 5.11 Provide the salary and position history for all employees.
> > Code Fragment 5.12 Provide the salary and position history for all employees, using CASE.

> > Microsoft SQL Server does not support user defined functions. It does support user defined procedures. However this procedures are not useful inside an iterative SQL query.

> > Code Fragment 5.13 Define a first_instant function.

##

## Nonsequenced Variants

> Examples.

> A nonsequenced query considers the timestamp columns as just additional columns.

> > Code Fragment 5.15 List all the salaries, past and present, of employees who had been a hazardous waste specialist at some time.
> > Code Fragment 5.16 When did employees receive raises?

##

## Eliminating Duplicates

### Easy Duplicate Elimination

Removing current, value-equivalent, and nonsequenced duplicates is easy.

> Code Fragment 5.17 Remove nonsequenced duplicates from INCUMBENTS.
> Code Fragment 5.18 Remove value-equivalent rows from INCUMBENTS.
> Code Fragment 5.19 Remove current duplicates INCUMBENTS.

### Coalescing while Removing Duplicates

> Code Fragment 5.21 Coalesce INCUMBENTS while removing duplicates (entirely in SQL)
> Code Fragment 5.22 Coalesce INCUMBENTS while removing duplicates (using a cursor).

##

## Modifying State Tables

### Current Modifications

A current modification concerns something that happened right now.

> Current Insertions

> Current insertions are mapped into insertions with two additional (timestamp) columns.

> > Code Fragment 6.1 Bob was assigned the position of Associate Director of the Computer Center

> > Ensuring uniqueness with a current insertion requires a where predicate or an augmented primary key constraint

> > Code Fragment 6.2 Bob was assigned the position of Associate Director of the Computer Center, ensuring primary key.

> > Ensuring referential integrity with a current insertion in the restricted case requires an additional where predicate.

> > Code Fragment 6.3 Bob was assigned the position of Associate Director of the Computer Center, also ensuring referential integrity, in the restricted case.

> > Filling the hole in the referenced table is an easy way to ensure referential integrity for current insertions into the referencing table.

> > Code Fragment 6.4 Fill the hole (in the restricted case) in the POSITIONS table for the position of Associate Director of the Computer Center.

> > Code Fragment 6.5 Bob was assigned the position of Associate Director of the Computer Center, ensuring the primary key, in the restricted case.

> > When unrestricted modifications are possible, such modifications may generate holes to be filled to ensure referential integrity.

> > Code Fragment 6.6 Fill holes in the POSITIONS table for the position of Associate Director of the Computer Center, in the general case.

> Current Deletion

> In the restricted case, a current deletion is converted into an update of the end date.

> > Code Fragment 6.7 Bob was fired as Associate Director of the Computer Center (only current modifications assumed).

> > In the general case, a current deletion is implemented as an update, for those periods overlapping "now", and a delete, for those periods starting in the future.

> > Code Fragment 6.8 Bob was fired as Associate Director of the Computer Center.

> Current Updates

> A current update in the restricted case is implemented by an update to end the current row at "now" and an insertion of the new values.

> > Code Fragment 6.9 Bob was promoted to Director of the Computer Center (nontemporal version).

> > Code Fragment 6.10 Bob was promoted to Director of the Computer Center (assuming only current modifications).

> > A current update in the general case is implemented by two updates and an insertion.

> > Code Fragment 6.11 Bob was promoted to Director of the Computer Center.

##

## Sequenced Modifications

A current modification is simply a sequenced modification with a period of applicability of "now" to "forever".

> Sequenced Insertions

> > Code Fragment 6.12 Bob was assigned the position of Associate Director of the Computer Center for 1997.

> > Code Fragment 6.13 Bob was assigned the position of Associate Director of the Computer Center for 1997, ensuring the primary key.

> > Code Fragment 6.14 Bob was assigned the position of Associate Director of the Computer Center for 1997, also ensuring the primary key.

> Sequenced Deletions

> A sequenced deletion is implemented by four statements: an insertion, two updates, and a deletion.

> > Code Fragment 6.15 Bob was removed as Associate Director of the Computer Center for 1997.

> Sequenced Updates

> > Code Fragment 6.16 Bob was promoted to Director of the Computer Center (nontemporal version).

> > A sequenced update is implemented by five statements: two insertions and three updates.

> > Code Fragment 6.17 Bob was promoted to Director of the Computer Center for 1997.

##

## Nonsequenced Modifications

A nonsequenced modification treats timestamps as just additional columns.

> Examples

> Nonsequenced modifications are usually difficult to express in English, but easier to express in SQL.

> > Code Fragment 6.18 Delete Bob's records that include 1997 stating that he was Associate Director of the Computer Center.

> > Correctly implementing a nonsequenced modification in the presence of sequenced constraints is difficult, and must be done on a case by case basis.

> > Code Fragment 6.19 Extend Bob's position as Associate Director of the Computer Center for an additional year.

##

## Modifications that Mention Other Tables

> Complex Current Modifications

> > Code Fragment 6.20 Bob was promoted to Director of the Computer Center (nontemporal version).

> > Current modifications that mention other tables require and additional overlap with "now" predicate for each correlated name.

> > Code Fragment 6.21 Bob was promoted to Director of the Computer Center (current version).

> Complex Sequenced Modifications

> > Code Fragment 6.22 Bob was promoted to Director of the Computer Center.

> > Sequenced updates referring other tables are complex to convert into SQL.

> > Code Fragment 6.23 Bob was promoted to Director of the Computer Center for 1997 (sequenced version).

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Wenmin Chen, Department of Computer Science, University of Arizona (wenmin@cs.arizona.edu)
Rachana Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Ines Fernando Vega Lopez, Department of Computer Science, University of Arizona (ifvega@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
March 30, 1999 (Last Update)

#### Microsoft SQL Server Specific Code Credits

Ines Fernando Vega Lopez, Department of Computer Science, University of Arizona (ifvega@cs.arizona.edu)
July 20, 1998 (Last Update)