Previous Next Contents

32. Documentation and Reference Books

32.1 User Guides and Manuals

The following are included in the PostgreSQL distribution in the postscript format and unix man-pages. If you have access to internet, you can find the documents listed below at http://www.postgresql.org/docs

32.2 Online Documentation

32.3 Useful Reference Textbooks

Hundreds of other titles on SQL are available! Check out a bookstore.

32.4 ANSI/ISO SQL Specifications documents - SQL 1992, SQL 1998

ANSI/ISO SQL specifications documents can be found at these sites listed below -

32.5 Syntax of ANSI/ISO SQL 1992

See Appendix A of this document Appendix A

32.6 Syntax of ANSI/ISO SQL 1998

See Appendix B of this document Appendix B

32.7 SQL Tutorial for beginners

See Appendix C of this document Appendix C

32.8 Temporal Extension to SQL92

This directory contains the language specification for a temporal extension to the SQL-92 language standard. This new language is designated TSQL2.

The language specification present here is the final version of the language.

Correspondence may be directed to the chair of the TSQL2 Language Design Committee, Richard T.Snodgrass, Department of Computer Science, University of Arizona, Tucson, AZ 85721, rts@cs.arizona.edu . The affiliations and e-mail addresses of the TSQL2 Language Design Committee members may be found in a separate section at the end of the language specification.

The contents of this directory are as follows.

spec.dvi,.ps TSQL2 Language Specification, published in September, 1994

bookspec.ps TSQL2 Language Specification, as it appears in the TSQL2 book, published in September, 1995 (see below).

sql3 change proposals submitted to the ANSI and ISO SQL3 committees.

Associated with the language specification is a collection of commentaries which discuss design decisions, provide examples, and consider how the language may be implemented. These commentaries were originally proposals to the TSQL2 Language Design Committee. They now serve a different purpose: to provide examples of the constructs, motivate the many decisions made during the language design, and compare TSQL2 with the many other language proposals that have been made over the last fifteen years. It should be emphasized that these commentaries are not part of the TSQL2 language specification per se, but rather supplement and elaborate upon it. The language specification proper is the final word on TSQL2.

The commentaries, along with the language specification, several indexes, and other supporting material, has been published as a book:

Snodgrass, R.T., editor, The TSQL2 Temporal Query Language, Kluwer Academic Publishers, 1995, 674+xxiv pages.

The evaluation commentary appears in the book in an abbreviated form; the full commentary is provided in this directory as file eval.ps

The file tl2tsql2.pl is a prolog program that tranlates allowed temporal logic to TSQL2. This program was written by Michael Boehlen boehlen@iesd.auc.dk He may be contacted for a paper that describes this translation. This is a rather dated version of that program. Newer versions are available at http://www.cs.auc.dk/general/DBS/tdb/TimeCenter/Software (the TimeDB and Tiger systems).

