Transcript ch5c
CS240A: Databases and Knowledge Bases A Taxonomy of Temporal DBs Carlo Zaniolo Department of Computer Science University of California, Los Angeles 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: 1. Snapshot 2. Valid-time 3. Transaction-time 4. Bitemporal 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). 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)? Bitemporal Tables Bitemporal Tables are appendonly 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 '19841001‘ AND TRANSACTION(F) OVERLAPS DATE '19841001' Overview of Temporal Data Models What is timestamped? Tuple timestamping Attributevalue timestamping Tuple Timestamping and Coalescing Time stamping the individual tuples: deptno start end Engineer d01 1995-01-01 1995-05-31 70000 Engineer d01 1995-06-01 1995-09-30 10003 70000 Sr Engineer d02 1995-10-01 1996-01-31 10003 70000 Tech Leader d02 1996-02-01 1996-12-31 name empno salary title Bob 10003 60000 Bob 10003 Bob Bob If we want the salary history, we have to coalesce the last three tuples into one: name empno salary start end Bob 10003 60000 1995-01-01 1995-05-31 Bob 10003 70000 1995-06-01 1996-12-31 Attribute Timestamping Time-stamped tuples in relations name empno salary title deptno start end Bob Bob Bob Bob 10003 10003 10003 10003 60000 70000 70000 70000 Engineer Engineer Sr Engineer Tech Leader d01 d01 d02 d02 1995-01-01 1995-06-01 1995-10-01 1996-02-01 1995-05-31 1995-09-30 1996-01-31 1996-12-31 Time-stamped name empno attributes: Temporal grouping salary title 60000 Engineer 1995-01-01: 1996-05-31 Bob 1995-01-01: 1996-12-31 deptno d01 1995-01-01: 1995-09-30 1995-01-01: 1995-09-30 10003 1995-01-01: 1996-12-31 70000 1995-06-01:1996-12-31 Sr Engineer 1995-10-01:1996-01-31 Tech Leader 1996-02-01: 1996-12-31 d02 1995-10-01: 1996-12-31 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 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 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.