Nội dung Text: Managing time in relational databases- P8

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 123
prevent us from inserting another row for policy P861, with an
effective begin date of, let’s say, April 2010?
The answer is that Asserted Versioning explicitly recognizes
and enforces temporal entity integrity, a concept we introduced
in the preceding chapter. As we said there, any Asserted
Versioning implementation must reject a transaction that would
create a version whose effective time period [intersects], within
shared assertion time, even a single effective-time clock tick
included in another version of that object already in the table.
Most physical transactions against bi-temporal tables are
inserts. When we distinguish inserts from updates, as we have
done here, we are talking about logical transactions against
bi-temporal tables, transactions we call temporal transactions.
The Temporal Foreign Key
A temporal foreign key (TFK) is analogous to a normal foreign
key, and serves much the same purpose; but it is one which the
DBMS does not know how to use. A foreign key, at the schema
level, points from one table to a table it is dependent on. At
the row level, a foreign key value points from one row in the for-
mer table to one row in the latter table.
At the schema level, a TFK also points from one table to a
table it is dependent on. But at the row level, it points from
one row, which is a version, to a group of one or more rows
which make up an episode of the object whose oid matches
the oid in the temporal foreign key.
So a TFK does not point to a specific row in its referenced
table. But it also does not point to the episode which the version
containing it is dependent on. It points only to an object. In our
example, it says only that this version of policy P861 belongs to
client C882. But since no row in particular represents a client,
i.e. since clients are themselves versioned, and since their vers-
ions are also asserted, the TFK points to no row in particular.
Since there may be multiple episodes for any object, the TFK
points to no episode of that object in particular.
The very existence of a TFK instance does make the claim,
however, that there is an episode of the designated object in
the referenced table, and that the effective time period of that
episode in the referenced table includes, i.e. is filled by ([fills–1]),
the effective time period of the version which contains the refer-
ring TFK. And it is one of the responsibilities of the Asserted
Versioning Framework to insure that for every TFK instance in
a database, there is exactly one such episode. Although the TFK,
by itself, does not designate this parent episode, the TFK together

124 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
with the assertion and effective time periods on the child row
does designate it.
The Effective Time Period
A pair of dates defines the effective time period for each ver-
sion. As we explained in Chapter 3, we use the closed-open con-
vention, for both effective dates and assertion dates, in which a
time period starts on its begin date, and ends one clock tick prior
to its end date. Effective begin and end dates, of course, indicate
when the version began and ceased to be effective. They delimit
the period of time during which the object was as that row in the
table describes it to be.
With non-temporal tables, we create a version of an object by
the act of inserting a row into its table. But because there are no
dates to delimit the effective time period of the row, the row goes
into effect when it is physically created, and remains in effect
until it is physically deleted. And while it exists in the table, no
other versions of that object may co-exist with it. For if they
did, there would be two or more statements each claiming to
be the true description of the object during the entire time that
all of them co-existed in the table.
The Episode Begin Date
Just as versions have effective time periods, episodes do too.
An episode’s begin date is an effective begin date, and it is the
same as the effective begin date of its earliest version. An
episode’s end date, although not explicitly represented in the
Asserted Versioning schema, is the same as the effective end date
of its latest version.
The episode begin date is repeated on every row so that by
looking at an episode’s latest version we can determine the entire
effective time period for the episode itself. By the same token, we
can also look at any version and know that from its episode
begin date to its own effective end date, its object was continu-
ously represented in effective time. The ability to do this without
retrieving and looking at multiple rows will be important, later
on, when we see how temporal referential integrity is enforced.
The Assertion Time Period
Using the same closed-open convention, this pair of dates
indicates when we began to assert a version as true and when,
if ever, we stopped asserting that it is true. Even when a version

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 125
ceases to be in effect, i.e. even when it has an effective end date
in the past, we will usually continue to assert that, during its
effective time period, it was a true description of what its object
was like at that time.
However, and this is a very important point, there are
exceptions. With both the Asserted Versioning and the standard
temporal models, assertions may end even though the rows
that made them remain in the table. We terminate assertions if
and when we learn that they are mistaken, that the statement
they make is not true. In addition, with Asserted Versioning,
but not with the standard temporal model, rows can also be
created whose assertion is postponed until some later point in
time.
With non-temporal tables, we assert a row to be true by the
act of inserting it into its table, and we cease to assert that it is
true by deleting it from its table. With non-temporal tables, the
assertion time period of a row coincides with the row’s physical
presence in the table. In these cases, the assertion begin date
is the same as the row creation date. Also, in most cases, once
we assert a row to be true, we continue to do so “until further
notice”.
The Row Creation Date
The row creation date is the date the row is physically
inserted into its table. In most cases, the row creation date will
be identical with the assertion begin date. In the standard tem-
poral model, it always is, and consequently, in that model, the
two dates are not distinguished. However, in our Asserted
Versioning implementation of bi-temporal data management,
it is valid to create a row with an assertion begin date in the
future. Thus, for Asserted Versioning, it is necessary to have a
row creation date which is distinct from the assertion begin
date.
The Basic Asserted Versioning Diagram
Figure 6.2 is an example of the basic diagram we will use to
illustrate Asserted Versioning. The schema in this diagram was
explained in the previous section. Now it’s time to explain how
to read the diagram itself. Figure 6.2 shows us the state of an
asserted version table after a temporal insert transaction which
took place on January 2010, and a temporal update transaction
which took place on May 2010.

