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.
We are about to split an episode. The user knows nothing of this - all the user knows is what the database tells us right now and what changes he/she wants to make to the database.
With his/her delete transaction, as shown in Figure 1, the user is telling us, first of all, that she believes that the database currently asserts that P861 was in effect from 4/01/08 to 8/01/08. If that turns out not to be true, then the asserted versioning framework will reject his/her transaction. Next, the user is also telling us that he/she believes that P861 was not in effect during that time. The user now believes, in other words, that the correction made a month ago was a mistake. So he/she is submitting a transaction to return the database to its pre-3/01/09 assertion about P861, namely that it was in effect from January to April of last year, and from August of last year going forward, but it was not in effect from April to August.
Figure 1 shows us the Policy table after the delete transaction has been applied.
Figure 1: Splitting Episodes: After the Split
Applying the Deletion
As with any original delete transaction, the first thing that must be done is to withdraw the affected versions of the designated object from current assertion time. In this case, there is only one such assertion. It is the one made with row 3. Last month, row 3 began asserting that policy P861 was in effect from January to August of last year. We now believe (once again) that the assertion made by row 3 is incorrect, and so we withdraw it. We do this by overwriting the assertion end date of 9999 in row 3 with the current date, April 1, 2009.
The second temporal transaction creates the replacement assertion. That replacement is row 5; and starting in April of this year, it claims that policy P861 was in effect from January to April of last year not from January to August.
After both the physical update and the physical insert are completed, the original transaction is complete. Locks are released on the affected data and its isolation ends. It is now a visible part of the database.
Viewing Asserted Version Table
Figures 2 and 3 show us two view tables a non-temporal and a uni-temporal one, respectively that are based on our asserted version Policy table. Like a corresponding conventional Policy table, what we see in these view tables depends on when we look at them.
By means of the dynamic views which these two SQL statements provide, we can always see what our policies look like at the current moment. We could do this with the view statement shown in Figure 2 by substituting "CURRENT-DATE" for "6/01/08." If we then direct all other conventional queries against that view, they can remain completely unchanged, and their result sets will be identical to the same queries executed against the corresponding conventional table at identical points in time.
When the SQL specifies a particular date, the dynamic view is a view of that data as of that point in time. As Figure 2 shows, from January 2008 forward, with the exception of March of this year, our Policy table asserted that there was no policy P861 in effect (or about to go into effect) on June 1, 2008. But during March of this year, our Policy table asserted that there was a policy P861 in effect on March 1, 2008.
Note that with all three views, what changed was not the policy. What changed was what we believed about the policy. In the view statement, the only variable is CURRENT-DATE, and it is being compared to the assertion time period of P861 rows. A row's assertion time period is the period of time during which we believe that the row makes a true statement.
Figure 2: Current Conventional Table Views of the Asserted Version Policy Table
Figure 3 shows us another view based on the same asserted version table, but one which materializes a versioned view table (i.e., a uni-temporal table).
Figure 3: Current Uni-temporal Views of the Asserted Version Policy Table
Note that with all three of these views, we are seeing policy P861 during the period of time that starts on January 1, 2008 and continues up to the present moment. This is a period in effective time, the time in which the object exists. And again, what changes from view 1 to view 2, and then from view 2 to view 3, is not the policy. It is what we believe the policy to be like. As time went by in the real world, the policy was what it was. It was in effect when it was in effect, and it was not in effect at all other times. But as that same real-world time went by, we changed our beliefs and, therefore, our assertions about what the policy was like over that stretch of real-world time.
That is one kind of history that asserted versioning makes immediately available, a history of our changing claims about what the world is like. But it also makes other kinds of history available. For out there in the real world, during this sixteen month and more stretch of time, our policy did change. All three point-in-time views agree on that. They all agree that starting on August 1, 2008, P861's co-pay amount increased by five dollars. Views 1 and 3 show us the assertion that the policy was in effect continuous from January 1, to August 1, 2008; view 2 shows us the assertion that there was a three-month lapse just prior to August 1.
Seamless Real-time Access to Bi-Temporal Data
Our primary point, both here and throughout this series, is that all this information is immediately available when it is kept in asserted version tables. No data has to be restored. Queries don't have to be redirected to different databases or different tables. IT operations, developers or database administrators do not have to be called upon for an interested user to get to this data.
Furthermore, all this is only the tip of the iceberg of the information that resides in the repositories we have been calling asserted version tables. Such tables can also contain data that will not go into effect until some time in the future. And then, as time goes by and that future point in time is reached, that data automatically becomes current data.
And there is one last thing. For all the information we have just described now could be obtained from a standard bi-temporal table, not exclusively from an asserted version table. The additional functionality provided by asserted versioning, as we have said, is that it supports deferred transactions and deferred assertions. This is a topic we will discuss only after our presentation of conventional bi-temporal functionality is complete.
Figure 4 shows where we are in our asserted versioning transformation taxonomy. Having completed our discussion of a transformation, which splits an episode into two episodes, the only transformations left to discuss are those which truncate an episode. We move on to that topic next time.
Figure 4: Where We Are
After completing this taxonomy, however, there are additional topics to cover. Among them are the following:
- Original transactions that do not alter the effective time boundaries of episodes in any way, but that do alter effective-time boundaries of versions within those episodes.
- Original transactions that involve two or more tables and that must satisfy temporal referential integrity constraints.
- Temporal referential integrity constraints that involve asserted version associative tables.
- How deferred transactions eliminate external batch transaction files and thus the cost of their maintenance.
- How deferred assertions eliminate external staging areas and thus the cost of their maintenance.
- Advanced queries that are supported by asserted version tables, including queries that implement what we call left and right temporal outer joins.
- Consideration of queries presented in the works by Richard Snodgrass, C.J. Date, Hugh Darwen and Nikos Lorentzos in their original forms and as they would be expressed against asserted version tables.
Note: the authors are writing a book on temporal data management, based in large part on the material in this series. It will be published by Morgan-Kaufmann and will be available in the second quarter of 2010.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access