temporal databases managing time varying data rob squire uk consulting l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Temporal Databases (Managing time varying data) Rob Squire - UK Consulting PowerPoint Presentation
Download Presentation
Temporal Databases (Managing time varying data) Rob Squire - UK Consulting

Loading in 2 Seconds...

play fullscreen
1 / 125

Temporal Databases (Managing time varying data) Rob Squire - UK Consulting - PowerPoint PPT Presentation


  • 228 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Temporal Databases (Managing time varying data) Rob Squire - UK Consulting' - leigh


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
temporal databases
Temporal Databases

Am I a good guy or a bad guy?

temporal databases3
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 databases4
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
temporal databases5
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
what are temporal databases
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 databases7
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 databases8
What are temporal databases?

Valid (stated) Time

The 2 dimensions of time

Transaction (logged) Time

what are temporal databases9
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

what are temporal databases10
What are temporal databases?

Valid (stated) Time

The moving point ‘now’

Transaction (logged) Time

what are temporal databases11
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.
temporal databases12
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
what is time varying data
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?
what is time varying data14
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 andwhat 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?
what is time varying data15
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)
what is time varying data16
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 data17
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 data18
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 databases19
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
implementation approaches
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)
implementation approaches21
Implementation Approaches
  • Generalise a non-temporal data model into a temporal data model (Temporal Normal Form)
  • Re-design core database kernel (Temporal Database)
implementation approaches22
Implementation Approaches

Q: Why don’t temporal databases already exist?

A: Dealing with time-varying data is complex

implementation approaches23
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.
temporal databases24
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
why now
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 now26
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 databases27
Temporal Databases
  • What are temporal databases?
  • What is time varying data?
  • Implementation Approaches
  • Why now?
  • Demonstration
  • Questions and Answers
demonstration
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
demonstration29

SUPPLIER

Demonstration

Now

Fix Valid Time

demonstration30

SUPPLIER

Demonstration

Timestamp or

Now + 2 days

Fix Valid Time

demonstration31
Demonstration

Fix Transaction Time

SUPPLIER

Now

demonstration32
Demonstration

Fix Transaction Time

SUPPLIER

Timestamp or

Now - 2 days

demonstration34
Demonstration

Demo 01

Generating, populating and querying TNF

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

demonstration36
Demonstration

Non Temporal Schema (SP)

TNF Temporal Schema (TSP)

SUPPLIER

SUPPLIER

Generate

SUPPLIER

PART

SUPPLIER

PART

demonstration37
Demonstration

Record Timestamp 1

03-NOV-05 15.45.23.125990000

demonstration38
Demonstration

Non Temporal Schema (SP)

TNF Temporal Schema (TSP)

SUPPLIER

SUPPLIER

Populate

Insert as

Select * from

SUPPLIER

PART

SUPPLIER

PART

slide39

t0(now)

Transaction time = now

DEMO 1

slide40