126 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
May10 UPDATE Policy [P861, , , $20]
Jan10 1 The table as asserted from Jan 1st, 2010 to May 1st, 2010
May10 21 3 The table as asserted from May 1st , 2010 until further notice
Jan Jan Jan Jan Jan
2010 2011 2012 2013 2014
Temporal Primary Key Temporal Foreign Key
Episode Begin Date
Row oid eff-beg eff-end asr-beg asr-end epis- client type copay row-crt
# beg
1 P861 Jan10 9999 Jan10 May10 Mar10 C882 HMO $15 Jan10
2 P861 Jan10 May10 May10 9999 Jan10 C882 HMO $15 May10
3 P861 May10 9999 May10 9999 Jan10 C882 HMO $20 May10
Effective Period Assertion Period Row Creation Date
Figure 6.2 The Basic Asserted Versioning Diagram.
On January 2010, a temporal insert transaction was processed
for policy P861.1 It said that policy P861 was to become effective
on that date, and that, as is almost always the case when a tem-
poral transaction is processed, was to be immediately asserted.
Then in May, a temporal update transaction for that policy
was processed. The transaction changed the copay amount from
$15 to $20, effective immediately. But this invalidated row 1
because row 1 asserted that a copay of $15 would continue to
be in effect after May. So as part of carrying out the directives
specified by that temporal transaction, we withdrew the asser-
tion made by row 1, by overwriting the original assertion end
date of 12/31/9999 on row 1 with the date May 2010.
When a row is withdrawn, it is given a non-12/31/9999 asser-
tion end date. Withdrawn rows, in these diagrams, are graphically
1
The format for showing bi-temporal dates, in the text itself, is slightly different from
the format used in the sample tables. For example, a date shown as “Jan10” in any of
the tables will be written as “January 2010” in the text. Time periods are shown in the
text as date pairs with the month still shortened but the century added to the year.
Thus “[Feb 2010 – Oct 2012]” designates the time period beginning on February 2010
and ending one clock tick before October 2012, but would be represented in the
diagram by “Feb10” and “Oct12”.

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 127
indicated by shading them. In addition, every row is represented
by a numbered rectangular box on a horizontal row of all versions
whose assertions begin on the same date. We will call these hori-
zontal rows assertion time snapshots. These snapshots are located
above the calendar timeline, and when their rectangular boxes
represent withdrawn rows, those boxes are also shaded. So in Fig-
ure 6.2, the box for row 1 is shaded.
After row 1 was withdrawn, the update whose results are
reflected in Figure 6.2 was then completed by inserting two rows
which together represent our new knowledge about the policy,
namely that it had a copay of $15 from January 2010 to May
2010, and had a copay of $20 thereafter.
The clock tick box is located to the left of the transaction, at
the top of the diagram. It tells us that it is currently May 2010,
in this example. This is graphically indicated by the solid vertical
bar representing that month on the calendar timeline above the
sample table. In this example, rows 2 and 3 have just been cre-
ated. We can tell this because their row create date is May 2010.
The first row is no longer asserted. It has been withdrawn. It
was first asserted on January 2010, but it stopped being asserted
as part of the process of being replaced as a current assertion by
row 2, and then superceded by row 3. This is indicated by the
May 2010 value in row 1’s assertion end date and the May 2010
value in the assertion begin dates of rows 2 and 3. It is graph-
ically indicated by the two assertion time snapshots above and
to the left of the timeline. Each snapshot shows the rows of the
table that were currently asserted starting on that date. So from
January 2010 to May 2010, row 1 is what we asserted about
policy P861. From May 2010 forwards, rows 2 and 3 began to
be asserted instead.
We say “instead” because rows 2 and 3 together replace and
supercede row 1, in the following sense. First of all, they describe
the same object that row 1 describes, that object being policy
P861. Second, rows 2 and 3 together [equal] the effective time
period of row 1, the period [Jan 2010 – 12/31/9999]. Row 2’s
effective time period is [Jan 2010 – May 2010]. Then, without
skipping a clock tick, row 3’s effective time period is [May 2010
– 12/31/9999]. Row 2 includes the part of row 1’s effective time
whose business data is not changed by the update; so we will
say that row 2 replaces that part of row 1. Row 3 includes the part
of row 1’s effective time whose business data is changed by the
update; so we will say that row 3 supercedes that part of row 1.
In our illustrations, 9999 represents the latest date that the
DBMS can represent. In the case of SQL Server, for example, that
date is 12/31/9999. This date does not represent a New Year’s Eve

