CS240A: Databases and Knowledge Bases Temporal Databases

1. CS240A: Databases and Knowledge BasesTemporal Databases Carlo Zaniolo Department of Computer Science University of California, Los Angeles

2. Temporal Databases: Overview • Many applications • The problem is harder than what you think • Support for time in SQL: the good and the bad • A time ontology • Many approaches proposed • TSQL2 • The physical level: efficient storage and indexing techniques.

3. An Introduction to Temporal Databases • Applications abound • Queries on time­varying data are hard to express in SQL--A case study. • Solution: Temporal Database Systems • A Temporal Database System is one that provides built­in support for storing and querying time-varying information.

4. Applications Abound: Examples • Academic: Transcripts record courses taken in previous and the current semester or term and grades for previous courses • Accounting: What bills were sent out and when, what payments were received and when? • Delinquent accounts, cash flow over time • Money­management software such as Quickencan show e.g., account balance over time. • Budgets: Previous and projected budgets, multi­quarter or multi­year budgets

5. Temporal DB Applications (cont.) • Data Warehousing: Historical trend analysis for decision support • Financial: Stock market data • Audit: why were financial decisions made, and with what information available? • GIS: Geographic Information Systems () • Land use over time: boundary of parcels changeover time, as parcels get partitioned and merged. • Title searches • Insurance: Which policy was in effect at each point in time, and what time periods did that policy cover?

6. Temporal DB Applications (cont.) • Medical records: Patient records, drug regimes, lab tests.Tracking course of disease • Payroll: Past employees, employee salary history, salaries for future months, records of withholding requested by employees • Capacity planning for roads and utilities. Configuring new routes, ensuring high utilization • Project scheduling: Milestones, task assignments • Reservation systems: airlines, hotels, trains. • Scientific: Timestamping satellite images. Dating archeological finds

7. Temporal DBs Applications: Conclusion • It is difficult to identify applications that do not involve the management of temporal data. • These applications would benefit from built­in temporal support in the DBMS. Main benefits: • More efficient application development • Potential increase in performance

8. Reviewing the Situation • The importance of temporal applications has motivated much research work on temporal DBs, no completely satisfactory solution has been found yet: • SQL:1999 does not support temporal queries • Temporal DBs remain an open research problem. • The problem is much more difficult than it appears at first: we have become so familiar with the time domain that we tend to overlook its intrinsic complexity. • Some of the solutions proposed by researchers were too ambitious.

9. Case Study • University of Arizona's Office of Appointed Personnel has some information in a database. Employee(Name, Salary, Title) • Finding an employee's salary is easy. • The OAP wishes to add the date of birth Employee(Name, Salary, Title, DateofBirth DATE) SELECT Salary, DateofBirth FROM Employee WHERE Name = 'Bob‘SQL:1999 will do fine here, since support for the DATE type is all is needed

10. Case Study (cont.) • Now the OAP wishes to computerize the employment history. Employee (Name, Salary, Title, DateofBirth,Start DATE, Stop DATE) Converting to a Temporal Database

11. Converting to a Temporal Database Example Name Salary Title DateofBirth Start Stop AssistantProvost Bob 60000 1945­04­19 1993­01­01 1993­06­01 AssistantProvost Bob 70000 1945­04­19 1993­06­01 1993­10­01 Bob 70000 Provost 1945­04­19 1993­10­01 1994­02­01 Bob 70000 Professor 1945­04­19 1994­02­01 1995­01­01 • Now the OAP wishes to computerize the employment history. Employee (Name, Salary, Title, DateofBirth,Start DATE, Stop DATE)

12. Extracting the Salary • To find the employee's current salary, things are a bit more difficult. SELECT SalaryFROM EmployeeWHERE Name = 'Bob‘AND Start <= CURRENT_TIMESTAMPAND CURRENT_TIMESTAMP < Stop

13. Distributing the Salary History • OAP wants to distribute to all employees their salary history • Output: For each person, maximal intervals at each salary • Employee could have arbitrarily many title changes between salary changes Name Salary Start Stop Bob 60000 1993­01­01 1993­06­01Bob 70000 1993­06­01 1995­01­01

14. Extracting the Salary History (cont.) Alternative 1: Give the user a printout of Salary and Title information, and have user determine when his/her salary changed. Alternative 2: Use SQL as much as possible. Find those intervals that overlap or are adjacent and thus should be merged.

15. Bob’s Salary History in SQL CREATE TABLE Temp(Salary, Start, Stop)AS SELECT Salary, Start, StopFROM Employee WHERE Name = 'Bob'; repeatUPDATE Temp AS T1 SET (T1.Stop)=(SELECT MAX(T2.Stop) FROM Temp AS T2 WHERE T1.Salary = T2.Salary AND T1.Start < T2.Start AND T1.Stop >= T2.Start AND T1.Stop < T2.Stop) WHERE EXISTS (SELECT * FROM Temp AS T2 WHERE T1.Salary = T2.Salary AND T1.Start < T2.Start AND T1.Stop >= T2.Start AND T1.Stop < T2.Stop) until no tuples updated;

