Note: A glossary of technical terms used in these articles can be found on the Web sites of the authors. The URLs are MindfulData.com and InbaseInc.com.

 

This glossary is being constructed as a controlled vocabulary. By that we mean that any expression involving temporal concepts which is used in the definition of a glossary entry must itself be a glossary entry.

 

One reason for being this careful with definitions is that this kind of rigor must be applied to definitions before they can be "ontologized," i.e., expressed in a formal notation like first-order logic. That, in turn, is what is needed to make definitions available for manipulation by software-realized inferencing engines.

 

Another reason for being this careful with definitions is that this is the kind of rigor that must be applied to any set of technical definitions before we can claim not only that we say what we mean, but also that we know what we mean when we say it.

 

As this series continues, context becomes increasingly important so that the thread of the discussion is not lost. Please do a search for "Time and Time Again" on www.dmreview.com for a list of previous articles in this series.

 

We will proceed now to a discussion of original and temporal inserts, and will explain how the temporal correlates of entity integrity and referential integrity apply to them.

 

Inserting a Versioned Object: the Original Insert Transaction

 

When businesspeople issue an insert of an object, they don't care if the object is versioned or not, and indeed may not know. They are directing us to insert a row representing an object that is not already represented by a row in the target table. Thus, an original insert transaction doesn't need to specify a version, just the object being inserted, its business key, whether or not match logic should be applied and the date the insert is to take effect. This is illustrated in Figure 1.

 

 

Inserting a Versioned Object: Temporal Entity Integrity Constraints

 

When a conventional table is the target of an insert, the transaction is invalid if a row representing that object already exists in the table. This reflects the tacitly understood semantics of the transaction, which is that its creator is claiming that he knows that no such row already exists in the table. Note that a row for that object may have existed in the table at some point in the past. But if it was deleted prior to the insert, it has no effect on the insert, and the insert may proceed. The constraint which enforces these semantics against relational tables is known as the entity integrity constraint.

 

What are the corresponding semantics when the target table is a versioned table? To the user, of course, i.e., to the author of the transaction, there is no difference. She neither knows nor cares whether the target table is versioned or conventional. But consider the situation just mentioned, in which a row for the object being inserted did exist in the table at some point in the past but was deleted prior to the insert. In a conventional table, the delete means that, at the time of insert, no such row exists in the table. But in a versioned table, a versioned delete of the object means two things. It means that at the time of insert:

 

  • No current episode of that object exists in the table. This means that no episode exists with the same business key, and with effectivity begin and end dates that enclose {now}. This includes the situation in which the effectivity end date is 12/31/9999.
  • No future episode of that object exists that with the passage of time, could become a current episode.

This does not mean that there are no episodes of that object in the target versioned table. It means, again, that there are no episodes which are current at the time of the insert, or which could later become current. So suppose that there are one or more episodes of that object on the table, at the time of insert, but that all of them are past episodes.

 

Past episodes in a versioned table correspond to deleted objects in a conventional table. If an object was inserted and deleted three times in a conventional table, the corresponding representation of that object in a versioned table is three past episodes. If an object was inserted three times and deleted twice in a conventional table, the corresponding representation of that object in a versioned table is two past episodes and one current episode.

 

So whether or not object matching is requested on the original transaction, temporal entity integrity must be enforced. And we can now define this constraint.

 

 

Entity integrity, as it applies to insert transactions, guarantees that on conclusion of the transaction, there are no duplicate rows in the target table. Temporal entity integrity (TEI), as it applies to insert transactions, guarantees that on conclusion of the transaction, there neither are nor ever will be, as a result of the transaction, two versions of the same object with overlapping effectivity time ranges.

 

Temporal Entity Integrity and 12/31/9999

 

Figure 3 shows a policy on a versioned table. Its effective begin date is 2/1/04, and its effective end date is 10/1/05. Any group of one or more versions which are all contiguous - which all have no clock tick between themselves and the versions adjacent to them - constitute an episode of the version. In this example, we have an episode with three versions.

 

 

