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

# MAINTAINING STATE TABLES

## Microsoft Access 2000 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 Join
> > Nonsequenced Variants
> > Duplicate Elimination

##

## Detailed Contents

> 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

##

# About the work

Most of the code fragments in chapters 5 through 7 have been converted to MS Access format. However, some SQL statements are not supported by MS Access. Those that can be directly converted require little alteration. In particular, the End of Time date value has to be changed: End of Time in MS Access is 12/31/9999 instead of 1/1/3000. Wherever possible, queries are designed using both SQL statements and the MS Access Query Wizard. Both formats for these queries are presented in this file to give a better explanation.

MS Access does not support assertions. Therefore, queries involving assertions have to be designed using Visual Basic functions. These functions can only be activated by the events provided in MS Access forms. In particular the After Insert event is used to activate the functions. Unfortunately, this event is triggered only after the actual data is inserted and a rollback strategy is employed if the inserted data violates the assertion. Some complex queries involving nested SQL statements are not directly supported in MS Access SQL. These queries have to be divided into several one-statement queries and implemented as visual basic functions.

Finally, queries designed using the MS Access Query Wizard are indicated using the word Wizard, whereas the SQL queries and the functions are indicated by the words SQL and Function respectively. All assumptions have been listed before the queries to which they apply.

##

##

## Initial Schema

This section of the book elaborates the initial schema when no temporal aspects have been added to the tables.

Code Fragment 5.1 - SQL What is Bob's salary?

Code Fragment 5.1 - Wizard What is Bob's salary?

Code Fragment 5.2 - SQL What is Bob's position?

Code Fragment 5.2 - Wizard What is Bob's position?

Code Fragment 5.3 - SQL What is Bob's date of birth?

Code Fragment 5.3 - Wizard What is Bob's date of birth?

##

##

## Temporal Keys

Two columns are appended to each of the tables. The first column indicating when the row became valid and the second one indicating when the information was no longer valid. Now, the primary keys of these tables need to take the timestamp into consideration.

Code Fragment 5.4 - Function (SSN, PCN) is a sequenced primary key for INCUMBENTS

Code Fragment 5.5 - Function (SSN, PCN) is a sequenced primary key for INCUMBENTS, assuming a closed-closed time-stamp representation

##

##

## Keys Reexamined

Code Fragment 5.6 - Wizard Prevent value-equivalent rows in INCUMBENTS

Code Fragment 5.7 - Wizard Prevent nonsequenced duplicates in INCUMBENTS

Code Fragment 5.8 - Function Prevent current duplicates in INCUMBENTS

Code Fragment 5.9 - Wizard Prevent current duplicates in INCUMBENTS, assuming no future data

Code Fragment 5.10 - Function Prevent sequenced duplicates in INCUMBENTS

##

##

## Referential Integrity

Code Fragment 5.11 - Wizard INCUMBENTS.PCN is a foreign key for POSITIONS.PCN (neither table is temporal)

Code Fragment 5.12 - Function INCUMBENTS .PCN is a current foreign key for POSITIONS.PCN (both tables are temporal)

Code Fragment 5.13 INCUMBENTS.PCN is a current foreign key for POSITIONS.PCN (both tables are temporal, but do not include future data)

Code Fragment 5.14 - Function INCUMBENTS .PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal)

Code Fragment 5.15 - Function POSITIONS. PCN defines a contiguous history

Code Fragment 5.16 - Function INCUMBENTS .PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal, version 2)

Code Fragment 5.17 - Function INCUMBENTS .PCN is a foreign key for POSITIONS.PCN (only POSITIONS is temporal)

##

##

## Extracting Prior States

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

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

Code Fragment 6.6 - SQL Who makes or has made more than $50,000 annually?

Code Fragment 6.6 - Wizard Who makes or has made more than $50,000 annually?

Code Fragment 6.7 - SQL List the social security numbers of current and past employees.

Code Fragment 6.7 - Wizard List the social security numbers of current and past employees.

Code Fragment 6.8 - SQL Sequenced sort INCUMBENTS on the position code (first version).

Code Fragment 6.8 - Wizard Sequenced sort INCUMBENTS on the position code (first version).

Code Fragment 6.9 - SQL Sequenced sort INCUMBENTS on the position code (second version)

Code Fragment 6.9 - Wizard Sequenced sort INCUMBENTS on the position code (second version)

Code Fragment 6.10 - SQL Who makes or has made more than $50,000 annually or less than $10,000?

Code Fragment 6.10 - Wizard Who makes or has made more than $50,000 annually or less than $10,000?

##

##

## Sequenced Join

### Temporal Join

> #### Union

