Where does time go - PowerPoint PPT Presentation

1 / 43
About This Presentation
Title:

Where does time go

Description:

The key property is extended to include the temporal semantics ... on the specified attributes and coalesces the result on its temporal properties ... – PowerPoint PPT presentation

Number of Views:68
Avg rating:3.0/5.0
Slides: 44
Provided by: kers151
Category:
Tags: time

less

Transcript and Presenter's Notes

Title: Where does time go


1
Where does time go ?
2
Applications abound
  • Temporal database systems provide built-in
    support for recording and querying time-varying
    information
  • Application areas
  • Media planning advertisements
  • Accounting cash flow analysis
  • Financial stock market
  • GIS land use
  • Medical patient records
  • Academic course attendance
  • Scientific archeological findings

3
Applications abound
  • It is difficult to identify applications that do
    not need support for temporal data
  • Temporal database systems provide built-in
    support for recording and querying time-varying
    information
  • Temporal applications would benefit from built-in
    support at the data model and query language
    layers

4
What time ?
5
Time ontology
  • Time is an ordered sequence of points
  • Linear time
  • Branching time (futures)
  • Partial time ordering (DAG)
  • Cyclic times
  • Bounded or unbounded time lines
  • Bounded origin exist and it ends
  • Unbounded only partial order is known

6
Time ontology
  • Time density
  • Discrete
  • Time line is isomorphic to the integers
  • Time line is composed of a sequence of
    non-decomposable time periods of some fixed,
    minimal duration, termed chronons
  • Between each pair of chronons is a finite number
    of other chronons
  • Dense
  • Time line is isomorphic to rational numbers
  • Between each pair of chronons is a finite number
    of other chronons
  • Continuous
  • Time line is isomorphic with the real numbers
  • Between each pair of chronons is a finite number
    of other chronons

7
Time ontology
  • A time instant is a time point on the real time
    line
  • An event is an instantaneous fact, i.e something
    occurring at an instant. The event occurrence
    time of an event is the instant at which the
    event occurs in the real world
  • An instant set is a set of instants
  • A time period is the time between two instants
  • also called interval
  • A time interval is a directed duration of time. A
    duration is an amount of time with a known
    length, but no specific starting or endings
    instants. It is also called a span.
  • A temporal element is a finite union of periods

8
Clocks
  • A clock is a physical process coupled with a
    method of measuring that process
  • The units of measurement are the chronons of the
    clock
  • Clocks provide the semantics of the timestamp
    representation
  • Clocks can be defined over various calendar
    structures

9
Times and facts
  • Valid time of a fact when the fact is true in
    the modeled reality
  • Transaction time of a fact when the fact is
    current in the database and may be retrieved
  • Four kinds of temporal tables
  • Snapshot table without time
  • Valid-time
  • Transaction-time append only tables
  • Bi-temporal

10
Transaction and valid time tables
  • Transaction time tables
  • Append only corrections to previous snapshot
    states is not permitted
  • Support of transaction time
  • Support rollback queries
  • Valid time tables
  • May be modified
  • Support valid time
  • Short historical queries

11
Cant we use SQL for temporal support ?
12
Time Specification in SQL-92
  • date four digits for the year (1--9999), two
    digits for the month (1--12), and two digits for
    the date (1--31).
  • time two digits for the hour, two digits for the
    minute, and two digits for the second, plus
    optional fractional digits.
  • timestamp the fields of date and time, with six
    fractional digits for the seconds field.
  • Times are specified in the Universal Coordinated
    Time, abbreviated UTC (from the French) supports
    time with time zone.
  • interval refers to a period of time (e.g., 2
    days and 5 hours), without specifying a
    particular time when this period starts could
    more accurately be termed a span.

13
A case study
  • Consider a simple university database
  • Employee(Name,Salary,Title)
  • Finding an employees salary is easy
  • Select Salary
  • From Employee
  • Where name Bob

14
A case study
  • Now consider addition of a Date-of-birth field
  • Employee(Name,Salary,Title, Dob DATE)
  • Finding the employees date of birth is easy
  • Select Dob
  • From Employee
  • Where NameBob

15
A case study
  • Extend the database with an employee history
  • Employee(Name,Salary,Title, Dob,
  • Start DATE, stop DATE)
  • To the datamodel these columns are identical to
    Dob

16
A case study
  • Find the current salary
  • Select Salary
  • From Employee
  • Where NameBoband Start lt current_time_stamp
  • And current_time_stap lt Stop

17
A case study
  • Find the salary history
  • Select Salary, start, stop
  • From Employee
  • Where NameBoband Start lt current_time_stamp

