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., Toms
  past record is changed once his rank is changed
  retroactively.
• Valid time tables support historical queries,
  such as
• What was Toms rank on October 1 (according to
  our current database)?

5
Bitemporal Tables

• Bitemporal Tables are appendonly and supports
  queries of both kinds (rollbackhistorical) 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
  '19841001 AND TRANSACTION(F) OVERLAPS DATE
  '19841001'

6
Overview of Temporal Data Models

• What is timestamped?
• Tuple timestamping
• Attributevalue timestamping

7
Tuple Timestamping and Coalescing

• Time stamping the individual tuplesIf we
  want the salary history, we have to coalesce the
  last three tuples into one

name empno salary title deptno start end
Bob 10003 60000 Engineer d01 1995-01-01 1995-05-31
Bob 10003 70000 Engineer d01 1995-06-01 1995-09-30
Bob 10003 70000 Sr Engineer d02 1995-10-01 1996-01-31
Bob 10003 70000 Tech Leader d02 1996-02-01 1996-12-31
name empno salary start end
Bob 10003 60000 1995-01-01 1995-05-31
Bob 10003 70000 1995-06-01 1996-12-31
8
Attribute Timestamping-

• Time-stamped tuples in relations

name empno salary title deptno start end
Bob 10003 60000 Engineer d01 1995-01-01 1995-05-31
Bob 10003 70000 Engineer d01 1995-06-01 1995-09-30
Bob 10003 70000 Sr Engineer d02 1995-10-01 1996-01-31
Bob 10003 70000 Tech Leader d02 1996-02-01 1996-12-31

• Time-stamped attributes Temporal grouping

name empno salary title deptno
Bob 1995-01-01 1996-12-31 10003 1995-01-01 1996-12-31 60000 1995-01-01 1996-05-31 Engineer 1995-01-01 1995-09-30 d01 1995-01-01 1995-09-30
Bob 1995-01-01 1996-12-31 10003 1995-01-01 1996-12-31 70000 1995-06-011996-12-31 Engineer 1995-01-01 1995-09-30 d01 1995-01-01 1995-09-30
Bob 1995-01-01 1996-12-31 10003 1995-01-01 1996-12-31 70000 1995-06-011996-12-31 Sr Engineer 1995-10-011996-01-31 d02 1995-10-01 1996-12-31
Bob 1995-01-01 1996-12-31 10003 1995-01-01 1996-12-31 70000 1995-06-011996-12-31 Tech Leader 1996-02-01 1996-12-31 d02 1995-10-01 1996-12-31
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
  representationavoids coalescing)
• Plethora of data models and query language
  extensions thus proposed

10
Desiderata for a Temporal Data Model

• Capture the semantics of timevarying 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 DatabasesState 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.