t1(now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration41

SUPPLIER

Demonstration

Fix Valid Time

timestamp1

slide42

t2(timestamp1)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration43

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide44

t3 (now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration45

SUPPLIER

Demonstration

Fix Valid Time

Now + 2 days

slide46

t4 (now+2days)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

slide47

delete

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration48

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide49

t5 (now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

slide50

eovt

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

slide51

t6 (now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration52
Demonstration

Record Timestamp 2

03-NOV-05 15.57.04.334588000

demonstration53

SUPPLIER

Demonstration

Now + 30 seconds

Fix Valid Time

slide54

t7(now+30 seconds)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

slide55

delete

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration56

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide57

t8(now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

slide58

t9(now)

S1

S2

S3

S4

S5

Transaction time = now

DEMO 1

demonstration59
Demonstration

Demo 02

Fixing transaction time

slide60

t10(now)

33

S1

S2

S3

S4

S5

Transaction time = now

DEMO 2

slide61

t11(now)

45

S1

S2

S3

S4

S5

Transaction time = now

DEMO 2

slide62

t12(now)

65

S1

S2

S3

S4

S5

Transaction time = now

DEMO 2

demonstration63
Demonstration

Fix Transaction Time

SUPPLIER

Timestamp 2

slide64

t13(now)

171000

S1

S2

S3

S4

S5

Transaction time < t7

DEMO 2

slide65

t14(now)

170900

S1

S2

S3

S4

S5

Transaction time < t7

DEMO 2

slide66

t15(now)

170800

S1

S2

S3

S4

S5

Transaction time < t7

DEMO 2

slide67

Lifetime >2 days

S1

S2

S3

S4

S5

Transaction time < t7

DEMO 2

demonstration68
Demonstration

UnFix Transaction Time

SUPPLIER

Now

slide69

t16(now)

Lifetime 1 hour

S1

S2

S3

S4

S5

Transaction time = now

DEMO 2

demonstration70
Demonstration

Demo 03 (part1)

DML not allowed when transaction time is fixed

demonstration71

SUPPLIER

Demonstration

Fix Transaction Time

Current Timestamp

slide72

t17(now)

ORA-20001: S: Cannot insert while system Y time is set.

Transaction time <> now

DEMO 3

demonstration73
Demonstration

UnFix Transaction Time

SUPPLIER

Now

demonstration74
Demonstration

Demo 03 (part 2)

Updating in TNF

demonstration75

SUPPLIER

Demonstration

Now – 10 days

Fix Valid Time

slide76

t18(now-10days)

London

Paris

Paris

London

Athens

Transaction time = now

DEMO 3

demonstration77

SUPPLIER

Demonstration

Now – 8 days

Fix Valid Time

slide78

t19(now-8days)

London

Paris

Lyons

Paris

Lyons

London

Athens

Transaction time = now

DEMO 3

demonstration79

SUPPLIER

Demonstration

Now – 6 days

Fix Valid Time

slide80

t20(now-6days)

London

Paris

Lyons

Paris

Lyons

London

Athens

Corinth

Transaction time = now

DEMO 3

demonstration81

SUPPLIER

Demonstration

Now – 4 days

Fix Valid Time

slide82

t21(now-4days)

London

Manchester

Paris

Lyons

Paris

Lyons

London

Manchester

Athens

Corinth

Transaction time = now

DEMO 3

demonstration83

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide84

t22(now)

London

Manchester

Paris

Lyons

Paris

Lyons

London

Manchester

Athens

Corinth

Transaction time = now

DEMO 3

slide85

t18

t19

t20

t21

London

Manchester

Paris

Lyons

Paris

Lyons

London

Manchester

Athens

Corinth

Transaction time = now

DEMO 3

demonstration86
Demonstration

Demo 04 (part1)

Maintaining Referential Integrity

demonstration87

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide88

t23(now)

S

SP

ORA-20001: :Integrity Constraint violated – parent key not found

Transaction time = now

DEMO 4

(showing one S relvar)

slide89

t23(now)

S

SP

Transaction time = now

DEMO 4

(showing one S relvar)

slide90

t23(now)

S

SP

Transaction time = now

DEMO 4

(showing one S relvar)

demonstration91
Demonstration

Demo 04 (part2)

Foreign Key Rules for TNF

demonstration92

SUPPLIER

Demonstration

Now – 10 days

Fix Valid Time

slide93

t24(now-10days)

S1

Transaction time = now

DEMO 4

(showing one S relvar)

demonstration94

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide95

t25(now)

S1

Transaction time = now

DEMO 4

(showing one S relvar)

demonstration96

SUPPLIER

Demonstration

Now – 5 days

Fix Valid Time

slide97

t26(now-5days)

S1

S1,P1

ORA-20001: :Integrity Constraint violated – parent key not found

Transaction time = now

DEMO 4

(showing one S relvar)

slide98

t26(now-5days)

S1

delete

restrict

S1,P1

Delete rule on foreign key constraint SP_S_FK is RESTRICT

Transaction time = now

DEMO 4

(showing one S relvar)

slide99

t26(now-5days)

S1

delete

cascade

S1,P1

Delete rule on foreign key constraint SP_S_FK is CASCADE

Transaction time = now

DEMO 4

(showing one S relvar)

demonstration100

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide101

t27(now)

S1

S1,P1

Transaction time = now

DEMO 4

(showing one S relvar)

demonstration102
Demonstration

Demo 05

A more complex example

demonstration103
Demonstration

UnFix Transaction Time

SUPPLIER

Now

demonstration104

SUPPLIER

Demonstration

Now – 100 days

Fix Valid Time

slide105

S1,P1

S1,P2

S1,P3

S2,P4

S2,P5

S2,P6

S3,P1

S3,P3

S3,P6

S1,P4

S1,P5

Transaction time = now

DEMO 5

(showing all SP relvars)

slide106

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

Transaction time = now

DEMO 5

(showing all SP relvars)

slide107

S1,P1

S1,P2

S1

S1

S1,P3

S1

S2,P4

S2

S2

S2,P5

S2,P6

S3

S3

S3,P1

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

Transaction time = now

DEMO 5

(showing all SP relvars)

demonstration108
Demonstration

Demo 06 (part1)

The classic Employee Department schema example

demonstration109

Now

SUPPLIER

Demonstration

Un Fix Valid Time

demonstration110
Demonstration

UnFix Transaction Time

SUPPLIER

Now

slide111

t28(now)

Dept 10, Sales, New York

Transaction time = now

DEMO 6

(showing Dept relvar)

slide112

t29(now)

Dept 10, Sales, New York

Dept 20, Finance, New York

Transaction time = now

DEMO 6

(showing Dept relvars)

slide113

t30(now)

Dept 10, Sales, New York

Dept 20, Finance, New York

Emp 1, John, Clerk,…,Dept 10

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

demonstration114

SUPPLIER

Demonstration

Now + 20 days

Fix Valid Time

slide115

t31(now+20)

Dept 10, Sales, New York

Dept 20, Finance, New York

Emp 1, John, Clerk,…,Dept 10

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

demonstration116

Now

SUPPLIER

Demonstration

Un Fix Valid Time

slide117

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

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

slide118

t33(now)

Dept 10, Sales, New York

Dept 20, Finance, New York

delete

cascade

Emp 1, John, Clerk,…,Dept 20

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

demonstration119
Demonstration

Demo 06 (part2)

Non Transferable foreign keys

slide120

t33(now)

Dept 10, Sales, New York

Dept 20, Finance, New York

transferable

Emp 1, John, Clerk,…,Dept 20

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

slide121

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.

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

slide122

t34(now)

Dept 10, Sales, New York

Dept 20, Finance, New York

Non

transferable

Emp 1, John, Clerk,…,Dept 20

Transaction time = now

DEMO 6

(showing Dept/Emp relvars)

demonstration123
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)
demonstration124
Demonstration

Next Steps

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

Q

&

A

Q U E S T I O N S

A N S W E R S

Rob Squire UK Consulting

rob.squire@oracle.com