32.9 Part 0 - Acquiring ISO/ANSI SQL Documents

  This document shows you how to (legally) acquire a copy
  of the SQL-92 standard and how to acquire a copy of the "current" SQL3
  Working Draft.

  The standard is copyrighted ANSI standard by ANSI, the ISO standard by ISO.

  There are two (2) current SQL standards, an ANSI publication
  and an ISO publication. The two standards are word-for-word identical
  except for such trivial matters as the title of the document, page
  headers, the phrase "International Standard" vs "American Standard", and
  so forth.

                    Buying the SQL-92 Standard

  The ISO standard, ISO/IEC 9075:1992, Information Technology - Database
  Languages - SQL, is currently (March, 1993) available and in stock from
  ANSI at:

     American National Standards Institute
     1430 Broadway
     New York, NY 10018 (USA)
     Phone (sales): +1.212.642.4900

  at a cost of US$230.00. The ANSI version, ANSI X3.135-1992, American
  National Standard for Information Systems - Database Language SQL, was
  not available from stock at this writing, but was expected to be
  available by some time between late March and early May, 1993). It is
  expected to be be priced at US$225.00.

  If you purchase either document from ANSI, it will have a handling
  charge of 7% added to it (that is, about US$9.10). Overseas shipping
  charges will undoubtedly add still more cost. ANSI requires a hardcopy
  of a company purchase order to accompany all orders; alternately, you
  can send a check drawn on a US bank in US dollars, which they will cash
  and clear before shipping your order. (An exception exists: If your
  organization is a corporate member of ANSI, then ANSI will ship the
  documents and simply bill your company.)

  The ISO standard is also available outside the United States from local
  national bodies (country standardization bodies) that are members of
  either ISO (International Organization for Standardization) or IEC
  (International Electrotechnical Commission). Copies of the list of
  national bodies and their addresses are available from ANSI or from
  other national bodies. They are also available from ISO:

     International Organization for Standardization
     Central Secretariat
     1, rue de Varembi
     CH-1211 Genhve 20
     Switzerland

  If you prefer to order the standard in a more convenient and quick
  fashion, you'll have to pay for the privilege. You can order ISO/IEC
  9075:1992, Information Technology - Database Languages - SQL, from:

     Global Engineering Documents
     2805 McGaw Ave
     Irvine, CA 92714 (USA)
     USA
     Phone (works from anywhere): +1.714.261.1455
     Phone (only in the USA): (800)854-7179

  for a cost of US$308.00. I do not know if that includes shipping or not,
  but I would guess that international shipping (at least) would cost
  extra. They will be able to ship you a document fairly quickly and will
  even accept "major credit cards". Global does not yet have the ANSI
  version nor do they have a price or an expected date (though I would
  expect it within a few weeks following the publication by ANSI and at a
  price near US$300.00).

             Buying a copy of the SQL3 Working Draft
  You can purchase a hardcopy of the SQL3 working draft from the ANSI X3
  Secretariat, CBEMA (Computer and Business Equipment Manufacturers
  Association). They intend to keep the "most recent" versions of the SQL3
  working draft available and sell them for about US$60.00 to US$65.00.
  You can contact CBEMA at:

     CBEMA, X3 Secretariat
     Attn: Lynn Barra
     1250 Eye St.
     Suite 200
     Washington, DC 20005 (USA)

  Lynn Barra can also be reached by telephone at +1.202.626.5738 to
  request a copy, though mail is probably more courteous.

           Electronic Access to the SQL3 Working Draft
  The most recent version (as of the date of this writing) of the SQL3
  (both ANSI and ISO) working draft (and all of its Parts) is available by
  "anonymous ftp" or by "ftpmail" on:

     gatekeeper.dec.com

  at

     /pub/standards/sql/

  In this directory are a number of files.  There are PostScript. files
  and "plain text" (not prettily formatted, but readable on a screen
  without special software).

  In general, you can find files with names like:

     sql-bindings-mar94.ps
     sql-bindings-mar94.txt
     sql-cli-mar94.ps
     sql-cli-mar94.txt
     sql-foundation-mar94.ps
     sql-foundation-mar94.txt
     sql-framework-mar94.ps
     sql-framework-mar94.txt
     sql-psm-mar94.ps
     sql-psm-mar94.txt

  As new versions of the documents are produced, the "mar94" will change
  to indicate the new date of publication (e.g., "aug94" is the expected
  date of the next publication after "mar94").

  In addition, for those readers unable to get a directory listing by FTP,
  we have placed a file with the name:

     ls

  into the same directory.  This file (surprise!) contains a directory
  listing of the directory.

               Retrieving Files Directly Using ftp

  This is a sample of how to use FTP. Specifically, it shows how to
  connect to gatekeeper.dec.com, get to the directory where the base
  document is kept, and transfer the document to your host. Note that your
  host must have Internet access to do this. The login is 'ftp' and the
  password is your email address (this is sometimes referred to as
  'anonymous ftp'). The command 'type binary' is used to ensure that no
  bits are stripped from the file(s) received. 'get' gets one file at a
  time. Comments in the script below are inside angle brackets <like so>.

  % ftp gatekeeper.dec.com
  Connected to gatekeeper.dec.com.
  220- *** /etc/motd.ftp ***
       Gatekeeper.DEC.COM is an unsupported service of DEC Corporate Research.
       <...this goes on for a while...>
  220 gatekeeper.dec.com FTP server (Version 5.83 Sat ... 1992) ready.
  Name (gatekeeper.dec.com:<yourlogin here>): ftp  <anonymous also works>
  331 Guest login ok, send ident as password.
  Password: <enter your email address here >
  230 Guest login ok, access restrictions apply.
  Remote system type is UNIX.  <or whatever>
  Using binary mode to transfer files.
  ftp> cd pub/standards/sql
  250 CWD command successful.
  ftp> dir
  200 PORT command successful.
  150 Opening ASCII mode data connection for /bin/ls.
  total 9529
  -r--r--r--  1 root     system     357782 Feb 25 10:18 x3h2-93-081.ps
  -r--r--r--  1 root     system     158782 Feb 25 10:19 x3h2-93-081.txt
  -r--r--r--  1 root     system     195202 Feb 25 10:20 x3h2-93-082.ps
  -r--r--r--  1 root     system      90900 Feb 25 10:20 x3h2-93-082.txt
  -r--r--r--  1 root     system    5856284 Feb 25 09:55 x3h2-93-091.ps
  -r--r--r--  1 root     system    3043687 Feb 25 09:57 x3h2-93-091.txt
  226 Transfer complete.
  ftp> type binary
  200 Type set to I.
  ftp> get x3h2-93-082.txt
  200 PORT command successful.
  150 Opening BINARY mode data connection for x3h2-93-082.txt (90900 bytes).
  226 Transfer complete.
  90900 bytes received in 0.53 seconds (166.11 Kbytes/s)
  ftp> quit
  % <the file is now in your directory as x3h2-93-082.txt>

           Retrieving Files Without Direct ftp Support
  Digital Equipment Corporation, like several other companies, provides
  ftp email service. The response can take several days, but it does
  provide a service equivalent to ftp for those without direct Internet
  ftp access. The address of the server is:

     ftpmail@decwrl.dec.com

  The following script will retrieve the PostScript for the latest version
  of the SQL3 document:

     reply joe.programmer@imaginary-corp.com
     connect gatekeeper.dec.com anonymous
     binary
     compress
  The following script will retrieve the PostScript for the latest version
  of the SQL3 document:

     reply joe.programmer@imaginary-corp.com
     connect gatekeeper.dec.com anonymous
     binary
     compress
     uuencode
     chdir /pub/standards/sql
     get x3h2-93-091.ps
     quit

  The first line in the script commands the server to return the requested
  files to you; you should replace "joe.programmer@imaginary-corp.com"
  with your Internet address. The file in this example, x3h2-93-091.ps,
  is returned in "compress"ed "uuencode"d format as 34 separate email
  messages. If your environment does not provide tools for reconstructing
  such files, then you could retrieve the file as plain text with the
  following script:

     reply joe.programmer@imaginary-corp.com
     connect gatekeeper.dec.com anonymous
     chdir /pub/standards/sql
     get x3h2-93-091.ps
     quit

  But be warned, the .ps file will probably be sent to you in more than 70
  parts!

  To retrieve any particular file, other than x3h2-93-091.ps, simply
  replace "x3h2-93-091.ps" with the name of the desired file. To get a
  directory listing of all files available, replace "get x3h2-93-091.ps"
  with "dir".

