Transcript Transaction Time, cont.

CS240A: Databases and Knowledge Bases
TSQL2
Carlo Zaniolo
Department of Computer Science
University of California, Los Angeles
Revised: February 2003
Notes From Chapter 6 of
Advanced Database
Systems by Zaniolo, Ceri,
Faloutsos, Snodgrass,
Subrahmanian and Zicari.
Morgan Kaufmann, 1997
TSQL2 Language Constructs
Schema Specification
 Snapshot Queries
 The FROM Clause---Restructuring
 ValidTime Selection
 ValidTime Projection
 Modification Statements
 Event Tables
 Transaction Time
 Aggregates
 The NOW construct

Many other constructs: Temporal Indeterminacy, Granularity,Schema
Versioning, etc.
Schema Specification

Scenario: Patient records include information on the drugs
prescribed to each patient.

The valid time specifies the period(s) during which the
drug was prescribed.

The valid time has a granularity of day (transaction time
granularity is system defined):
CREATE TABLE Prescription
(Name, Physician, Drug, Dosage,
Frequency INTERVAL MINUTE)
AS VALID DAY
AND TRANSACTION
Six Different Kinds of Tables

Snapshot table: nothing after the attributes

Validtime state table: AS VALID [ STATE ] <granularity>

Validtime event table: AS VALID EVENT <granularity>

Transactiontime table: AS TRANSACTION

Bitemporal state table:
AS VALID [ STATE ] <granularity> AND TRANSACTION

Bitemporal event table:
AS VALID EVENT <granularity> AND TRANSACTION

The type of a table can be changed at any time, using the
ALTER statement.
Snapshot Queries

Who has been prescribed drugs?
SELECT SNAPSOT Name
FROM Prescription
 Result
is a list of names of those with current or past
prescriptions.

Who is or was taking the drug Proventil?
SELECT SNAPSHOT Name
FROM Prescription
WHERE Drug = 'Proventil‘

Result is a list of names.
Valid Time History Queries
Can request history, rather than just current state.
 Who has been prescribed drugs, and when?

SELECT Name
FROM Prescription
Result is a list of names, each associated with one
or more maximal periods.
 Since we have projection: coalescing is used to
compute those maximal periods.

Temporal Joins

