Next Previous Contents

29. Documentation and Reference Books

29.1 User Guides and Manuals

The following are included in the PostgreSQL distribution in the postscript, HTML formats and unix man-pages. They are located in /usr/doc/postgresql* directory. If you have access to internet, you can find the documents listed below at http://www.postgresql.org/docs and at http://www.postgresql.org/users-lounge/docs.

29.2 Online Documentation

29.3 Useful Reference Textbooks

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

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

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

29.5 Syntax of ANSI/ISO SQL 1992

See Appendix A of this document Appendix A

29.6 Syntax of ANSI/ISO SQL 1998

The SQL 1998 (SQL 3) specification is still under development. See 'Electronic Access to the SQL3 Working Draft' of this document at SQL 1998

29.7 SQL Tutorial for beginners

See Appendix B of this document Appendix B

29.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,

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

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 (the TimeDB and Tiger systems).

29.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 an 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".

29.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 an 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:

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:

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:

NIST Validation

SQL implementations are validated (in the Unites States) by the National Institute of Standards and Training (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:

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.

29.11 Part 2 - ISO/ANSI SQL Foundation

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

There are several prerequisites to the object oriented capabilities:

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.

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

29.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:

SQL/CLI Timing

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

29.13 Part 4 - ISO/ANSI SQL Persistent Stored Modules

SQL/PSM expands SQL by adding:

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:

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

The procedural language extensions include other programming language constructs:

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.

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:

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

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

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

INTRODUCTION

Valid-time support goes beyond that of a temporal ADT. With the later, 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.

A CASE STUDY - 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'

A CASE STUDY - 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.

A CASE STUDY - 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

A CASE STUDY - 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

A CASE STUDY - 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

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.

29.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:

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


Next Previous Contents