128 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
some 8000 years hence. But it is a date as far as the DBMS is
concerned. The importance of this “dual semantics” will become
important later on when we explain how Asserted Versioning
queries work.
Notice that all three rows in this example have assertion
begin dates that are identical to their corresponding row creation
dates. In the standard temporal model, a transaction time period
is used instead of an assertion time period; and with transaction
time periods, the begin date is always identical to the row crea-
tion date, and so a separate row creation date is not necessary.
But in the Asserted Versioning model, assertion begin dates
and row creation dates are not semantically identical, and they
do not necessarily have the same value in every row in which
they appear. With Asserted Versioning, while no assertion begin
date can be earlier than the corresponding row creation date,
it can be later. If it is later, the transaction which creates the
row is said to be a deferred transaction, not a current one.
The row it creates in an asserted version table is said to be a
deferred assertion, not a current one. Such rows are rows that
we may eventually claim or assert are true, but that we are not
yet willing to.
In this example, both before and after May 2010, the effective
end date for policy P861 is not known. But sometimes effective
end dates are known. Perhaps in another insurance company,
all policies stop being in effect at the end of each calendar year.
In that case, instead of an effective end date of 12/31/9999 in
rows 1 and 3, the example would show a date of January 2011
(meaning, because of the closed-open convention, that the last
date of effectivity for these policies is one clock tick prior to Jan-
uary 2011, that being December 2010).
We turn now to the graphics, the part of Figure 6.2 above the
sample table. The purpose of these graphics is to abstract from
the business details in the sample table and focus attention
exclusively on the temporal features of the example.
Above a calendar which runs from January 2010 to February
2014, there are two horizontal rows of rectangular boxes. These
rows are what we have already referred to as assertion time
snapshots, with each rectangular box representing one version
of the associated table. The lowest snapshot in a series of them
contains a representation of the most recently asserted row or
rows. These most recently asserted rows are almost always cur-
rently asserted and usually will continue to be asserted until fur-
ther notice.
There are, however, two exceptions. Neither of them is part of
the standard temporal model, but both of them support useful

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 129
semantics. One exception is created by the presence of deferred
assertions in the table which are, by definition, assertions whose
begin dates, at the time the transaction is processed, lie in the
future. The other exception is created when assertions are with-
drawn without being replaced or superceded, i.e. when after a
certain point in time we no longer wish to claim that those
assertions ever were, are or will be a true description of what
their object was, is or might be like during some stretch of effec-
tive time. But as we said earlier, we will not discuss deferred
assertions until several chapters from now, at which time we will
also discuss withdrawn assertions that are not replaced and/or
superceded by other assertions.
Each of these assertion time snapshots consists of one or
more boxes. As we said, each box contains the row number of
the row it represents. The vertical line on the left-hand side of
each box corresponds to the effective begin date of the row it
represents. In this illustration, only one of the boxes is closed,
in the sense of having a line on its right-hand side. The other
two are open both graphically and, as we will see, semantically.
Let’s consider these boxes, one at a time. The box for row 1 is
open-ended. This always means that the corresponding row has
an effective end date of 12/31/9999. The box directly below the
box for row 1 represents row 2. Because that box is closed on
its right-hand side, we know that the row it represents has a
known effective end date which, in this case, is May 2010.
In these boxes that line up one under the other, the business
data in the rows may or may not be identical. If the business
data is identical, then the box underneath the other represents
a replacement, and we will indicate that it has the same business
data as the row it replaces by using the row number of the row it
replaces as a superscript. But if the business data in two rows for
the same object, during the same effective time period, is not
identical, then the row represented by the lower box supercedes
the row represented by the upper box, and in that case we
will not include a superscript. This convention is illustrated in
Figure 6.2, in which the box for row 2 has a superscript
designating row 1, whereas the box for row 3 has no superscript.
The box directly to the right of the box for row 2 represents
row 3. We can tell that the two rows are temporally adjacent
along their effectivity timelines because the same vertical line
which ends row 2’s effective time period also begins row 3’s
effective time period. So this diagram shows us that there is an
unbroken effective time period for policy P861, which began to
be asserted on May 2010, and which extends from row 2’s effec-
tive begin date of January 2010 to row 3’s effective end date of

