A temporal database is a database management system with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language.

More specifically the temporal aspects include valid-time, transaction-time, and bitemporal data.

• Valid time denotes the time period during which a fact is true with respect to the real world.
• Transaction time is the time period during which a fact is stored in the database.
• Bitemporal time is the combination of both Valid and Transaction Time

Note that these two time periods do not have to be the same for a single fact. Imagine that we come up with a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1700 and 1799, where as the transaction time starts when we insert the facts into the database, for example, January 21, 1998.

Contents

1 Example 1.1 Non temporal example 1.2 Valid Time 1.3 Transaction Time 1.4 Bitemporal 2 Remarks/Issues 3 External Links

Example

In order to explain how Temporal Databases work, we need a short biography of John Doe. John Doe was born on April 3th, 1975 in the Kids Hospital of Medicine County, as son of Jack Doe and Jane Don't who lived in Smallville. Jack Doe proudly registered the birth of his first-born on April 4th, 1975 at the Smallville City Hall. John grew up as a joyful boy, turned out to be a brilliant student and graduated with honors in 1993. After graduation he went to live on his own in Bigtown. Although he moved out on August 26th, 1994, he forgot to register the change of address officially. It was only at the turn of the seasons that his mother reminded him that he had to register, which he did a few days later on December 27, 1994. Although John had a promising future, his story ends tragically. John Doe was accidentally hit by a truck on April 1st, 2001. The coroner reported his date of death on the very same day.

Non temporal example

In order to store the live of John Doe in a (non-temporal) database table we use this table Person(Name, Address). In order to simplify we define Name as the primary key of Person.

Joe's father officially reported Joe's birth on April 4th, 1975. This means that a Smallville official, inserted the following entry in the database on this date: Person(John Doe, Smallville) Note that the date itself is not stored in the database.

After graduation John moves out, but forgets to register his new address. John's entry in the database is not changed until December 27, 1993, when he finally enters Bigtown's city hall. A Bigtown official updates his address in the database. The Person table now contains Person(John Doe, Bigtown) Note that the information of John living in Smallville has been overwritten. There is no way to retrieve that information from the database. Any official accessing the database on December 28, 1993 would be told that John lives in Bigtown. More technically: if a computer scientist ran the query SELECT ADDRESS * FROM PERSON WHERE NAME='John Doe' on December 26, 1993, the result would be: Smallville. Running the same query 2 day later would result in Bigtown.

Until his tragical death the database would state the he lived in Bigtown. On April 1, 2001 the coroner deletes the John Doe entry from the database. Running the above query would return no result at all.

Valid Time

The Person table is being extended with two extra fields, Valid-From and Valid-To, specifying when a person's address was valid in the real world. On April 4th, 1975 Joe's father proudly registered his son's birth. An official will then insert a new entry to the database stating that John lives in Smallville from the April, 3rd. Notice that although the data was inserted on the 4th, the databases states that the information is valid since the 3rd. The official does not yet know if or when John will ever move to a better place so in the database the Valid-To is filled with infinity (∞). Resulting in this entry in the database: Person(John Doe, Smallville, 3-Apr-1975, ∞).

December 27, 1994 John reports his new address in Bigtown where has been living since August 26th, 1994. The Bigtown official does not change the address of the current entry of John Doe in the database. He adds a new one:

  Person(John Doe, Big Town, 26-Aug-1993, ∞).

The original entry Person(John Doe, Smallville, 3-Apr-1975, ∞) is then updated (not removed!). Since it is now know that John stopped living in Smallville on August 26, 1993 the Valid-To entry can be filled in. The database now contains two entries for John Doe

• Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1993).
• Person(John Doe, Bigtown, 26-Aug-1993, ∞).

When John dies the database is once more updated. The current entry will be updated stating that John does not live in the Bigtown any longer. No new entry is being added because officials never report heaven as a new address. The database now looks like this

• Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1993).
• Person(John Doe, Bigtown, 26-Aug-1993, 1-Apr-2001).

Transaction Time

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 Beacy during these dates, so they update the database as follows:

• Person(John Doe, Bigtown, 26-Aug-1993, 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 two records are updated 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 superceeded. 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-1993, 27-Dec-1994, ∞).
• Person(John Doe, Bigtown, 26-Aug-1993, ∞, 27-Dec-1994).
• Person(John Doe, Bigtown, 26-Aug-1993, 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, 1-Apr-2001, 2-Feb-2001, ∞).

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

Bitemporal

Remarks/Issues

• The temporal examples will be added here.

External Links

• TimeCenter (http://www.cs.auc.dk/TimeCenter/about.htm)