“Temporal database”的意思、由来-开放百科全书

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time.

More specifically the temporal aspects usually include valid time, transaction time or decision time

Uni-Temporal

A uni-temporal database has one axis of time, either the validity range or the system time range.

Bi-Temporal

Tri-Temporal

Temporal databases are in contrast to current databases (not to be confused with currently available databases), which store only facts which are believed to be true at the current time.

Features

Temporal databases support managing and accessing temporal data by providing one or more of the following features:^[1]^[2]

History

With the development of SQL and its attendant use in real-life applications, database users realized that when they added date columns to key fields, some issues arose. For example, if a table has a primary key and some attributes, adding a date to the primary key to track historical changes can lead to creation of more rows than intended. Deletes must also be handled differently when rows are tracked in this way. In 1992, this issue was recognized but standard database theory was not yet up to resolving this issue, and neither was the then-newly formalized SQL-92 standard.

An attempt was made to incorporate parts of TSQL2 into the new SQL standard 1999, called SQL3. Parts of TSQL2 were included in a new substandard of SQL3, ISO/IEC 9075-7, called SQL/Temporal.^[1] The TSQL2 approach was heavily criticized by Chris Date and Hugh Darwen.^[3] The ISO project responsible for temporal support was canceled near the end of 2001.

As of December 2011, ISO/IEC 9075, Database Language 2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (valid time tables), "system-versioned tables" (transaction time tables) and "system-versioned application-time period tables" (bitemporal tables). A substantive difference between the TSQL2 proposal and what was adopted in SQL:2011 is that there are no hidden columns in the SQL:2011 treatment, nor does it have a new data type for intervals; instead two date or timestamp columns can be bound together using a PERIOD FOR declaration. Another difference is replacement of the controversial (prefix) statement modifiers from TSQL2 with a set of temporal predicates.^[1]

Other features of 2011 standard related with temporal databases are the Automatic time period splitting, Temporal primary keys, Temporal referential integrity, Temporal predicates with Allen's interval algebra and Time-sliced and sequenced queries.

Example

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

Using a no temporal database

To store the life of John Doe in a current (non-temporal) database we use a table Person (Name, Address). (In order to simplify Name is defined as the primary key of Person.)

John's father officially reported his birth on April 4, 1975. On this date a Smallville official inserted the following entry in the database: Person(John Doe, Smallville).

After graduation, John moves out, but forgets to register his new address. John's entry in the database is not changed until December 27, 1994, when he finally reports it. 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, so it is no longer possible to retrieve that information from the database. An official accessing the database on December 28, 1994 would be told that John lives in Bigtown.

More technically: if a database administrator ran the query SELECT ADDRESS FROM PERSON WHERE NAME='John Doe' on December 26, 1994, the result would be Smallville. Running the same query 2 days later would result in Bigtown.

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

Using Single Axis : Valid time or Transaction Time

For the example above, to record valid time, the Person table has two fields added, Valid-From and Valid-To. These specify the period when a person's address is valid in the real world.

On April 4, 1975 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 4th, the database states that the information is valid since the 3rd. The official does not yet know if or when John will move to another place, so the Valid-To field is set to infinity (∞). The entry in the database is:

On December 27, 1994 John reports his new address in Bigtown where he has been living since August 26, 1994. A new database entry is made to record this fact:

The original entry Person (John Doe, Smallville, 3-Apr-1975, ∞) is not deleted, but has the Valid-To attribute updated to reflect that it is now known that John stopped living in Smallville on August 26, 1994.

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

Using Two Axes : Valid Time and Transaction time

To enable transaction time in the example above, 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 (which may be infinity if it has not yet been superseded). This makes the table into a 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 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 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:

However, this leaves no record that the database ever claimed that he lived in Bigtown during 1-Jun-1995 to 3-Sep-2000. 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:

The database records not only what happened in the real world, but also what was officially recorded at different times.

Bitemporal Modelling