32.10 Part 1 - ISO/ANSI SQL Current Status

This chapter is a source of information about the SQL
standards process and its current state.

----------------------------------------------------------------------------
Current Status

Development is currently underway to enhance SQL into a computationally
complete language for the definition and management of persistent, complex
objects. This includes: generalization and specialization hierarchies,
multiple inheritance, user defined data types, triggers and assertions,
support for knowledge based systems, recursive query expressions, and
additional data administration tools. It also includes the specification of
abstract data types (ADTs), object identifiers, methods, inheritance,
polymorphism, encapsulation, and all of the other facilities normally
associated with object data management.

In the fall of 1996, several parts of SQL3 went through a ISO CD ballot.
Those parts were SQL/Framework, SQL/Foundation, and SQL/Bindings. Those
ballots failed (as expected) with 900 or so comments. In Late January, there
was an ISO DBL editing meeting that processed a large number of problem
solutions that were either included with ballot comments or submitted as
separate papers. Since the DBL editing meeting was unable to process all of
the comments, the editing meeting has been extended. The completion of the
editing meeting is scheduled for the end of July, 1997, in London.

Following the July editing meeting, the expectation is that a Final CD
ballot will be requested for these parts of SQL. The Final CD process will
take about 6 months and a DBL editing meeting, after which there will be a
DIS ballot and a fairly quick IS ballot.

The ISO procedures have changed since SQL/92, so the SQL committees are
still working through the exact details of the process.

If everything goes well, these parts of SQL3 will become an official ISO/IEC
standard in late 1998, but the schedule is very tight.

In 1993, the ANSI and ISO development committees decided to split future SQL
development into a multi-part standard. The Parts are:

   * Part 1: Framework A non-technical description of how the document is
     structured.
   * Part 2: Foundation The core specification, including all of the new ADT
     features.
   * Part 3: SQL/CLI The Call Level Interface.
   * Part 4: SQL/PSM The stored procedures specification, including
     computational completeness.
   * Part 5: SQL/Bindings The Dynamic SQL and Embedded SQL bindings taken
     from SQL-92.
   * Part 6: SQL/XA An SQL specialization of the popular XA Interface
     developed by X/Open
   * Part 7:SQL/TemporalAdds time related capabilities to the SQL standards.

In the USA, the entirety of SQL3 is being processed as both an ANSI Domestic
("D") project and as an ISO project. The expected time frame for completion
of SQL3 is currently 1999.

The SQL/CLI and SQL/PSM are being processed as fast as possible as addendums
to SQL-92. In the USA, these are being processed only as International ("I")
projects. SQL/CLI was completed in 1995. SQL/PSM should be completed
sometime in late 1996.

In addition to the SQL3 work, a number of additional projects are being
persued:

   * SQL/MM An ongoing effort to define standard multi-media packages using
     the SQL3 ADT capabilities.
   * Remote Data Access (RDA)

----------------------------------------------------------------------------

Standards Committee and Process

