The value of reviewing all the Allen relationships in terms of queries against asserted version tables is that, as we already know, the Allen relationships are exhaustive. There are no positional relationships along a common timeline, among time periods and/or points in time, other than those ones. Thus, by showing how to write a query for each one of them, as well as for the groups of them identified in our taxonomy, we will have provided the basic material out of which any query against any assertion version table may be expressed.

In addition to the thirteen Allen relationships themselves, our taxonomy provides five additional relationships, each of which is a logical combination of two or more Allen relationships. And these combinations are not formed simply by stringing together Allen relationships with OR predicates. Although they are, necessarily, logically equivalent to the OR'd set of those relationships, they are often much simpler expressions, easier to understand and faster when executed.

In these sample queries, we will not include predicates for assertion time, and will pretend that our sample tables are uni-temporal versioned tables. This eliminates unnecessary detail from these examples. We will do this by using two version table views, shown below: V_Wellness_Program_Curr_Asr and V_Policy_Curr_Asr. The former is a view of all currently asserted Wellness Program versions. The latter is a view of all currently asserted Policy versions.

In these example queries, as we said before, we will be selecting episodes, not versions. For the two tables used in this chapter, these are the views which provide episodes as queryable managed objects:

These episode views are the query-side work of defining an episode datatype. The AVF presents episodes as maintainable managed objects. These views present episodes as queryable managed objects.

This is a very important point. Both computer science research and IT practice have shown the importance of the concept of a string of one or more contiguous clock ticks with a known location in time. SQL does not directly support this concept; and so instead, we, and others, have to write code to exclude gaps and overlaps occurring in the timespan between a pair of dates or timestamps. A PERIOD datatype is the direct support needed for this concept. This datatype implements this concept at the correct level of abstraction.

By the same token, our own research and practice has shown the importance of the concept of an episode, a string of one or more contiguous and non-overlapping versions of the same object. Without that concept, and the concepts of objects and versions on which it depends, there is also no concept of temporal entity integrity and temporal referential integrity. Without that concept, collections of rows are defined, as needed, within each SQL statement. As we can see with both the standard and alternative temporal models, their SQL insert, update and delete statements do result in bi-temporal data that satisfies what we call TEI and TRI. Their SQL queries do find episodes, when they need them, past assertions when they need them, and so on. But the level of abstraction is wrong, for the same reason that getting the same results with a pair of dates that one would get with a PERIOD datatype is wrong.

So we now have two views which externalize, as queryable managed objects, the best data we currently have (i.e. our currently asserted data) about policy episodes and wellness program episodes. Now, using these two views, we will define another view that we will use to illustrate each of the Allen relationships. This is the view V_Allen_Example. This view will keep the examples as small and easy to understand as possible, eliminating all extraneous and repetitive detail while focusing on the Allen relationships themselves. Here is the V_Allen_Example view:

As we said previously, these queries are not intended to represent realistic scenarios. Their sole purpose is to demonstrate that all Allen relationships can be expressed as queries against asserted version tables. Note in particular that the two tables in this view are not correlated by a key, whereas they normally would be correlated with a join predicate. This is so we can see how the Allen relationships correlate the two tables using dates.

With the Asserted Versioning queries developed and discussed in this book, we have generally chosen to sacrifice realism in the examples in order to guarantee completeness of coverage. For example, in Chapter 13 we developed and discussed SQL views of nine categories of bi-temporal data, eight of those categories being what we called pipeline datasets. And yet few real-world collections of bi-temporal data, we acknowledged, would correspond to precisely one of those nine categories of data (with the one exception of currently asserted current versions of data, the category which corresponds to the data in a conventional table). Completeness of coverage was guaranteed, however, because those nine categories are a mathematical partitioning of all possible combinations of past, present and future assertion and effective time data.

Thus far in Chapter 14, we have developed and discussed SQL predicates, and SQL statements illustrating their use, each corresponding to one of the Allen relationships or one of the nodes in our taxonomy of Allen relationships. We have reviewed all possible Allen relationships, and taxonomic groupings of them, between pairs of time periods, between a time period and a point in time, and between two points in time. Once again, completeness of coverage has been guaranteed because of the use of a mathematical partitioning of all possible types of queries. But once again, the examples have not been very realistic.

In this section, we move on from completeness to verisimilitude. Having used a simplified set of health insurance data throughout this book, we continue by developing a query about claims issued against policies held by clients.

Claims data has not been used, so far, and so we begin with the SQL definition of a simplified Adjudicated Claims table.

This Adjudicated Claims table is not an asserted version table. It is an event table, not a persistent object table, and each of its rows represent an event on which an insurance claim was adjudicated. As an event, each claims adjudication has no persistence. It happens, and then it's over.

