Title: CS240A: Databases and Knowledge Bases A Taxonomy of Temporal DBs
1CS240A Databases and Knowledge BasesA Taxonomy
of Temporal DBs
- Carlo Zaniolo
- Department of Computer Science
- University of California, Los Angeles
2Valid 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
3Example 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).
4Queries 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'
6Overview of Temporal Data Models
- What is timestamped?
- Tuple timestamping
- Attributevalue timestamping
7Tuple 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
8Attribute 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
9What 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
10Desiderata 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
11Temporal 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.