The graphical conventions used in this and the following two illustrations are these:

 

  1. The vertical bars constitute a timeline, with each bar representing one month. The timeline runs from Jan. 1, 2004 through Dec. 31, 2009.
  2. A version is represented by a rectangle shown above the timeline.
  3. Version rectangles may be open or closed. If closed, they have four sides. If open, the right-most side is absent. Closed rectangles represent versions with known effective end dates. Open rectangles represent versions with effective end dates that are not known and that, therefore, contain the value 12/31/9999. Graphically, open episodes also show an ellipsis extending out from the rectangle.
  4. Adjacent versions share a vertical bar. Adjacent versions have no clock tick between them.
  5. {Now}, relative to the example, is indicated by the black box arrow, pointing to June 1, 2008. So, for purposes of discussing the example, we should assume that it is now June 1, 2008.

If {now}, as indicated, is June 1, 2008, and we receive an insert transaction for policy P138, do entity integrity constraints permit that transaction to be applied or not? The answer is that those constraints do permit the transaction to be applied. But we should be able to understand why that is so.

 

An episode of an object which begins on date X and ends on date Y is the representation of that object on a versioned table between those dates. If the object were represented instead on a nontemporal table what would that look like? Clearly, it would be represented by a row which was inserted on date X and deleted on date Y. So if date X and date Y are, respectively, 2/1/04 and 10/1/05, and it is now 6/1/08, there would be no row for P138 on the non-temporal table and, consequently, an insert of a row for P138 would, now, be valid.

 

By the same token, in the versioned table shown in Figure 3, an insert for P138 would also be valid because right now, no row for P138 is effective. Thus, an insert would not, in this case, violate the TEI constraint stated in Figure 2.

 

But suppose this close-ended episode did not end until 5/1/09. In this case, the TEI constraint would prevent the insert from taking place because, if it did, that would create a situation in which two versions' effectivity time periods overlapped for 10 months. So for those 10 months, we would have two different versions of the truth about P138. But we can't. Truth isn't like that.

 

Now let's look at an open-ended episode, one with an end date value of 12/31/9999 and whose semantics, let us remind ourselves, is that of "end date unknown, but presumed beyond the current date until further notice."

 

 

As stated previously, "(t)emporal entity integrity (TEI), as it applies to insert transactions, guarantees that on conclusion of the transaction, there neither are nor ever will be, as a result of the transaction, two versions of the same object with overlapping effectivity time ranges." We are now in a position to examine the reason behind the phrase "nor ever will be."

 

Suppose that we did proceed with an insert of P138. Although we haven't discussed future-dated transactions yet, it's necessary to jump ahead and use one as our example, in order to clarify this point about TEI. In this case, it doesn't matter whether the insert creates a close-ended or open-ended episode, so let's make it close-ended. It is now June 1, 2008. Our insert transaction has effective dates 1/9/09 through 11/1/09. Figure 5 shows the result.

 

 

As we will see later on, starting a new episode with a future effective begin date is almost as straightforward as starting a new episode with {now} as the effective begin date. But if there already is an episode for that object, at the time of the insertion, and if that episode is open, then if we simply inserted the version which the transaction directs us to do, we would create a potential problem. The problem is that when that future version becomes current, the latest version of the open episode begins to conflict with that future version. In Figure 5, as the black arrow which indicates {now} moves to the right, it ends up under the second P138 episode six months from now. If nothing else has happened to P138 during that time, the first episode then begins to conflict with the second one.

 

One solution would be to turn the insert into an update, which would then create a new most current version for the current episode and extend the previous most current version up to the time the new one begins, so there is no clock tick between them. But while technically possible, and while this would preserve TEI, it is inadvisable because it is misleading.

 

When an insert is submitted to a nontemporal table, the author of the transaction is asserting that no row currently exists for that object on that table. The same assertion is made when the target is a versioned table. But the way the non-current-presence of an object is manifested, on a versioned table, is by there being no row for that object such that {now} falls between its effective begin and end dates. In our example above, because the database management system treats "12/31/9999" as just another date, then provided the begin date of that open-ended episode is in the past, that episode is current at the time of the insert and the insert is thus invalid. There is also the case where we are trying to do an insert although an episode exists with a begin date in the future. The TEI constraint here may or may not be obvious; but in either case, we won't go into further detail about how the future affects transactions until several articles from now.1

 

