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

# MAINTAINING STATE TABLES

## IBM DB2 Universal Server Implementation Examples

#

## Contents at a Glance

> Defining State Tables

> > Initial Schema
> > 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

> DB2 Information
> Executing Examples
> Creating Sample Tables
> Appendix : Loading Sample Tables

##

## Detailed Contents

This document outlines implementation experiments for Chapter 5-7 using the DB2 DBMS. It follows the general outline of the TDB book and is arranged in the following manner.

> Defining State Tables

> > Initial Schema
> > Temporal Keys
> > Keys Reexamined
> > Referential Integrity

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Join

> > > Union
> > > Union All
> > > Greatest/Least

> > Nonsequenced Variants

> > Duplicate Elimination

> 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

> DB2 Information
> Executing Examples
> Creating Sample Tables
> Appendix I : Loading DB2 Sample Tables

##

##

## Defining State Tables

## Initial Schema

Queries extracting information from nontemporal tables.

> 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 ?

##

## Temporal Keys

Primary key constraints for tables with valid time support.
Since DB2 does not have ASSERTIONs, we use TRIGGERs. Also DB2 does not have a rowid pseudo-column so we use a COUNT instead that checks that no more than one row contain the same value for SSN and PCN at any given point in time.

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

##

## Keys, Reexamined

Constraints used to prevent duplicates in temporal tables. DB2 does not have a UNIQUE column constraint, so we create a primary key constraint on the corresponding columns. This requires that the primary key columns be declared as NOT NULL.

> Code Fragment 5.6 Prevent value-equivalent rows in INCUMBENTS.
> Code Fragment 5.7 Prevent nonsequenced duplicates in INCUMBENTS.

Since DB2 does not allow a SELECT statement inside a CHECK constraint, we create a TRIGGER that performs the same check.

> Code Fragment 5.8 Prevent current duplicates in INCUMBENTS.
> Code Fragment 5.9 Prevent current duplicates in INCUMBENTS, assuming no future data.
> Code Fragment 5.10 Prevent sequenced duplicates in INCUMBENTS.

##

## Referential Integrity

Constraints preserving referential integrity in temporal tables.
For referential integrity, you need 4 TRIGGERS, INSERT/UPDATE TRIGGERs on the referencing table (INCUMBENTS) and DELETE/UPDATE TRIGGERs on the referenced table (POSITIONS).

> Code Fragment 5.11 INCUMBENTS.PCN is a foreign key for POSITIONS.PCN (neither table is temporal).
> Code Fragment 5.12 INCUMBENTS.PCN is a current foreign key for POSITIONS.PCN (both tables are temporal).
> Code Fragment 5.14 INCUMBENTS.PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal).
> Code Fragment 5.15 POSITIONS.PCN defines a contiguous history.
> Code Fragment 5.16 INCUMBENTS.PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal, version 2).
> Code Fragment 5.17 INCUMBENTS.PCN is a current foreign key for POSITIONS.PCN (only POSITIONS is temporal).

##

##

## Querying State Tables

### Extracting the Current State

Queries extracting current information from temporal tables.

> Code Fragment 6.1 What is Bob's current position ?
> Code Fragment 6.2 What is Bob's current position ?
> Code Fragment 6.3 What is Bob's current position and salary ?
> Code Fragment 6.4 What employees currently have no position ?

##

## Extracting Prior States

Queries extracting prior information from temporal tables.

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

##

## Sequenced Queries

Queries extracting temporal information from tables with valid time support.

> Code Fragment 6.6 Who makes or has made more than $50,000 annually ?
> Code Fragment 6.7 List the social security numbers of current and past employees.
> Code Fragment 6.8 Sequenced sort INCUMBENTS on the position code (first version)
> Code Fragment 6.9 Sequenced sort INCUMBENTS on the position code (second version)
> Code Fragment 6.10 Who makes or has made more than $50,000 annually or less than $10,000 ?

