History Of Temporal Database Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.


Temporal Database is a database with built in tine aspects like temporal database model and a version of structured query language. It consists of valid time and transaction time which will combine to form a bitemporal data. There were some issues when a date was added to a primary key to track historical changes. A wide range of applications depend on time varying data. Normal database supports less to manage that data when compared to temporal database. This report would briefly explain how it was developed and how it can be managed. It concisely introduces the structure and characteristics of temporal database and also discusses some of the relevant issues. Briefly it also further looks the comparison between the regular database and the temporal database and also the future of temporal databases.

History of Temporal Database:

Richard Snodgrass proposed in 1992 that temporal extensions to SQL be developed by the temporal database community. Committee was formed to design extensions to the 1992 edition of the SQL standard (ANSI X3.135.-1992 and ISO/IEC 9075:1992); extensions known as TSQL2 were developed during 1993 by committee meeting only via email (Snodgrass, 1999, Page 9). In 1993 Snodgrass first presented this work to the group responsible for the American National Standard for Database Language SQL, ANSI 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 new version of the TSQL2 Language Specification was introduced.

Some of the language parts of TSQL2 were tried to put into the new SQL standard SQL 1999 called SQL3. They were incorporated into SQL3, ISO/IEC 9075-7 which is called as SQL/Temporal.

Databases and different types:

It is a collection of interrelated data and set of programs to access data stored. There are some models which are used to store information using different types of data models. They are

Relational Database: It stores information using relations. Table consists of rows which are also called as tuples and columns that are called as fields or attributes. Stores a set of tables.

Object- oriented Database: Stores data about entities in objects. It will contain the attributes of all objects that are present in the table. Stores a set of collections which are in turn the set of objects.

Spatial Database: Stores data in relations to space. This database mainly relates to the geographical space information like measurements of earth's surface and 3D space consisting of molecules. Stores the collection of space related data.

Temporal Database: Stores information related to time instances. This information will take care of the time factors that relate to the present, past and future instances. For example take an employee working in an organization. This type of database will store information of that employee from the time he was born till the time he will be expired. It will have all the history of his work related experience and till the time he gets retired. Stores the collection of time related data.

Temporal Databases:

All database applications that require some aspect of time when organizing their information. Valid time and transaction time together form the temporal database. These will form as bi-temporal data.

Valid time will denote time period which will relate to real world situations

Transaction time will denote time that are stored in database.

And bi-temporal data is the combination of both valid time and transaction time.

Some of the applications used in temporal database are:



Health Care


Personal Management System

Two Types of databases are available:

Bi-temporal Database: With each tuple in a relation two kinds of time are stored-the valid time (when a particular tuple is true) and the transaction time (when the particular tuple was inserted/deleted in the database)

Spatial Database: multiple dimensions over an interpreted domain can be used for representing spatial data where multiple dimensions serve as coordinates of points in a k-dimensional Euclidean space.

Standard Database with an example:

Data in this example is stored a non temporal database. And we will use name as primary key.

John's father reported birth on April 4, 1975. This means that a Smallville official, inserted the following entry in the database on this date: Person (John Doe, Smallville) Here the data is not store 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. 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 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.


What happened in the real world

Database Action

What the database shows

April 3, 1975

John is born


There is no person called John Doe

April 4, 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


John Doe lives in Smallville

December 26, 1994



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

Temporal Database Structure:

In temporal databases we add a temporal dimension to a relational model employed by most database models. A model with ordered set of chronons where time, t  {0, 1, 2, 3,……, n} will have different time points. Each point will be used to index different places of database so that the historical data Dn will be considered as a set of relational databases {Dt | 0 ≤ t ≤ n}. Each instance of t is known as tick.

Example of the temporal database structure:

League (team, pos) - Each row or tuple consists of football team name and league position the team finished for the campaign at the specific t which is tick. The campaign here will be football premiership title. Consider 5 time points and each will describe a new campaign. In real each t point will represent a season of the campaign.

This above table shows the row that holds a specific tick. These results are taken from for the years from 1998-1999 to 2002-2003. The 0 to 4 ticks represent each season.

Valid time temporal data model:

Temporal databases use relational databases using different data models and different SQL queries. There are two problems when using the relational databases which are

Does not give good support for storing complex temporal databases like does not support the automatic merging of temporally overlapping data.

And also less support for expressing temporal queries.

Therefore to eradicate this we need to define our own temporal database and queries for building a complex temporal database.

For valid time the time is attached to all tuples in the table. And two-dimensional tables are extended to include time factor as the third dimension. Every tuple holds the information which is indirectly denoting the valid time.

There are two types of types of tables

Event tables: Holds instant timestamps

State tables: Holds interval timestamps

The tables are specially bounded by start and stop in the temporal data.

Ex: [d01:d05]. It means it will start the time at d01 which will denote the 1st day and stop at the time point d05 which will denote the 5th day.

Time Normalization:

Time normalization is the use of normal as in the relational databases. A relation is in time normal form (TNF) if it's in BCNF and there is no temporal dependency among non-key attributes. For example Sal-Mgr can be divided into 2 columns as salary and manager which will have relation between them.

Need for time normalization:

Rows can be semantically independent of one another.

In un-normalized table there will rows with incomplete information and there they can be dependent on other rows to determine the whole information.

Some tables will not have complete information like the start time and end time and for those we need normalization as well.

Without normal form primary keys may be seen as duplicate at some point of time.

Implementation of the query would be complicated.

Temporal query language:

Temporal query language is called as TSQL which is designed for querying a temporal database. It is the superset of SQL which has new semantics and new components. We need to use some conditions to the normal SQL and in that way we follow the TSQL. They are conditional temporal expressions using the when clause and retrieval of timestamp values with or without computation.

Syntax of TSQL:


SELECT item_list

FROM table_name_list

WHEN temporal_comparison_list

WHERE search_condition_list;

Three types of operators are used in this querying language

Temporal Projection: Restriction applies to only non temporal attributes. Timestamp column will be excluded in the history. Adjoining intervals will be merged into a sine interval in the final result.

Temporal Selection: Comparison of time points and intervals using a WHEN clause. Syntax of WHEN clause would be: WHEN a interval_compare_operator b; Here a and b are the intervals and we will use the compare operator between the two intervals. Some of the compare operators are BEFORE, AFTER, OVERLAPS, FOLLOWS, DURING. Example: WHEN [a, b] BEFORE [c, d] if b<c. A sample query question is given below. 1) Find the salary of employee 10 when smith was his manager.

SELECT sal FROM s, m WHERE s.eno=m.eno AND m.eno=10 AND m.mgr='smith' WHEN s.interval OVERLAP m.interval. (S=Salary, M=Manager).

Temporal Join: Valid time intervals are created from the intersection of overlapping valid time elements of the tables joined together. Assume here that the valid time will be well defined before doing the joins.

Temporal Ordering:

Several versions of entity are associated with each time invariant key called as TIK. Each version will have a unique pair of timestamp that are associated with it. They have inherent order. Temporal ordering is done when all the rows or tuples with the same TIK are sorted in ascending order by their timestamp values. Sorting order can be done on the starting timestamp. A unique ordinal number is associated with all the rows of each TIK in the temporal ordering relation.


eno salr TS TE


1 25 16K 3 7

2 25 18K 8 13

3 25 21K 17 22

4 25 25K 23 26

5 25 31K 27 30

6 25 34K 33 35

7 25 40K 36 38

1 61 17K 4 8

2 61 25K 9 11

3 61 31K 12 17

1 73 18K 10 16

2 73 24K 17 22

3 73 30K 25 31

Find the time start and salary for employees who started with a salary exceeding 50K.


System Architecture:

TRA is the extension of the relation algebra and includes new operators.

User: interacts with TDBMS

Interface: On top of middleware. Allows user to make requests to TDBMS and displays the result.

Middleware: Contains the functionality of the system that processes the request via the interface and retrieves information. Modules of middleware are shown below

Relational Database System: Stores temporal data and behaves as a temporal database.

Temporal Annotations:

Some of the temporal annotations are

User defined time attributes: Each attribute is mapped to a column in the associated table

Entity life span: Life span or valid times of an associated attribute are captured for each entity in a table.

Relationship valid time: To each table corresponding to a relationship type with a recorded valid-time extent or having attribute(s) whose valid time is recorded, we add either instant or period timestamps.

Valid time of attributes: If some attributes have a valid time and if the lifespan of the associated entity or the valid time of the associated relationship is not recorded, the time-varying columns should be placed in a separate table, along with the primary key of the original table, which also serves as a foreign key to that table. 

Temporal Keys: The first consequence of adding valid-time support to a table is that the primary key of such tables needs to take the timestamp into consideration. Primary of the table must be unique. Example: Banking Database

- ACCOUNT: non temporal entity type

- INTEREST_TYPE: entity type with valid time

- TRANSACTION: event entity type. 

- BELONGS_TO: relationship type with valid time

- Balance: an attribute (of ACCOUNT) with valid time

Relational Schema:

 ACCOUNT (acc-no, date_opened,…)

INTEREST_TYPE (int-type, int_rate, ts, te)

TRANSACTION (transaction-no, event-time, event_date, type, amount, from-acc-no, to-acc-no,…)

BELONGS_TO (acc_no, int_type, ts, te)

BALANCE (acc-no, ts, te, balance)

Implementing Temporal Database application:

Two methods to implement temporal database are.

Method 1: Firstly, build a software layer which supports the temporal data model and its temporal query language on top of a RDBMS (That means the layer can analyze and process the temporal queries. The main advantage of this method is the possibility of reusing the services of the RDBMS). Then develop the database application by utilizing the layer.

Method 2: After understanding all the complexity of temporal databases, the developer develops the application using directly the SQL language and the host language supplied by RDBMS. That means he has to deal with all the complexity of temporal databases in the application programs.