130 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
12/31/9999, this being exactly the same effective time period
previously (in assertion time) represented by row 1 alone.
This description of Asserted Versioning’s basic diagram has
focused on a sample table whose contents reflect one temporal
insert transaction, and one temporal update transaction.
Additional Diagrams and Notations
Before proceeding, we need a more flexible way to supple-
ment English in our discussions of Asserted Versioning. In the
last section, we used what we called the “basic diagram” of an
asserted version table. That diagram contains five main compo-
nents. They are:
(i) The current clock tick, which indicates what time it is in the
example;
(ii) A temporal insert, update or delete transaction;
(iii) A calendar timeline covering approximately four years, in
monthly increments;
(iv) A stacked series of assertion time snapshots of the table
used in the example; and
(v) The table itself, including all rows across all effective and
assertion times.
We will still need to use this basic diagram to illustrate many
points. But for the most part, discussions in the next several
chapters will focus on effective time. So we will often use a dia-
gram in which rows in past assertion time are not shown in the
sample table, and in which there are no assertion time snapshots
either.
So, leaving assertion time snapshots out of the picture, we
will often use the kind of diagram shown in Figure 6.3. And
sometimes we will simply show a few rows from a sample table,
as in Figure 6.4.
Sep10 UPDATE Policy [P861, , PPO] Jun 2010
Jan Jan Jan Jan Jan
2010 2011 2012 2013 2014
Row oid eff-beg eff-end asr-beg asr-end epis- client type copay row-crt
# beg
1 P861 Jan10 Apr10 Jan10 9999 Jan10 C882 HMO $15 Jan10
2 P861 Apr10 Jul10 Apr10 9999 Jan10 C882 HMO $20 Apr10
3 P861 Jul10 Oct10 Jul10 9999 Jan10 C882 PPO $20 Jul10
Figure 6.3 The Effective Time Diagram.

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 131
Row oid eff-beg eff-end asr-beg asr-end epis- client type copay row-crt
# beg
1 P861 Jan10 Apr10 Jan10 9999 Jan10 C882 HMO $15 Jan10
2 P861 Apr10 Jul10 Apr10 9999 Jan10 C882 HMO $20 Apr10
3 P861 Jul10 Oct10 Jul10 9999 Jan10 C882 PPO $20 Jul10
Figure 6.4 A Sample Asserted Version Table.
While illustrations are essential to understanding the com-
plexities of bi-temporal data management, it will also be useful
to have a notation that we can embed in-line with the text. In
this notation, we will use capital letters to represent our sample
tables. So far we have concentrated on the Policy table, and we
will use “P” to represent it.
Almost always, what we will have to say involves rows and
transactions that all contain data about the same object. For
example, we will often be concerned with whether time periods
for rows representing the same policy do or do not [meet]. But
for the most part we will not be concerned with whether or not
time periods for rows representing different policies [meet]. So
the notation “P[X]” will indicate all rows in the Policy table that
represent policy X.
In the next several chapters, we will be primarily concerned
with the effective time periods of asserted version rows. So for
example, the notation P[P861[Jun12-Mar14]] stands for the row
(or possibly multiple rows) in the Policy table for the one or
more versions of P861 that are in effect from June 2012 to March
2014. With this notation, we could point out that there is exactly
one clock tick between P[P861[Jun12-Mar14]] and P[P861
[Apr14-9999]]. If we needed to include assertion time as well,
the notation would be, for example, P[P861[Jun12-Mar14]
[Jun12-9999]]. If we were concerned with assertion time but
not with effective time, we would refer to the row(s) P[P861[]
[Jun12-9999]].
An example of the notation describing a complete asserted
version row is:
P[P861[Jun12-Mar14][Jun12-9999][Jun12][C882, HMO, $15]
[Jun12]]
We will use abbreviated forms of the notation wherever possi-
ble. For one thing, we will seldom refer to the row creation date,
until Chapter 12, because until we discuss deferred assertions,
the row creation date will always be the same as the assertion
begin date. Also, the episode begin date is always identical to
the effective begin date of the first version of an episode, so we

