REVIEW - Developing Time-oriented Database Applications in SQL


Developing Time-oriented Database Applications in SQL


Richard T. Snodgrass



Morgan Kaufmann (2000)




Ian Bolland


August 2001



The author does an exceptionally good job of explaining the problems, describing the possible solutions and explaining the trade-offs between different solutions.

One of the major database vendors has a SQL tutorial which uses, for its examples, a system that maintains information on employees and departments. Students learn how to use INSERT statements to add employees to the system, UPDATE statements to change employee details (such as the department to which they belong) and DELETE statements to remove them from the system. They learn to use SELECT statements to retrieve information about specific employees or about all employees who satisfy particular criteria (such as all employees in a particular department). Students go away with the feeling that SQL is not very complicated.

Suppose we take this example and add a few reasonable requirements to the system. Suppose that employees incur costs on particular dates and that we wish to produce periodic reports that allocate these costs to their departments. If we want these reports to be accurate we must maintain a history of the departments to which the employee has belonged and the dates on which he moved between departments. This means, for example, that moving an employee from one department to another requires an UPDATE followed by an INSERT and that the SELECT statement required to allocate costs to departments becomes quite complex. If departments themselves can be created and removed, the SQL becomes more complex still.

This book explains the problems encountered when trying to work with temporal data using SQL (and the description above has only scratched the surface of the complexity). It uses real-world examples to motivate its descriptions of the problems and explains how these problems can be solved in ideal SQL-92 and in half a dozen of the major database systems. The author does an exceptionally good job of explaining the problems, describing the possible solutions and explaining the trade-offs between different solutions.

The author is involved in the effort to add support for temporal data to the emerging SQL3 standard and part of the motivation for this book is to show how much simpler the solutions would be if SQL provided adequate support for temporal data. He takes eleven chapters and almost four hundred pages to solve his problems using SQL2 and in chapter twelve he presents alternative solutions using his proposed SQL3 extensions. The advantages of providing explicit SQL support for temporal data can be judged from the fact that the solutions in this chapter are often ten times smaller and also much simpler than those in the earlier chapters.

While I hope that support for temporal data will eventually be implemented in commercial database products, realistically I do not expect this to happen any time soon and I would be pleasantly surprised if all database vendors supported the same extensions. This means that anyone who needs to work with temporal data will have to use SQL-92 for the foreseeable future. I would therefore recommend anyone in this position to buy this book, read it from cover to cover and refer back to it whenever they design their SQL.

Book cover image courtesy of Open Library.

Your Privacy

By clicking "Accept Non-Essential Cookies" you agree ACCU can store non-essential cookies on your device and disclose information in accordance with our Privacy Policy and Cookie Policy.

Current Setting: Non-Essential Cookies REJECTED

By clicking "Include Third Party Content" you agree ACCU can forward your IP address to third-party sites (such as YouTube) to enhance the information presented on this site, and that third-party sites may store cookies on your device.

Current Setting: Third Party Content EXCLUDED

Settings can be changed at any time from the Cookie Policy page.