Preface

Time present and time past

Are both perhaps present in time future

And time future contained in time past.

T. S. Eliot, The Four Quartets: Burnt Norton

In this fragment from Burnt Norton, Eliot describes a Buddhist conception of time, one which encourages us to think of past time, present time and future time as interwoven with one another. This Buddhist concept is a useful counter-balance to our mechanistic notion of time as a linear sequence of moments which occur one after the other, and which constitute a series which can be traversed in one direction only.

Anything at all – you, or me, or any of the changeable objects around us – is at the present moment the latest stage in the history of what we are. With a different history, we would, at this present moment, be other than what we are now. In this sense, William Faulkner was correct when he wrote (in Requiem for a Nun), “The past is never dead. It’s not even past.”

It is perhaps with human beings, and the short-term and long-term projects and plans that inform their lives, that it is most obviously true that time present and time past are present in time future. Somewhere, a store manager is reviewing a history of product price changes and their effect on sales. She isn’t doing this out of simple curiosity. She is doing it because she wants to maximize future profits for her store. Somewhere, an author is working on the Great American Novel. He isn’t doing it just to pass the time. He imagines a future in which he has accomplished the great work of his life, in which accolades are heaped on him, and in which royalty checks are more than pittances. If and when either of those futures is achieved, it will be because of a history of present moments, each the culmination of a sequence of past moments during which those people worked towards those future goals.

So the intimate relationships of past, present and future manifest themselves in the changes that take place in the world. But they also manifest themselves in the changes that take place in what we say about the world.

This brings us to the subject of this book: temporal data and, in particular, bitemporal data. Bitemporal data is data that is associated with two kinds of time. One of these is the time in which things happen in the world; the other is the time in which descriptions of the world accumulate. The first kind of time is about when things were, are, or will be as the data which describes those things says they were, are, or will be. The second kind of time is about that data itself. It is about when we once thought, or still think, or may eventually come to think, that that data correctly describes what things were, are, or will be like; or at least when we once thought or still think that that data constitutes the best descriptions currently available to us.

This book is about bitemporal data that is persisted in relational databases, and about the information which that data provides. However, the extension to non-relational ways of persisting data is straightforward. I talk about data in relational databases, first of all, because that is the prevalent way of storing character set data, and because character set data is still the prevalent kind of data that describes the things an enterprise engages with, and the processes in which it engages with them.

I talk about data in relational databases, secondly, because the language of relational data and relational databases is a lingua franca among data management professionals. For example, we all know what tables, rows and columns are, and we all know what entity integrity and referential integrity are. Or, at least, we all should know these things.

But I also talk about data in relational databases, thirdly and most importantly, because relational theory is the richest and most mathematically informed of theories of data management. It is thus best suited to incorporate extensions needed to manage bitemporal data while itself remaining stable and well-grounded.

Relational theory also has both an ontology and a semantics, although neither are much discussed. To the best of my knowledge, little has been written about how the ontology and the semantics of the Relational Paradigm (as I will call the use of relational theory in data management) give meaning to the mathematical structures of sets, Cartesian Products and relations, and to their concrete manifestations as tables, columns and rows.

But in this book, I would like to say something about the ontology and the semantics of the Relational Paradigm – a set of concepts based on the relational theory invented by Dr. E. F. Codd, and on the implementation of that theory in the world’s major Database Management Systems (DBMSs). In fact, I don’t think that the Relational Paradigm can be correctly extended to accommodate bitemporal data unless these perspectives are understood and taken into consideration.

Perspectives on the Relational Paradigm of Data

One of the distinctive features of this book is that it discusses relational concepts, and their extension into the realm of bitemporal data, from several perspectives. In these discussions, I try to avoid explanations which mix these perspectives because I think that when that happens, explanations become pseudo-explanations which in fact explain nothing at all. In these discussions, I will occasionally point out examples of perspectival confusion so the reader may be better prepared to recognize it when she encounters it in her own working environment.

One perspectival distinction is the distinction between syntax and semantics. This distinction will become clearer through repeated use, but this much can be said at the outset. The syntax of the Relational Paradigm describes relational data structures, instances of those structures, and transformations made to those instances. It’s about the things that DBAs and programmers construct and manipulate. A Customer table is a data structure, for example, and one row in that table is an instance of that structure. An update to a row in that table is a transformation made to that instance. Syntax describes structures and their instances, and transformations on those instances. Those transformations add instances to a database, change instances in a database, and remove instances from a database. The instances have the structure described by their syntax. The transformations add and remove syntactically valid instances, and change valid instances into other valid instances.

The semantics of the Relational Paradigm is about the information expressed in those data structures and in their instances. Data is created and modified so that it accurately conveys information. If customer Smith changes her name to “Jones”, then we change her name on her row in the Customer table to reflect that change.

The important point here is that what we do to data, we do in order to preserve its value as an embodiment of information. That is all too obvious, of course. But once we get deep into the syntax of data and its management, it is easy to lose sight of this important fact. Information is the master; data is the servant.

Here is a brief example. Relational entity integrity is often explained as the rule that no primary key in a table can be null, and that each primary key must be unique. That is a rule of syntax that a relational DBMS enforces.

Is the semantics of entity integrity left undescribed because it is too obvious to be worth mentioning? Well, consider the fact that the semantics of entity integrity is that a database may never contain contradictory statements. Is this so widely recognized and so obvious as to not be worth mentioning? I don’t think so.

A consideration of contradictory statements is an entry into the realm of propositional logic and predicate logic. I discuss these perspectives on the Relational Paradigm in this book because we data management professionals should have some understanding of that logic, of how it is expressed in the Relational Paradigm, and of how it is used to manage data in relational databases.

We are all willing to do the hand-waving which acknowledges that relational theory is based on mathematics and logic. But if we can catch on to the trick of seeing mathematics and logic embedded in the data structures and transformations that we manage, then we will build better databases and better applications. In particular, we will be more likely to provide generalized solutions to specific problems. These solutions are always more stable in the face of changing requirements than point solutions to specific problems are. They are easier to code and to maintain because they express simpler and clearer patterns than do idiosyncratic implementations of solutions to narrowly conceptualized problems. They are always better solutions.

The Temporal SQL Standards: ISO 9075:2011 and TSQL2

In late 2011, the ISO published the latest release of its SQL standard, ISO 9075:2011. This was the first ISO release to include support for bitemporal data. Prior to that, in 1994, a group of computer scientists published the TSQL2 proposed standard for the management of bitemporal data, but this proposal was never accepted by the ISO. Nonetheless, I will refer to it as a standard because it is a draft standard which represented, at the time, a consensus among a significant part of the computer science community.

A current implementation of the ISO SQL standard can be found in IBM’s DB2 10 DBMS and its successive releases, and a current implementation of the TSQL2 standard can be found in the Teradata 13 DBMS and its...