16. Example • Initial table • After one pass • After two passes

17. Salary History (cont.) • Intervals that are not maximal must be deleted DELETE FROM Temp T1WHERE EXISTS (SELECT * FROM Temp AS T2 WHERE T1.Salary = T2.Salary AND ((T1.Start > T2.Start AND T1.Stop <= T2.Stop) OR (T1.Start >= T2.Start AND T1.Stop < T2.Stop) ) The loop is executed lg N times in the worst case, where N is the number of tuples in a chain of overlapping or adjacent, value­equivalent tuples. Then delete extraneous, non­maximal intervals.

18. Alternative 3: Entirely in SQL CREATE TABLE Temp(Salary, Start, Stop) AS SELECT Salary, Start, Stop FROM Employee WHERE Name = 'Bob'; SELECT DISTINCT F.Salary, F.Start, L.StopFROM Temp AS F, Temp AS LWHERE F.Start < L.StopAND F.Salary = L.SalaryAND NOT EXISTS (SELECT * FROM Temp AS M WHERE M.Salary = F.Salary AND F.Start < M.Start AND M.Start < L.Stop AND NOT EXISTS (SELECT * FROM Temp AS T1 WHERE T1.Salary = F.Salary AND T1.Start < M.Start AND M.Start <= T1.Stop))AND NOT EXISTS (SELECT * FROM Temp AS T2 WHERE T2.Salary = F.Salary AND ( (T2.Start < F.Start AND F.Start <= T2.Stop) OR (T2.Start < L.Stop AND L.Stop < T2.Stop)))

19. Alternative 4: Using More Procedural Code • Use SQL only to open a cursor on the table • Maintain a linked list of intervals, each with a salary; Initialize this linked list to empty; DECLARE emp_cursor CURSOR FORSELECT Salary, Start, StopFROM Employee;OPEN emp_cursor;loop: FETCH emp_cursor INTO :salary,:start,:stop; if no data returned then go to finished;find position in linked list to insert this information; go to loop;finished:CLOSE emp_cursor;iterate through linked list, printing out dates and salaries

20. Extracting the Salary IN TSQL2 SELECT Salary FROM Employee WHERE Name = 'Bob'

21. A More Drastic Alternatives Reorganize the schema • Separate Salary, Title, and DateofBirth information: Employee1 (Name, Salary, Start DATE, Stop DATE) Employee2 (Name, Title, Start DATE, S top DATE) • Getting the salary information is now easy: SELECT Salary, Start, Stop FROM Employee1 WHERE Name = 'Bob‘ • But what if we want a table of salary, title intervals?

22. Name Salary Start Stop Temporal Projection andTemporal Joins Bob 60000 1993­01­01 1993­06­01 Bob 70000 1993­06­01 1995­01­01 Name Title Start Stop Bob AssistantProvost 1993­01­01 1993­10­01 Bob Provost 1993­10­01 1994­02­01 Bob FullProfessor 1994­02­01 1995­01­01 Name Salary Title Start Stop Bob 60000 AssistantProvost 1993­01­01 1993­06­01 Bob 70000 AssistantProvost 1993­06­01 1993­10­01 Bob 70000 Provost 1993­10­01 1994­02­01 Bob 70000 FullProfessor 1994­02­01 1995­01­01 Employee1: Employee2: Their Temporal Join:

23. SELECT Employee1.Name, Salary, Title,Employee1.Start, Employee1.Stop FROM Employee1, Employee2 WHERE Employee1.Name=Employee2.Name AND Employee2.Start <Employee1.Start AND Employee1.Stop <Employee2.Stop UNION ALL SELECT Employee1.Name, Salary, Title,Employee1.Start, Employee2.Stop FROM Employee1, Employee2 WHERE Employee1.Name =Employee2.Name AND Employee1.Start >Employee2.Start AND Employee2.Stop < Employee1.Stop AND Employee1.Start < Employee2.Stop UNION ALL SELECT Employee1.Name, Salary, Title Employee2.Start, Employee1.St FROM Employee1, Employee2 WHERE Employee1.Name = Employee2.Name AND Employee2.Start > Employee1.Start AND Employee1.Stop <= Employee2.Stop AND Employee2.Start < Employee1.Stop UNION ALL SELECT Employee1.Name, Salary, TitleEmployee2.Start, Employee2.Stop FROM Employee1, Employee2 WHERE Employee1.Name = Employee2 Name AND Employee2.Start => Employee1.Start AND Employee2.Stop <= Employee1.Stop AND NOT (Employee1.Start = Employee2.StartAND Employee1.Stop = Employee2.Stop) Temporal Join in SQL

24. Temporal Projection: SELECT Salary FROM Employee WHERE Name = ‘Bob’ Temporal Joins: SELECT Employee1.Name, Salary, Title FROM Employee1, Employee2 WHERE Employee1.Name = Employee2.Name TSQL2