Temporal database

Key takeaways

Temporal database belongs to science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Temporal database to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Temporal database from memory before moving on to harder problems.

Reference excerpt

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases can be uni-temporal, bi-temporal or tri-temporal. More specifically the temporal aspects usually include valid time, transaction time and/or decision time.

Valid time is the time period during or event time at which a fact is true in the real world. Transaction time is the time at which a fact was recorded in the database. Decision time is the time at which the decision was made about the fact. Used to keep a history of decisions about valid times.

Types

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

Bi-temporal

A bi-temporal database has two axes of time:

Valid time Transaction time or decision time

Tri-temporal

A tri-temporal database has three axes of time:

Valid time Transaction time Decision time This approach introduces additional complexities. 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:

A time period datatype, including the ability to represent time periods with no end (infinity or forever) The ability to define valid and transaction time period attributes and bitemporal relations System-maintained transaction time Temporal primary keys, including non-overlapping period constraints Temporal constraints, including non-overlapping uniqueness and referential integrity Update and deletion of temporal records with automatic splitting and coalescing of time periods Temporal queries at current time, time points in the past or future, or over durations Predicates for querying time periods, often based on Allen's interval relations

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. Richard Snodgrass proposed in 1992 that temporal extensions to SQL be developed by the temporal database community. In response to this proposal, a committee was formed to design extensions to the 1992 edition of the SQL standard (ANSI X3.135.-1992 and ISO/IEC 9075:1992); those extensions, known as TSQL2, were developed during 1993 by this committee. In late 1993, Snodgrass presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI Technical Committee X3H2 (now known as NCITS H2). The preliminary language specification appeared in the March 1994 ACM SIGMOD Record. Based on responses to that specification, changes were made to the language, and the definitive version of the TSQL2 Language Specification was published in September, 1994 An attempt was made to incorporate parts of TSQL2 into the new SQL standard SQL:1999, called SQL3. Parts of TSQL2 were included in a new substandard of SQL3, ISO/IEC 9075-7, called SQL/Temporal. The TSQL2 approach was heavily criticized by Chris Date and Hugh Darwen. The ISO project responsible for temporal support was canceled near the end of 2001. As of December 2011, ISO/IEC 9075, Database Language SQL: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 columns with datestamps (DS) or date-timestamps (DTS) 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. Other features of SQL:2011 standard related to temporal databases are 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:

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.

… excerpt ends here. Continue reading the full article.

Worked examples

Example 1 — a first encounter with Temporal database

Start with the simplest possible case. Write down what Temporal database claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Temporal database before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Temporal database ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Temporal database

In research: Temporal database appears in science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Temporal database in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Temporal database is common in secondary-school and first-year university syllabi. It links to neighbouring topics Database management systems, Database theory, Temporal databases, so understanding it makes those chapters shorter.
In everyday life: Look for Temporal database outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

How to study Temporal database in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Temporal database means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Temporal database out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Temporal database in simple terms?

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

Why does Temporal database matter?

Because it connects several science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Temporal database?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Temporal database.