Transcript Document

Advanced Databases
Temporal Databases
Dr Theodoros Manavis
[email protected]
1
Why now?
• Plummeting cost of storage
• Widespread adoption of warehouse
technology has led to an increasing
interest in temporal databases
• The idea of maintaining and processing
historical data has become not just a
goal but a reality for many organisations
Why need temporal data?
Ask yourself two questions
• Does your organisation need to know
the situation as it was known at a
particular date (e.g. the reprint of the
customer's invoice)?
• Does your business use information that
was effective in the past or will become
effective in the future (e.g. the new
address of the customer)?
Temporal DBs – Motivation
• Conventional databases represent the state of an enterprise at a
single moment of time
• Many applications need information about the past (time-varying
data, see next 2 slides)
– Financial (payroll)
– Medical (patient history)
– Government
• Temporal DBs: a system that manages time varying
data
What is time varying data?
Examples of application domains dealing with time
varying data:
• Financial Apps (e.g. history of stock market data)
• Insurance Apps (e.g. when were the policies in effect)
• Reservation Systems (e.g. when is which room in a
hotel booked)
• Medical Information Management Systems (e.g.
patient records)
• Decision Support Systems (e.g. planning future
contigencies)
• HR applications (e.g Date tracked positions in
hierarchies)
Comparison
• Conventional DBs:
– Evolve through transactions from one state to the
next
– Changes are viewed as modifications to the state
– No information about the past
– Snapshot of the enterprise
• Temporal DBs:
– Maintain historical information
– Changes are viewed as additions to the information
stored in the database
– Incorporate notion of time in the system
– Efficient access to past states
Temporal Databases
• Temporal Data Models: extension of
relational model by adding temporal
attributes to each relation
• Temporal Query Languages: TQUEL,
SQL3 (rather controversial field)
• Temporal Indexing Methods and Query
Processing
Taxonomy of time
• Transaction time databases
– Transaction time is the time when a fact is
stored in the database
• Valid time databases:
– Valid time is the time that a fact becomes
effective in reality
• Bi-temporal databases:
– Support both notions of time
Example
• Sales example: data about sales are stored
at the end of the day
• Transaction time is different than valid time
• Valid time can refer to the future also!
– Credit card: 03/01-04/06
Transaction Time DBs
• Time evolves discretely, usually is associated
with the transaction number:
T1 -> T2 -> T3 -> T4 ….
• A record R is extended with an interval [t.start,
t.end). When we insert an object at t1 the
temporal attributes are updated -> [t1, now)
• Updates can be made only to the current state!
– Past cannot be changed
– “Rollback” characteristics
Transaction Time DBs
• Transaction time records the time period during which a
database entry is accepted as correct.
• This enables queries that show the state of the database
at a given time.
• Transaction time periods can only occur in the past or up
to the current time.
• In a transaction time table, records are never deleted.
Only new records can be inserted, and existing ones
updated by setting their transaction end time to show that
they are no longer current.
Temporal databases
We can use these two dimensions to distinguish between
different forms of temporal database
• A rollback database stores data with respect to
transaction time
• A historical database stores data with respect to valid
time
• A bi-temporal database stores data with respect to both
valid time and transaction time.
Transaction Time DBs
• Deletion is logical (never physical deletions!)
– When an object is deleted at t2, its temporal
attribute changes from [t1, now)  [t1, t.t2)
(lifetime)
– Object is “alive” from insertion to deletion time, ex.
t1 to t2. If “now” then the object is still alive
eid
salary start
end
10
20K
9/93
10/94
20
50K
4/94
*
33
30K
5/94
6/95
10
50K
1/95
*
time
Transaction Time DBs
1 2
4
8
10
15 16 17
25
28
30
33
41 42
45
47 48
u
b
f
c
d
id
g
p
j
k
i
m
e
Database evolves through insertions and deletions
51
53
Valid Time DBs
• Valid time is the time for which a fact is true in
the real world.
• A valid time period may be in the past, span the
current time, or occur in the future.
• Time evolves continuously
• Each object is a line segment representing its
time span (eg. Credit card valid time)
• Physical deleteion is possible
• Support full operations on interval data:
– Deletion at any time
– Insertion at any time
– Value change (modification) at any time (no ordering)
Bi-temporal DBs
• A bi-temporal relation contains both valid and transaction
time. This provides both historical and rollback
information. Historical information (e.g.: "Where did John
live in 1992?") is provided by the valid time. Rollback
(e.g.: "In 1992, where did the database believe John
lived?") is provided by the transaction time. A
transaction-time Database, but each record is an interval
(plus the other attributes of the record)
• At each timestamp, it is a valid time database
Bitemporal DBs
C(t1)
t1
Ix
t3
t2
v
Iy
C(t3)
C(t2)
Iy
Ix
Iz
Iy
Ix
Iz
Iw
t
t5
t4
v
v
C(t5)
C(t4)
v
Iy
v
Iy
Iw
Ix
Iw
Ix
Thank You for Your Attention 
18