Temporal Database - Example - Transaction Time

Transaction Time

Transaction time is the time a transaction was made. This enables queries that show the state of the database at a given time. Two more fields are added to the Person table: Transaction-From and Transaction-To. Transaction-From is the time a transaction was made, and Transaction-To is the time that the transaction was superseded (or infinity if it has not yet been superseded).

What happens if the person's address as stored in the database is incorrect? Suppose an official accidentally entered the wrong address or date? Or, suppose the person lied about their address for some reason. Upon discovery of the fact, the officials go back and update the database.

For example, from 1-Jun-1995 to 3-Sep-2000 John Doe moved to Beachy. But, to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later, it is discovered on 2-Feb-2001, during a tax investigation that he was in fact in Beachy during these dates, so they update the database as follows:

Person(John Doe, Smallville, 3-Apr-1975, 1-Jun-1995). Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001).

So the existing record about John living in Bigtown is split into two separate records and a new record is inserted recording his residence in Beachy.

However, this leaves no record that the database ever claimed that he lived in Bigtown during 1-Jun-1995 to 3-Sep-2000. Which might be important for say auditing reasons (or to use as evidence in the official's tax investigation.) This is where transaction time comes in. We record in each record when it was entered and when it was superseded. Thus we get something like this:

Person(John Doe, Smallville, 3-Apr-1975, ∞, 4-Apr-1975, 27-Dec-1994). Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994, 27-Dec-1994, ∞ ). Person(John Doe, Bigtown, 26-Aug-1994, ∞, 27-Dec-1994, 2-Feb-2001 ). Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995, 2-Feb-2001, ∞ ). Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000, 2-Feb-2001, ∞ ). Person(John Doe, Bigtown, 3-Sep-2000, ∞, 2-Feb-2001, 1-Apr-2001 ). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).

So we record not only changes in what happened at different times, but also changes in what was officially recorded at different times.

A particularly challenging issue is the support of temporal queries in a transaction time database under evolving schema. In order to achieve perfect archival quality it is of key importance to store the data under the schema version under which they firstly appeared. However even the most simple temporal query rewriting the history of an attribute value would be required to be manually rewritten under each of the schema versions, potentially hundreds as in the case of MediaWiki This process would be particularly taxing for users. A proposed solution is to provide automatic query rewriting, although this is not part of the SQL or similar standards.

Read more about this topic:  Temporal Database, Example

Other articles related to "time, transaction, transaction time":

Temporal Database
... A temporal database is a database with built-in time aspects, for example a temporal data model and a temporal version of Structured Query Language (SQL) ... More specifically the temporal aspects usually include valid-time and transaction-time ... Valid time denotes the time period during which a fact is true with respect to the real world ...
Transaction Time

Transaction time (TT), a concept originated by Richard T. Snodgrass and his doctoral student, is used in temporal databases. It denotes the time period during which a database fact is/was stored in the database. As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "system-versioned tables" (that is, transaction-time tables).

In a database table transaction time is often represented by two extra table-columns StartTT and EndTT. The time interval is closed at its lower bound and open at its upper bound.

When the ending transaction time is unknown, it may be considered as "Until Closed". Academic researchers and some RDBMS have represented "Until Closed" with the largest timestamp supported or the keyword "forever". This convention is not technically precise.

Famous quotes containing the word time:

    The first time many women hold their tiny babies, they are apt to feel as clumsy and incompetent as any man. The difference is that our culture tells them they’re not supposed to feel that way. Our culture assumes that they will quickly learn how to be a mother, and that assumption rubs off on most women—so they learn.
    Pamela Patrick Novotny (20th century)