##

## 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. Performance is roughly linear. Adding composite index to the table does not improve the performance.

> > DB2 CLP code (PerfJoin.clp)
> > Result (TJoin original table and result table)
> > Try it yourself (Makefile)

> #### Union All

> Uses Union-All to bypass the duplicate elimination and sorting operations performed by the ordinary DB2 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.

> > DB2 CLP code (PerfJoin.clp)
> > Result (TJoin original table and result table)
> > Try it yourself (Makefile)

> Discussion: If there is no nonsequenced duplicates in the original table, the code (PerfJoin.clp) works well. Otherwise, the result might contain duplicates. A robust code (PerfJoinDistinct.clp) should contain DISTINCT. Table emp2000, emp4000, emp10000 contain nonsequenced duplicates, the TJoin result tables have duplicates. So the number of tuples is greater than the number of the tuples in TJoin UNION result table.

> #### Greatest/Least

> Some languages incorporate auxiliary SQL functions such as Least/Greatest to accomplish the Temporal Join operation using only one select statement. DB2, however, does not have these particular functions, so users must rely on the previously shown union query (four separate select statements statements) to generate the same results as Least/Greatest.

##

## Nonsequenced Variants

Queries that extract information from temporal tables, but whose results do not depend on the time varying nature of the tables.

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

##

## Duplicate Elimination

### Coalescing While Removing Duplicates

> #### Coalescing

> > #### Coalesce via a Cursor

> Uses ordered cursors to go through every record, comparing period boundaries. The algorithm is quite efficient because the cursor passes the table just once. The program was written in C with embedded SQL statements. The performance is linear. The sorting time is relatively small compared to the total time. Most of time is spent on insertions.

> > > C program with embedded SQL statements (coalesce.sqc)
> > > Result (Coalescing original table and result table)
> > > Try it yourself (Makefile and a CLP file used in Makefile)

##

##

## Modifying State Tables

### Current Modifications

Modifications on temporal tables that happen now and apply into the future.

### Current Insertions

Since DB2 does not allow a WHERE clause in an INSERT statement, we use an INSERT..WITH..SELECT combination.

> Code Fragment 7.1 Bob was assigned the position of Associate Director of the Computer Center.
> Code Fragment 7.2 Bob was assigned the position of Associate Director of the Computer Center, ensuring primary key.
> Code Fragment 7.3 Bob was assigned the position of Associate Director of the Computer Center, also ensuring referential integrity, in the restricted case.
> Code Fragment 7.4 Fill the hole (in the restricted case) in the POSITIONS table for the position of the Associate Director of the Computer Center.
> Code Fragment 7.5 Bob was assigned the position of the Associate Director of the Computer Center, ensuring the primary key, in the unrestricted case.
> Code Fragment 7.6 Fill holes in the POSITIONS table for the position of Associate Director of the Computer Center, in the general case.

### Current Deletions

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

### Current Updates

> Code Fragment 7.9 Bob was promoted to Director of the Computer Center (nontemporal version).
> Code Fragment 7.10 Bob was promoted to Director of the Computer Center (assuming only current modifications).
> Code Fragment 7.11 Bob was promoted to Director of the Computer Center.

##

## Sequenced Modifications

Modifications on temporal tables that apply over a specified period of time which may be in the past, present or future.

### Sequenced Insertions

> Code Fragment 7.12 Bob was assigned the position of Associate Director of the Computer Center for 1997.
> Code Fragment 7.13 Bob was assigned the position of Associate Director of the Computer Center for 1997, ensuring primary key.
> Code Fragment 7.14 Bob was assigned the position of Associate Director of the Computer Center for 1997, also ensuring referential integrity.

### Sequenced Deletions

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

### Sequenced Updates

> Code Fragment 7.16 Bob was promoted to Director of the Computer Center (nontemporal version).
> Code Fragment 7.17 Bob was promoted to Director of the Computer Center for 1997.

