Editing Temporal database (section)

==Example==
For illustration, consider the following short biography of a fictional man, John Doe:

:John Doe was born on 1975-04-03 in the Kids Hospital of Medicine County, as son of Jack Doe and Jane Doe who lived in Smallville. Jack Doe proudly registered the birth of his first-born on April 4, 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 1994-08-26, 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 1994-12-27. Although John had a promising future, his story ends tragically. John Doe was accidentally hit by a truck on 2001-04-01. The coroner reported his date of death on the very same day.

===Using a non-temporal database===
To store the life of John Doe in a current (non-temporal) database we use a table {{code|person (name, address)}}. (In order to simplify, <code>name</code> is defined as the [[primary key]] of <code>person</code>.)

John's father officially reported his birth on 1975-04-04. On this date a Smallville official inserted the following entry in the database: <code>Person(John Doe, Smallville)</code>.
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 1994-12-27, when he finally reports it. A Bigtown official updates his address in the database. The <code>person</code> table now contains <code>Person(John Doe, Bigtown)</code>. Note that the information of John living in Smallville has been overwritten, so it is no longer possible to retrieve that information from the database. An official accessing the database on 1994-12-28, would be told that John lives in Bigtown. More technically: if a database administrator ran the query {{code|1=SELECT ADDRESS FROM PERSON WHERE NAME='John Doe'|2=sql}} on 1994-12-26, the result would be <code>Smallville</code>. Running the same query 2 days later would result in <code>Bigtown</code>.

Until his death, the database would state that he lived in Bigtown. On 2001-04-01, the coroner deletes the John Doe entry from the database. After this, running the above query would return no result at all.

{| class="wikitable"
|-
!Date!!Real world event!!Database action!!What the database shows
|-
|style="text-align:right;"|{{nowrap|1975-04-03}}||John is born||{{CNone|Nothing}}||There is no person called John Doe
|-
|style="text-align:right;"|{{nowrap|1975-04-04}}||John's father officially reports John's birth||Inserted:Person(John Doe, Smallville)||John Doe lives in Smallville
|-
|style="text-align:right;"|{{nowrap|1994-08-26}}||After graduation, John moves to Bigtown, but forgets to register his new address||{{CNone|Nothing}}||John Doe lives in Smallville
|-
|style="text-align:right;"|{{nowrap|1994-12-26}}||Nothing||{{CNone|Nothing}}||John Doe lives in Smallville
|-
|style="text-align:right;"|{{nowrap|1994-12-27}}||John registers his new address||Updated:Person(John Doe, Bigtown)||John Doe lives in Bigtown
|-
|style="text-align:right;"|{{nowrap|2001-04-01}}||John dies||Deleted:Person(John Doe)||There is no person called John Doe
|}

===Using a single axis: valid time or transaction time===
{{Main article|Valid time}}[[Valid time]] is the time for which a fact is true in the real world. A valid time period may be in the past, span the current time, or occur in the future.

For the example above, to record valid time, the <code>person</code> table has two fields added, <code>valid_from</code> and <code>valid_to</code>. These specify the period when a person's address is valid in the real world. On 1975-04-04, John's father registered his son's birth. An official then inserts a new entry into the database stating that John lives in Smallville from April 3. Note that although the data was inserted on the fourth, the database states that the information is valid since the third. The official does not yet know if or when John will move to another place, so the <code>valid_to</code> field is set to [[infinity]] (∞). The entry in the database is:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
|-
|John Doe
|Smallville
|1975-04-03
|∞
|}
On 1994-12-27, John reports his new address in Bigtown where he has been living since 1994-08-26. A new database entry is made to record this fact:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
|-
|John Doe
|Bigtown
|1994-08-26
|∞
|}
The original entry <code>Person (John Doe, Smallville, 1975-04-03, ∞)</code> is not deleted, but has the <code>valid_to</code> attribute updated to reflect that it is now known that John stopped living in Smallville on 1994-08-26. The database now contains two entries for John Doe:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
|-
|John Doe
|Smallville
|1975-04-03
|1994-08-26
|-
|John Doe
|Bigtown
|1994-08-26
|∞
|}
When John dies his current entry in the database is updated stating that John does not live in Bigtown any longer. The database now looks like this:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
|-
|John Doe
|Smallville
|1975-04-03
|1994-08-26
|-
|John Doe
|Bigtown
|1994-08-26
|2001-04-01
|}

===Using two axes: valid time and transaction time===
{{Main article|Transaction time}}

[[Transaction time]] records the time period during which a database entry is accepted as correct. This enables queries that show the state of the database at a given time. Transaction time periods can only occur in the past or up to the current time. In a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their transaction end time to show that they are no longer current.

