1 / 125

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

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

leigh
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  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 don’t 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. SUPPLIER Demonstration Now Fix Valid Time

  30. SUPPLIER 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. Fix Transaction and Valid Time SUPPLIER 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) Transaction time = now DEMO 1

  40. t1(now) S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  41. SUPPLIER Demonstration Fix Valid Time timestamp1

  42. t2(timestamp1) S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  43. Now SUPPLIER Demonstration Un Fix Valid Time

  44. t3 (now) S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  45. SUPPLIER Demonstration Fix Valid Time Now + 2 days

  46. t4 (now+2days) S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  47. delete S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  48. Now SUPPLIER Demonstration Un Fix Valid Time

  49. t5 (now) S1 S2 S3 S4 S5 Transaction time = now DEMO 1

  50. eovt S1 S2 S3 S4 S5 Transaction time = now DEMO 1

More Related