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 timevarying data are hard to express
  in SQL--A case study.
• Solution Temporal Database Systems
• A Temporal Database System is one that provides
  builtin 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
• Moneymanagement software such as Quickencan show
  e.g., account balance over time.
• Budgets Previous and projected budgets,
  multiquarter or multiyear 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 builtin
  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
• SQL1999 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 'BobSQL1999 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

• 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
  'BobAND Start lt CURRENT_TIMESTAMPAND
  CURRENT_TIMESTAMP lt 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 19930101 19930601Bob 70000
  19930601 19950101

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
Bobs 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 lt
  T2.Start AND T1.Stop gt T2.Start AND
  T1.Stop lt T2.Stop) WHERE EXISTS (SELECT
  FROM Temp AS T2 WHERE T1.Salary
  T2.Salary AND T1.Start lt T2.Start AND
  T1.Stop gt T2.Start AND T1.Stop lt 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 gt T2.Start AND T1.Stop lt
  T2.Stop) OR (T1.Start gt T2.Start AND
  T1.Stop lt 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, valueequivalent
  tuples. Then delete extraneous, nonmaximal
  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 lt L.StopAND
  F.Salary L.SalaryAND NOT EXISTS (SELECT
  FROM Temp AS M WHERE M.Salary F.Salary AND
  F.Start lt M.Start AND M.Start lt L.Stop AND NOT
  EXISTS (SELECT FROM Temp AS T1 WHERE
  T1.Salary F.Salary AND T1.Start lt M.Start AND
  M.Start lt T1.Stop))AND NOT EXISTS (SELECT
  FROM Temp AS T2 WHERE T2.Salary F.Salary
  AND
• ( (T2.Start lt F.Start AND F.Start lt T2.Stop)
  OR (T2.Start lt L.Stop AND L.Stop lt 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 EmployeeOPEN emp_cursorloop
• FETCH emp_cursor INTO salary,start,stop if
  no data returned then go to finishedfind
  position in linked list to insert this
  information
• go to loopfinishedCLOSE emp_cursoriterate
  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
Temporal Projection andTemporal Joins
Employee1
Employee2
Their Temporal Join
23
Temporal Join in SQL

• SELECT Employee1.Name, Salary, Title,Employee1.St
  art, Employee1.Stop
• FROM Employee1, Employee2
• WHERE Employee1.NameEmployee2.Name
• AND Employee2.Start ltEmployee1.Start
• AND Employee1.Stop ltEmployee2.Stop
• UNION ALL
• SELECT Employee1.Name, Salary, Title,Employee1.St
  art, Employee2.Stop
• FROM Employee1, Employee2
• WHERE Employee1.Name Employee2.Name
• AND Employee1.Start gtEmployee2.Start
• AND Employee2.Stop lt Employee1.Stop
• AND Employee1.Start lt Employee2.Stop

• UNION ALL
• SELECT Employee1.Name, Salary,
  Title Employee2.Start, Employee1.St FROM
  Employee1, Employee2 WHERE
  Employee1.Name Employee2.Name AND
  Employee2.Start gt Employee1.Start AND
  Employee1.Stop lt Employee2.Stop AND
  Employee2.Start lt Employee1.Stop
• UNION ALL
• SELECT Employee1.Name, Salary,
  TitleEmployee2.Start, Employee2.Stop FROM
  Employee1, Employee2 WHERE
  Employee1.Name Employee2 Name AND
  Employee2.Start gt Employee1.Start AND
  Employee2.Stop lt Employee1.Stop AND NOT
  (Employee1.Start Employee2.StartAND
  Employee1.Stop Employee2.Stop)

24
TSQL2

• Temporal Projection
• SELECT Salary
• FROM Employee
• WHERE Name Bob

• Temporal Joins
• SELECT Employee1.Name, Salary, Title
• FROM Employee1, Employee2
• WHERE Employee1.Name Employee2.Name