1 / 29

CS240A: Databases and Knowledge Bases TSQL2

CS240A: Databases and Knowledge Bases TSQL2. 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.

sybil
Download Presentation

CS240A: Databases and Knowledge Bases TSQL2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 only • Temporal 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. ----------------------------Granularity: day, minute, etc.

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

  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. • The query specified a projection: thus a coalescing operation is performed internally 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—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

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

  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 '[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'

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

  18. 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 PrescriptionSET Dosage TO '50 mg‘VALID PERIOD '[1993/03/01 ­ 1993/07/30]‘WHERE Name = 'Melanie‘AND Drug = 'Proventil'

  19. Modification Statements 1993/01/01 1993/06/30 Original 30 30 INSERT INTOPrescription 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'))

  20. Modification Statements-cont 1993/01/01 03/01 05/31 06/30 1993/11/01 1994/04/30 Original 30 30 DELETE 30 30 30 yields DELETE FROM Prescription WHERE Name = 'Melanie‘ VALID PERIOD '[1993/03/01 ­ 1993/05/31]‘ VALID PERIOD '[1993/11/01 ­ 1994/04/30]‘ now

  21. Modification Statements (cont.) now Original 30 30 DELETE 30 30 30 yields UPDATE 30 30 50 30 yields UPDATE Prescription SET Dosage TO '50 mg‘ WHERE Name = 'Melanie‘ AND Drug = 'Proventil‘ now

  22. UPDATE PrescriptionSET Dosage TO '50 mg‘VALID PERIOD '[1993/03/01 ­ 1993/07/30]‘WHERE Name = 'Melanie‘AND Drug = 'Proventil' Update withValid Time now Original 30 30 DELETE 30 30 30 yields UPDATE 30 30 50 30 yields now UPDATE 50 with valid time 30 50 50 30 50 yields

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

  24. Event Tables, cont. • Patients who were the solereceivers ofall 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.

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

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

  27. 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 • There is also a new aggregate called RISING which evaluate to the longest period during which the specified attribute was rising.

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

  29. Discussion • A critique of TSQL2

More Related