To enable transaction time in the example above, two more fields are added to the Person table: <code>transaction_from</code> and <code>transaction_to</code>. Here, <code>transaction_from</code> is the time a transaction was made, and <code>transaction_to</code> is the time that the transaction was superseded (which may be infinity if it has not yet been superseded). This makes the table into a [[#Bitemporal_relations|bitemporal table]].

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 error, the officials update the database to correct the information recorded.

For example, from 1995-06-01 to 2000-09-03, John Doe moved to Beachy. But to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later during a tax investigation, it is discovered on 2-Feb-2001 that he was in fact in Beachy during those dates. To record this fact, the existing entry about John living in Bigtown must be split into two separate records, and a new record inserted recording his residence in Beachy. The database would then appear as follows:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
|-
|John Doe
|Smallville
|1975-04-03
|1994-08-26
|-
|John Doe
|Bigtown
|1994-08-26
|1995-06-01
|-
|John Doe
|Beachy
|1995-06-01
|2000-09-03
|-
|John Doe
|Bigtown
|2000-09-03
|2001-04-01
|}
However, this leaves no record that the database ever claimed that he lived in Bigtown during 1995-06-01 to 2000-09-03.This might be important to know for auditing reasons, or to use as evidence in the official's tax investigation. Transaction time allows capturing this changing knowledge in the database, since entries are never directly modified or deleted. Instead, each entry records when it was entered and when it was superseded (or logically deleted). The database contents then look like this:
{| class="wikitable"
!Name
!City
!Valid from
!Valid to
!Entered
!Superseded
|-
|John Doe
|Smallville
|1975-04-03
|∞
|1975-04-04
|1994-12-27
|-
|John Doe
|Smallville
|1975-04-03
|1994-08-26
|1994-12-27
|∞
|-
|John Doe
|Bigtown
|1994-08-26
|∞
|1994-12-27
|2001-02-02
|-
|John Doe
|Bigtown
|1994-08-26
|1995-06-01
|2001-02-02
|∞
|-
|John Doe
|Beachy
|1995-06-01
|2000-09-03
|2001-02-02
|∞
|-
|John Doe
|Bigtown
|2000-09-03
|∞
|2001-02-02
|2001-04-01
|-
|John Doe
|Bigtown
|2000-09-03
|2001-04-01
|2001-04-01
|∞
|}
The database records not only what happened in the real world, but also what was officially recorded at different times.

===Using three axes: valid time, decision time, and transaction time===
[[Decision time]] is an alternative to the transaction time period for recording the time at which a database entry may be accepted as correct. This enables queries that show the officially recognized facts at a given time, even if there was a delay in committing those facts to the database. Support for decision time preserves the entire history and prevents the loss of information during updates.<ref>Mario A. Nascimento, Margaret H. Eich, “[http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.50.8187 Decision Time in Temporal Databases]”, In ''Proceedings of the Second International Workshop on Temporal Representation and Reasoning'', 1995, pp. 157-162</ref>

Decision time periods can only occur in the past or up to the transaction time. As in a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their decision end time to show that they are no longer current.

To enable decision time, two more fields are added to a database table: <code>decision_from</code> and <code>decision_to</code>. Here, <code>decision_from</code> is the time a decision was made, and <code>decision_to</code> is the time that the decision was superseded (which may be infinity if it has not yet been superseded). When combined with transaction time, this makes the table into a [[#Tritemporal_relations|tritemporal table]].
The following is a list of real events that occurred between the 1964 and 1976 [[United States presidential election]]s:
{| class="wikitable" 
!Date
!Decision maker
!Real world event
|-
|1964-11-03
|[[United States Electoral College|Electoral College]]
|[[1964 United States presidential election|Election of 1964]]
|-
|1968-11-05
|Electoral College
|[[1968 United States presidential election|Election of 1968]]
|-
|1972-11-07
|Electoral College
|[[1972 United States presidential election|Election of 1972]]
|-
|1973-10-10
|[[Spiro Agnew]]
|[[Spiro Agnew#Criminal investigation and resignation|Agnew resigns]]
|-
|1973-10-12
|[[Richard Nixon]]
|[[Gerald Ford#Vice presidency (1973–1974)|Nixon nominates Ford]]
|-
|1973-12-06
|[[United States Congress|Congress]]
|Congress confirms Ford
|-
|1974-08-09
|Richard Nixon
|[[Richard Nixon#Resignation|Nixon resigns]]
|-
|1974-08-20
|[[Gerald Ford]]
|[[Nelson Rockefeller#Vice President of the United States (1974–1977)|Ford nominates Rockefeller]]
|-
|1974-12-19
|Congress
|Congress confirms Rockefeller
|-
|1976-11-02
|Electoral College
|[[1976 United States presidential election|Election of 1976]]
|}

In this example, a constant 7-day delay is assumed between the decision time and the transaction time when the data is committed to the database. Given those conditions, the database would have contained the following information after the election in 1976:
{| class="wikitable"
| colspan="2" |
! colspan="2" |Valid
! colspan="2" |Decision
! colspan="2" |Transaction
|-
!President
!Vice
!From
!To
!From
!To
!From
!To
|- style="border-bottom:double"
|Johnson
|Humphrey
|1965-01-20
| style="color:red" |1969-01-20
|1964-11-03
|∞
|1964-11-10
|∞
|- style="border-bottom:double"
|Nixon
|Agnew
| style="color:red" |1969-01-20
| style="color:orange" |1973-01-20
|1968-11-05
|∞
|1968-11-12
|∞
|- style="border-bottom:double"
|Nixon
|Agnew
| style="color:orange" |1973-01-20
|1977-01-20
|1972-11-07
|∞
|1972-11-14
| style="color:red" |1973-10-17
|-
|Nixon
|Agnew
| style="color:orange" |1973-01-20
|1977-01-20
|1972-11-07
| style="color:red" |1973-10-10
| style="color:red" |1973-10-17
|∞
|-
|Nixon
|Agnew
| style="color:orange" |1973-01-20
| style="color:lime" |1973-10-10
| style="color:red" |1973-10-10
|∞
|1973-10-17
|∞
|- style="border-bottom:double"
|Nixon
|(Vacant)
| style="color:lime" |1973-10-10
|1977-01-20
|1973-10-10
|∞
|1973-10-17
| style="color:orange" |1973-12-13
|- style="border-bottom:double"
|Nixon
|Ford
|∞
|1977-01-20
|1973-10-12
|∞
|1973-10-19
| style="color:lime" |1973-12-13
|-
|Nixon
|(Vacant)
| style="color:lime" |1973-10-10
|1977-01-20
|1973-10-10
| style="color:orange" |1973-12-06
| style="color:orange" |1973-12-13
|∞
|-
|Nixon
|(Vacant)
| style="color:lime" |1973-10-10
| style="color:green" |1973-12-06
| style="color:orange" |1973-12-06
|∞
|1973-12-13
|∞
|-
|Nixon
|Ford
|∞
|1977-01-20
|1973-10-12
| style="color:lime" |1973-12-06
| style="color:lime" |1973-12-13
|∞
|- style="border-bottom:double"
|Nixon
|Ford
| style="color:green" |1973-12-06
|1977-01-20
| style="color:lime" |1973-12-06
|∞
|1973-12-13
| style="color:green" |1974-08-15
|-
|Nixon
|Ford
| style="color:green" |1973-12-06
|1977-01-20
|1973-12-06
| style="color:green" |1974-08-08
| style="color:green" |1974-08-15
|∞
|-
|Nixon
|Ford
| style="color:green" |1973-12-06
| style="color:blue" |1974-08-09
| style="color:green" |1974-10-08
|∞
|1974-08-15
|∞
|- style="border-bottom:double"
|Ford
|(Vacant)
| style="color:blue" |1974-08-09
|1977-01-20
|1974-10-08
|∞
|1974-08-15
| style="color:blue" |1974-12-26
|- style="border-bottom:double"
|Ford
|Rockefeller
|∞
|1977-01-20
|1974-10-20
|∞
|1974-08-27
| style="color:purple" |1974-12-26
|-
|Ford
|(Vacant)
| style="color:blue" |1974-08-09
|1977-01-20
|1974-10-08
| style="color:blue" |1974-12-19
| style="color:blue" |1974-12-26
|∞
|-
|Ford
|(Vacant)
| style="color:blue" |1974-08-09
| style="color:purple" |1974-12-19
| style="color:blue" |1974-12-19
|∞
|1974-12-26
|∞
|-
|Ford
|Rockefeller
|∞
|1977-01-20
|1974-08-20
| style="color:purple" |1974-12-19
| style="color:purple" |1974-12-26
|∞
|- style="border-bottom:double"
|Ford
|Rockefeller
| style="color:purple" |1974-12-19
|1977-01-20
| style="color:purple" |1974-12-19
|∞
|1974-12-26
|∞
|-
|Carter
|Mondale
|1977-01-20
|1981-01-20
|1976-11-02
|∞
|1976-11-09
|∞
|}
Given the 7-day delayed table above, the question "who was president and vice president for the valid time of 1977-01-01" (which given the 7-day delay could provide data for 1976-12-25) would be:

* Nixon/Agnew when using a decision time and transaction time of 1972-11-14
* Nixon/(Vacant) when using a decision time and transaction time of 1973-10-17
* Nixon/Ford when using a decision time and transaction time of 1974-08-08
* Ford/(Vacant) when using a decision time of 1974-08-08 and transaction time of current
* Ford/Rockefeller when using a decision time and transaction time of current