##

## Nonsequenced Modifications

Modifications on temporal tables that ignore the time varying nature of data by treating timestamps identical to other columns.

> Code Fragment 7.18 Delete Bob's records that include 1997 stating that he was Associate Director of the Computer Center.
> Code Fragment 7.19 Extend Bob's position as Associate Director of the Computer Center for an additional year.

##

## Modifications that Mention Other Tables

Modifications performing complex subqueries on several tables.

### Complex Current Modifications

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

### Complex Sequenced Modifications

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

##

## DB2 Information

DB2 Version 2.1 :
(c) Copyright IBM Corporation 1993,1995
db2 : Command Line Processor for DB2 SDK 2.1.2

DB2 References : 'Using the new DB2, IBM's Object Relational Database System'
- Don Chamberlin, Morgan Kaufmann Publishers, Inc.

##

## Executing Examples

All the Code Fragments above and in the appendices can be executed at the db2 prompt. Enter 'db2' at the command shell prompt, this should start DB2 and the 'db2 =>' prompt should appear (make sure the shell environment variables are set properly, refer a DB2 manual or contact the database administrator for more information, in the event that DB2 does not start). Now connect to a database that has been created by the database administrator, using the command

db2 => CONNECT TO sampleDbase

where sampleDbase is the name of the database created by the database administrator. The tables may now be created using the SQL statements given in appendix III below. Populate the tables using the information given in the appendix I below. Now start executing the Code Fragments given above sequentially starting from Code Fragment 5.1 It is important that the order of execution of the Code Fragments be sequential since the results of some statements depend on the state of the database left by previous statements. So some Code Fragments may create temporal versions of the tables (or non-temporal versions) and subsequent Code Fragments assume that the tables are temporal (or non-temporal respectively).

Executing the SQL statements are simple, just enter them at the db2 prompt (you may cut and paste the statement). The '\' character appears at the end of every line in the DB2 SQL sample examples, this is because DB2 requires that every SQL statement entered at the db2 prompt appear on a single line. The '\' indicates that the same statement continues on to the next line (you may omit the '\' depending on your version of the db2 command line processor or if you wish to enter statements in a single line. The '\' has been included in the sample examples for readability and to facilitate cut-and-paste).

##

## Creating Sample Tables

The link below provides SQL statements used to create the required tables. Create the non-temporal tables only, the individual Code Fragments will indicate when to create the temporal versions of the tables. Refer Appendix II above for information on how to execute the SQL statements.

> Creating Tables

##

## Appendix : Loading Sample Tables

Refer 'Sample Example Tables' in the 'Generic Implementation Section'. The text data files used to load data into tables are given below, these files should be placed in the directory in which DB2 is executed. Create and load data into the non-temporal tables only, the individual Code Fragments will indicate when to create and load the temporal versions of the tables.

> Text Data Files
> EMP.TXT Text data file for table EMPLOYEES
> JOB.TXT Text data file for table JOB_TITLES
> INC.TXT Text data file for non-temporal table INCUMBENTS
> POS.TXT Text data file for non-temporal table POSITIONS
> INC_TEMPORAL.TXT Text data file for temporal table INCUMBENTS
> POS_TEMPORAL.TXT Text data file for temporal table POSITIONS
> SAL_TEMPORAL.TXT Text data file for temporal table SAL_HISTORY
> Loading Data into Tables

#### HTML Credits

Vijay Immanuel, Department of Computer Science, University of Arizona (vijay@cs.arizona.edu)
Jie Li, Department of Computer Science, University of Arizona (lij@cs.arizona.edu)
Kristin M. Tolle, Department of Management Information Systems, University of Arizona (ktolle@bpa.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 2, 1999 (Last Update)

#### DB2 Specific Code Credits

Vijay Immanuel, Department of Computer Science, University of Arizona (vijay@cs.arizona.edu)
December 31, 1997 (Last Update)