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
Will be a part of PSQL command in the next release 6.3. Already completed.
There is a script in the /tools directory to do that
Use the command psql \h
Look at the regression tests at src/test. There you can find the directories
regress/sql and suite/*.sql.
SQL tutorial scripts is in the directory src/tutorial
Morgan Kaufman Publisher is one of best SQL books. This deals with SQL92.
Addison-Wesley Publishing company is also a good book. Very popular book for SQL.
Stephen Cannan and Gerard Otten
McGraw-Hill Book Company Europe , Berkshire, SL6 2QL, England
Martin Gruber, Technical Editor: Joe Celko
SYBEX Inc. 2021 Challenger Drive Alameda, CA 94501
This book is the Bible of Database Management Systems.
The book details normalization, SQL, recovery, concurrency, security,
integrity, and extensions to the original relational model, current issues
like client/server systems and the Object Oriented model(s). Many
references are included for further reading. Recommended for most users.
Book details theory of relational databases, relational algebra, calculus
and normalisation. But it does not cover real world issues and examples
beyond simple examples. Recommended for most users.
Recommended for most users.
This book is a collection of papers that have been published over the
years on databases. It's not for the casual user but it is really a
reference for advanced (post-graduate) students or database system
developers.
This book is a collection of papers that have been published over the
years on databases. It's not for the casual user but it is really a
reference for advanced (post-graduate) students or database system
developers.
This book goes into the details of access methods, storage techniques.
It's not for the casual user but it is for advanced (post-graduate)
students or database system developers.
Wrox Press Ltd.
Unit 16, 20 James Road, Tyseley
Birmingham, B11 2BA, England
1995
Technology
QED and John Wiley
1991
Presents the requirements of building client/server database
applications via repository metamodels and the use of ANSI standard SQL
1993
Hundreds of other titles on SQL are available! Check out a bookstore.
ANSI/ISO SQL specifications documents can be found at these sites listed below -
See Appendix A of this document Appendix A
See Appendix B of this document Appendix B
See Appendix C of this document Appendix C
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).
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".
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."
----------------------------------------------------------------------------
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.
----------------------------------------------------------------------------
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
----------------------------------------------------------------------------
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.
----------------------------------------------------------------------------
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.
----------------------------------------------------------------------------
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.
----------------------------------------------------------------------------
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
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
----------------------------------------------------------------------------