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

# MAINTAINING STATE TABLES

## Oracle8 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

> > > Easy Duplicate Elimination
> > > Coalescing (General Discussion)
> > > Coalescing While Removing Duplicates

> > Modifying State Tables

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

> > Appendix:

> > > Appendix I : Information on Oracle Used in the Code Fragments, Sample Data

##

## Detailed Contents

This document outlines implementation experiments using the Oracle 7.3.2.1.0 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

> > > Easy Duplicate Elimination
> > > Coalescing (General Discussion)
> > > Coalescing While Removing Duplicates

> > > > Looping SQL Version (with Repeat-Until construct)
> > > > Pure SQL Version
> > > > Coalesce via a Cursor

> 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

> Appendix I : Information on Oracle Used in the Code Fragments, Sample Data
> Appendix II : Loading Oracle Sample Tables

##

##

## Initial Schema

> Initial Schema of tables and some simple queries.

> > Oracle SQLPLUS code (CF5-2.sql)

##

## Temporal Keys

> Primary Keys that take timestamps into consideration.

> > Oracle SQLPLUS code (CF5-4.sql)
> > Oracle SQLPLUS code (CF5-5.sql)

##

## Keys, Reexamined

> Preventing value-equivalent, sequenced, nonsequenced, and current duplicates.

> > Oracle SQLPLUS code (CF5-6.sql)
> > Oracle SQLPLUS code (CF5-7.sql)
> > Oracle SQLPLUS code (CF5-8.sql)
> > Oracle SQLPLUS code (CF5-9.sql)
> > Oracle SQLPLUS code (CF5-10.sql)

##

## Referential Integrity

> Preserving Referential Integrity among tables

> > Oracle SQLPLUS code (CF5-11.sql)
> > Oracle SQLPLUS code (CF5-12.sql)
> > Oracle SQLPLUS code (CF5-14.sql)
> > Oracle SQLPLUS code (CF5-15.sql)
> > Oracle SQLPLUS code (CF5-16.sql)
> > Oracle SQLPLUS code (CF5-17.sql)

##

## Extracting the Current State

> Current state of temporal tables.

> > Oracle SQLPLUS code (CF6-1.sql)

##

## Extracting Prior States

> Prior state of temporal tables.

##

## Sequenced Queries

> Sequenced Queries temporal tables.

##

## 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.

> > Oracle SQLPLUS code (Tjoin.SQL)
> > Result (TJoin.result)
> > Try it yourself (Makefile)

> #### Union All

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

> > Oracle SQLPLUS code (Tjoin.SQL)
> > Result (TJoin.result)
> > Try it yourself (Makefile)

> #### Greatest/Least

> This approach uses the Least/Greatest auxiliary SQL functions to accomplish the Temporal Join operation using only one select statement (versus 4 separate ones UNIONed together). This approach is about 4 times faster than any of the Union based techniques.

> > Oracle SQLPLUS code (Tjoin.SQL)
> > Result (TJoin.result)
> > Try it yourself (Makefile)

##

## Nonsequenced Variants

##

## Duplicate Elimination

### Easy Duplicate Elimination

### Coalescing (General Discussion)

### Coalescing While Removing Duplicates

> #### Coalescing

> > #### Looping SQL Version (with Do-Until construct)

> >      Uses SQL statements in a loop. Not as efficient as the cursor-based but competes when indexed.

> > > Oracle SQLPLUS code (Iterative.SQL)
> > > Result (Iterative.result)
> > > Try it yourself (Makefile)

> > #### Pure SQL Version

> > Uses *ONLY* SQL Statements. From performance measures taken on larger tables, seems to be slower than iterative or cursor-based strategies. Being in pure SQL, this approach is the most portable.

> > > Oracle SQLPLUS code (PureSQL.SQL)
> > > Result (PureSQL.result)
> > > Try it yourself (Makefile)

> > #### 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 Oracle (PLSQL).

> > > Oracle SQLPLUS code (CursorBased.SQL)
> > > Result (CursorBased.result)
> > > Try it yourself (Makefile)

##

## Current Modifications

### Current Insertions

> Insertion with start and end date specified

> > Oracle SQLPLUS code (CF7-2.sql)
> > Oracle SQLPLUS code (CF7-3.sql)
> > Oracle SQLPLUS code (CF7-4.sql)
> > Oracle SQLPLUS code (CF7-5.sql)
> > Oracle SQLPLUS code (CF7-6.sql)

### Current Deletions