A bitemporal model contains both valid and transaction time. This provides both historical and rollback information. Historical information (e.g.: "Where did John live in 1992?") is provided by the valid time. Rollback (e.g.: "In 1992, where did the database believe John lived?") is provided by the transaction time. The answers to these example questions may not be the same{{snd}} the database may have been altered since 1992, causing the queries to produce different results.

The valid time and transaction time do not have to be the same for a single fact. For example, consider a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1701 and 1800. The transaction time would show when the facts were inserted into the database (for example, January 21, 1998).

Schema evolution

A 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 first 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,^[4]^[5] although this is not part of SQL or similar standards.

Implementations in notable products

The following implementations provide temporal features in a relational database management system (RDBMS).

Non-relational, NoSQL database management systems that provide temporal features including the following:

Alternatives

See also

References

1. ^¹Snodgrass, 1999, p. 9
2. ^{{cite web| url = http://www.cs.arizona.edu/~rts/tsql2.html| title = TSQL2 Temporal Query Language| work = www.cs.arizona.edu| author = Richard T. Snodgrass| authorlink = Richard T. Snodgrass| publisher = Computer Science Department of the University of Arizona| accessdate = 14 July 2009}}
3. ^Hugh Darwen, C.J. Date, “An overview and Analysis of Proposals Based on the TSQL2 Approach”, In Date on Database: Writings 2000-2006, C.J. Date, Apress, 2006, pp. 481-514
4. ^{{cite conference| title=Managing and querying transaction-time databases under schema evolution|author1=Hyun J. Moon |author2=Carlo A. Curino |author3=Alin Deutsch |author4=C.-Y. Hou |author5=Carlo Zaniolo |last-author-amp=yes | conference= Very Large Data Base VLDB| year = 2008| url = http://yellowstone.cs.ucla.edu/schema-evolution/index.php/Prima}}
5. ^{{cite conference| title=Scalable Architecture and Query Optimization for Transaction-time DBs with Evolving Schemas|author1=Hyun J. Moon |author2=Carlo A. Curino |author3=Carlo Zaniolo |last-author-amp=yes | conference= SIGMOD| year = 2010| url = http://yellowstone.cs.ucla.edu/schema-evolution/index.php/AIMS}}
6. ^{{cite conference| title=Why Banks Care About Bitemporality| author=Anthony B. Coates| conference=MarkLogic World 2015| year=2015| url=https://www.youtube.com/watch?t=28&v=n29Gtit3lMU}}
7. ^https://mariadb.com/kb/en/library/system-versioned-tables/
8. ^{{cite web|last1=Paquier|first1=Michael|title=Postgres 9.2 highlight: range types |url=http://michael.otacoo.com/postgresql-2/postgres-9-2-highlight-range-types/ |date=1 November 2012 |archiveurl=https://web.archive.org/web/20160423215529/http://michael.otacoo.com/postgresql-2/postgres-9-2-highlight-range-types/ |archivedate=2016-04-23 |work=Michael Paquier - Open source developer based in Japan}}
9. ^{{cite web|last1=Katz|first1=Jonathan S.|title=Range Types: Your Life Will Never Be The Same|url=https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf|accessdate=14 July 2014}}
10. ^Al-Kateb, Mohammed et al. "Temporal Query Processing in Teradata{{Dead link|date=June 2018 |bot=InternetArchiveBot |fix-attempted=no }}". EDBT/ICDT ’13 March 18–22, 2013, Genoa, Italy
11. ^¹http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/
12. ^¹²Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011". ACM SIGMOD Record 41.3 (2012): 34-43.
13. ^http://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016
14. ^{{cite web|last1=Bridgwater|first1=Adrian|title=Data Is Good, 'Bidirectionalized Bitemporal' Data Is Better|url=https://www.forbes.com/sites/adrianbridgwater/2014/11/24/data-is-good-bidirectionalized-bitemporal-data-is-better/#5424ff9b567b |date=24 November 2014}}