> This query does not use UNION statements because JetSQL in ACCESS does not al low the nested UNION statements within an INSERT statement. Hence, this query had to be broken down into separate INSERT statements. (The same thing happens to the UNION ALL statement.) In order to remove the duplicates from the resulting table, another operation with "SELECT DISTINCT" has to be performed.

> > ACCESS JetSQL code (Union with Index) (Union without Index)

> #### Union All

> This query does not use UNION ALL statements because JetSQL in ACCESS does not allow the nested UNION ALL statements within an INSERT statement. Hence, this query had to be broken down into separate INSERT statements.

> > ACCESS JetSQL code (Union All with Index) (Union All without Index)

> #### Greatest/Least

> The query for the greatest and least cannot be translated into the JetSQL in MS ACCESS. This is because the equivalent functions are not defined in ACCESS.

##

##

## Nonsequenced Variants

Code Fragment 6.13 - SQL List all the salaries, past and present, of employees who have been hazardous waste specialists at some time.

Code Fragment 6.13 - Wizard List all the salaries, past and present, of employees who have been hazardous waste specialists at some time.

Code Fragment 6.14 - SQL When did employees receive raises?

Code Fragment 6.14 - Wizard When did employees receive raises?

##

##

## Duplicate Elimination

#### Coalesce via a Cursor - Removes Duplicates

This query 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 ACCESS (JetSQL).

> ACCESS JetSQL code (ACCESS JetSQL Cursor)

##

##

## Current Modifications

Code Fragment 7.1 - SQL Bob was assigned the position of Associate Director of the Computer Center.

Code Fragment 7.2 - Function Bob was assigned the position of Associate Director of the Computer Center, ensuring primary key

Code Fragment 7.3 - Function Bob was assigned the position of Associate Director of the Computer Center, also ensuring referential integrity, in the restricted case

Code Fragment 7.4 - Function Fill in the hole (in the restricted case) in the POSITIONS table for the position of Associate Director of the Computer Center

Code Fragment 7.5 - Function Bob was assigned the position of Associate Director of the Computer Center, ensuring the primary key, in the unrestricted case

Code Fragment 7.6 cannot be done in MS Access since it does not support COALESCE.

Code Fragment 7.7 - SQL Bob was fired as Associate Director of the Computer Center (only current modifications assumed)

Code Fragment 7.8 - Function Bob was fired as Associate Director of the Computer Center

Code Fragment 7.9 - SQL Bob was promoted to Director of the Computer Center (nontemporal version)

Code Fragment 7.10 - Function Bob was promoted to Director of the Computer Center (assuming only current modifications)

Code Fragment 7.11 - Function Bob was promoted to Director of the Computer Center

##

##

## Sequenced Modifications

Code Fragment 7.12 - SQL Bob was assigned the position of Associate Director of the Computer Center for 1997

Code Fragment 7.13 - Function Bob was assigned the position of Associate Director of the Computer Center for 1997, also ensuring referential integrity

Code Fragment 7.14 - Function Bob was assigned the position of Associate Director of the Computer Center for 1997, also ensuring referential integrity.

Code Fragment 7.15- Function Bob was removed as Associate Director of the Computer Center for 1997

Code Fragment 7.16 - SQL Bob was promoted to Director of the Computer Center (nontemporal version)

Code Fragment 7.17 - Function Bob was promoted to Director of the Computer Center for 1997

##

##

## Nonsequenced Modifications

Code Fragment 7.18 - SQL Delete Bob's records that include 1997 stating that he was Associate Director of the Computer Center

MS Access does not support the INTERVAL construct. However, adding one year in Code Fragment 7.19 is implemented by adding the result of subtracting two dates differing by exactly one year.

Code Fragment 7.19 - SQL Extend Bob's position as Associate Director of the Computer Center for an additional year.

##

##

## Modifications that Mention Other Tables

Code Fragment 7.20 - SQL Bob was promoted to Director of the Computer Center (nontemporal version)

Code Fragment 7.21 - Function Bob was promoted to Director of the Computer Center (nontemporal version)

Code Fragment 7.22 - SQL Bob was promoted to Director of the Computer Center

Code Fragment 7.23 - Function 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)
Rachana R. 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)
Ahmad Arsalan, Department of Computer Science, University of Arizona (arsalan@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
February 2, 1999 (Last Update)

Queries and Code Credits

Ines Fernando Vega Lopez, Department of Computer Science, University of Arizona (ifvega@cs.arizona.edu)
Ahmad Arsalan, Department of Computer Science, University of Arizona (arsalan@cs.arizona.edu)
November 11, 1997 (Last Update)

##