Two recent trends—the plummeting cost of storage and the widespread adoption of data warehouse technology—have led to an increasing interest in temporal databases. Indeed, the idea of maintaining and processing historical data has become not just a goal but also a reality for many organizations. Consequently, the ability to deal properly with the time dimension in databases has become an increasingly important practical problem. And today’s mainstream DBMS products are beginning to include features that are specifically designed to help with this important requirement—though it has to be said too that the support currently found in those products falls a long way short of the theoretical ideal.
This Master Class video describes and explains that theoretical ideal—which, interestingly, is 100 percent consistent with the classical relational model—in depth. It also discusses, in depth, the new temporal support to be found in the SQL standard (“SQL:2011”). This video includes five major parts:
- A Review of Relational Concepts—consists of a careful review of the relational model, with emphasis on features that seem not to be as widely understood as they should be.
- Laying the Foundations—explains some of the basic problems of temporal databases (some of which are far from obvious, incidentally) and lays the groundwork for solving those problems.
- Building on the Foundations—uses the material from Part II as a basis for addressing a variety of practical issues, including temporal database design, temporal integrity constraints, and temporal querying and updating.
- SQL Support—explains the pertinent features of the SQL standard in detail..
Table of Contents
- Introduction 00:09:04
- Types 00:29:05
- Relations 00:35:13
- Relational Algebra - Part 1 00:23:05
- Relational Algebra - Part 2 00:25:30
- Relation Variables 00:40:14
- Time and the Database 00:29:42
- What's the Problem? 00:25:36
- Intervals 00:32:50
- Expand and Collapse 00:13:41
- Pack and Unpack I - The Single Attribute Case 00:25:11
- Pack and Unpack II - The Multiattribute Case 00:21:26
- Generalizing the Relational Operators 00:30:06
- Database Design I - Structure - Part 1 00:37:39
- Database Design I - Structure - Part 2 00:15:19
- Database Design II - Keys and Related Constraints - Part 1 00:16:42
- Database Design II - Keys and Related Constraints - Part 2 00:17:43
- Database Design III - General Constraints 00:32:33
- Queries 00:18:46
- Updates - Part 1 00:28:16
- Updates - Part 2 00:20:03
- Logged Time and Stated Time 00:30:48
- Point and Interval Types Revisited 00:28:01
- Numeric Types 00:20:34
- Interval Types Revisited 00:18:05
- SQL Support - Periods 00:29:32
- SQL Support - Queries 00:16:08
- SQL Support - System Time 00:18:00
- SQL Support - Summary and Assessment 00:20:11
- Appendixes 00:25:14
- Title: Time and Relational Theory
- Release date: January 2015
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9781491917763