> Deletion that apply from now to forever.

### Current Updates

> Changes the validity from now to forever.

> > Oracle SQLPLUS code (CF7-11.sql)

##

## Sequenced Modifications

### Sequenced Insertions

> Insertion that applies to period of applicability

> > Oracle SQLPLUS code (CF7-13.sql)
> > Oracle SQLPLUS code (CF7-14.sql)

### Sequenced Deletions

> Deletion with period of applicability and validity

> > Oracle SQLPLUS code (CF7-15.sql)

### Sequenced Updates

> Updates with a specific period of applicability

> > Oracle SQLPLUS code (CF7-17.sql)

##

## Nonsequenced Modifications

> Modifications that treats the timestamps identically to the other columns.

##

## Modifications that Mention Other Tables

### Complex Current Modifications

> Current modifications that uses other table columns as predicates

> > Oracle SQLPLUS code (CF7-21.sql)

### Complex Sequenced Modifications

> Sequenced modifications that uses other table columns as predicates

> > Oracle SQLPLUS code (CF7-23.sql)

## Appendix : Information on Oracle Used in the Code Fragments

## Oracle 7.3.2.1.0

All the Code Fragments were coded and tested on Oracle 7.3.2.1.0 (64-bit) RDBMS, using SQLPLUS 2.3.2.0.0. All the Code Fragments can be executed via one of the following methods: On the SQLPLUS command line, type in the desired code fragment (Don't forget the ';' or '/' at the end of the entire code fragment). On the SQLPLUS command line, enter "@{filename containing the Oracle SQL script}". For example: SQL> @cf5-4.sql Where "SQL>" is the default prompt line in Oracle SQLPLUS On an UNIX system command line, Enter "sqlplus {login}/{password} \@{filename containing the Oracle SQL script}". Where {login} is your Oracle User login id and {password} is your password for the {login}Some of the Code Fragments operate on only temporal tables, only non-temporal tables, or a mixture of both. Some sample Code Fragment SQL scripts will inform you on what type(s) of tables to operate on. Based on the section of the book, the other code fragments can only operate on the tables for that section.

### Sample Data (different from performance test data) for Code Fragments

The following SQL scripts can be executed just like a code fragment.

> Oracle SQLPLUS code for EMPLOYEES table (employees.sql)
> Oracle SQLPLUS code for JOB_TITLES table (job_titles.sql)
> Oracle SQLPLUS code for NON-TEMPORAL INCUMBENTS table (incumbents_snap.sql)
> Oracle SQLPLUS code for TEMPORAL INCUMBENTS DATA (incumbents_tdata.sql)

> The creation of TEMPORAL INCUMBENTS table SQL script is already a code fragment.

> Oracle SQLPLUS code for NON-TEMPORAL POSITIONS table (positions_snap.sql)
> Oracle SQLPLUS code for TEMPORAL POSITIONS table (positions_temporal.sql)

### Oracle 7.3 Assertions

Oracle 7.3 does not have SQL92 ASSERTIONS. Instead, assertions are maintained through referential integrity, table constraints, column constraints, or TRIGGERS. Table and column constraints are set when a table is created and cannot be altered once set without recreating the table. Column constraints can also be created when a new column is added to an existing table. TRIGGERS are Oracle SQL statements that are executed based on a transaction on a table or row. What activates a TRIGGER is an action (user/program) type of UPDATE-ing, DELETE-ing, or INSERT-ing at least one row in a table that has at least 1 trigger associated with it. Each table can only have 1 TRIGGER of each type (UPDATE,DELETE,INSERT). When an action is performed on a table, i.e. an insert of a row, a trigger can check for complex data/database integrity conformance for the data about to be entered that a table/column or referential integrity constraint cannot. If the data to be inserted in invalid in some way (either RDBMS or user/DBA-defined), a descriptive error can be issued to inform the user and the insertion (transaction) can/is aborted. Usually, for extremely complex assertions, a TRIGGER is created for a table that invokes a Oracle PL/SQL procedure.

### Oracle 7.3 References

I would like to thank the following Oracle reference book for some of the help in translating the SQL92 Code Fragments to Oracle 7.3. Kock, George and Loney, Kevin, "Oracle, The Complete Reference". Oracle Press. Berkeley. 1997.

#### 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)

#### ORACLE Specific Code Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Bruce C. Huang, Department of Computer Science, University of Arizona (bchuang@cs.arizona.edu)
December 1, 1997 (Last Update)