Hugh Darwen

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.

A normal form for preventing redundant tuples in relational databases

We introduce a new normal form, called essential tuple normal form (ETNF), for relations in a relational database where the constraints are given by functional dependencies and join dependencies. ETNF lies strictly between fourth normal form and fifth normal form (5NF, also known as projection-join normal form). We show that ETNF, although strictly weaker than 5NF, is exactly as effective as 5NF in eliminating redundancy of tuples. Our definition of ETNF is semantic, in that it is defined in terms of tuple redundancy. We give a syntactic characterization of ETNF, which says that a relation schema is in ETNF if and only if it is in Boyce-Codd normal form and some component of every explicitly declared join dependency of the schema is a superkey.

Database Design I: Structure

This chapter is the first of three devoted to the topic of logical database design in the temporal context. Database design in that context has the potential to be a much more complicated matter than its analog in the conventional (nontemporal) context. There are several reasons for this state of affairs, including (a) the need to deal with the fact that different “properties” of the same “entity” tend to vary at different rates and (b) the need to deal with the concept of “until further notice”—i.e., the need to be able to record the fact that a given “property” of a given “entity” has a given value right now and will continue to have that same value until some unknown time in the future. The chapter proposes some new design techniques (in particular, a new normal form) for dealing with such matters.

Time and the Database

This chapter discusses time and the database. A temporal database is a database that contains historical data instead of or in addition to current data. Such databases have been under active investigation since the early 1980s. Some of those investigations have taken the extreme position that data in such a database, once inserted, should never be deleted or changed in any way, in which case the database can be thought of as containing historical data only. Conventional databases, by contrast, are typically at the other extreme; such a database typically contains current data only, and data in such a database is changed or deleted as soon as the propositions represented by that data cease to be ones that evaluate to true. The propositions in nontemporal database are generally taken to be true “now,” that is, at the time the database is inspected. Temporal database research has therefore involved a certain amount of investigation into the nature of time itself. This chapter explores some questions that regarding whether or not time has a beginning or an end; if time is a continuum or is it divided into discrete quanta; and the best way to characterize the important concept “now.”

Chapter 3 – Time and the Database

Publisher Summary This chapter discusses time and the database. A temporal database is a database that contains historical data instead of or in addition to current data. Such databases have been under active investigation since the early 1980s. Some of those investigations have taken the extreme position that data in such a database, once inserted, should never be deleted or changed in any way, in which case the database can be thought of as containing historical data only. Conventional databases, by contrast, are typically at the other extreme; such a database typically contains current data only, and data in such a database is changed or deleted as soon as the propositions represented by that data cease to be ones that evaluate to true. The propositions in nontemporal database are generally taken to be true “now,” that is, at the time the database is inspected. Temporal database research has therefore involved a certain amount of investigation into the nature of time itself. This chapter explores some questions that regarding whether or not time has a beginning or an end; if time is a continuum or is it divided into discrete quanta; and the best way to characterize the important concept “now.”

Chapter 5 – Intervals

Publisher Summary This chapter discusses intervals. Intervals are the fundamental abstraction needed for dealing with temporal data satisfactorily. The first and most fundamental step is to recognize the need to deal with intervals as such that is, the need to treat intervals as values in their own right, instead of treating them as pairs of separate values. Conventionally, therefore, an interval is denoted by a pair of points separated by a colon, preceded by an opening bracket or parenthesis and followed by a closing bracket or parenthesis. A bracket is used where one wants the closed interpretation, a parenthesis where one wants the open one. The applications for intervals are varied. Tax brackets are represented by taxable-income ranges—in other words, intervals whose begin and end points are money values. Machines are built to operate within certain temperature and voltage ranges—in other words, intervals whose contained points are temperatures and voltages, respectively. Animals vary in the range of frequencies of light and sound waves to which their eyes and ears are receptive. Various natural phenomena occur and can be measured in ranges in depth of soil or sea or height above sea level.

Chapter 11 – Integrity Constraints I: Candidate Keys and Related Constraints

Publisher Summary This chapter discusses addresses the question of the integrity constraints that might apply to temporal data. It discusses integrity constraints candidate keys and related constraints, which are further segmented into the redundancy problem, the circumlocution problem, the contradiction problem, combining specifications, PACKED ON without WHEN/THEN, WHEN/THEN without PACKED ON, neither PACKED ON nor WHEN/THEN, candidate keys revisited, and PACKED ON revisited. The redundancy problem is addressed by considering the revlar S_STATUS_DURING specifically. The limitations of using this revlar are then discussed and the method of addressing the circumlocution problem is illustrated. The methods of fixing the redundancy problem and circumlocution problem are also discussed. The contradiction problem is also addressed using the revlar definition of S_STATUS_DURING specifically and the method to fix the contradiction problem is described.

Die SQL-Norm (The SQL Standard)

Zusammenfassung In diesem Artikel geben die Autoren einen Überblick über die neuesten Funktionen der nächsten Version der SQL-Norm (SQL:2003) sowie eine kurze Zusammenfassung der vorhandenen Funktionalität in der aktuell gültigen Norm (SQL:1999) und den früheren Versionen. Zusätzlich wird auf ausgewählte Teile des Standards ausführlicher eingegangen. Abschließend wird kurz auf die Zukunft der SQL-Norm aus Sicht der Autoren eingegangen.

Logged Time and Stated Time

This chapter takes a much closer look at two concepts originally introduced in Chapter 4, viz., “valid time” and “transaction time.” Valid time has to do with when we believe (explicitly or implicitly) that some proposition is, was, or will be true. Transaction time has to do with when the database said (explicitly or implicitly) that we believe (again, explicitly or implicitly) that some proposition is, was, or will be true. Valid times are maintained—at least in a relational system—just like any other kind of data; in particular, they’re updatable just like any other kind of data. By contrast, transaction times are maintained by the system; in particular, therefore, they’re read-only (at least from the user’s point of view). The chapter proposes a model for querying transaction times. It also proposes some alternative technology: stated time for valid time, and logged time for transaction time.

Database Design III : General Constraints

This chapter proposes a general framework for dealing with temporal database design questions. To be specific, it describes a set of requirements that apply to the design of the running example and (it’s claimed) can be used as a template for pinning down the requirements that apply to any temporal database. The chapter then shows how those requirements can be expressed in terms of formal integrity constraints for (a) a database consisting of current relvars only, (b) a database consisting of historical relvars only, and (c) a database consisting of a mixture of current and historical relvars. The chapter also offers some hope for automating the definitions of such constraints.