Transcript slides - UCLA Computer Science

CS240A: Databases and Knowledge Bases
TSQL2
Carlo Zaniolo
Department of Computer Science
University of California, Los Angeles
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 only
 Temporal 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.
---------------------------Granularity: day, minute, etc.
Queries: Snapshots

Who has been prescribed drugs?
SELECT SNAPSHOT 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 of people who ever took
Proventil
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.

The query specified a projection: thus a coalescing
operation is performed internally 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—explicit projection
Projects on the specified attributes, then automatically coalesces
the result:
FROM R(A, B) AS MyR is equivalent to:
FROM (SELECT A, B FROM R) AS MyR
Other attributes in R are not accessible via MyR, and A is not accessible in the
enclosing SELECT.
Restructuring declarations can also be cascaded, but semantics is quirky:
FROM R(A, B) AS MyR, MyR(C) AS nextR
is equivalent to
FROM (SELECT A, B FROM R) AS MyR,
FROM (SELECT A, B,C FROM R) AS nextR
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.
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.
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
deviates from 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.
ValidTime Restriction

What will this query do?
SELECT Drug
FROM Prescription
WHERE Name = 'Melanie‘
AND INTERSECT(VALID(Prescription),
PERIOD '[1994]' DAY)

The result is a list of drugs, each associated with a set of
the periods were prescribed to Melanie—under the
condition that she was on medication during 1994.
Modification Statements—for valid periods

The valid times can be specified for rows that are being
inserted.

Inserted values will be coalesced with existing valueequivalent rows.
INSERT INTO Prescription
VALUES (‘Melanie','Dr. Beren','Proventil',‘30 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 '[1993/03/01 1993/05/31]‘
VALID PERIOD '[1993/11/01 1994/04/30]‘

If the temporal element becomes empty, the row itself is deleted (no
valid time specified means ‘from now’)
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 '[1993/02/01 1993/07/30]‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil'
Satements at Execution Time
INSERT INTO Prescription
VALUES (‘Melanie','Dr. Beren','Proventil',‘ 30 mg',
INTERVAL '8:00' MINUTE)
VALID PERIOD '[1993/01/01 1993/06/30]‘
VALID PERIOD(CURRENT_TIMESTAMP, NOBIND(TIMESTAMP
'now'))
---say that this insert occurred at the end of 1993--DELETE FROM Prescription
WHERE Name = 'Melanie‘
VALID PERIOD '[1993/03/01 1993/05/31]‘
VALID PERIOD '[1993/11/01 1994/04/30]‘
UPDATE Prescription
SET Dosage TO '50 mg‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil‘
UPDATE Prescription
SET Dosage TO '50 mg‘
VALID PERIOD '[1993/03/01 1993/07/30]‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil'
Modification Statements
1993/01/01
Original
1993/06/30
30
INSERT INTO Prescription
VALUES (‘Melanie','Dr. Beren','Proventil',‘ 30 mg',
INTERVAL '8:00' MINUTE)
VALID PERIOD '[1993/01/01 1993/06/30]‘
VALID PERIOD(CURRENT_TIMESTAMP,
NOBIND(TIMESTAMP 'now'))
30
Modification Statements-cont
1993/01/01
03/01
05/31
06/30
1993/11/01 1994/04/30
30
Original
30
DELETE
yields
30
30
30
DELETE FROM Prescription
WHERE Name = 'Melanie‘
VALID PERIOD '[1993/03/01 1993/05/31]‘
VALID PERIOD '[1993/11/01 1994/04/30]‘
now
Modification Statements (cont.)
now
30
Original
30
DELETE
yields
UPDATE
yields
30
30
30
30
30
30
UPDATE Prescription
SET Dosage TO '50 mg‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil‘
50
now
UPDATE Prescription
SET Dosage TO '50 mg‘
VALID PERIOD '[1993/03/01 1993/07/30]‘
WHERE Name = 'Melanie‘
AND Drug = 'Proventil'
Update with
Valid Time
now
30
Original
30
DELETE
yields
UPDATE
yields
30
30
30
30
30
30
now
UPDATE
with valid time
yields
50
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.

Patients who were the sole receivers of all 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.

The transaction timestamp is specified with TRANSACTION(name),
and evaluates to a period.

What was recorded as current on March 1, 1995 about Melanie?
SELECT SNAPSHOT Drug
FROM Prescription AS P
WHERE Name = 'Melanie‘
AND TRANSACTION(P) OVERLAPS DATE '19950301‘
AND VALID(P) OVERLAPS DATE '19950301‘
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
There is also a new aggregate called RISING which
evaluate to the longest period during which the
specified attribute was rising.
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