Using Transaction time Temporal database

transaction time records time period during database entry accepted correct. enables queries show state of database @ given time. transaction time periods can occur in past or current time. in transaction time table, records never deleted. new records can inserted, , existing ones updated setting transaction end time show no longer current.

to enable transaction time in example above, 2 more fields added person table: transaction-from , transaction-to. transaction-from time transaction made, , transaction-to time transaction superseded (which may infinity if has not yet been superseded). makes table bitemporal table.

what happens if person s address stored in database incorrect? suppose official accidentally entered wrong address or date? or, suppose person lied address reason. upon discovery of error, officials update database correct information recorded.

for example, 1-jun-1995 3-sep-2000, john doe moved beachy. avoid paying beachy s exorbitant residence tax, never reported authorities. later during tax investigation, discovered on 2-feb-2001 in fact in beachy during dates. record fact, existing entry john living in bigtown must split 2 separate records, , new record inserted recording residence in beachy. database appear follows:

person(john doe, smallville, 3-apr-1975, 26-aug-1994).
person(john doe, bigtown, 26-aug-1994, 1-jun-1995).
person(john doe, beachy, 1-jun-1995, 3-sep-2000).
person(john doe, bigtown, 3-sep-2000, 1-apr-2001).

however, leaves no record database ever claimed lived in bigtown during 1-jun-1995 3-sep-2000. might important know auditing reasons, or use evidence in official s tax investigation. transaction time allows capturing changing knowledge in database, since entries never directly modified or deleted. instead, each entry records when entered , when superseded (or logically deleted). database contents this:

name, city, valid from, valid till, entered, superseded


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, ∞ ).

the database records not happened in real world, officially recorded @ different times.