Information about Temporal Database
A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language.
More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form bitemporal data.
John's father officially reported 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, 1994, 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, 1994 would be told that John lives in Bigtown. More technically: if a computer scientist ran the query
Until his tragic death the database would state that 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.
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 known 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).
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 Beachy 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 superseded. 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, 2-Feb-2001 ). 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, ∞, 2-Feb-2001, 1-Apr-2001 ). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).
So we record not only changes in what happened at different times, but also changes in what was officially recorded at different times.
More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form 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 data combines both Valid and Transaction Time.
Example
For illustration, we will take data from the following short biography of a fictional man John Doe. John Doe was born on April 3rd, 1975 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 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.Using a Standard Database
In order to store the life 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.John's father officially reported 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, 1994, 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, 1994 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, 1994, the result would be: Smallville. Running the same query 2 days later would result in Bigtown.
Until his tragic death the database would state that 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.
| Date | What happened in the real world | Database Action | What the database shows |
|---|---|---|---|
| April 3rd, 1975 | John is born | Nothing | There is no person called John Doe |
| April 4th, 1975 | John's father officially reports John's birth | Inserted:Person(John Doe, Smallville) | John Doe lives in Smallville |
| August 26, 1994 | After graduation, John moves to Bigtown, but forgets to register his new address | Nothing | John Doe lives in Smallville |
| December 26, 1994 | Nothing | Nothing | John Doe lives in Smallville |
| December 27, 1994 | John registers his new address | Updated:Person(John Doe, Bigtown) | John Doe lives in Bigtown |
| April 1, 2001 | John dies | Deleted:Person(John Doe) | There is no person called John Doe |
Bitemporal Relations
A bi-temporal relation contains both valid and transaction time. This is good because it provides both temporal rollback and historical information. Temporal rollback (eg: "In 1992, where did the database believe John lived?") is provided by the transaction time. Historical information (eg: "Where did John live in 1992?") can be derived from valid time. The answers to these example questions may not be identical - the database may have been altered since 1992, causing the queries to produce different results.Valid Time
Valid time is the time for which a fact is true in the real world. In the example above, the Person table gets two extra fields, Valid-From and Valid-To, specifying when a person's address was valid in the real world. On April 4th, 1975 John'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 known 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
Transaction time is the time a transaction was made. This enables queries that show the state of the database at a given time. 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 (or infinity if it has not yet been superseded).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 Beachy 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 superseded. 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, 2-Feb-2001 ). 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, ∞, 2-Feb-2001, 1-Apr-2001 ). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).
So we record not only changes in what happened at different times, but also changes in what was officially recorded at different times.
Books
- C.J. Date, Hugh Darwen, Nikos Lorentzos (2002). Temporal Data & the Relational Model, First Edition (The Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 1st edition; 422 pages. ISBN 1-55860-855-9.
- Joe Celko (2005). Joe Celko's SQL for Smarties: Advanced SQL Programming (The Morgan Kaufmann Series in Data Management); Morgan Kaufmann; 3rd edition; 808 pages. ISBN 0-12-369379-9.—Chapters 4 and 29 in particular discuss temporal issues.
- Richard T. Snodgrass, Christian S. Jensen (1999). Developing Time-Oriented Database Applications in SQL PDF (4.77 MiB) (Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 504 pages; ISBN 1-55860-436-7
See also
External links
Topics in database management systems (DBMS)
| |
|---|---|
|
Concepts Database Database models Database storage Relational model Distributed DBMS ACID Null Relational database Relational algebra Relational calculus Database normalization Referential integrity Relational DBMS Primary key, Foreign key, Surrogate key, Superkey, Candidate key | |
|
Objects Trigger View Table Cursor Log Transaction Index Stored procedure Partition |
Topics in SQL Select Insert Update Merge Delete Join Union Create Drop Begin work Commit Rollback Truncate Alter |
| Implementations of database management systems | |
|
Types of implementations Relational Flat file Deductive Dimensional Hierarchical Object oriented Object relational Temporal XML data stores | |
|
Database products Object-oriented (comparison) Relational (comparison) |
Components Query language Query optimizer Query plan ODBC JDBC |
database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
..... Click the link for more information.
..... Click the link for more information.
SQL
Paradigm: multi-paradigm
Appeared in: 1974
Designed by: Donald D. Chamberlin and Raymond F. Boyce
Developer: IBM
Latest release: SQL:2003/ 2003
Typing discipline: static, strong
Major implementations: Many
SQL
..... Click the link for more information.
Paradigm: multi-paradigm
Appeared in: 1974
Designed by: Donald D. Chamberlin and Raymond F. Boyce
Developer: IBM
Latest release: SQL:2003/ 2003
Typing discipline: static, strong
Major implementations: Many
SQL
..... Click the link for more information.
Valid time (VT) is a concept used in temporal databases. It denotes the time period during which a database fact was, is, or will be valid in the modeled reality.
In a database table valid time is often represented by two extra table-columns StartVT and
..... Click the link for more information.
In a database table valid time is often represented by two extra table-columns StartVT and
..... Click the link for more information.
Transaction time (TT) is a concept used in temporal databases. It denotes the time period during which a database fact is/was stored in the database.
In a database table transaction time is often represented by two extra table-columns StartTT and EndTT.
..... Click the link for more information.
In a database table transaction time is often represented by two extra table-columns StartTT and EndTT.
..... Click the link for more information.
Bitemporal data is a concept used in a temporal database. It denotes both the valid time and transaction time of the data.
In a database table bitemporal data is often represented by four extra table-columns StartVT and EndVT, StartTT and EndTT.
..... Click the link for more information.
In a database table bitemporal data is often represented by four extra table-columns StartVT and EndVT, StartTT and EndTT.
..... Click the link for more information.
In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information.
..... Click the link for more information.
The word infinity comes from the Latin infinitas or "unboundedness." It refers to several distinct concepts (usually linked to the idea of "without end") which arise in philosophy, mathematics, and theology.
..... Click the link for more information.
..... Click the link for more information.
Hugh Darwen, employee of IBM UK from 1967 to 2004, has been involved in the history of the relational model since the beginning. From 1978 to 1982 he was a chief architect on Business System 12, a database management system that faithfully embraced the principles of the relational
..... Click the link for more information.
..... Click the link for more information.
Nikos Lorentzos is a Greek professor of Informatics. He is a specialist on the Relational Model of Database Management, having made significant contributions in the field of temporal databases, where he has co-authored an important book with Hugh Darwen and Christopher J Date.
..... Click the link for more information.
..... Click the link for more information.
Joe Celko is a relational database expert and author from Austin, Texas. He has participated on the ANSI X3H2 Database Standards Committee, and helped write the SQL-89 and SQL-92 standards.
..... Click the link for more information.
..... Click the link for more information.
Portable Document Format (PDF)
Adobe Reader displaying a PDF in Microsoft Windows Vista
File extension:
MIME type:
Type code: 'PDF ' (including a single space)
..... Click the link for more information.
Adobe Reader displaying a PDF in Microsoft Windows Vista
File extension:
.pdfMIME type:
application/pdfType code: 'PDF ' (including a single space)
..... Click the link for more information.
mebibyte (a contraction of mega binary byte) is a unit of information or computer storage, abbreviated MiB.
..... Click the link for more information.
- 1 MiB = 220 bytes = 1,048,576 bytes = 1,024 kibibytes
- 1 MiB = 1024 (= 210
..... Click the link for more information.
Database theory encapsulates a broad range of topics related to the study and research of the theoretical realm of databases and database management systems.
..... Click the link for more information.
Subtopics in database theory
- Precedence graphs
See Also
- Database
- Temporal database
..... Click the link for more information.
A database management system (DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include Oracle, DB2, Microsoft Access, Microsoft SQL Server, PostgreSQL, MySQL, FileMaker and Sybase Adaptive Server Enterprise.
..... Click the link for more information.
..... Click the link for more information.
database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
..... Click the link for more information.
..... Click the link for more information.
A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join.
..... Click the link for more information.
..... Click the link for more information.
flat file database describes any of various means to encode a data model (most commonly a table) as a plain text file.
..... Click the link for more information.
Unordered
Unordered storage typically stores the records in the order they are inserted, while having..... Click the link for more information.
The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to
..... Click the link for more information.
..... Click the link for more information.
A distributed database management system is a software system that permits the management of a distributed database and makes the distribution transparent to the users. A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network.
..... Click the link for more information.
..... Click the link for more information.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
..... Click the link for more information.
..... Click the link for more information.
Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information.
..... Click the link for more information.
A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information.
..... Click the link for more information.
Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. Operators operate on one or more relations to yield a relation. Relational algebra is a part of computer science.
..... Click the link for more information.
..... Click the link for more information.
The relational calculus refers to the two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and that provide a declarative way to specify database queries.
..... Click the link for more information.
..... Click the link for more information.
Database normalization is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
..... Click the link for more information.
..... Click the link for more information.
referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table.
..... Click the link for more information.
..... Click the link for more information.
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Relational databases are the most common kind of database in use today (assuming one does not count a file system as a database).
..... Click the link for more information.
..... Click the link for more information.
In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information.
..... Click the link for more information.
In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.
..... Click the link for more information.
..... Click the link for more information.
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.
..... Click the link for more information.
..... Click the link for more information.
This article is copied from an article on Wikipedia.org - the free encyclopedia created and edited by online user community. The text was not checked or edited by anyone on our staff. Although the vast majority of the wikipedia encyclopedia articles provide accurate and timely information please do not assume the accuracy of any particular article. This article is distributed under the terms of GNU Free Documentation License.
Herod_Archelaus
