Transcript 5_temporaldb - WordPress.com

Temporal Databases
(Managing time varying data)
Rob Squire - UK Consulting
Temporal Databases
Am I a good guy or a bad guy?
Temporal Databases
 Interval Data Type (Timestamps)
 6NF (horizontal and vertical
decomposition aka TNF)
 Pack/UnPack (Collapsed form)
 No ‘special’ attributes
 SQL with no extensions
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
What are temporal databases?
 Non Temporal
–
–
–
store only a single state of the real world, usually
the most recent state
classified as snapshot databases
application developers and database designers
need to code for time varying data requirements
eg history tables, forecast reports etc
What are temporal databases?
 Temporal
–
stores upto two dimensions of time i.e VALID
(stated) time and TRANSACTION (logged) time
–
Classified as historical, rollback or bi-temporal
–
No need for application developers or database
designers to code for time varying data
requirements i.e time is inherently supported
What are temporal databases?
Transaction (logged) Time
Valid (stated) Time
The 2 dimensions of time
What are temporal databases?
Transaction (logged) Time
Valid (stated) Time
Granularity of the time axis
Chronons can be days,
Seconds, milliseconds
depending on the
application domain
What are temporal databases?
Transaction (logged) Time
Valid (stated) Time
The moving point ‘now’
What are 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. The transaction time for a fact is the
time interval during which the fact is current within the
database system.
 A historical database stores data with respect to valid
time. Facts in temporal relations have associated
times when they are valid, which can be represented
as a union of intervals.
What are temporal databases?
 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 database stores data with respect to
both valid time and transaction time.
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
What is time varying data?
 You want a reprint of a customer's invoice of August
12, 1999.
 What was the stock value of the Oracle shares on
June 15th, last year?
 What was the lowest stock quantity for every product
last year? How much money will you save, if you keep
the stocks at those levels?
 Where do you enter the new address of this customer
as from the first of next month?
What is time varying data?
And combinations of the situations can be very complex
 You offered these goods to the customer on January
10 this year. What were the billing prices and what
was his discount level when you sent him this offer?
He has not accepted yet.
Is it smart to offer him an actualized discount now?
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)
 Decision Support Systems (e.g. planning future
contigencies)
 HR applications (e.g Date tracked positions in
hierarchies)
What is time varying data?
In fact, time varying data has ALWAYS been
in business requirements – but existing
technology does not deal with it elegantly!
What is time varying data?
Ask yourself two questions
 Does your business 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)?
What is time varying data?
If you answer "Yes" on one or both of these
questions
then
your data varies over time and you could
consider adopting a
temporal approach
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
Implementation Approaches
Several implementation strategies are available
 Use a date type supplied in a non-temporal
DBMS and build temporal support into
applications (traditional)
 Implement an abstract data type for time
(object oriented)
 Provide a program layer (api) above a nontemporal data model (stratum) ?
Implementation Approaches
 Generalise a non-temporal data model into a
temporal data model (Temporal Normal Form)
 Re-design core database kernel (Temporal
Database)
Implementation Approaches
Q: Why don’t temporal databases already exist?
A: Dealing with time-varying data is complex
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
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 now?
 DW vendors are themselves faced with
temporal problems (slowly changing time
dimension) and have begun to feel the need
for a new solution
 DB Vendors considering adding temporal
support to existing product (Oracle flashback
query) and applications (Oracle HR date
tracking/payroll)
 SQL bodies are beginning to think about
adding syntax to the standard to support
temporal features (SQL3, TSQL)
Temporal Databases






What are temporal databases?
What is time varying data?
Implementation Approaches
Why now?
Demonstration
Questions and Answers
Demonstration
Valid Time
Transaction Time
QUESTIONS
ANSWERS
Rob Squire UK Consulting
[email protected]