132 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
will often leave it out of our in-line representation of an asserted
version row unless it is relevant to the example at hand.
What is lost with this in-line notation is context. What is
gained is focus. Diagrams also present us with a graphical repre-
sentation of a timeline and a snapshot of effective time periods,
grouped by common assertion times along that timeline. With
the in-line notation, that context, too, is not represented.
Viewing the Asserted Version Table
We can already see that asserted version tables are more com-
plex than non-temporal tables. For one thing, they have about
half a dozen columns that are not present in non-temporal
tables; and the semantics of these columns, the rules governing
their correct use and interpretation, are sometimes subtle. For
another thing, the fact that there are now multiple rows
representing a single object adds another level of complexity to
an asserted version table. Some of those rows represent what
those objects used to be like, others what they are like right
now, and yet others what they may, at some time in the future,
be like. In addition, and quite distinctly, some of those rows rep-
resent what we used to say those objects were, are, or will be like;
others what we currently say they were, are, or will be like; and
yet others what we may, at some time in the future, say they
were, are, or will be like.
All in all, the sum and interplay of these factors make asserted
version tables quite complex. They can be complex to maintain in
the sense that they can be easy to update in ways whose results do
not reflect what the person writing the updates intended to do.
And they can be difficult to interpret, as we just said.
Asserted Versioning attempts to eliminate this maintenance
complexity, as far as possible, by making it seem to the user
who writes temporal transactions which utilize default values
for their temporal parameters that the insert she writes creates
a single row, that the updates she writes update that single row,
and that the delete she writes removes it. In this way, by
providing a set of transactions that conform to the paradigm
she is already familiar with, the possibility of misinterpretation,
on the maintenance side of things, is minimized.
But what about the query side of things? What about looking
at the data in an asserted version table? How can this data be
presented so as to hide the mechanisms by which it is man-
aged—those extras columns and extra rows we referred to a

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 133
moment ago—leaving what looks like a non-temporal or a uni-
temporal table?
It is particularly important to hide the complexity of asserted
version tables from query authors who are not IT professionals,
from query authors who may be business analysts, scientists or
researchers. For these query authors, the solution is to query
asserted version tables through views. And no matter how much
history is contained in an asserted version table, and no matter
how much conjecture about or anticipation of the future may
be contained in an asserted version table, we are still going to
be primarily interested in what we believe, right now, things
are like right now. That is, our most frequent view of an asserted
version table will be the view that makes it look like a non-
temporal table containing current data.
The Conventional Table View
So let’s create that view. Its DDL looks something like this:
CREATE VIEW V_Policy_Curr AS
SELECT oid, client, type, copay
FROM Policy_AV
WHERE eff_beg_dt