There are actually a number of SQL standards committees around the world.
There is an international SQL standards group as a part of ISO. A number of
countries have committees that focus on SQL. These countries (usually) send
representatives to ISO/IEC JTC1/SC 21/WG3 DBL meetings. The countries that
actively participate in the ISO SQL standards process are:

   * Australia
   * Brazil
   * Canada
   * France
   * Germany
   * Japan
   * Korea
   * The Netherlands
   * United Kingdom
   * United States

----------------------------------------------------------------------------

NIST Validation

SQL implementations are validated (in the Unites States) by the National
Institute of Standards and Testing (NIST). NIST currently has a validation
test suite for entry level SQL-92. The exact details of the NIST validation
requirements are defined as a Federal Information Processing Standard
(FIPS). The current requirements for SQL are defined in FIPS 127-2. The
Postscript and Text versions of this document can be retrieved from NIST.
The current SQL Validated Products List can also be retrieved from NIST.

----------------------------------------------------------------------------

Standard SQL Publications and Articles

There are two versions of the SQL standard. Both are available from ANSI:

   * ISO/IEC 9075:1992, "Information Technology --- Database Languages ---
     SQL"
   * ANSI X3.135-1992, "Database Language SQL"

The two versions of the SQL standard are identical except for the front
matter and references to other standards. Both versions are available from:

   * American National Standards Institute
     1430 Broadway
     New York, NY 10018
     USA
     Phone (sales): +1.212.642.4900

In additon to the SQL-92 standard, there is now a Technical Corrigendum (bug
fixes):

   * Technical Corrigendum 1:1994 to ISO/IEC 9075:1992

TC 1 should also be available from ANSI. There is only an ISO version of TC
1 -- it applies both to the ISO and ANSI versions of SQL-92.

In addition to the standards, several books have been written about the 1992
SQL standard. These books provide a much more readable description of the
standard than the actual standard.

----------------------------------------------------------------------------

Related Standards

A number of other standards are of interest to the SQL community. This
section contains pointers to information on those efforts. These pointers
will be augmented as additional information becomes available on the web.

   * SQL Environments (FIPS 193)
   * Next Generation Repository Systems (X3H4) - a News Release calling for
     particpation in "Developing Standards for the Next Generation
     Repository Systems."
----------------------------------------------------------------------------

32.11 Part 2 - ISO/ANSI SQL Foundation

A significant portion of the SQL3 effort is in the SQL Foundation document:

   * Base SQL/PSM capabilities (moved form SQL/PSM-92)
   * New data types
   * Triggers
   * Subtables
   * Abstract Data Types (ADT)
   * Object Oriented Capabilities

There are several prerequisites to the object oriented capabilities:

   * Capability of defining complex operations
   * Store complex operations in the database
   * External procedure calls  Some operations may not be in SQL, or may
     require external interactions

These capabilities are defined as a part of SQL/PSM

A great deal of work is currently being done to refine the SQL-3 object
model and align it with the object model proposed by ODMG. This effort is
described in the X3H2 and ISO DBL paper: Accomodating SQL3 and ODMG. A
recent update on the SQL3/OQL Merger is also available.

SQL3 Timing

Work on SQL3 is well underway, but the final standards is several years
away.

   * International ballot to progress SQL3 Foundation from Working Draft to
     Committee Draft (CD) taking place fall, 1996.
   * Ballot is expected to generate numerous comments
   * A second CD ballot is likely to be required
   * Draft International Standard ballot is likely to be take place in mid
     1998
   * International Standard could be completed by mid 1999.

The ANSI version of the standard will be on a similar schedule.

----------------------------------------------------------------------------

32.12 Part 3 - ISO/ANSI SQL Call Level Interface

The SQL/CLI is a programing call level interface to SQL databases. It is
designed to support database access from shrink-wrapped applications. The
CLI was originally created by a subcommittee of the SQL Access Group (SAG).
The SAG/CLI specification was published as the Microsoft Open DataBase
Connectivity (ODBC) specification in 1992. In 1993, SAG submitted the CLI to
the ANSI and ISO SQL committees. (The SQL Access Group has now merged with
X/Open consortium.)

SQL/CLI provides an international standard for:

   * Implementation-independent CLI to access SQL databases
   * Client-server tools can easily access database through dynamic Link
     Libraries
   * Supports and encourages rich set of Client-server tools

SQL/CLI Timing

For the standards process, SQL/CLI is being processed with blinding speed.

   * SQL/CLI is an addendum to 1992 SQL standard (SQL-92)
   * Completed as an ISO standard in 1995
   * ISO/IEC 9075-3:1995 Information technology -- Database languages -- SQL
     -- Part 3: Call-Level Interface (SQL/CLI)
   * Current SQL/CLI effort is adding support for SQL3 features
----------------------------------------------------------------------------

32.13 Part 4 - ISO/ANSI SQL Persistent Stored Modules

SQL/PSM expands SQL by adding:

   * Procedural language extensions
   * Multi-statement and Stored Procedures
   * External function and procedure calls