Coalesce these tuples
18
A case study
  • Find the salary history but take into account the
    title
  • Give the user a print out
  • Or combine intervals with the same function and
    salary, I.e. compress the table as much as
    possible

19
A case study
  • Find the salary history but take into account the
    title
  • Or combine intervals with the same function and
    salary, I.e. compress the table as much as
    possible
  • Create table Temp(salary,start,stop) as select
    Salary,Start,Stop from Employee where Name
    Bob
  • Repeat
  • Update Temp as T1
  • Set t1.stop (select max(T2.stop) from Temp as T2
    where T1.salaryT2.salary and T1.startltT2.start
    and T1.StopgtT2.Start and T1.stopltT2.stop)
  • Where exist( select from Temp as T2 where
    T1.salaryT2.salary and T1.Start ltT2.start and
    T1.startltT2.start and T1.StopgtT2.Start and
    T1.stopltT2.stop)
  • Until no tuples are updated

20
A case study
  • Find the salary history but take into account the
    title
  • Or combine intervals with the same function and
    salary, I.e. compress the table as much as
    possible
  • Use standard SQL leads to triple nested queries
  • Use SQL cursor methods and rely on iterations

21
Objectives of a temporal model
  • Conclusion It would be nice if the datamodel and
    query language understood the intended temporal
    behavior
  • Capture the semantics of time-varying information
  • Retain simplicity of the relational model
  • Strict super-set of relational model
  • Present information in a coherent fashion
  • Ensure ease of implementation
  • Ensure high performance
  • Design your own extension to the relational model

22
Temporal Relational Model and algebra
  • What kind of temporal data would you introduce in
    the relational model ?
  • Where would you introduce temporal components?
  • How would you define the interpretation of the
    relational primitives, I.e.
  • Temporal selection
  • Temporal projection
  • Temporal join
  • Temporal union
  • How would your extend the integrity rules ?

23
Temporal Relational Model and algebra
  • What kind of temporal data would you introduce in
    the relational model ?
  • Calender information
  • Timezones
  • Accuracy
  • Time administration
  • Points on a time axe
  • Intervals
  • Sets of points
  • Sets of intervals
  • Fake time

24
Temporal Relational Model and algebra
  • Where would you introduce temporal components?
  • AT the schema level
  • At the table
  • At the column level
  • At the attribute level
  • At the tuple level

Temporal tuple
25
Temporal Relational Model and algebra
  • What kind of integrity rules ?
  • The key property is extended to include the
    temporal semantics
  • No two tuples defined at the same time have
    identical values
  • Referential constraints should obey the temporal
    relationships as well

26
Temporal Relational Model and algebra
  • How would you define the interpretation of the
    relational primitives?
  • When time is a tuple attribute, then the tuples
    are coalesced on their temporal attribute
  • Duplicate elimination respects the temporal
    dimension
  • Relational joins are only defined for the same
    time intervals, outerjoins should be used
    otherwise.

27
Time In Databases
  • While most databases tend to model reality at a
    point in time (at the current'' time), temporal
    databases model the states of the real world
    across time.
  • Facts in temporal relations have associated times
    when they are valid, which can be represented as
    a union of intervals.
  • The transaction time for a fact is the time
    interval during which the fact is current within
    the database system.
  • In a temporal relation, each tuple has an
    associated time when it is true the time may be
    either valid time or transaction time.
  • A bi-temporal relation stores both valid and
    transaction time.

28
Time In Databases (Cont.)
  • Example of a temporal relation
  • Temporal query languages have been proposed to
    simplify modeling of time as well as time
    related queries.

29
Temporal Query Languages
  • Predicates precedes, overlaps, and contains on
    time intervals.
  • Intersect can be applied on two intervals, to
    give a single (possibly empty) interval the
    union of two intervals may or may not be a single
    interval.
  • A snapshot of a temporal relation at time t
    consists of the tuples that are valid at time t,
    with the time-interval attributes projected out.
  • Temporal selection involves time attributes
  • Temporal projection the tuples in the projection
    inherit their time-intervals from the tuples in
    the original relation.
  • Temporal join the time-interval of a tuple in
    the result is the intersection of the
    time-intervals of the tuples from which it is
    derived. It intersection is empty, tuple is
    discarded from join.

30
TSQL
  • Over 40 temporal datamodels have been studied
    over the last two decades. The clear winner has
    been TSQL, a temporal extension that is part of
    the SQL99 standard
  • TSQL supports a bitemporal data model
  • It provides condense representation of temporal
    queries
  • The TSQL project is part of the ACM Anthology
  • The language is illustrated through a few examples

