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

Interval Operations

HSQL

By Mujtaba Al-Tameemi

Made with Slides.com

HSQL

  • 225

More from Mujtaba Al-Tameemi