Temporal Databases Managing time varying data Rob Squire UK Consulting

1
Temporal Databases(Managing time varying
data)Rob Squire - UK Consulting

2
Temporal Databases
Am I a good guy or a bad guy?
3
Temporal Databases

• Interval Data Type (Timestamps)
• 6NF (horizontal and vertical decomposition aka
  TNF)
• Pack/UnPack (Collapsed form)
• No special attributes
• SQL with no extensions

4
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

5
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

6
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

7
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

8
What are temporal databases?
Valid (stated) Time
The 2 dimensions of time
Transaction (logged) Time
9
What are temporal databases?
Valid (stated) Time
Granularity of the time axis Chronons can be
days, Seconds, milliseconds depending on the
application domain
Transaction (logged) Time
10
What are temporal databases?
Valid (stated) Time
The moving point now
Transaction (logged) Time
11
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 e.g. Oracle 10g has flashback
  query
• 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.

12
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

13
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 will your profits be next month, given the
  price list and cost prices by then?

14
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?
• Given the final settlements for all the insurance
  claims of the last three years, what will be the
  minimum insurance premium your customers have to
  pay next year?

15
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)
• CRM applications (eg customer history / future)
• HR applications (e.g Date tracked positions in
  hierarchies)

16
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!

17
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)?

18
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

19
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

20
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
  non-temporal data model (stratum)

21
Implementation Approaches

• Generalise a non-temporal data model into a
  temporal data model (Temporal Normal Form)
• Re-design core database kernel (Temporal Database)

22
Implementation Approaches

• Q Why dont temporal databases already exist?
• A Dealing with time-varying data is complex

23
Implementation Approaches

• For example
• Avoiding duplicates requires complex logic.
• Avoiding gaps in a time-varying data requires
  complex logic.
• A simple join when applied to time-varying data
  turns into many lines of code consisting of
  multiple FROM and WHERE clauses.
• A simple update translates into several
  modification statements requiring many lines of
  code.

24
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

25
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

26
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)

27
Temporal Databases

• What are temporal databases?
• What is time varying data?
• Implementation Approaches
• Why now?
• Demonstration
• Questions and Answers

28
Demonstration

• Temporal Normal Form (approach 4)
• Generate TNF for supplier, supplier part schema
• Show select, insert, update and delete operations
• Show Referential Integrity
• With a Temporal Data Dictionary
• Using simple standard SQL with no extensions

