CS145 Lecture Notes -- Temporal Databases

Premise: Storing and querying inherently temporal data in a conventional relational DBMS can be painful and inefficient.

Oracle's DATE type is not enough.
There's an active field of research called temporal databases to address the problems.

Example - Database of prescription information: patient, drug, start date, end date.

  CREATE TABLE P(patient string, drug string, start DATE, end DATE)

Question: What is key for table?

Example query: Who was taking "Amox" on 5/15/03?

Example query: Has anyone ever taken "Amox" and "Bactrim" at the same time?

Example query: For how long altogether has "Mary" taken "Amox"?

Example query: Who has taken "Amox" for the longest time?

Example query: Who is taking "Amox" now?

Example query: Who will have taken "Amox" the longest a week from now, assuming the currently active prescriptions remain active?

New attribute type called TIMESTAMP
A TIMESTAMP is a set of disjoint INTERVALs
An INTERVAL is a start and end TIME
A TIME is either a fixed point in time, the special symbol "NOW", or a "NOW-relative" time (e.g., NOW+7days).
Special operations on TIMESTAMPs:
Overlaps(T1,T2), Contains(T1,T2), Start(T), Length(T), etc.
Every temporal relation contains exactly one attribute of type TIMESTAMP

New version of prescriptions table:

  CREATE TABLE P (patient string, drug string, time TIMESTAMP)

Example tuple:

  (Sue, Amox, {[5/1/03, 5/4/03],[5/12/03, NOW]})

Question: What is key for table?

Every operator takes one or more temporal relations as operands, produces temporal relation as result
Can use special operators Overlaps, Contains, Length, etc. in SELECT conditions and PROJECT lists

Special interpretation for PROJECT: coalesce timestamps

Example: PROJECT[drug](P)

Example query: Who was taking "Amox" on 5/15/03?

Special interpretation for JOIN: implicit intersection of timestamps

Example query: Has anyone ever taken "Amox" and "Bactrim" at the same time?

Example query: For how long altogether has "Mary" taken "Amox"?

Example query: Who is taking "Amox" now?

There is a proposed standard called TSQL2 that incorporates these kinds of temporal operations (and many many others) into SQL

AggFun(R,W,G,A)

AggFun is a standard aggregate function (sum, count, avg, min, max)
R is a temporal relation (more generally can be any expression producing a temporal relation)
W is a window size
G is a set of grouping attributes; A is the aggregated attribute
Result is a temporal relation: at time T contains result of grouping and aggregation on all tuples "alive" any time in window [T-W,T]
Instantaneous aggregate is special case with W=O

Example: Smoothed stock trends

  Stock(ticker, value, timestamp)
  Query: Avg(Stock, 2min, ticker, value)

Example: Network monitoring

  Monitor(node, latency, timestamp)
  Query: Max(Monitor, 2sec, NULL, latency)