CS240A: Databases and Knowledge Bases A Taxonomy of Temporal DBs

1. CS240A: Databases and Knowledge BasesA Taxonomy of Temporal DBs Carlo Zaniolo Department of Computer Science University of California, Los Angeles

2. Valid Time and Transaction Time • Valid Time of a fact: when the fact is true in the modeled reality • Transaction Time of a fact: when it was recorded in the database • Thus we have four different kinds of tables: • Snapshot • Valid-time • Transaction-time • Bitemporal

3. Example: Tom's Employment History • On January 1, 1984, Tom joined thefaculty as an Instructor. • On December 1, 1984, Tom completed his doctorate, and so was promoted to Assistant Professor effective retroactively on July 1, 1984. • On March 1, 1989, Tom was promoted to Associate Professor, effective July 1, 1989 (proactive update).

4. Queries and Updates • A transaction time table is append only it keeps the history of the updates made on the database. • Transaction time tables supports rollback queries, such as: • On October 1, what rank was our database showing for Tom? • A valid time table can be updated: e.g., Tom’s past record is changed once his rank is changed retroactively. • Valid time tables support historical queries, such as: • What was Tom’s rank on October 1 (according to our current database)?

5. Bitemporal Tables • Bitemporal Tables are append­only and supports queries of both kinds (rollback&historical) such as: • On October 1, 1984, what did we think Tom's rank was at that date? • TSQL3: SELECT Rank FROM Faculty AS F WHERE Name = 'Tom‘ AND VALID(F) OVERLAPS DATE '1984­10­01‘ AND TRANSACTION(F) OVERLAPS DATE '1984­10­01'

6. Overview of Temporal Data Models • What is timestamped? • Tuple timestamping • Attribute­value timestamping

7. Tuple Timestamping and Coalescing • Time stamping the individual tuples:If we want the salary history, we have to coalesce the last three tuples into one:

8. Attribute Timestamping- • Time-stamped tuples in relations • Time-stamped attributes: Temporal grouping

9. What Is Timestamped? • The value of an individual attributes: temporally grouped data models. • Individual tuples • Set of tuples: Generally used for transaction time, to timestamp a set of tuples inserted or modified by a transaction. • Object: O-O DBs, XML documents • Object graph: E.g., associate a connected set of modules (a configuration) with a particular version identifier. • Schema Item: support for schema versions represents a difficult and important problem. • Granularity of time-stamps: • Maximum continuous periods • Set of periods • Single instant in time (point-based representation—avoids coalescing) • Plethora of data models and query language extensions thus proposed

10. Desiderata for a Temporal Data Model • Capture the semantics of time­varying information • Retain simplicity of the relational model: Strict superset of the relational model • Present all the information concerning an object in a coherent fashion • Ensure ease of implementation • Ensure high performance

11. Temporal Databases:State of the Art • Over 40 temporal data models and associated temporal query languages have been defined. • Design space has been fairly well covered. • A single data model satisfying all desirable objectives appears to be unattainable • TSQL2: a consensus approach proposed for inclusion in SQL3 standards. • TSQL2 supports valid time, transaction-time and bitemporal relations, and • Uses set of periods as its basic representation for time.