Example Temporal database

1 example

1.1 using current database
1.2 using valid time
1.3 using transaction time

example

for illustration, consider following short biography of fictional man, john doe:

john doe born on april 3, 1975 in kids hospital of medicine county, son of jack doe , jane doe lived in smallville. jack doe proudly registered birth of first-born on april 4, 1975 @ smallville city hall. john grew joyful boy, turned out brilliant student , graduated honors in 1993. after graduation, went live on own in bigtown. although moved out on august 26, 1994, forgot register change of address officially. @ turn of seasons mother reminded him had register, did few days later on december 27, 1994. although john had promising future, story ends tragically. john doe accidentally hit truck on april 1, 2001. coroner reported date of death on same day.

using current database

to store life of john doe in current (non-temporal) database use table person (name, address). (in order simplify name defined primary key of person.)

john s father officially reported birth on april 4, 1975. on date smallville official inserted following entry in database: person(john doe, smallville). note date not stored in database.

after graduation, john moves out, forgets register new address. john s entry in database not changed until december 27, 1994, when reports it. bigtown official updates address in database. person table contains person(john doe, bigtown). note information of john living in smallville has been overwritten, no longer possible retrieve information database. official accessing database on december 28, 1994 told john lives in bigtown. more technically: if database administrator ran query select address person name= john doe on december 26, 1994, result smallville. running same query 2 days later result in bigtown.

until death, database state lived in bigtown. on april 1, 2001, coroner deletes john doe entry database. after this, running above query return no result @ all.

using valid time

valid time time fact true in real world. valid time period may in past, span current time, or occur in future.

for example above, record valid time, person table has 2 fields added, valid-from , valid-to. these specify period when person s address valid in real world. on april 4, 1975 john s father registered son s birth. official inserts new entry database stating john lives in smallville april 3. note although data inserted on 4th, database states information valid since 3rd. official not yet know if or when john move place, valid-to field set infinity (∞). entry in database is:

person(john doe, smallville, 3-apr-1975, ∞).

on december 27, 1994 john reports new address in bigtown has been living since august 26, 1994. new database entry made record fact:

person(john doe, bigtown, 26-aug-1994, ∞).

the original entry person (john doe, smallville, 3-apr-1975, ∞) not deleted, has valid-to attribute updated reflect known john stopped living in smallville on august 26, 1994. database contains 2 entries john doe

person(john doe, smallville, 3-apr-1975, 26-aug-1994).
person(john doe, bigtown, 26-aug-1994, ∞).

when john dies current entry in database updated stating john not live in bigtown longer. database looks this

person(john doe, smallville, 3-apr-1975, 26-aug-1994).
person(john doe, bigtown, 26-aug-1994, 1-apr-2001).

using transaction time

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.