134 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
Row # oid client type copay
1 P861 C882 HMO $20
Figure 6.6 The Current Non-Temporal View from April to July.
The Version Table View
Next, let’s create a view which looks like the best of our best
practice versioning methods, the one we called effective time
versioning, in Chapter 4. As a version table view, it knows noth-
ing about assertions. But because the table the view is based on
is a bi-temporal table, there may be multiple assertions about
the same set of one or more effective time clock ticks. So in order
to filter out any past assertions, those being rows which contain
data we no longer think is correct, this view will present only
currently asserted versions, versions that we currently believe
make true statements about the objects they represent.
CREATE VIEW V_Policy_Ver AS
SELECT oid, eff_beg_dt, eff_end_dt, client, type, copay
FROM Policy_AV
WHERE asr_beg_dt

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 135
A unique index on them would find nothing wrong. But as far as
versions are concerned, these two rows should not both be in the
table. As far as versions are concerned, there is something very
wrong.
Semantically, these are not two pairs of dates. They are the
delimiters of a PERIOD datatype, and in this case, there is a 6-
month overlap of these two time periods. The AVF enforces tem-
poral entity integrity on the basis of an interpretation of these
date pairs as time period delimiters. As far as integrity con-
straints are concerned, the AVF is code which implements, in a
middleware layer located between temporal transactions and
the DBMS, a user-defined PERIOD datatype, and which then
uses that PERIOD datatype in its enforcement of both temporal
entity integrity and temporal referential integrity.
This SQL creates a view that is current whenever it is run.
That is, whenever it is run, it will show all currently asserted
versions about the past, the present and the future. For example,
run any time prior to January 2010, against a table containing
just the three rows used in this example, it would return an
empty result set. Run on January 2010, or on any date after that,
up to but not including April 2010, it would return the result
shown in Figure 6.7.
And if run on July 2010 or any date after that and prior to any
further changes to the table, it would return the result shown in
Figure 6.8.
The Assertion Table View
While there are several best practices supporting versions,
there are none that we know of supporting assertions. Future-
dated assertions are something no one other than ourselves
appears to have thought of. Past-dated assertions are just errors
Row # oid eff-beg eff-end client type copay
1 P861 Jan10 Apr10 C882 HMO $15
Figure 6.7 The Current Version Table View as of April 2010.
Row # oid eff-beg eff-end client type copay
1 P861 Jan10 Apr10 C882 HMO $15
2 P861 Apr10 Jul10 C882 HMO $20
3 P861 Jul10 Oct10 C882 PPO $20
Figure 6.8 The Current Version Table View from July Forwards.

136 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
that we keep around, probably for auditing purposes. And we
have logfiles to take care of that.
The value of assertion table views on bi-temporal tables will
become clearer as this book progresses. As we will see, assertion
table views are of particular interest to auditors.
For now, let’s look at the SQL which will provide a view of all
assertions in our bi-temporal Policy table that have anything to
do with currently effective data. The version table view was a
view of all currently asserted versions. This assertion table view
is a view of all currently effective assertions.
CREATE VIEW V_Policy_Asr AS
SELECT oid, asr_beg_dt, asr_end_dt, eff_beg_dt,
eff_end_dt, client, type, copay
FROM Policy_AV
WHERE eff_beg_dt