What drugs have been prescribed with Proventil?
SELECT P1.Name, P2.Drug
FROM Prescription AS P1, Prescription AS P2
WHERE P1.Drug = 'Proventil‘
AND P2.Drug <> `Proventil‘
AND P1.Name = P2.Name

Result is a list of patient names and drugs, along
with their associated maximal period(s).
Temporal Projection

Who has been on a drug for more than a total of
six months?
SELECT Name, Drug
FROM Prescription(Name, Drug) AS P
WHERE CAST(VALID(P) AS INTERVAL MONTH)
> INTERVAL '6' MONTH

Result will contain the maximal interval(s) when
the patient has been on the drug---provided that
the sum of all those intervals exceeds 6 months.
Restructuring

Nested SELECT projects on the specified
attributes, then automatically coalesces the result.
FROM R(B, C) AS MyR
is equivalent to
FROM (SELECT B, C FROM R) AS MyR
Other attributes in R are not accessible via MyR,
and A is not accessible in the enclosing SELECT.
Restructuring:
coupling of correlation names

Who has been on Proventil throughout their drug regime?
SELECT SNAPSHOT P1.Name
FROM Prescription(Name) AS P1, P1(Drug) AS P2
WHERE P2.Drug = 'Proventil’
AND VALID(P2) = VALID(P1)

P1 contains all the times that any drug has been
prescribed to a patient.

P2 is coalesced on the Name and Drug columns. Also, the
values of the Name column for both P1 and P2 are
identical.
Restructuring:
very useful syntactic sugar


SELECT ...
FROM A(B,C,D) AS A2, A2(E,F) AS A3
WHERE ...
is equivalent to: SELECT ...
FROM (SELECT B,C,D FROM A) AS A2,
(SELECT B,C,D,E,F FROM A) AS A3
WHERE ...
AND A2.B = A3.B AND A2.C=A3.C
AND A2.D = A3.D
AND VALID(A2) OVERLAPS VALID(A3)
Intuitively, A2 is timestamped with a temporal element
when attributes B, C, and D remained constant, and A3
ranges over different values of D and E, with the timestamp
of A3 being a subset of that of A2.
Partitioning

Who has been on the same drug for more than six
consecutive months?
SELECT Name, Drug
FROM Prescription(Name, Drug) AS (Period) P
WHERE CAST(VALID(P) AS INTERVAL MONTH)
> INTERVAL '6' MONTH

P ranges over NameDrug pairs associated with individual
maximal periods.

The result may contain several rows with the same Name
and Drug values.
Partitioning, cont.

Partitioning is more than syntactic sugar.
 It
violates the data model, in that it produces valueequivalent tuples that the correlation name ranges over.


Note, however, that underlying tables and the result
table are always coalesced so that the violation is
temporary and internal to a query.
Useful for queries specifying “consecutive time” or
“continuous periods”.
ValidTime Projection

What drugs was Melanie prescribed during 1994?
SELECT Drug
VALID INTERSECT(VALID(Prescription),
PERIOD '[1994]' DAY)
FROM Prescription
WHERE Name = 'Melanie‘

The result is a list of drugs, each associated with a set of
the periods during 1994 that they were prescribed to
Melanie.

A new clause, the VALID clause, specifies the timestamp of
the resulting tuple.
Modification Statements

The valid times can be specified for rows that are being
inserted.

Inserted values will be coalesced with existing valueequivalent rows.

Default valid clause:
INSERT INTO Prescription
VALUES ('Sally','Dr. Beren','Proventil','100 mg',
INTERVAL '8:00' MINUTE)
VALID PERIOD '[1993/01/01 1993/06/30]‘
VALID PERIOD(CURRENT_TIMESTAMP,
NOBIND(TIMESTAMP 'now'))

An INSERT statement with a subquery is handled in the
same manner.
Modification Statements, cont.

The delete statement removes period(s) from the temporal
element of the qualifying row(s).

The columns of a row can be changed, as in SQL92.
DELETE FROM Prescription
WHERE Name = 'Melanie‘
VALID PERIOD '[19930601 19930630]‘

If the temporal element becomes empty, the row itself is
deleted.
UPDATE Prescription
SET Dosage TO '50 mg‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil'
Modification statements, cont.

The valid time of a row can also be changed:
UPDATE Prescription
SET Dosage TO '50 mg‘
VALID PERIOD '[19930301 19930530]'WHERE Name
= 'Melanie‘
AND Drug = 'Proventil'
Modification Statements (Con’t)
30
Original
30
now
DELETE
yields
UPDATE
yields
30
30
30
30
30
30 50
UPDATE
with valid time
yields
50
30
50
50
30 50
Event Tables

Event tables are timestamped with instant sets.

Each row identifies a particular kind of (instantaneous)
event, with the timestamp of that row specifying the
instant(s) when that event occurred.

Event tables may also be associated with transaction time:
CREATE TABLE LabTest (Name, Physician, TestID)
AS VALID EVENT HOUR AND TRANSACTION
Event Tables, cont.
Were any patients the sole receivers of tests ordered by a
physician?
SELECT L1.Name, L2.Physician
FROM LabTest(Name) AS L1, L1(Physician) AS L2,
LabTest(Physician) AS L3
WHERE VALID(L1) = VALID(L2)
AND L2.Physician = L3.Physician
AND VALID(L1) = VALID(L3)
 VALID(L1) is an event set containing all tests done to a
particular patient
 VALID(L2) contains the times of all tests done to a
particular patient and ordered by a particular physician
 VALID(L3) is an event set containing all tests ordered by a
particular physician.

Transaction Time


If transaction time is not mentioned in the query, the
results include only the information currently believed to be
true. E.g.: What is Melanie's prescription history?
SELECT Drug
FROM Prescription
WHERE Name = 'Melanie'
Can also rollback the database. E.g.: What did the
physician believe on June 1, 1994 was Melanie's
prescription history?
SELECT Drug
FROM Prescription AS P
WHERE Name = 'Melanie‘
AND TRANSACTION(P) OVERLAPS DATE '19940601‘
Transaction Time, cont.

Auditing can be done on previously corrected data.

When was Melanie's data valid on January 1, 1993 last
corrected?
SELECT SNAPSHOT BEGIN(TRANSACTION(P2))
FROM Prescription AS P P2
WHERE P1.Name = 'Melanie' AND P1.Name = P2.Name
AND VALID(P1) OVERLAPS DATE '19930101‘ AND
VALID(P2) OVERLAPS DATE '19930101‘
AND TRANSACTION(P1) MEETS TRANSACTION(P2)
Transaction Time, cont.
The transaction timestamp is specified with
TRANSACTION(name), and evaluates to a period.
 What was recorded as current on March 1 about
Melanie?
 The default is
SELECT SNAPSHOT Drug
FROM Prescription AS P
WHERE Name = 'Melanie‘
AND TRANSACTION(P) OVERLAPS DATE '19950301‘
AND VALID(P) OVERLAPS DATE '19950301‘
AND TRANSACTION(...) OVERLAPS
CURRENT_TIMESTAMP

Aggregates

SQL92 supports the aggregates MIN, MAX,
COUNT, SUM, and AVG, plus a new temporal
aggregate called RISING.

Snapshot reducibility implies that these
aggregates return timevarying results when
applied to temporal tables.

The aggregate is applied to each snapshot in turn.

Evaluation can be optimized, as many snapshots
will contain the same rows, and hence evaluate to
the same result.
Aggregates, cont.

How many drugs is/was Melanie taking?
SELECT COUNT(*)
FROM Prescription
WHERE Name = 'Melanie‘
AND Drug = 'Proventil’

How many people are taking each drug?
SELECT Drug, COUNT(*)
FROM Prescription
GROUP BY Drug.

Again, the result is a temporal table.
Discussion

A critique of TSQL2