CS240A: Databases and Knowledge Bases TSQL2

1. CS240A: Databases and Knowledge BasesTSQL2 Notes From Chapter 6 of Advanced Database Systems by Zaniolo, Ceri, Faloutsos, Snodgrass, Subrahmanian and Zicari. Morgan Kaufmann, 1997 Carlo Zaniolo Department of Computer Science University of California, Los Angeles

2. TSQL2 Language Constructs • Schema Specification • Snapshot Queries • The FROM Clause---Restructuring • Valid­Time Selection • Valid­Time Projection • Modification Statements • Event Tables • Transaction Time • Aggregates • The NOW construct Many other constructs: Temporal Indeterminacy, Granularity,Schema Versioning, etc.

3. 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

4. Six Different Kinds of Tables • Snapshot table: nothing after the attributes • Valid­time state table: AS VALID [ STATE ] <granularity> • Valid­time event table: AS VALID EVENT <granularity> • Transaction­time 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.

5. 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.

6. 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.

7. 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).

8. Temporal Projection • Who has been on a drug for more than a total of six months? SELECT Name, DrugFROM Prescription(Name, Drug) AS PWHERE 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.

9. 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.

10. 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.

11. 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.

12. 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 Name­Drug pairs associated with individual maximal periods. • The result may contain several rows with the same Name and Drug values.

13. Partitioning, cont. • Partitioning is more than syntactic sugar. • It violates the data model, in that it produces value­equivalent 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”.

14. Valid­Time 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.

15. Modification Statements • The valid times can be specified for rows that are being inserted. • Inserted values will be coalesced with existing value­equivalent 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.

16. 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 SQL­92. DELETE FROM Prescription WHERE Name = 'Melanie‘ VALID PERIOD '[1993­03­01 ­ 1993­05­31]‘ VALID PERIOD '[1994­03­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'

17. Modification statements, cont. • The valid time of a row can also be changed: UPDATE PrescriptionSET Dosage TO '50 mg‘VALID PERIOD '[1993­03­01 ­ 1993­07­30]'WHERE Name = 'Melanie‘AND Drug = 'Proventil'

18. Modification Statements (Con’t) Original 30 30 now DELETE 30 30 30 yields UPDATE 30 30 30 50 yields UPDATE 50 with valid time 30 50 50 30 50 yields

19. 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

20. Event Tables, cont. • Were any patients the sole receivers of tests ordered by a physician? SELECT L1.Name, L2.PhysicianFROM LabTest(Name) AS L1, L1(Physician) AS L2,LabTest(Physician) AS L3WHERE VALID(L1) = VALID(L2)AND L2.Physician = L3.PhysicianAND 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.

21. 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 '1994­06­01‘

22. 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 P2WHERE P1.Name = 'Melanie' AND P1.Name = P2.NameAND VALID(P1) OVERLAPS DATE '1993­01­01‘ AND VALID(P2) OVERLAPS DATE '1993­01­01‘AND TRANSACTION(P1) MEETS TRANSACTION(P2)

23. 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 '1995­03­01‘ AND VALID(P) OVERLAPS DATE '1995­03­01‘ AND TRANSACTION(...) OVERLAPS CURRENT_TIMESTAMP

24. Aggregates • SQL­92 supports the aggregates MIN, MAX, COUNT, SUM, and AVG, plus a new temporal aggregate called RISING. • Snapshot reducibility implies that these aggregates return time­varying 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.

25. 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.

26. Discussion • A critique of TSQL2