Chapter 6 DIAGRAMS AND OTHER NOTATIONS 137
Understanding why such asymmetries exist almost always
deepens one’s understanding of the underlying theory. This par-
ticular asymmetry is important in this way, and we will have
more to say about it in later chapters.
Like the version table view, this assertion table view is also
current whenever it is run. Whenever this view is run, it will
show all assertions, past, present or future, that reference even
a single clock tick in current effective time. Our sample database,
shown in Figures 6.3 and 6.4, does not contain data that will
illustrate this view which presents a current assertion table. But
we will discuss assertion table views in depth in Chapters 12
and 13.
Views and Mental Images
Assuming (until Chapter 12) that temporal transactions never
specify assertion dates, we can have three kinds of temporal
transactions against asserted version tables, for example against
our Policy table. With the first kind, no effective dates are speci-
fied. To authors of these transactions, they appear to be doing
normal maintenance to a conventional Policy table, one that
looks like the table shown in Figures 6.5 and 6.6. They have, or
need have, no idea that the table they are actually maintaining
is the table shown in Figures 6.3 and 6.4.
With the second kind of temporal transaction, effective begin
and end dates may be specified, but neither of them can be a
date which is already past at the time of the transaction. This
is the way maintenance is done to version tables. Normally, cur-
rent versions are created whenever they are needed to reflect a
change in the object they represent. The version that was current
at the time of the transaction is given an end date, and the trans-
action creates the new current version.
But Asserted Versioning, and the two most complete best prac-
tice forms of versioning, also permit transactions to create future
versions. To authors of these transactions, they appear to be
doing normal maintenance to a uni-temporal versioned Policy
table, one that looks like the table shown in Figures 6.7 and 6.8.
They have, or need have, no idea that the table they are actually
maintaining is the table shown in Figures 6.3 and 6.4.
With the third kind of temporal transaction, effective begin
and end dates may be specified, and either or both of them
may be dates which are already past at the time of the transac-
tion. This is the way maintenance is done to tables which sup-
port assertions of versions. But to authors whose mandate is to

138 Chapter 6 DIAGRAMS AND OTHER NOTATIONS
maintain either conventional or uni-temporal versioned tables,
this kind of maintenance is impossible. A conventional table
has no way of recognizing effective time. And it is semantically
illegitimate to create past versions in uni-temporal version
tables.
Here’s why. If a past version were created in a version table,
let’s say one with an effective begin date of a week ago, it would
be a lie. For all that week, the database would have said that the
object represented by that version was not in effect during that
week. Then, after the transaction, the database would say that
the object was in effect during that same week. One or the other
of those claims must be false. In fact, the latter one is.
But in a chapter whose purpose is to introduce notation that
will be used in the rest of this book, much of what we have just
said anticipates more detailed discussions that will occur later,
particularly in Part 3.
Glossary References
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 sev-
eral 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.
12/31/9999
until further notice
asserted version table
Asserted Versioning Framework (AVF)
assertion begin date
assertion end date
assertion table
assertion time period
transaction time period
bi-temporal data
business data
business key

THE BASIC SCENARIO
7
CONTENTS
The Representation of Objects in Time Periods 142
Occupied and Represented 144
Basic Temporal Transactions: The Mental Model 144
Maintaining Asserted Version Tables: The Basic Scenario 145
A Temporal Insert Transaction 145
A Temporal Update Transaction 147
A Second Temporal Update Transaction 152
A Temporal Delete Transaction 154
Glossary References 159
When representing an object in a non-temporal table, the
most basic things we can do are to insert a row representing
the object, update the row as changes happen to the object,
and delete the row if and when the object ceases to exist, or
ceases to be of interest to us. Almost always, we don’t know in
advance if or when any of these things will happen: if or when
a row will be inserted; if or when the next update will occur; if
or when the row will be deleted.
These rows represent objects of interest to us. They are not
the objects themselves. A row in a Policy table is not a policy;
it is data which represents the policy in our database. Through
the DBMS, for the most part mediated by application software,
we manage these rows. We insert them, update them and some-
times delete them. We put them in result sets in response to
queries. We put them on reports. By managing them, we are able
to manage the policies which they represent. For this reason, we
will call these rows managed objects.
Sometimes it is harmless enough to finesse this distinction, and
speak of the DBMS, for example, updating policies. But what the
DBMS updates, of course, are rows representing policies.
It updates managed objects, not the objects they represent.
As we enter into a series of chapters which will describe our method
of temporal data management in great detail, we will sometimes
Managing Time in Relational Databases. Doi: 10.1016/B978-0-12-375041-9.00007-8
Copyright # 2010 Elsevier Inc. All rights of reproduction in any form reserved. 141

