Managing Time in Relational Databases: How to Design, Update and Query Temporal Data

✍ Scribed by Tom Johnston, Randall Weis

• Publisher: Morgan Kaufmann
• Year: 2010
• Tongue: English
• Leaves: 490
• Edition: Har/Psc
• Category: Library

✦ Synopsis

No doubt about it, this is an old-school college text book with hard cover and durable binding.
This book is no light read and is focused on the design and management of relational database tables using uni-temporal and bi-temporal data primarily in a business setting such as healthcare systems utilizing transaction processing.
Objectives as mentioned in the book:
* Seamless Access to Temporal Data -- One objective of this book is to describe how to manage uni-temporal and bi-temporal data in relational databases in such a way that they can be seamlessly accessed together with current data.
* Encapsulation Of Temporal Data Structures and Processes -- A second objective is to describe how to encapsulate the complexities of uni-temporal and bi-temporal data management.
* Enterprise Contextualization -- A third objective of this book is to explain how to implement temporal data management as an enterprise solution.
* Internalization of Pipeline Datasets -- The final objective of this book is to describe how to bring pending transactions into their production tables that are their targets, and how to retain posted transactions in those same tables.
Working with temporal data on a daily basis I find this subject interesting and this book relevant to my needs but I can see where this subject matter would apply to a niche market.
This textbook is not for the casual reader and it assumes that the reader has a level of database skills already in place, has a familiarity with temporal data, some familiarity with relational database tables, and assumes the reader has some SQL coding skills or is familiar with the syntax.
There are many examples throughout the book and all examples are in black & white with diagrams or coding examples; there are no glossy photos. Although I have not yet been able to go through the entire book I can see that I will benefit from the information contained and apply it to my daily work.
This book is not for everyone and is not misleading in trying to sell itself as an all-in-one database development resource. The title says it all -- Managing Time in Relational Databases: How to Design, Update and Query Temporal Data

✦ Table of Contents