29
Demonstration
Now
Fix Valid Time
30
Demonstration
Timestamp or Now 2 days
Fix Valid Time
31
Demonstration
Fix Transaction Time
SUPPLIER
Now
32
Demonstration
Fix Transaction Time
SUPPLIER
Timestamp or Now - 2 days
33
Demonstration
34
Demonstration
Demo 01 Generating, populating and querying TNF
35
Demonstration
Non Temporal Schema (SP)
TNF Temporal Schema (TSP)
SUPPLIER
SUPPLIER PART
Example schema taken from Temporal Data and the
Relational Model by CJ Date, H Darwin, NA
Lorentzos (2003)
36
Demonstration
Non Temporal Schema (SP)
TNF Temporal Schema (TSP)
SUPPLIER
SUPPLIER
Generate
SUPPLIER PART
SUPPLIER PART
37
Demonstration
Record Timestamp 1 03-NOV-05 15.45.23.125990000
38
Demonstration
Non Temporal Schema (SP)
TNF Temporal Schema (TSP)
SUPPLIER
SUPPLIER
Populate Insert as Select from
SUPPLIER PART
SUPPLIER PART
39
t0(now)
DEMO 1
Transaction time now
40
t1(now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
41
Demonstration
Fix Valid Time
timestamp1
42
t2(timestamp1)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
43
Demonstration
Un Fix Valid Time
44
t3 (now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
45
Demonstration
Fix Valid Time
Now 2 days
46
t4 (now2days)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
47
delete
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
48
Demonstration
Un Fix Valid Time
49
t5 (now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
50
eovt
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
51
t6 (now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
52
Demonstration
Record Timestamp 2 03-NOV-05 15.57.04.334588000
53
Demonstration
Now 30 seconds
Fix Valid Time
54
t7(now30 seconds)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
55
delete
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
56
Demonstration
Un Fix Valid Time
57
t8(now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
58
t9(now)
S1
S2
S3
S4
S5
DEMO 1
Transaction time now
59
Demonstration
Demo 02 Fixing transaction time
60
t10(now)
33
S1
S2
S3
S4
S5
DEMO 2
Transaction time now
61
t11(now)
45
S1
S2
S3
S4
S5
DEMO 2
Transaction time now
62
t12(now)
65
S1
S2
S3
S4
S5
DEMO 2
Transaction time now
63
Demonstration
Fix Transaction Time
SUPPLIER
Timestamp 2
64
t13(now)
171000
S1
S2
S3
S4
S5
DEMO 2
Transaction time lt t7
65
t14(now)
170900
S1
S2
S3
S4
S5
DEMO 2
Transaction time lt t7
66
t15(now)
170800
S1
S2
S3
S4
S5
DEMO 2
Transaction time lt t7
67
Lifetime gt2 days
S1
S2
S3
S4
S5
DEMO 2
Transaction time lt t7
68
Demonstration
UnFix Transaction Time
SUPPLIER
Now
69
t16(now)
Lifetime 1 hour
S1
S2
S3
S4
S5
DEMO 2
Transaction time now
70
Demonstration
Demo 03 (part1) DML not allowed when transaction
time is fixed
71
Demonstration
Fix Transaction Time
Current Timestamp
72
t17(now)
ORA-20001 S Cannot insert while system Y time
is set.
DEMO 3
Transaction time ltgt now
73
Demonstration
UnFix Transaction Time
SUPPLIER
Now
74
Demonstration
Demo 03 (part 2) Updating in TNF
75
Demonstration
Now 10 days
Fix Valid Time
76
t18(now-10days)
London
Paris
Paris
London
Athens
DEMO 3
Transaction time now
77
Demonstration
Now 8 days
Fix Valid Time
78
t19(now-8days)
London
Lyons
Paris
Paris
Lyons
London
Athens
DEMO 3
Transaction time now
79
Demonstration
Now 6 days
Fix Valid Time
80
t20(now-6days)
London
Lyons
Paris
Paris
Lyons
London
Corinth
Athens
DEMO 3
Transaction time now
81
Demonstration
Now 4 days
Fix Valid Time
82
t21(now-4days)
London
Manchester
Lyons
Paris
Paris
Lyons
Manchester
London
Corinth
Athens
DEMO 3
Transaction time now
83
Demonstration
Un Fix Valid Time
84
t22(now)
London
Manchester
Lyons
Paris
Paris
Lyons
Manchester
London
Corinth
Athens
DEMO 3
Transaction time now
85
t18
t19
t20
t21
London
Manchester
Lyons
Paris
Paris
Lyons
Manchester
London
Corinth
Athens
DEMO 3
Transaction time now
86
Demonstration
Demo 04 (part1) Maintaining Referential Integrity
87
Demonstration
Un Fix Valid Time
88
t23(now)
S
SP
ORA-20001 Integrity Constraint violated
parent key not found
DEMO 4
Transaction time now
(showing one S relvar)
89
t23(now)
S
SP
DEMO 4
Transaction time now
(showing one S relvar)
90
t23(now)
S
SP
DEMO 4
Transaction time now
(showing one S relvar)
91
Demonstration
Demo 04 (part2) Foreign Key Rules for TNF
92
Demonstration
Now 10 days
Fix Valid Time
93
t24(now-10days)
S1
DEMO 4
Transaction time now
(showing one S relvar)
94
Demonstration
Un Fix Valid Time
95
t25(now)
S1
DEMO 4
Transaction time now
(showing one S relvar)
96
Demonstration
Now 5 days
Fix Valid Time
97
t26(now-5days)
S1
S1,P1
ORA-20001 Integrity Constraint violated
parent key not found
DEMO 4
Transaction time now
(showing one S relvar)
98
t26(now-5days)
S1
delete restrict
S1,P1
Delete rule on foreign key constraint SP_S_FK is
RESTRICT
DEMO 4
Transaction time now
(showing one S relvar)
99
t26(now-5days)
S1
delete cascade
S1,P1
Delete rule on foreign key constraint SP_S_FK is
CASCADE
DEMO 4
Transaction time now
(showing one S relvar)
100
Demonstration
Un Fix Valid Time
101
t27(now)
S1
S1,P1
DEMO 4
Transaction time now
(showing one S relvar)
102
Demonstration
Demo 05 A more complex example
103
Demonstration
UnFix Transaction Time
SUPPLIER
Now
104
Demonstration
Now 100 days
Fix Valid Time
105
S1,P1
S1,P2
S1,P3
S2,P4
S2,P5
S2,P6
S3,P1
S3,P3
S3,P6
S1,P4
S1,P5
DEMO 5
Transaction time now
(showing all SP relvars)
106
S1,P1
S2
S1,P2
S3
S1,P3
S2,P4
S2,P5
S1
S1
S2,P6
S3,P1
S3,P3
QUERY A Page 74 List of dates each supplier was
able to supply at least one part
S3,P6
S1,P4
S1,P5
DEMO 5
Transaction time now
(showing all SP relvars)
107
S1,P1
S1,P2
S1
S1,P3
S1
S1
S2,P4
S2
S2
S2,P5
S2,P6
S3
S3,P1
S3
S3,P3
QUERY B Page 75 List of dates each supplier was
unable to supply at least one part
S3,P6
S1,P4
S1,P5
DEMO 5
Transaction time now
(showing all SP relvars)
108
Demonstration
Demo 06 (part1) The classic Employee Department
schema example
109
Demonstration
Un Fix Valid Time
110
Demonstration
UnFix Transaction Time
SUPPLIER
Now
111
t28(now)
Dept 10, Sales, New York
DEMO 6
Transaction time now
(showing Dept relvar)
112
t29(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
DEMO 6
Transaction time now
(showing Dept relvars)
113
t30(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
Emp 1, John, Clerk,,Dept 10
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
114
Demonstration
Now 20 days
Fix Valid Time
115
t31(now20)
Dept 10, Sales, New York
Dept 20, Finance, New York
Emp 1, John, Clerk,,Dept 10
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
116
Demonstration
Un Fix Valid Time
117
t32(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
delete restrict
Emp 1, John, Clerk,,Dept 10
ORA-20001 Integrity Constraint violated
parent key not found
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
118
t33(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
delete cascade
Emp 1, John, Clerk,,Dept 20
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
119
Demonstration
Demo 06 (part2) Non Transferable foreign keys
120
t33(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
transferable
Emp 1, John, Clerk,,Dept 20
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
121
t34(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
Non transferable
Emp 1, John, Clerk,,Dept 20
ORA-20001 Illegal attempt to modify
non-transferable foreign key.
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
122
t34(now)
Dept 10, Sales, New York
Dept 20, Finance, New York
Non transferable
Emp 1, John, Clerk,,Dept 20
DEMO 6
Transaction time now
(showing Dept/Emp relvars)
123
Demonstration

• You have just seen
• A practical implementation of TNF
• Using Standard SQL
• Where existing data modelling techniques for
  current view apply
• Providing bi temporal support
• Can underpin any application development platform
  (forms, java, html etc)

124
Demonstration

• Next Steps
• Gather feedback and responses on TNF from Oracle
  user organisations
• Contact Oracle Expert Services
• 0870 550 3060
• expertservices_uk_at_oracle.com
• www.oracle.com/uk/expert_services

125
Q

A
Q U E S T I O N S
A N S W E R S
Rob Squire UK Consulting rob.squire_at_oracle.com