In addition to being a valuable application development tool, SQL/PSM
provides the foundation support for the object oriented capabilities in
SQL3.

Multi-statement and Stored Procedures

Multi-statement and stored procedures offer a variety of advantages in a
client/server environment:

   * Performance - Since a stored procedure can perform multiple SQL
     statements, network interaction with the client are reduced.
   * Security - A user can be given the right to call a stored procedure
     that updates a table or set of tables but denied the right to update
     the tables directly
   * Shared code - The code in a stored procedure does not have to be
     rewritten and retested for each client tool that accesses the database.
   * Control - Provides a single point of definition and control for
     application logic.

Procedural Language Extensions

Procedural language add the power of a traditional programming language to
SQL through flow control statements and a variety of other programming
constructs.

Flow Control Statements

   * If-then-else
   * Looping constructs
   * Exception handling
   * Case statement
   * Begin-End blocks

The procedural language extensions include other programming language
constructs:

   * Variable declarations
   * Set statements for value assignment
   * Get diagnostics for process and status information

In addition, all of the traditional SQL statements can be included in
multi-statement procedures.

External Procedure and Function Calls

One feature frequently mentioned in the wish lists for many database
products, and implemented in some, is a capability augmenting the built-in
features with calls to user-written procedures external to the database
software.

   * Allows a particular site or application to add their own database
     functions
   * Can be used throughout the database applications

The benefit of this capability is that it gives the database (and therefore
database applications) access to a rich set of procedures and functions too
numerous to be defined by a standards committee.

SQL/PSM Timing

SQL/PSM is proceeding quickly:

   * SQL/PSM is an addendum to SQL-92
   * International ballot to progress SQL/PSM from a Draft International
     Standard to an International Standard ended January, 1996.
   * Editing meeting in May, 1996 did not resolve all of the comments
   * Continuation of PSM Editing meeting is scheduled for September 30
     through October 4, 1996
   * The schedule is tight but there is a chance that PSM will be published
     with a 1996 date.
   * The official designation will be:
     ISO/IEC DIS 9075-4:199? Information technology -- Database languages --
     SQL -- Part 4: SQL Persistent Stored Modules (SQL/PSM)
   * Work is well underway on adding SQL/PSM support for SQL3 features.

----------------------------------------------------------------------------

32.14 Part 5 - ISO/ANSI SQL/Bindings

For ease of reference, the programming language bindings have been pulled
out into a separate document. The current version is simply an extract of
the dynamic and embedded bindings from SQL-92.

A variety of issues remain unresolved for the programming language bindings.

For traditional programming language, mappings exist for the SQL-92
datatypes. However, mappings must be defined between SQL objects and
programming language variables.

For object oriented languages, mapping must be defined for the current SQL
datatypes and between the SQL object model and the object model of the
object-oriented language.

The object model needs to stabilize before these can be addressed.

The language bindings will be completed as a part of SQL3.

----------------------------------------------------------------------------

32.15 Part 6 - ISO/ANSI SQL XA Interface Specialization (SQL/XA)

This specification would standardize an application program interface (API)
between a global Transaction Manager and an SQL Resource Manager. It would
standardize the function calls, based upon the semantics of ISO/IEC 10026,
"Distributed Transaction Processing", that an SQL Resource Manager would
have to support for two-phase commit. The base document is derived from an
X/Open publication, with X/Open permission, that specifies explicit input
and output parameters and semantics, in terms of SQL data types, for the
following functions: xa_close, xa_commit, xa_complete, xa_end, xa_forget,
xa_open, xa_prepare, xa_recover, xa_rollback, and xa_start.

ISO is currently attempting to fast-track the X/Open XA specification. The
fast-track process adopts a current industry specification with no changes.
The XA fast-track ballot at the ISO SC21, JTC 1 level started on April 27,
1995 and ends on October 27, 1995. If the XA specification is approved by
75% of the votes, and by 2/3 of the p-members of JTC 1, it will become an
International Standard. If the fast-track ballot is approved, SQL/XA could
become a standard in 1996.
----------------------------------------------------------------------------

32.16 Part 7 - ISO/ANSI SQL Temporal

Temporal SQL deals with time-related data. The concept is that it is useful
to query data to discover what it looked like at a particular point in time.
Temporal SQL is a December, 1994 paper by Rick Snodgrass describing the
concepts.

X3 Announces the Approval of a New Project, ISO/IEC 9075 Part 7:
SQL/Temporal is a press release related to SQL/Temporal.

----------------------------------------------------------------------------
                                Temporal SQL
                                ************
Rick Snodgrass (chair of the TSQL2 committee)
31-Dec-1994

