TEMPORAL QUERYING CONSTRUCTS AND THE TSQL2 LANGUAGE

The TSQL2 language, extends SQL for querying valid time, transaction time, and bitemporal relational databases.

 TEMPORAL  DATABASES

In a temporal database, the conditions may involve time in addition to attributes.

A pure time condition involves only time

PURE TIME CONDITIONS:

  Suppose a user wants to select all employee versions that were valid at any point during 1997.

                 SELECT NAME

                 FROM   EMPLOYEE

                 WHERE   [t.VST, t.VET] OVERLAPS [1997-01-01, 1997-12-31

ATTRIBUTE AND TIME CONDITION

Suppose we wish to retrieve all EMP_VT tuple versions t for

employees who worked in department 5 at any time during 1997.

 SELECT  *  FROM  EMPLOYEE WHERE

 ([t.VST, t.VET] OVERLAPS [1997-01-01, 1997-12-31]) AND (t.DNO = 5)

3.3   Common operations used in queries

[t.VST, t.VET] INCLUDES [t1, t2]              Equivalent to  (t1>= t.VST AND t2<= 1 t.VET )

[t.VST, t.VET] INCLUDED_IN [t1, t2]      Equivalent to (t1 <=1 t.VST AND t2>= t.VET)

[t.VST, t.VET] OVERLAPS [t1, t2]                         Equivalent to (t1 1<= t.VET AND t2>= t.VST) 

[t.VST, t.VET] BEFORE [t1, t2]                   Equivalent to   (t1 >=t.VET)

[t.VST, t.VET] AFTER [t1, t2]                     Equivalent to   (t2<= t . VST)            

[t.VST, t.VET] MEETS_BEFORE [t1, t2]      Equivalent to   (t1 = t.VET + 1 )   

[t.VST, t.VET] MEETS_AFTER [t1, t2]      Equivalent to   (t2 + 1 = t.VST )

           

For any two time periods [t1, t2] and [t3, t4] in a temporal element, the

following three conditions must hold:

            • [t1, t2] intersection [t3, t4] is empty.

            • t3 is not the time point following t2 in the given granularity.

            • t1 is not the time point following t4 in the given granularity.

COALESCING OF TIME PERIODS

             If two time periods [t1, t2] and [t3, t4] are adjacent, they are combined into a single time period [t1, t4].

SELECT   OPERATION

    select_item_list

   FROM table_name_list

   WHEN temporal_comparison_list

   WHERE search_condition_list

INSERT OPERATION:

INSERT INTO  <table-name> (<column-name-list>) VALUES <field-name values>

DELETION  OPERATION :

DELETE  FROM <table-name>

            WHEN     <valid-time>

            WHERE  < condition>

UPDATE  OPERATION:

UPDATE <table name> SET <attribute-name=new value>

WHEN <valid_time> WHERE <condition>

CREATE  TABLE

The CREATE TABLE statement is extended with an optional AS-clause to allow users to declare different temporal options. The following options are available:

 AS VALID STATE <granularity>            (valid time relation with valid time period)

 AS VALID EVENT <granularity>            (valid time relation with valid time point)

 AS TRANSACTION             (transaction time relation with transaction time period)

 AS VALID STATE <granularity> AND TRANSACTION    (bitemporal relation, valid time period)

 AS VALID EVENT <granularity> AND TRANSACTION     (bitemporal relation, valid time point)