Databases Reference
In-Depth Information
existing queries to that view. We can achieve this objective for
temporal inserts, updates and deletes by defining default effec-
tive and assertion dates for these transactions. These will be
default dates that cause these transactions, as written by their
authors, and as parsed and submitted to the DBMS by the AVF,
to physically insert and update current assertions of the current
versions of the objects they reference.
The PERIOD Datatype
A second issue related to changes in the SQL standard is the
need for a PERIOD datatype. This new datatype will not change
the semantics of temporal data management, but it will simplify
the expression of those semantics. For one thing, a single col-
umn will replace a pair of dates. This will simplify the way that
Allen relationships are specified in SQL statements. For example,
it will change the expression with which we ask whether a point
in time is or is not contained in a period of time. Where P and T
are, respectively, a point in time and a period of time, and T
1
and
T
2
are dates delimiting T using the closed-open representation,
we currently must ask whether P is a clock tick within T like this:
T
2
With the PERIOD datatype, and the new Allen relationship
operators that will accompany it (including such derivative
operators as those used in our taxonomy), we will be able to
ask the same question like this:
WHERE T
1
<ΒΌ
P AND P
<
WHERE T OCCUPIES P
A PERIOD datatype will also make it easier to enforce con-
straints on time periods, such as insuring that two time periods
do not intersect. When representing time periods by means of
begin and end dates, this is impossible to do with only an index.
Here's why.
Consider the time period represented by the closed-open pair
[4/23/2012 - 8/04/2014]. Suppose that we want to define an
exclusive index on time periods. The problem is that there is
no way, by means of any standards-compliant indexing method
available with today's technology, to exclude [3/12/2011 - 4/24/
2012], or [10/15/2012 - 9/30/2013], or [6/1/2014 - 12/31/2014],
or any other time period that in fact [
intersects]
the time period
designated by [4/23/2012 - 8/04/2014]. The index sees two
columns of values, and knows that the combination of values
must be unique in each instance. That's all it sees, that's all it
knows, and that's all it can enforce.
