Subject: Valid/Transaction times orthogonality

From: (Fabio Grandi) <[email-masked]>
Date: Tue, 6 Apr 93 18:44:49 +0100

The following message regards valid and transaction time
orthogonality in temporal queries.
It continues a correspondence started with Shashi Gadia which
- I agree - may be of general interest.

=============================================================================

My original questions arose from Gadia and Sunil's email on March 31 (EM1),
which was a contribution to the TSQL benchmark discussion
(as well, such questions may be part of the discussion
around a next phase of the benchmark, when transaction-time
queries are going to be included).

First of all, as far as the Gadia's reply on April 5 (EM2)
is concernded, I completely agree with his view.
It also helped to clarify some points of EM1 which
seemed to me a little questionable.

I make no doubt that:

 1)  PURE valid time query: "when did John work in toys"
     (i.e. valid time query in a valid-time database)

and:

 2)  PURE transaction time query: "when did John work in toys"
     (i.e. transaction time query in a transaction-time database)

are orthogonal and are to be expressed in a similar way
in a (mono)temporal query language. Consequently, we can also accept
a common English formulation for them. Only the (*implied*)
time dimension varies.

In a bitemporal database, I also share the view that 
the counterpart of (1) and (2) is:

 3)  PURE bitemporal query: "when did John work in toys"
     (i.e. bitemporal query in a bitemporal database)

Although (3) can have the same expression as (1) and (2)
in a (bi)temporal query language (provided that bitemporal
rather than monotemporal timestamps are retrieved),
a little more questionable thing is the most "natural" meaning
of its English formulation in this framework.

Well, my initial observations addressed to Gadia arose from the examples
contained in section "transaction time queries" of EM1, which
seemed to me somewhat confusing.

EM1 suggested the *addition* of transaction time queries
to the tsql benchmark, based so far on a valid-time model,
so I assumed the scope of EM1 to be bitemporal and, thus,
I understood a bitemporal context as I was reading the examples:

  4) valid time query: "when did John work in toys"
     (valid time query in a bitemporal database)

  5) transaction time query: "when was John believed to have worked in toys"
     (transaction time query in a bitemporal database (?))

In my opinion, IN A BITEMPORAL CONTEXT, there's not a clear orthogonality
between (4) and (5), such that their corresponding sql-queries are identical.
For instance, it can be noticed that (2) and (4) have the same English
formulation though they are supposed to represent different kinds of queries.

I feel that the most "natural" meaning of the question word "when"
- concerning some "fact" and not some "belief" -
in a bitemporal context is to denote a historical inquiry,
along valid time, in a database which is kept up-to-date
by a human being (and not by a wizzard) as best as he can (now).
Hence, I feel that (4) usually implies in a "natural" way the
transaction time qualifier *as of now* and its more explicit meaning is:

 4a) retrieve the valid timestamps of the data
     telling that John worked in toys
     from the database restricted to the transaction timepoint "now"

The transaction-time counterpart of (4a) could be as well:

 5a) retrieve the transaction timestamps of the data
     telling that John worked in toys
     from the database restricted to the valid timepoint "now"

which is orthogonal to (4a) but rather different, I believe, from (5).

On the other hand, the English query "when was John believed
to have worked in toys" may be something "more" than a simple
transaction time query in a bitemporal database.
I think it presents some very interesting semantic issues
which should be taken into account in future versions of the tsql benchmark.

First, if we say "John worked in toys" in a bitemporal context,
we likely mean he did it [in reality] in some valid time span.
Second, if we say "when was John believed" in a bitemporal context,
we likely mean he was believed [according to the information in the DB]
in some transaction time span, which is the target of our query.
Therefore, combining the two things, our query seems to look for
the transaction time spans in which it was stored in the database
that John worked in toys in some valid time span. Since such a valid
time span is not *explicit* in the query, we have to assume some kind
of *default* to fully map our query onto bitemporal data.
I would suggest that a "natural" default for the valid time span
could be in this case the same time span used for transaction time.

If we want to specify a *different* valid time span, we have to express
it explicitly, as in the query "when was John believed to have worked
in toys in 1990", which could be answered as:

  pi                 ( sigma                                       ( R ) )
    Transaction time         Name=John, Dept=toys, Valid time=1990 

if R is the bitemporal relation containing John's and departments' data.

Assuming the suggested default ("coherent" values of transaction and
valid time), the query can be detailed as:

 5b) retrieve all the (transaction/valid) time points T such that
     John worked in toys
     from the data valid at valid time T
     from the database as of transaction time T

In this case, the qualifying timestamps must be retrieved
from the data laying along the *diagonal* of the time quadrant
whose axes are transaction and valid time.

This kind of retrieval should be supported by a temporal query language,
since its semantics seems to be interesting from an application point
of view.

Let us consider, for instance, a database storing employees' data
including an attibute "Qualification" on which the salary value depends.
Let us make the following (simplified) assumptions:

 a) salaries are payed monthly, in advance, on the 1st of each month;

 b) for each employee, the value to be payed on the 1st of "M" 
    is evaluated as a function of the attribute value Employee.Qualification
    valid on the 1st of "M" as of the 1st of "M",
    which is the correct value for month "M" as best known on the 1st of "M"
    (no wizzard can forecast future retroactive updates);

 c) due to a mistake of a terminal operator, a wrong value, say "Q2",
    of the John's Qualification was stored in the database;

 d) some time later, John (or someone else) noticed the mistake
    and a retroactive modification was issued to correct the
    Qualification value;

 e) the accounting department is then informed that John's salaries
    must be audited;

 f) the accounting department has to know for which months
    the John's salary must be recomputed; this can be answered
    by the query:

    "when John's Qualification was believed to have been Q2"

This query can be detailed as:

     retrieve all the months "M" such that
     John's Qualification=Q2 
     from the data valid on the 1st of "M"
     from the database as of the 1st of "M"

As a matter of fact, it should be noticed that
the Qualification value Q2 (valid on and as of the 1st of "M")
was used on the 1st of "M" to compute the wrong salary amount
which was *actually* payed to John, according to (b).

I hope that this example (and my suggestions)
would come out more clear than obscure.

I would greatly appreciate any comments on the topic.

Sincerely,
    Fabio Grandi.