Krishna G. Kulkarni

TSQL2 language specification

This docuinent specifies a temporal extension to the SQL-92 language standard. The language is designated TSQLZ. The document is organized as follows. The next section indicates the starting point of the design, the SQL92 language. Section 4 lists the desired features on which the TSQL2 Language Design Committee reached consensus. Section 5 presents the major concepts underlying TSQL2. Compatibility with SQL-92 is the topic of Section 6. Section 7 briefly discusses how the language can be implemented. Subsequent sections specify the syntax of the language extensions.

SQL:2003 has been published

SQL:2003 has finally achieved final publication as an International Standard, replacing SQL:1999. SQL:2003 is popularly believed to be largely a “bugfix release” of the SQL standard — except, of course, for the SQL/XML work on which we have previously reported. However, as you will learn from this and future columns, there are many compelling new features in the 2003 edition of the SQL standard. We are pleased that three of the more active SQL proposal writers have joined forces to present several of those new features in this month’s column. Krishna Kulkarni is, among other responsibilities, the formal International Representative for the INCITS H2 Technical Committee for Database. Jan-Eike Michels is a frequent USA representative to the corresponding international group, ISO/IEC JTC1/SC32/WG3. Fred Zemke is widely acknowledged as a principle expert in many areas of the SQL standard and also a regular USA representative to WG3. In the coming months, we will provide information about even more of SQL:2003’s new features.

Temporal features in SQL:2011

SQL:2011 was published in December of 2011, replacing SQL:2008 as the most recent revision of the SQL standard. This paper covers the most important new functionality that is part of SQL:2011: the ability to create and manipulate temporal tables.

SQL and management of external data

In late 2000, work was completed on yet another part of the SQL standard [1], to which we introduced our readers in an earlier edition of this column [2].Although SQL database systems manage an enormous amount of data, it certainly has no monopoly on that task. Tremendous amounts of data remain in ordinary operating system files, in network and hierarchical databases, and in other repositories. The need to query and manipulate that data alongside SQL data continues to grow. Database system vendors have developed many approaches to providing such integrated access.In this (partly guested) article, SQLs new part, Management of External Data (SQL/MED), is explored to give readers a better notion of just how applications can use standard SQL to concurrently access their SQL data and their non-SQL data.

Active Database Features in SQL3

Many commercial relational database systems provide support for active rules, which are generally referred to as triggers. However, these facilities have been developed independently by different vendors, and triggers developed for one system generally do not work with another. This chapter describes how triggers are supported in the SQL3 standard, and in particular how the triggers relate to other features of the standard.

A TSQL2 tutorial

This tutorial presents the primary constructs of the consensus temporal query language TSQL2 via a media planning scenario. Media planning is a series of decisions involved in the delivery of a promotional message via mass media. We will follow the planning of a particular advertising campaign. We introduce the scenario by identifying the marketing objective. The media plan involves placing commercials, and is recorded in a temporal database. The media plan must then be evaluated; we show how TSQL2 can be used to derive information from the stored data. We then give examples that illustrate storing and querying indeterminate information, comparing multiple versions of the media plan, accommodating changes to the schema, and vacuuming a temporal database of old data.

SQL/MED: a status report

In March, 2001, we delivered a (partly) guested column covering the topic of Management of External Data [1]. The column you are reading right now reports on the on-going development of the SQL/MED standard and is authored by all but one of the authors of that earlier column. We trust that our readers will benefit from this update on an interesting and important part of SQL. Jim Melton and Andrew Eisenberg

The SQL Standard

Temporal database support was added to the SQL standard in 2011. This lengthy chapter explains that support in detail and compares and contrasts it with the ideas introduced in previous chapters. It discusses “periods” (SQL’s analog of intervals, represented by explicit from/to pairs); an SQL base table can have at most one application time period (corresponding to valid time) and at most one system time period (corresponding to transaction time). SQL supports analogs of certain of the interval operators discussed in previous chapters; unfortunately, however, it has nothing analogous to PACK and UNPACK. The chapter discusses all of these operators, also database design considerations, queries, and updates in the SQL context. In particular, it explains how queries and updates work on “tables with system time” (especially system-versioned tables) and on “bitemporal tables” (tables with both application time and system time). The chapter concludes with a detailed analysis and assessment of the SQL temporal features.

Object-oriented extensions in SQL3: a status report

SQL3 is a new database language standard being developed by both ANSI X3H2 and 1S0 DBL committees for the last three years. SQL3 is upward compatible with SQL-92, the current ANSI/ISO database language standard, and is targeted for completion in 1997. SQL3 extends SQL-92 in many significant ways, one of the major extensions being the addition of an extensible, object-oriented type system. This talk will describe the current status of SQL3 type system.

Integration of SQL and XQuery in IBM DB2

Relational database systems have dominated the database industry for a quarter century. However, the advent of the Web has led to requirements for storage of new kinds of information in which the order of information is important and data structure can vary over time and from one document to another. These evolving requirements have given rise to Extensible Markup Language (XML) as a widely accepted data format and to XQuery as an emerging standard language for querying XML data sources. A set of extensions to the Structured Query Language (SQL) called SQL/XML enables XML data to be stored in relational databases, taking advantage of the mature infrastructure of relational systems and combining the advantages of SQL and XQuery. However, building a bridge between SQL and XQuery is challenging due to the many syntactic and semantic differences between the two languages. This paper describes how IBM DB2® deals with this challenge and provides users with a flexible system for storing and processing both relational and XML data.