These considerations lead us to a corollary of TEI, shown in Figure 6.

 

 

The reason for this corollary, to emphasize the point once again, is that if a versioned table did contain both an open episode (one whose most recent version has a 12/31/9999 effective end date) and a later version whose effective begin date is in the future, then the mere passage of time could create a TEI conflict.

 

Inserting a Versioned Object: Temporal RI Constraints

 

As we said in Part 19, "temporal RI is object RI plus the constraint that the time period of a child row must always be wholly contained within the time period of its parent row." No matter what kind of temporal transaction we are considering - inserts, updates, deletes or upserts - all the rest is just details. Just as conventional RI guarantees that a referenced object exists, temporal RI guarantees that a referenced object exists throughout the effectivity time period of the referring object.

 

But what of those details? First of all, if the target table for the insert is a conventional table, then temporal RI collapses into conventional RI. The rule, in that case, is this: every non-nullable foreign key in the row being inserted must be a valid reference, i.e., its value must exist as a primary key value in the referenced table. Now if the target table is a versioned table, then the corresponding temporal RI rule for insert transactions is shown in Figure 7.

 

 

Consider a policy that is being inserted effective from 1/1/2007 through 12/31/2007. In the examples we have been using throughout this series, this policy has an OFK reference to the client that has taken out the policy. This means that, at the time of creating a new episode of the policy, or of extending the current episode with a new version, the OFK-referenced client that owns that policy must exist in the Client Version table, and also there must be an episode of that client whose effectivity begin and end dates wholly contain the dates 1/1/2007 through 12/31/2007, with the understanding that an effectivity end date of 12/31/9999 functions as a real date as far as the database management system is concerned, even though its semantics is that of "in effect until further notice."

 

Upsert Transactions

 

But what if the author doesn't know (or care) whether or not such a row already exists? In that case, he creates an "upsert" transaction, whose semantics is this: if a matching row already exists in the target table, replace it. Otherwise, treat the transaction as an insert. Semantically, of course, the result is the same - the data in the transaction becomes the data on the target table.

 

We have now examined insert, update, delete and upsert transactions against versioned tables. These are not yet the "bi-temporal versioned tables" which we have called the "ultimate history pattern," for the only kind of time that is used to distinguish rows for the same object from one another is effectivity time.

 

We have also discussed TEI and temporal RI. TEI prevents two rows for the same object having overlapping effective time periods. Temporal RI prevents a child row from being effective before or after the continuous time range of an episode of the parent object.

 

In our discussions, we have frequently compared transactions whose targets are versioned tables with the same transactions whose targets are or would be nontemporal tables. And, of course, we have also emphasized the parallels between entity integrity for nontemporal tables and what we call "temporal entity integrity," and between referential integrity for non-temporal tables and what we call "temporal referential integrity."

 

At first, these parallels seemed to us to be merely analogies, a way to make a point about something unfamiliar by comparing it to something familiar with which it shares some intuitively appealing similarities. But we have come to see these parallels as more than "analogies" based on "similarities." For a versioned table should have exactly the same semantics as its corresponding non-temporal table, except that past (and future) states of the objects represented in these tables are immediately available, queryable states.

 

Because of the importance of these parallels, we will present in our next installment a side-by-side series of inserts, updates and deletes against non-temporal tables and against corresponding versioned tables. The core importance of these parallels is that, instead of being similarities, they are actually constraints on the semantics expressed by versioned tables. Another way that these parallels are important is that they constitute a "mental model" of versioned history, a model that will also apply to bi-temporal versions. We think this mental model will prove to be quite helpful in understanding any of the work that has been done on temporal data management, our own work as well as that of Dr. Snodgrass, and Date, Darwen and Lorentzos.

 

References:

 

  1. C. J. Date, Hugh Darwen and Nikos Lorentzos. Temporal Data and the Relational Model. Morgan-Kaufmann: San Francisco, 2002.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access