Michael D. Soo

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.

Unifying temporal data models via a conceptual model

Abstract To add time support to the relational model, both first normal form (1NF) and non-1NF data models have been proposed. Each has associated advantages and disadvantages. For example, remaining within 1NF when time support is added may introduce data redundancy. On the other hand, well-established storage organization and query evaluation techniques require atomic attribute values, and are thus intended for 1NF models; utilizing a non-1NF model may degrade performance. This paper describes a new temporal data model designed with the single purpose of capturing the time-dependent semantics of data. Here, tuples of bitemporal relations are stamped with sets of two-dimensional chronons in transaction-time/valid-time space. We use the notion of snapshot equivalence to map temporal relation instances and temporal operators of one existing model to equivalent instances and operators of another. We examine five previously proposed schemes for representing bitemporal data: two are tuple-timestamped 1NF representations, one is a Backlog relation composed of 1NF timestamped change requests, and two are non-1NF attribute value-timestamped representations. The mappings between these models are possible using mappings to and from the new conceptual model. The framework of well-behaved mappings between models, with the new conceptual model at the center, illustrates how it is possible to use different models for display and storage purposes in a temporal database system. Some models provide rich structure and are useful for display of temporal data, while other models provide regular structure useful for storing temporal data. The equivalence mappings effectively move the distinction between the investigated data models from a semantic basis to a display-related or a physical, performance-relevant basis, thereby allowing the exploitation of different data models by using each for the task(s) for which they are best suited.

Efficient evaluation of the valid-time natural join

Joins are arguably the most important relational operators. Poor implementations are tantamount to computing the Cartesian product of the input relations. In a temporal database, the problem is more acute for two reasons. First, conventional techniques are designed for the optimization of joins with equality predicates, rather than the inequality predicates prevalent in valid-time queries. Second, the presence of temporally-varying data dramatically increases the size of the database. These factors require new techniques to efficiently evaluate valid-time joins. The authors address this need for efficient join evaluation in databases supporting valid-time. A new temporal-join algorithm based on tuple partitioning is introduced. This algorithm avoids the quadratic cost of nested-loop evaluation methods; it also avoids sorting. Performance comparisons between the partition-based algorithm and other evaluation methods are provided. While the paper focuses on the important valid-time natural join, the techniques presented are also applicable to other valid-time joins.<<ETX>>

Extending existing dependency theory to temporal databases

Normal forms play a central role in the design of relational databases. Several normal forms for temporal relational databases have been proposed. These definitions are particular to specific temporal data models, which are numerous and incompatible. The paper attempts to rectify this situation. We define a consistent framework of temporal equivalents of the important conventional database design concepts: functional dependencies, primary keys, and third and Boyce-Codd normal forms. This framework is enabled by making a clear distinction between the logical concept of a temporal relation and its physical representation. As a result, the role played by temporal normal forms during temporal database design closely parallels that of normal forms during conventional database design. These new normal forms apply equally well to all temporal data models that have timeslice operators, including those employing tuple timestamping, backlogs, and attribute value timestamping. As a basis for our research, we conduct a thorough examination of existing proposals for temporal dependencies, keys, and normal forms. To demonstrate the generality of our approach, we outline how normal forms and dependency theory can also be applied to spatial and spatiotemporal databases.

The TSQL2 Data Model

Adding time to the relational model has been a daunting task [Bolour et al. 1982, McKenzie 1986, Soo 1991, Stam & Snodgrass 1988]. More than two dozen time-extended relational data models have been proposed over the last fifteen years [Snodgrass 1992]. Most of these are valid-time models. Each fact in a valid-time relation has associated the time when it is true in the modeled reality. Other models support transaction-time relations where each fact has associated the time when it is current in the database. A few support both valid and transaction time [Ben-Zvi 1982, BhargavaG such models are termed bitemporal. As a whole, these data models are referred to as temporal data models [Jensen et al. 1994].

An Algebra for TSQL2

TSQL2 is a declarative query language, and as such, requires a procedural (algebraic) equivalent for implementation. In this chapter, we describe such an algebraic language. We undertook this design in order to show that TSQL2 can be implemented efficiently, with minimal extension of existing techniques.

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.

Unification of temporal data models

A conceptual temporal data model that captures the time-dependent semantics of data while permitting multiple data models at the representation level is described. A conceptual notion of a bitemporal relation in which tuples are stamped with sets of two-dimensional chronons in transaction-time/valid-time space is defined. A tuple-timestamped first normal form representation is introduced to show how the conceptual bitemporal data model is related, by means of snapshot equivalence, with representational models. Querying within the two-level framework is discussed. An algebra is defined at the conceptual level and mapped to the sample representational model in such a way that new operators compute equivalent results for different representations of the same conceptual bitemporal relation.<<ETX>>

The Data Model for Time

TSQL2 adds comprehensive support for time to SQL-92. This support rests on three temporal dimensions: user-defined time, valid time, and transaction time. A single model of time, however, is the foundation for each of these separate dimensions. In this chapter, we present TSQL2’s model of time. We focus on the concepts of an instant and a period, discuss how each is modeled in TSQL2, and give an overview of the semantics of operations on the modeled entities.

SQL-92 Compatibility Issues

This chapter will consider compatibility of TSQL2 and SQL-92. Some of the changes in TSQL2 provide more functionality, while others provide space savings, reducing the amount of storage required for time, tamps Through the use of a predefined, default calendar, SQL92, full upward compatibility with SQL-92 is ensured.