Developing Time-Oriented Database Applications in SQL
An enjoyable plunge into the how-to of time and data, with real-world situations and code.
TABLE OF CONTENTS Developing Time-Oriented Database Applications in SQL
Chapter 5: Defining State Tables
OVERVIEW
A valid-time state table records the history of the enterprise. Such a table is easily specified by appending two timestamp columns, one specifying when the row became valid and one specifying when the row stopped being valid. The intervening time is termed the period of validity of the row.
The primary key must be changed when these timestamp columns are added. There are several kinds of temporal primary keys. The most natural variant is the sequenced primary key, which states that the value of the indicated columns is unique at every instant of time.
There are analogous kinds of referential integrity constraints, with the most natural being again the sequenced variant. Unfortunately, this kind is the most challenging to express in SQL.
In 1992 the University of Arizona was confronted with a knotty dilemma. Its data was managed by a suite of COBOL legacy systems, each with its own underlying DBMS or file structure. Personnel records were (and continue to be) managed by PSOS (Personnel Operating System), using the Transact DBMS. Financial records, including purchasing, reorders, property management, fixed assets, and the ledger, are managed by FRS (Financial Records System), which uses IDMS. Student records are contained in yet another system, SIS (Student Information System), which uses VSAM. To obtain information from a specific system, someone in the Center for Computing and Information Technology (CCIT) who was familiar with that system would be tasked to write a report program, which often took several weeks. Getting an...
Copyright Morgan Kaufmann Publishers, Inc. 2000 under license agreement with Books24x7