Cover Page
......Page 1
Title Page
......Page 2
Copyright......Page 5
About the Authors......Page 6
Preface......Page 8
Encapsulation of Temporal Data Structures and Processes......Page 10
Enterprise Contextualization......Page 12
Internalization of Pipeline Datasets......Page 14
Asserted Versioning as Methodology and As Software......Page 16
Acknowledgements......Page 17
Tom Johnston's Acknowledgements......Page 18
Randy Weis' Acknowledgements......Page 19
Glossary References......Page 21
An Iintroduction to Temporal Data Management
......Page 23
Non-Temporal, Uni-Temporal and Bi-Temporal Data......Page 25
Not to Be Misled......Page 29
Glossary References......Page 30
Contents......Page 32
Excluding Time From Databases: A Ubiquitous Paradigm......Page 33
Clocks and Clock Ticks......Page 34
Time Periods and Date Pairs......Page 75
Reconstructable Temporal Data......Page 35
The Inmon/Kimball Religious Wars......Page 37
The Near Future and the Far Future......Page 295
Philosophical Concepts......Page 416
Inmon and Kimball: Going Forward......Page 38
Data Cubes......Page 39
Slowly Changing Dimensions......Page 40
The Future of Databases: Seamless Access to Temporal Data......Page 41
Closing in on Seamless Access......Page 43
Glossary References......Page 45
Contents......Page 47
Partitioned Semantic Trees......Page 48
Jointly Exhaustive......Page 51
Lengthening an Episode Forwards......Page 52
A Taxonomy of Methods for Managing Temporal Data......Page 54
The Root Node of the Taxonomy......Page 55
P1 [equals] P2......Page 338
Advanced Indexing Strategies......Page 87
Temporal Referential Integrity......Page 58
State Temporal Data......Page 60
Uni-Temporal State Data......Page 61
Glossary References......Page 254
Glossary References......Page 255
Bi-Temporal State Data......Page 63
The Asserted Versioning Temporal Model......Page 64
Glossary References......Page 66
An Introduction to Asserted Versioning......Page 67
Glossary References......Page 182
The Roots of Asserted Versioning......Page 70
1988: Architecture for a Business and Information System......Page 413
Performance Tuning Bi-Temporal Tables Using Indexes......Page 367
General Considerations......Page 120
Indexes to Optimize Queries......Page 369
The Second Physical Transaction......Page 84
Temporal Upward Compatibility......Page 88
The Period Datatype......Page 89
Temporal Primary Keys......Page 90
Glossary References......Page 410
Null Vs. 12/31/9999......Page 387
Contents......Page 93
Bi-Temporal, Conventional, and Non-Temporal Databases......Page 95
An Insert Transaction......Page 97
P1 [finishes] P2......Page 332
Posted Projections: Past Claims About the Future......Page 99
"Trivial" Definitions......Page 420
Logical Delete Versioning......Page 101
Temporal Gap Versioning......Page 104
Asserted Versioning as a Bridge and As a Destination......Page 105
Effective Time Versioning and Retroactive Updates......Page 107
Pending Projections: Future Claims About the Future......Page 321
The Scope and Limits of Best Practice Versioning......Page 110
Glossary References......Page 111
Contents......Page 113
Objects, Episodes, Versions and Assertions......Page 115
Data Volumes in Bi-Temporal and in Conventional Databases......Page 365
Episodes......Page 116
Row-Level Vs. Column-Level Versioning......Page 118
Temporal Entity Integrity......Page 122
One Clock Tick: Convention Or Constraint?......Page 124
Child-Side Temporal Referential Integrity......Page 127
Parent-Side Temporal Referential Integrity......Page 128
Completeness Checks......Page 129
Indexes on Tri Children......Page 385
The First Physical Transaction......Page 133
Surrogate Keys, Business Keys and Asserted Version Tables......Page 134
Glossary References......Page 135
Contents......Page 137
The Asserted Version Table......Page 138
The Temporal Primary Key......Page 140
The Temporal Foreign Key......Page 141
The Temporal Insert Transaction......Page 142
The First Physical Transaction......Page 166
Additional Diagrams and Notations......Page 148
A Second Temporal Update Transaction......Page 150
The Conventional Table View......Page 151
The Version Table View......Page 152
A Temporal Delete Transaction......Page 270
Views and Mental Images......Page 155
Contents......Page 158
The Representation of Objects in Time Periods......Page 159
Basic Temporal Transactions: The Mental Model......Page 161
A Temporal Insert Transaction......Page 162
A Temporal Update Transaction......Page 164
Miscellaneous Metadata......Page 195
Pending Updates: Future Claims About the Present......Page 269
The Second Physical Transaction......Page 170
A Temporal Delete Cascade......Page 171
The Second Physical Transaction......Page 174
Glossary References......Page 176
Designing, Maintaining and Querying Asserted Version Databases......Page 178
Contents......Page 184
Grammar......Page 186
Components......Page 188
Table Type Metadata......Page 190
Temporal Foreign Key Metadata......Page 191
Business Key Metadata......Page 193
Foreign Key Mapping Metadata......Page 194
The Physical Data Model......Page 197
Generating an Asserted Versioning Database From a Physical Data Model and Metadata......Page 198
Temporalizing the Physical Data Model......Page 199
Generating Temporal Entity and Temporal Referential Integrity Constraints......Page 202
P1 [excludes] P2......Page 203
A Real Redundancy in the Asserted Versioning Schema......Page 205
Glossary References......Page 206
Contents......Page 208
Effective Time Within Assertion Time......Page 209
Explicitly Temporal Transactions: The Mental Model......Page 212
A Taxonomy of Temporal Extent State Transformations......Page 214
The Asserted Versioning Temporal Transactions......Page 217
The Temporal Insert Transaction: Mechanics......Page 221
The Temporal Update Transaction......Page 223
The Temporal Update Transaction: Mechanics......Page 225
The Temporal Delete Transaction......Page 226
P1 [before] P2......Page 227
Glossary References......Page 228
Temporal Transactions On Single Tables......Page 230
Temporal Managed Objects and Temporal Referential Integrity......Page 231
Assertions, Statements and Time......Page 233
Lengthening an Episode Backwards......Page 234
Merging Episodes......Page 237
The Temporal Delete Transaction......Page 243
Deleting One or More Episodes......Page 244
Shortening an Episode Forwards......Page 245
Asserted Versioning as a Destination......Page 247
Splitting an Episode......Page 248
An Allen Relationship Completeness Check......Page 250
Temporal Transactions on Multiple Tables......Page 257
Child Managed Objects......Page 259
Parent Managed Objects......Page 260
Temporal Referential Integrity: the Basic Diagram......Page 261
Foreign Keys and Temporal Foreign Keys......Page 263
Tfks: a Data Part and a Function Part......Page 265
Temporal Transactions and Associative Tables......Page 266
Tri With Multiple Tfks......Page 267
P1 [fills] P2......Page 268
Contents......Page 277
The Semantics of Deferred Assertion Time......Page 278
The Internalization of Pipeline Datasets......Page 283
A Deferred Update to a Current Episode......Page 285
2000: Primary Key Reengineering Projects......Page 414
P1 [occupies] P2......Page 291
Completing the Deferred Update to a Deferred Assertion......Page 294
Approving a Deferred Assertion......Page 296
Deferred Assertions and Temporal Referential Integrity......Page 300
Ongoing Research and Development......Page 301
Contents......Page 304
Internalized Pipeline Datasets......Page 307
Pipeline Datasets as Queryable Objects......Page 311
Posted History: Past Claims About the Past......Page 312
Current Data: Current Claims About the Present......Page 316
Current Projections: Current Claims About the Future......Page 318
Pending History: Future Claims About the Past......Page 319
Mirror Images of the Nine-Fold Way......Page 322
The Value of Internalizing Pipeline Datasets......Page 323
Allen Relationship and Other Queries......Page 326
Allen Relationship Queries......Page 328
P1 [starts] P2......Page 331
P1 [aligns] P2......Page 334
P1 [during] P2......Page 335
P1 [overlaps] P2......Page 340
P1 [intersects] P2......Page 341
P1 [meets] P2......Page 344
Point in Time to Period of Time Queries......Page 348
T1 [starts] P1......Page 349
T1 [finishes] P1......Page 350
T1 [during] P1......Page 351
T1 [occupies] P1......Page 352
T1 [before] P1......Page 353
T1 [meets] P1......Page 354
P1 [excludes] T1......Page 355
T1 [before] T2......Page 356
T1 [equals] T2......Page 357
A Claims Processing Example......Page 358
In Other Words......Page 361
Glossary References......Page 362
Optimizing Asserted Versioning Databases......Page 364
The Optimization Drill: Modify, Monitor, Repeat......Page 366
Currency Flags......Page 374
Using a Currency Flag to Optimize Queries......Page 375
Other Uses of the Circa Flag......Page 379
Asserted Versioning's Non-Unique Primary Keys......Page 381
Indexes on Tri Parents......Page 383
Partitioning......Page 388
Clustering......Page 390
Materialized Query Tables......Page 391
Standard Tuning Techniques......Page 392
Glossary References......Page 393
Seamless Access to Temporal Data......Page 395
Encapsulation of Temporal Data Structures and Processes......Page 397
1996: Building the Data Warehouse......Page 398
Query Encapsulation......Page 401
The Internalization of Pipeline Datasets......Page 402
Enterprise Contextualization......Page 404
Asserted Versioning as a Bridge......Page 405
2002: Temporal Data and the Relational Model......Page 415
The Computer Science Literature......Page 417
Dates and Times......Page 418
Non-Standard Glossary Definitions......Page 419
Glossary Entries......Page 421
Index......Page 477

✦ Subjects

Информатика и вычислительная техника;Базы данных;