Extensions to SQL for Historical Databases

Background

How would you model a database management system that maintains record of the changes that occur on its objects?

Example

model employee data
keep track of each employee's rank and salary
How would you do that?

What is HDBMS

Historical Database Management System
Provides timing information
Automatically maintains history
Allows for time-related queries

HSQL

An extension to SQL
Adds clauses and operations on the time domain
Adds facilities for retrospective updates and time-rollback

Why use HDBMS?

Tracking history
Time travel (undo/redo)
Debugging

Two types of Historical Relations

State

Attribute values prevail over an interval of time

Event

Attribute values prevail over one time instant only

Approaches

Add time as 3rd dimension to the 2d tables

2 types of time sampling

Instant Sampling
- use a time value of when the data became current
Interval Sampling
- use 2 time values to define an interval during which the data was current

2 ways of time stamping DB objects

Time sampling of tuples
Time sampling of attributes

Attribute Stamping vs. Tuple Stamping

Values of attributes within a relation may vary at different rates
Attribute Sampling allows each attribute to maintain its own history independently
It may be important for the application to track each attribute changes
Tuple sampling solution uses time normalization
Attribute Sampling adds more overhead

Proposed Solution

Interval stamping of tuples
Data are stored as conventional relations with 2 additional attributes for time-stamping
- From and To attributes
Designer needs to specify time granularity

State objects

The values of attributes of an object define its state
A change of value for any of its attributes represents a change of state
A state prevails over an interval of time, during which none of the attributes change their values
Designer needs to consider rate at which attributes change ("time normalization")

Event objects

Object prevails for only one time unit
An event becomes history as soon as it occurs
i.e. concept of history does not apply

CREATE STATE TABLE Employee (
  ENo CHAR(10) NOT NULL, /* Employee Number */
  Proj CHAR(10),         /* Project */
  Sal DECIMAL(5)         /* Salary */
  UNIQUE(ENo))
WITH TIME GRANULARITY DATE;

HDBMS will add 2 more attributes "From" and "To" whose values define a nonnull interval of time

Employee will actually be stored as 2 segments defined by the schemes:

CurrentEmp (ENo, Proj, Sal, From, To)
HistoryEmp (ENo, Proj, Sal, From, To)

The value of the attribute To in CurrentEmp will always be the current time value, represented by the keyword NOW
```
Event type relations don't have this segmentation
```
- Instead, they have single additional attribute At, instead of From and To.

Adding & Modifying Example

At time t1, insert
<"Smith", "Lotus", 30000>
- Adds to the current segment
  <"Smith", "Lotus", 30000, t1, NOW>
At time t2, change salary to 40000
- Replace the tuple in the current segment with
  <"Smith", "Lotus", 40000, t2, NOW>
- Adds to the history segment
  <"Smith", "Lotus", 30000, t1, t2 - 1>
- t2 - 1 is one instant (of the same granularity) before t2
At time t3, delete Smith from the table
- Deletes the tuple from the current segment
- Adds to the history segment
  <"Smith", "Lotus", 30000, t2, t3 - 1>

Time Operations

Instant comparisons: <, >, =, <=, >=, !=

t IN i: if t is included in interval i
t1 = t2: if both intervals include the same set of time instants
i1 OVERLAP i2: if both intervals include at least one common instant
i1 CONTAINS i2: if all instants of i2 are also contained in i1
i1 MEETS i2: when i1 is t1..t2 and i2 is t2+1..t3
i1 ADJACENT i2: (i1 MEETS i2) OR (i2 MEETS i1)
i1 PRECEDES i2: when i1 is t1..t2 and i2 is t3..t4 and t2 < t3

Interval comparisons (t is instant and i is interval):

"..": make interval given two time instants
"+": concatenate two overlapping or consecutive intervals
"*": extract common part of two overlapping intervals