Developing Time-Oriented Database Applications in SQL

by Snodgrass, Richard T.; Jensen, Christian S.

• ISBN13: 9781558604360
• ISBN10: 1558604367
• Format: Paperback
• Copyright: 1999-07-01
• Publisher: Elsevier Science Ltd

Summary

Whether you're a database designer, programmer, analyst, or manager, you've probably encountered some of the challenges-and experienced some of the frustrations-associated with time-varying data. Where do you turn to fix the problem and see that it doesn't happen again? In Developing Time-Oriented Database Applications in SQL, a leading SQL researcher teaches you effective techniques for designing and building database applications that must integrate past and current data. Written to meet a pervasive, enduring need, this book will be indispensible if you happen to be part of the flurry of activity leading up to Y2K. The enclosed CD-ROM contains all of the code fragments-implemented for Oracle8 Server, IBM DB2 Universal Database, Microsoft SQL Server, and other systems-and evaluation copies of the programs discussed in the book. * Offers incisive advice on recording temporal data using SQL data types, defining appropriate integrity constraints, updating temporal tables, and querying temporal tables with interactive and embedded SQL. * Provides case studies detailing real-world problems and solutions in areas such as event data, state-based data, partitioned data, and audit logs. * Contains over 400 code fragments with detailed explanations.

Author Biography

Richard T. Snodgrass is professor of computer science at the University of Arizona. His work on time-varying databases is well known through his heavily cited books and papers, his column in Database Programming and Design, and his contributions to the SQL3 standard. Snodgrass also codirects TimeCenter, an international center for the support of the temporal database applications in traditional and emerging DBMS technologies.

Table of Contents

Foreword	vii
Jim Gray
Foreword	ix
Jim Melton
Preface	xvii
Introduction	1	(10)
A Triad of Triples	2	(2)
The SQL Standard	4	(1)
Conventions	5	(2)
Implementation Considerations	7	(1)
Readings	8	(3)
Fundamental Concepts	11	(14)
Valid-Time State Tables	12	(6)
Transaction-Time State Tables	18	(2)
Bitemporal Tables	20	(2)
Summary	22	(1)
Readings	23	(2)
Instants and Intervals	25	(64)
Instants	26	(4)
Intervals	30	(3)
Predicates	33	(3)
Constructors	36	(6)
Implementation Considerations	42	(21)
The Year 2000 Problem*	63	(11)
Subtleties*	74	(9)
Implementation Considerations*	83	(1)
Summary	84	(1)
Readings	85	(4)
Periods	89	(22)
Literals	90	(1)
Predicates	90	(3)
Constructors	93	(4)
Implementation Considerations	97	(11)
Summary	108	(1)
Readings	108	(3)
Defining State Tables	111	(32)
Initial Schema	112	(1)
Adding History	113	(4)
Temporal Keys	117	(2)
Handling Now	119	(2)
Uniqueness Reexamined	121	(5)
Referential Integrity	126	(5)
Constraint Attributes*	131	(1)
Implementation Considerations	132	(7)
Summary	139	(1)
Readings	140	(3)
Querying State Tables	143	(34)
Extracting the Current State	143	(2)
Extracting Prior States	145	(1)
Sequenced Queries	145	(11)
Nonsequenced Variants	156	(2)
Eliminating Duplicates	158	(11)
Implementation Considerations	169	(4)
Summary	173	(1)
Readings	174	(3)
Modifying State Tables	177	(42)
Current Modifications	177	(11)
Sequenced Modifications	188	(9)
Nonsequenced Modifications	197	(1)
Modifications That Mention Other Tables*	198	(8)
Temporal Partitioning*	206	(7)
Implementation Considerations	213	(2)
Summary	215	(1)
Readings	216	(3)
Retaining a Tracking Log	219	(34)
Defining the Tracking Log	220	(2)
Queries	222	(7)
Modifications	229	(1)
Permitting Insertions	230	(3)
Backlogs	233	(2)
Using After-Images Consistently	235	(5)
Transaction Semantics*	240	(3)
Refinements*	243	(1)
Implementation Considerations	244	(4)
Summary	248	(2)
Readings	250	(3)
Transaction-Time State Tables	253	(24)
Definition	254	(1)
Maintenance	255	(4)
Queries	259	(3)
Temporal Partitioning*	262	(6)
Vacuuming*	268	(4)
Implementation Considerations	272	(1)
Summary	273	(2)
Readings	275	(2)
Bitemporal Tables	277	(66)
Definition	278	(4)
Modifications	282	(25)
Queries	307	(16)
Integrity Constraints	323	(6)
Temporal Partitioning*	329	(8)
Vacuuming*	337	(2)
Implementation Considerations	339	(1)
Summary	339	(1)
Readings	340	(3)
Temporal Database Design	343	(58)
Properly Sequencing the Design	343	(2)
Conceptual Design	345	(10)
Logical Design	355	(20)
Physical Design	375	(2)
Advanced Design Aspects*	377	(5)
Benefits	382	(1)
Application Development	383	(13)
Implementation Considerations	396	(1)
Summary	397	(1)
Readings	397	(4)
Language Directions	401	(68)
SQL-92	401	(1)
SQL-92 Limitations	401	(1)
SQL3	402	(1)
Periods	403	(3)
Defining Valid-Time State Tables	406	(5)
Querying State Tables	411	(5)
Modifying State Tables	416	(5)
Retaining a Tracking Log	421	(5)
Transaction-Time State Tables	426	(1)
Bitemporal Tables	427	(10)
Capstone Case	437	(9)
Migration	446	(9)
Additional Constructs of SQL3*	455	(2)
Implementation Considerations	457	(3)
Summary	460	(5)
Readings	465	(4)
Prospects	469	(2)
Glossary	471	(8)
Bibliography	479	(6)
Author Index	485	(2)
Subject Index	487	(15)
About the Author	502	(1)
About the CD-ROM	503