In the Adjudicated Claims table, policy_oid is not a foreign key, because there is no table for which it is the primary key. The Policy table is an asserted version table, and its primary key is the combination of policy_oid with assertion begin date and effective begin date. Nor is policy_oid a temporal foreign key, because Asserted Versioning does not recognize and manage referential relationships between non-temporal tables and asserted version tables. For example, the AVF may temporally delete a policy after several claims have been adjudicated that reference that policy, and it may temporally delete those policies effective at some point in time prior to the adjudication of those claims. It is the responsibility of the application which manages the Adjudicated Claims table to make sure it is not adjudicating claims against terminated policies.

Returning to our query, for each adjudicated claim, it will show the client number and name, the policy number, type and copay amount, and the claim service date, amount, and adjudication date.

For the policy associated with the claim, this query picks out the version of the policy that was in effect on the service date. Clearly, we are not interested in any other version of the policy. In particular, we are not interested in the version of the policy that is in effect when the query is run, or in the version of the policy that was in effect on the adjudication date. Those versions may in fact be correct, but we can't count on it. We want to see the version that was in effect at the time the medical service was rendered.

But there is more to picking out the correct policy data than this. We need, not simply the correct version, but the appropriate assertion of that version. For it is possible that, between the service date and the adjudication date or after the adjudication date, we found an error in the data about the policy as it was on the service date.

Say that the service date is 9/24/2009 and the adjudication date is 12/07/2009, and that between those two dates, we discovered and corrected an error in the version of the policy that was in effect on 9/24/2009, and then again after the adjudication another correction was made to the policy for the effective period of the service. This means that we have three rows representing the same version of the same policy, both purporting to describe the policy as it was on 9/24/2009. Which one do we want?

We want the one that was used to adjudicate the claim, of course, that being the assertion that was current on the adjudication date, 12/07/2009. So we want policy data that was effective on the service date, and asserted as of the adjudication date whether it was right or wrong because we want to see the source of the Explanation of Benefits (EOB) as the customer sees it.

This query also returns the client number and name of the client that owns the policy. Like most persistent object data, of course, that name may have changed over time. In this sample query, we choose to use the client data that was currently asserted at the time that the row in the Adjudicated Claims table was physically created.

Finally, we include one selection criterion on the query. We select those adjudicated claims where the claim amount is greater than the client's copay amount. These, of course, are the claims for which a payment will be made by the insurance company to the service provider.

Here is the query we have described.

To conclude this section, we show what this query might look like if the SQL language supported PERIOD datatypes, and also our taxonomy of Allen relationships. We suppose that the taxonomy node [fills⁻¹] is represented by the reserved word INCLUDES. With a SQL language like this, the Asserted Versioning schema no longer has pairs of dates to represent its two time periods. Instead, it has the single columns asr_per and eff_per.

In either form, what is striking about the query is its simplicity relative to the complexity of the bi-temporal semantics that underlies it. Unlike queries in the standard temporal model and, for that matter, uni-temporal queries in the alternative temporal model as well, this query does not assemble a collection of rows and then proceed to check for temporal gaps and temporal overlaps within sub-selected collections of those rows. Asserted Versioning enforces bi-temporal semantics once, as the data is being created and modified, rather than each time the data is queried.

With appropriate temporal extensions to the SQL language, the expression of all thirteen Allen relationships, and of this and other relationships which are combinations of those thirteen relationships, would be greatly simplified. The first thing that is needed to support predicates for these relationships is to provide a PERIOD datatype, as we discussed in Chapter 3. With that datatype available, SQL could express each of the relationships we have discussed with one binary predicate relating two time periods (not two pairs of dates).

For example, instead of having to request data associated with two time periods such that the first starts before the second and ends after the second starts but before the second ends, we could simply request data associated with two time periods such that the first [overlaps] the second.

Or, instead of having to request data associated with two time periods such that the first doesn't start after the second and doesn't end before the second, we could simply request data associated with two time periods such that the first [fills] the second.

It is clearly easier to think about what information one wants from the database at the higher level of abstraction provided by this new datatype and these new relationships, rather than at the level of abstraction in which begin and end dates have to be used, as they are in the original formulation of the example. And it is just as clearly easier to write the corresponding SQL.

But even with today's SQL which lacks these temporal extensions, Asserted Versioning manages assertion and effective time date pairs as user-defined PERIOD datatypes, and supports all the Allen relationships as well as the other relationships in our Allen relationship taxonomy. Asserted Versioning thus provides a migration path to the day when these extensions are supported in the SQL standard and in commercial DBMSs.

Glossary entries whose definitions form strong inter-dependencies are grouped together in the following list. The same glossary entries may be grouped together in different ways at the end of different chapters, each grouping reflecting the semantic perspective of each chapter. There will usually be several other, and often many other, glossary entries that are not included in the list, and we recommend that the Glossary be consulted whenever an unfamiliar term is encountered.

We note, in particular, that none of the nodes in the Asserted Versioning taxonomy of Allen relationships are included in this list. In general, we leave taxonomy nodes out of these lists since they are long enough without them.