Databases Reference
In-Depth Information
begin date that is on every version, and the version's own effective
end date, provide the effective time period of the episode itself.
We also note that the SQL in many of the following examples
does not represent typical queries that a business would write.
Each of these queries focuses on one specific Allen relationship,
and show how to express it in SQL. In particular, these sample
queries do not include typical join criteria. Instead, the only join
criteria used in these examples are two time periods and the
Allen relationship between them.
Another reason these sample queries don't look very real
world is that they select from two of the tables in our sample
database that don't have much to do with one another. In partic-
ular, there is no TRI relationship between them. They are the
Policy and Wellness Program tables. If we had used, for example,
the Client and Policy tables instead, many of the queries would
have been more realistic.
But TRI-related tables cannot illustrate all of the Allen
relationships. In fact, every instance of a TRI relationship
involves a parent and a child time period that is an instance of
one of seven of the Allen relationships. This leaves six other Allen
relationships that TRI-related tables cannot illustrate.
Nevertheless, as overly simple and unrealistic as most of
these sample queries may be, they are the foundation for all
queries that express temporal relationships. No query will ever
need to express a temporal relationship that is not one of these
relationships. So if we know how to write the temporal pre-
dicates in these queries, we will know how to write any temporal
predicate for any query.
Allen Relationship Queries
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 posi-
tional 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