Several people have questioned the need for additional support for time in
SQL3 (as proposed by DBL RIO-75, requesting a new part of SQL to support
temporal databases). The claim is that abstract data types (ADT's) are
sufficient for temporal support. In this informational item, I argue, using
concrete examples, that using columns typed with abstract data types is
inadequate for temporal queries. In particular, many common temporal queries
are either difficult to simulate in SQL, or require embedding SQL in a
procedural language. Alternatives are expressed in TSQL2, a temporal
extension to SQL-92.

1. INTRODUCTION

Valid-time support goes beyond that of a temporal ADT. With the latter, a
column is specified as of a temporal domain, such as DATE or INTERVAL
(examples will be given shortly). With valid time, the rows of a table vary
over time, as reality changes. The timestamp associated with a row of a
valid-time table is interpreted by the query language as the time when the
combination of values of the columns in the row was valid. This implicit
timestamp allows queries to be expressed succinctly and intuitively.

2. A CASE STUDY

2.1 STORING CURRENT INFORMATION

The University of Arizona's Office of Appointed Personnel has some
information in a database, including each employee's name, their current
salary, and their current title. This can be represented by a simple table.

        Employee(Name, Salary, Title)

Given this table, finding an employee's salary is easy.

        SELECT Salary

        FROM Employee

        WHERE Name = 'Bob'

Now the OAP wishes to record the date of birth. To do so, a column is added
to the table, yielding the following schema.

        Employee(Name, Salary, Title, DateofBirth DATE)

Finding the employee's date of birth is analogous to determining the salary.

        SELECT DateofBirth

        FROM Employee

        WHERE Name = 'Bob'

2.2 STORING HISTORY INFORMATION

The OAP wishes to computerize the employment history. To do so, they append
two columns, one indicating when the information in the row became valid,
the other indicating when the information was no longer valid.

Employee (Name, Salary, Title, DateofBirth, Start DATE, Stop DATE)

To the data model, these new columns are identical to DateofBirth. However,
their presence has wide-ranging consequences.

2.3 PROJECTION

To find an employee's current salary, things are more difficult.

        SELECT Salary

        FROM Employee

        WHERE Name = 'Bob' AND Start <= CURRENT_DATE AND CURRENT_DATE <= Stop

This query is more complicated than the previous one. The culprit is
obviously the two new columns. The OAP wants to distribute to each employee
their salary history. Specifically, for each person, the maximal intervals
at each salary needs to be determined. Unfortunately, this is not possible
in SQL. An employee could have arbitrarily many title changes between salary
changes.

Name    Salary  Title             DateofBirth   Start           Stop

----    ------  -----             -----------   -----           ----

Bob     60000   Assistant Provost 1945-04-09    1993-01-01      1993-05-30

Bob     70000   Assistant Provost 1945-04-09    1993-06-01      1993-09-30

Bob     70000   Provost           1945-04-09    1993-10-01      1994-01-31

Bob     70000   Professor         1945-04-09    1994-02-01      1994-12-31

                               Figure 1

Note that there are three rows in which Bob's salary remained constant at
$70,000. Hence, the result should be two rows for Bob.

Name    Salary  Start           Stop

----    ------  -----           ----

Bob     60000   1993-01-01      1993-05-30

Bob     70000   1993-06-01      1994-12-31

One alternative is to give the user a printout of Salary and Title
information, and have user determine when his/her salary changed. This
alternative is not very appealing or realistic. A second alternative is to
use SQL as much as possible.

CREATE TABLE Temp(Salary, Start, Stop)

AS      SELECT Salary, Start, Stop

        FROM Employee;

repeat

        UPDATE Temp T1

        SET (T1.Stop) = (SELECT MAX(T2.Stop)

                         FROM Temp AS T2

                         WHERE T1.Salary = T2.Salary AND T1.Start < T2.Start

                                AND T1.Stop >= T2.Start AND T1.Stop < T2.Stop)

        WHERE EXISTS (SELECT *

                      FROM Temp AS T2

                      WHERE T1.Salary = T2.Salary AND T1.Start < T2.Start

                                AND T1.Stop >= T2.Start AND T1.Stop < T2.Stop)

        until no rows updated;

DELETE FROM Temp T1

WHERE EXISTS (SELECT *

              FROM Temp AS T2

              WHERE T1.Salary = T2.Salary

                        AND ((T1.Start > T2.Start AND T1.Stop <= T2.Stop)

                        OR (T1.Start >= T2.Start AND T1.Stop < T2.Stop))

The loop finds those intervals that overlap or are adjacent and thus should
be merged. The loop is executed log N times in the worst case, where N is
the number of rows in a chain of overlapping or adjacent value-equivalent
rows. The reader can simulate the query on the example table to convince
him/herself of its correctness.

A third alternative is to use SQL only to open a cursor on the table. A
linked list of periods is maintained, each with a salary. This linked list
should be initialized to empty.

DECLARE emp_cursor CURSOR FOR

        SELECT Salary, Title, Start, Stop

        FROM Employee;

OPEN emp_cursor;

loop:

        FETCH emp_cursor INTO :salary, :start, :stop;

        if no-data returned then goto finished;

        find position in linked list to insert this information;

        goto loop;

finished:

CLOSE emp_cursor;

iterate through linked list, printing out dates and salaries

The linked list may not be necessary in this case if the cursor is ORDER BY
Start.

In any case, the query, a natural one, is quite difficult to express using
the facilities present in SQL-92. The query is trivial in TSQL2.

        SELECT Salary

        FROM Employee

2.4 JOIN

A more drastic approach is to avoid the problem of extracting the salary
history by reorganizing the schema to separate salary, title, and date of
birth information (in the following, we ignore the date of birth, for
simplicity).

        Employee1 (Name, Salary, Start DATE, Stop DATE)

        Employee2 (Name, Title, Start DATE, Stop DATE)

The Employee1 table is as follows.

Name    Salary  Start           Stop

----    ------  -----           ----

Bob     60000   1993-01-01      1993-05-30

Bob     70000   1993-06-01      1993-12-31

Here is the example Employee2 table.

Name    Title                   Start           Stop

----    ------                  -----           ----

Bob     Assistant Provost       1993-01-01      1993-09-30

Bob     Provost                 1993-10-01      1994-01-31

Bob     Professor               1994-02-01      1994-12-31

With this change, getting the salary information for an employee is now
easy.

        SELECT Salary, Start, Stop

        FROM Employee1

        WHERE Name = 'Bob'

But what if the OAP wants a table of salary, title intervals (that is,
suppose the OAP wishes a table to be computed in the form of Figure 1)? One
alternative is to print out two tables, and let the user figure out the
combinations. A second alternative is to use SQL entirely. Unfortunately,
this query must do a case analysis of how each row of Employee1 overlaps
each row of Employee2; there are four possible cases.

SELECT Employee1.Name, Salary, Dept, Employee1.Start, Employee1.Stop

FROM Employee1, Employee2

WHERE Employee1.Name = Employee2.Name

     AND Employee2.Start <= Employee1.Start AND Employee1.Stop < Employee2.Stop

UNION

SELECT Employee1.Name, Salary, Dept, Employee1.Start, Employee2.Stop

FROM Employee1, Employee2

WHERE Employee1.Name = Employee2.Name

     AND Employee1.Start >= Employee2.Start AND Employee2.Stop < Employee1.Stop

        AND Employee1.Start < Employee2.Stop

UNION

SELECT Employee1.Name, Salary, Dept, Employee2.Start, Employee1.Stop

FROM Employee1, Employee2

WHERE Employee1.Name = Employee2.Name

     AND Employee2.Start > Employee1.Start AND Employee1.Stop < Employee2.Stop

        AND Employee2.Start < Employee1.Stop

UNION

SELECT Employee1.Name, Salary, Dept, Employee2.Start, Employee2.Stop

FROM Employee1, Employee2

WHERE Employee1.Name = Employee2.Name

     AND Employee2.Start > Employee1.Start AND Employee2.Stop < Employee1.Stop

Getting all the cases right is a challenging task. In TSQL2, performing a
temporal join is just what one would expect.

        SELECT Employee1.Name, Salary, Dept

        FROM Employee1, Employee2

        WHERE Employee1.Name = Employee2.Name

2.5 AGGREGATES

Now the OAP is asked, what is the maximum salary? Before adding time, this
was easy.

        SELECT MAX(Salary)

        FROM Employee

Now that the salary history is stored, we'd like a history of the maximum
salary over time. The problem, of course, is that SQL does not provide
temporal aggregates. The easy way to do this is to print out the
information, and scan manually for the maximums. An alternative is to be
tricky and convert the snapshot aggregate query into a non-aggregate query,
then convert that into a temporal query. The non-aggregate query finds those
salaries for which a greater salary does not exist.

        SELECT Salary

        FROM Employee AS E1

        WHERE NOT EXISTS (SELECT *

                          FROM Employee AS E2

                          WHERE E2.Salary > E1.Salary)

Converting this query into a temporal query is far from obvious. The
following is one approach.

CREATE TABLE Temp (Salary, Start, Stop)

AS      SELECT Salary, Start, Stop

        FROM Employee;

INSERT INTO Temp

        SELECT T.Salary, T.Start, E.Start

        FROM Temp AS T, Employee AS E

        WHERE E.Start >= T.Start AND E.Start < T.Stop AND E.Salary > T.Salary;

INSERT INTO Temp

        SELECT T.Salary, T.Stop, E.Stop

        FROM Temp AS T, Employee AS E

        WHERE E.Stop > T.Start AND E.Stop <= T.Stop AND E.Salary > T.Salary;

DELETE FROM Temp T

WHERE EXISTS (SELECT *

              FROM Employee AS E

              WHERE ((T.Start => E.Start AND T.Start < E.Stop)

                        OR (E.Start >= T.Start AND E.Start < T.Stop))

                    AND E.Salary > T.Salary;

This approach creates an auxiliary table. We add to this table the lower
period of a period subtraction and the upper period of a period subtraction.
We then delete all periods that overlap with some row defined by the
subquery, thereby effecting the NOT EXISTS. Finally we generate from the
auxiliary table maximal periods, in the same way that the salary information
was computed above. As one might imagine, such SQL code is extremely
inefficient to execute, given the complex nested queries with inequality
predicates.

A third alternative is to use SQL as little as possible, and instead compute
the desired maximum history in a host language using cursors.

The query in TSQL2 is again straightforward and intuitive.

        SELECT MAX(Salary)

        FROM Employee

3. SUMMARY

Time-varying data is manipulated in most database applications. Valid-time
support is absent in SQL. Many common temporal queries are either difficult
to simulate in SQL, or require embedding SQL in a procedural language, due
to SQL's lack of support for valid-time tables in its data model and query
constructs.

Elsewhere, we showed that adding valid-time support requires few changes to
the DBMS implementation, can dramatically simplify some queries and enable
others, and can later enable optimizations in storage structures, indexing
methods, and optimization strategies that can yield significant performance
improvements.

With a new part of SQL3 supporting time-varying information, we can begin to
address such applications, enabling SQL3 to better manage temporal data.

----------------------------------------------------------------------------
           Accredited Standards Committee* X3, Information Technology
NEWS RELEASE

Doc. No.:       PR/96-0002

Reply to:       Barbara Bennett at bbennett@itic.nw.dc.us

             X3 Announces the Approval of a New Project, ISO/IEC

                         9075 Part 7:  SQL/Temporal

Washington D.C., January 1996 -- Accredited Standards Committee

X3, Information Technology is announcing the approval of a new

project on SQL/Temporal Support, ISO/IEC 9075 Part 7, with the work

being done in Technical Committee X3H2, Database.

The scope of this proposed standard specifies a new Part of the emerging

SQL3 standard, e.g., Part 7, Temporal SQL, to be extensions to the SQL

language supporting storage, retrieval, and manipulation of temporal data

in an SQL database environment.

The next X3H2 meeting is scheduled for March 11-14, 1996 in

Kansas.

Inquiries regarding this project should be sent to the Chairman of

X3H2, Dr. Donald R. Deutsch, Sybase, Inc., Suite 800, 6550 Rock Spring

Drive, Bethesda, MD  20817.  Email: deutsch@sybase.com.

An initial call for possible patents and other pertinent issues

(copyrights, trademarks) is now being issued.  Please submit

information on these issues to the X3 Secretariat at 1250 Eye Street

NW, Suite 200, Washington DC  20005. Email: x3sec@itic.nw.dc.us

FAX:  (202)638-4922.

----------------------------------------------------------------------------

X3 Press Index

X3 Home Page

32.17 Part 8 - ISO/ANSI SQL MULTIMEDIA (SQL/MM)

A new ISO/IEC international standardization project for development of an
SQL class library for multimedia applications was approved in early 1993.
This new standardization activity, named SQL Multimedia (SQL/MM), will
specify packages of SQL abstract data type (ADT) definitions using the
facilities for ADT specification and invocation provided in the emerging
SQL3 specification. SQL/MM intends to standardize class libraries for
science and engineering, full-text and document processing, and methods for
the management of multimedia objects such as image, sound, animation, music,
and video. It will likely provide an SQL language binding for multimedia
objects defined by other JTC1 standardization bodies (e.g. SC18 for
documents, SC24 for images, and SC29 for photographs and motion pictures).

The Project Plan for SQL/MM indicates that it will be a multi-part standard
consisting of an evolving number of parts. Part 1 will be a Framework that
specifies how the other parts are to be constructed. Each of the other parts
will be devoted to a specific SQL application package. The following SQL/MM
Part structure exists as of August 1994:

   * Part 1: Framework A non-technical description of how the document is
     structured.
   * Part 2: Full Text Methods and ADTs for text data processing. About 45
     pages.
   * Part 3: Spatial Methods and ADTs for spatial data management. About 200
     pages with active contributions from Spatial Data experts from 3
     national bodies.
   * Part 4: General Purpose Methods and ADTs for complex numbers,
     Facilities include trig and exponential functions, vectors, sets, etc.
     Currently about 90 pages.

There are a number of standards efforts in the area of Spatial and
Geographic information:

   * ANSI X3L1 - Geographic Information Systems.
     Mark Ashworth of Unisys is the liason between X3L1 and ANSI X3H2. He is
     also the editor for parts 1, 3, and 4 of the SQL/MM draft.
   * ISO TC 211 - Geographic information/Geomatics

----------------------------------------------------------------------------


Previous Next Contents