142 Chapter 7 THE BASIC SCENARIO
find it useful to emphasize the distinction between objects and
managed objects. But if we do occasionally speak of software doing
something to an object, that will simply be a non-tendentious
shorthand for speaking about software doing something to a man-
aged object which represents that object.
This basic sequence of insert, update and delete transactions
accounts for most of the activity against any table, whether non-tem-
poral or bi-temporal. And just as we usually don’t know if or when
updates to a non-temporal row will occur, and if or when it will be
deleted, so too, as each row representing some stage in the life his-
tory of an object is inserted into a bi-temporal table, we usually don’t
know if or when another row for that same object will come along.
In this chapter, we will follow a sequence of basic temporal
transactions. Temporal transactions are inserts, updates and
deletes whose target is an asserted version table. As we explained
in Chapter 5, all asserted version tables are bi-temporal tables.
Uni-temporal assertion tables and uni-temporal version tables,
as we sometimes call them, are actually views on bi-temporal
tables. Conventional tables, which we also call non-temporal
tables, are the third kind of view which can be defined on
Asserted Versioning’s physical bi-temporal tables.
Basic temporal transactions are temporal transactions that do
not specify any bi-temporal parameters. As we will see later, any
one or more of three bi-temporal parameters—an assertion
begin date, an effective begin date or an effective end date—
may be specified on a temporal transaction. On a basic temporal
transaction, none of them are specified.
By starting out with basic temporal transactions, we can
introduce the most common sequence of transactions that occur
against asserted version tables, and show how these transactions
are processed. In chapters that follow, we will examine temporal
transactions which are not basic, and whose processing is con-
siderably more complex, although because of the maintenance
encapsulation provided by Asserted Versioning, are just as easy
for the user to write as are basic temporal transactions.
The Representation of Objects in Time
Periods
The basic scenario, and by far the most commonly encoun-
tered one, is for a series of updates that would overwrite data
in a conventional table to result in an effective-time contiguous
series of versions in an asserted version table. Just as it is logi-
cally impossible to apply an update to a conventional table that

Chapter 7 THE BASIC SCENARIO 143
takes effect either in the past or in the future, basic temporal
updates to asserted version tables also take place in the present.
Just as an update to a conventional table remains in effect until
further notice, rather than for a predetermined length of time, a
basic temporal update to an asserted version table will be to a
current episode that is open, i.e. whose latest version has an
effective end date of 12/31/9999, and it will always leave that
episode open after the transaction is complete.
Basic temporal transactions are the Asserted Versioning equiva-
lent of conventional transactions. No bi-temporal parameters are
specified on them, and so their content is identical to that of their
corresponding conventional transactions. They contain exactly the
same information that is present on conventional transactions—
nothing more and nothing less. But if temporal transactions are to
take effect some time in the future, or some time in the past, then
those non-current effective time periods must be explicitly specified
on those transactions. And if we wish to update the database with
deferred assertions, the assertion begin date must also be specified.
In these cases, the temporal transactions are no longer basic; they
explicitly state the bi-temporal parameters they want.
Corresponding to each appearance of a managed object in a
conventional table, i.e. to each period of time starting when a
row for an object is inserted and ending if and when it is deleted,
there would be an episode of that object if that table were instead
an asserted version table. It follows that there can be any number
of episodes of the same object present at the same time in an
asserted version table, although no more than one of them, of
course, can be current. Each episode is a managed object,
representing an object as it exists over a continuous period of
time. Each version of that episode is also a managed object,
representing an object as it exists over one continuous period of
time that is included within the episode’s period of time.
In Figure 7.1, eight versions, grouped into three episodes, are
shown along a timeline. The first two episodes are closed, as all
but the latest episode of an object must be. The last one is open.
All are managed objects representing one object, policy P861.
Episode A Episode B Episode C
1 2 3 45 6 7 8
Jan Jan Jan Jan Jan
2010 2011 2012 2013 2014
Figure 7.1 Eight Versions and Three Episodes of Policy P861.