31
TSQL case
  • Consider a Patient database with records of
    information about prescribed drugs
  • Create table Prescription (Name, Physician, Drug,
    Dosage,
  • Frequency interval minute)
  • as valid day and transaction
  • A valid time specifies the period(s) during which
    the drug is prescribed
  • The valid time granularity is a day
  • The transaction time records when the information
    was added to the database
  • The transaction time granularity is system
    defined.

32
TSQL case
  • TSQL supports the following (temporal) tables
  • Snapshot nothing after the attributes
  • Valid-time state as valid stateltgranularitygt
  • Valid-time event as valide eventltgranularitygt
  • Transaction-time as transaction
  • Bitemporal as valid stateltgranularitygt and
    transacion
  • The type of the table can be altered at any time
  • Semantics is not irreversable

33
TSQL example
  • Who has been prescribed drugs?
  • Select snapshot Name from Prescription
  • Result in 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
    DrugProventil

34
TSQL examples
  • Snapshot reducibility, converts a temporal
    relation to its ordinary RDBMS equivalent.
    Temporal components loose their additional
    meaning
  • Who has been prescribed drugs, and when?
  • Select Name From Prescription
  • Results in a list of names, each associated with
    one or more maximal periods.

35
TSQL examples
  • Snapshot reducibility, converts a temporal
    relation to its ordinary RDBMS equivalent.
    Temporal components loose their additional
    meaning
  • 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 ltgt Proventil
  • And P1.Name P2.Name
  • Results in a list of patient names and drugs,
    along with their associated maximal periods.

36
TSQL examples
  • Operators are provided to reason about validity
    property
  • Who has been on drug for more than a total of 6
    months
  • Select Name, Drug
  • From Prescription(Name,Drug) as P
  • Where cast(valid(P)) as interval month gtinterval
    6month
  • Result will contain the maximal interval(s) when
    the patient has been on the drug
  • Restructuring of tables as part of the from
    clause helps keeping queries concise

37
TSQL restructuring
  • Restructuring is syntactic sugar
  • From A(B,C) as A2
  • Is equivalent to
  • From (select B,C from A) as A2
  • Nested select projects on the specified
    attributes and coalesces the result on its
    temporal properties
  • Other attributes in A are not accessible via A2
    and A is not accessible in the enclosing select

38
TSQL example
  • 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

39
Valid time projections
  • What drugs was Melanie prescribed during 1994?
  • Select Drug
  • Valid intersect(valid(Prescription), period
    1994day)
  • From Prescription
  • Where NameMelanie
  • The result is a list of drugs, each associated
    with a set of periods during 1994 that they were
    prescribed to Melanie

40
Valid time modifications
  • Insert into Prescription
  • Values (Sally,dr Green,Proventil,100mg,
  • Interval 800 minute
  • Valid period 1993/01/01-1993/06/03
  • Inserted values will be coalesced with existing
    value equivalent rows
  • Default valid clause
  • Valid period(current_timestamp,
  • nobind(timestamp now))

41
Valid time modifications
  • The delete statement removes period(s) from the
    temporal element of the qualifying rows
  • Only if the temporal element becomes empty, the
    row is deleted
  • Delete from Prescription
  • Where NameMelanie
  • Valid period 1993/06/01-now

42
Event tables
  • Event tables are timestamped with instant sets
  • Each row identifies a particular kind of
    (instantaneous) event, with the timestamp of the
    row specifying the instant(s) when that event
    occurred
  • The attribute valid(P) now produces a set of time
    points
  • Event tables may also be associated with
    transaction time

43
Transaction tables
  • The transaction timestamp is a system defined
    counter
  • If transaction time is not mentioned in the
    query, the results includee only the information
    currently believed to be true.
  • Transaction tables can be used to rollback
  • Select Drug
  • From Prescription as P
  • Where Name Melanie
  • And transaction(P) overlaps date 1994

44
Temporal aggregation
  • The standard aggregate operators are re-defined
    for TSQL to imply an automatic group on
    distinctive temporal periods.
  • Idea break the tuples on the smallest possible
    non-overlapping periods, thereafter perform
    aggregate and coalesce the result back into
    periods.

45
Conclusion
  • The possibilities to enhance the relational model
    with the notion of time has been widely studied
    (gt40 variants)
  • A consensus model has been included in SQL99
  • Time is an instantiation of a sequence, as in
    time-series. But temporal support to sequences is
    still rather mininal.
  • how to extend the relational model with
    sequences?
Write a Comment
User Comments (0)
About PowerShow.com