CS 240 A: Databases and Knowledge Bases Temporal Databases Carlo Zaniolo Department of Computer Science University of California, Los Angeles
Temporal Databases: Overview Many The applications problem is harder than what you think Support A for time in SQL: the good and the bad time ontology Many approaches proposed TSQL 2 The physical level: efficient storage and indexing techniques.
An Introduction to Temporal Databases Applications abound Queries on time varying data are hard to express in SQL--A case study. Solution: u. A Temporal Database Systems Temporal Database System is one that provides built in support for storing and querying timevarying information.
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? u Delinquent accounts, cash flow over time u Money management software such as. Quickencan show e. g. , account balance over time. Budgets: Previous and projected budgets, multi quarter or multi year budgets
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 () u Land use over time: boundary of parcels changeover time, as parcels get partitioned and merged. u Title searches Insurance: Which policy was in effect at each point in time, and what time periods did that policy cover?
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
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: u More efficient application development u Potential increase in performance
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 u Temporal DBs remain an open research problem. u 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.
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, Dateof. Birth DATE) SELECT Salary, Dateof. Birth FROM Employee WHERE Name = 'Bob‘ SQL: 1999 will do fine here, since support for the DATE type is all is needed
Case Study (cont. ) Now the OAP wishes to computerize the employment history. Employee (Name, Salary, Title, Dateof. Birth, Start DATE, Stop DATE) Converting to a Temporal Database
Converting to a Temporal Database Example Now the OAP wishes to computerize the employment history. Employee (Name, Salary, Title, Dateof. Birth, Start DATE, Stop DATE) Name Salary Bob Bob 60000 70000 Title Dateof. Birth Start Stop Assistant. Prov 1945 04 19 1993 01 01 1993 06 01 ost Assistant. Prov 1945 04 19 1993 06 01 1993 10 01 ost Provost Professor 1945 04 19 1993 10 01 1994 02 01 1945 04 19 1994 02 01 1995 01 01
Extracting the Salary To find the employee's current salary, things are a bit more difficult. SELECT Salary FROM Employee WHERE Name = 'Bob‘ AND Start <= CURRENT_TIMESTAMP AND CURRENT_TIMESTAMP < Stop
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 Bob 60000 70000 Start Stop 1993 01 01 1993 06 01 1995 01 01
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.
Bob’s Salary History in SQL CREATE TABLE Temp(Salary, Start, Stop) AS SELECT Salary, Start, Stop FROM Employee WHERE Name = 'Bob'; repeat UPDATE Temp AS T 1 SET (T 1. Stop)=(SELECT MAX(T 2. Stop) FROM Temp AS T 2 WHERE T 1. Salary = T 2. Salary AND T 1. Start < T 2. Start AND T 1. Stop >= T 2. Start AND T 1. Stop < T 2. Stop) WHERE EXISTS (SELECT * FROM Temp AS T 2 WHERE T 1. Salary = T 2. Salary AND T 1. Start < T 2. Start AND T 1. Stop >= T 2. Start AND T 1. Stop < T 2. Stop) until no tuples updated;
Example Initial table After one pass After two passes
Salary History (cont. ) Intervals that are not maximal must be deleted DELETE FROM Temp T 1 WHERE EXISTS (SELECT * FROM Temp AS T 2 WHERE T 1. Salary = T 2. Salary AND ((T 1. Start > T 2. Start AND T 1. Stop <= T 2. Stop) OR (T 1. Start >= T 2. Start AND T 1. Stop < T 2. 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 equivalenttuples. Then delete extraneous, non maximal intervals.
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. Stop FROM Temp AS F, Temp AS L WHERE F. Start < L. Stop AND F. Salary = L. Salary AND 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 T 1 WHERE T 1. Salary = F. Salary AND T 1. Start < M. Start AND M. Start <= T 1. Stop)) AND NOT EXISTS (SELECT * FROM Temp AS T 2 WHERE T 2. Salary = F. Salary AND ( (T 2. Start < F. Start AND F. Start <= T 2. Stop) OR (T 2. Start < L. Stop AND L. Stop < T 2. Stop)))
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 FOR SELECT Salary, Start, Stop FROM 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
Extracting the Salary IN TSQL 2 SELECT Salary FROM Employee WHERE Name = 'Bob'
A More Drastic Alternatives Reorganize the schema Separate Salary, Title, and Dateof. Birth information: Employee 1 (Name, Salary, Start DATE, Stop DATE) Employee 2 (Name, Title, Start DATE, S top DATE) Getting the salary information is now easy: SELECT Salary, Start, Stop FROM Employee 1 WHERE Name = 'Bob‘ But what if we want a table of salary, title intervals?
Temporal Projection and Temporal Joins Employee 1: Employe e 2: Name Salary Start Stop Bob 60000 1993 01 01 1993 06 01 Bob 70000 1993 06 01 1995 01 01 Name Title Bob Assistant. Provost Bob Full. Professor Start 1993 01 01 1993 10 01 1994 02 01 Stop 1993 10 01 1994 02 01 1995 01 01 Their Temporal Join: Name Bob Bob Salary 60000 70000 Title Assistant. Provost Full. Professor Start 1993 01 01 1993 06 01 1993 10 01 1994 02 01 Stop 1993 06 01 1993 10 01 1994 02 01 1995 01 01
Temporal Join in SQL SELECT Employee 1. Name, Salary, Title, Employee 1. Start, Employee 1. Stop FROM Employee 1, Employee 2 WHERE Employee 1. Name=Employee 2. Name AND Employee 2. Start <Employee 1. Start AND Employee 1. Stop <Employee 2. Stop UNION ALL SELECT Employee 1. Name, Salary, Title, Employee 1. Start, Employee 2. Stop FROM Employee 1, Employee 2 UNION ALL SELECT Employee 1. Name, Salary, Title Employee 2. Start, Employee 1. St FROM Employee 1, Employee 2 WHERE Employee 1. Name = Employee 2. Name AND Employee 2. Start > Employee 1. Start AND Employee 1. Stop <= Employee 2. Stop AND Employee 2. Start < Employee 1. Stop UNION ALL SELECT Employee 1. Name, Salary, Title Employee 2. Start, Employee 2. Stop FROM Employee 1, Employee 2 WHERE Employee 1. Name = Employee 2 Name
TSQL 2 Temporal Projection: Temporal Joins: SELECT Salary SELECT Employee 1. Name, Salary, Title FROM Employee WHERE Name = ‘Bob’ FROM Employee 1, Employee 2 WHERE Employee 1. Name = Employee 2. Name
Скачать презентацию CS 240 A Databases and Knowledge Bases Temporal
61aa11f7fd4761849da399426d6ee18c.ppt