Developing Time-Oriented Database Applications in SQL
RICHARD T. SNODGRASS

Contents
Foreword by Jim Gray vii
Foreword by Jim Melton ix
Preface xvii
1 Introduction 1
1.1 A Triad of Triples 2
1.2 The SQL Standard 4
1.3 Conventions 5
1.4 Implementation Considerations 7
1.5 Readings 8
2 Fundamental Concepts 11
2.1 Valid-Time State Tables 12
2.2 Transaction-Time State Tables 18
2.3 Bitemporal Tables 20
2.4 Summary 22
2.5 Readings 23
3 Instants and Intervals 25
3.1 Instants 26
3.2 Intervals 30
x i i CONTENTS
3.3 Predicates 33
3.4 Constructors 36
3.5 Implementation Considerations 42
3.6 The Year 2000 Problem* 63
3.7 Subtleties* 74
3.8 Implementation Considerations* 83
3.9 Summary 84
3.10 Readings 85
4 Periods 89
4.1 Literals 90
4.2 Predicates 90
4.3 Constructors 93
4.4 Implementation Considerations 97
4.5 Summary 108
4.6 Readings 108
5 Dening State Tables 111
5.1 Initial Schema 112
5.2 Adding History 113
5.3 Temporal Keys 117
5.4 Handling Now 119
5.5 Uniqueness Reexamined 121
5.6 Referential Integrity 126
5.7 Constraint Attributes* 131
5.8 Implementation Considerations 132
5.9 Summary 139
5.10 Readings 140
6 Querying State Tables 143
6.1 Extracting the Current State 143
6.2 Extracting Prior States 145
CONTENTS x i i i
6.3 Sequenced Queries 145
6.4 Nonsequenced Variants 156
6.5 Eliminating Duplicates 158
6.6 Implementation Considerations 169
6.7 Summary 173
6.8 Readings 174
7 Modifying State Tables 177
7.1 Current Modications 177
7.2 Sequenced Modications 188
7.3 Nonsequenced Modications 197
7.4 Modications That Mention Other Tables* 198
7.5 Temporal Partitioning* 206
7.6 Implementation Considerations 213
7.7 Summary 215
7.8 Readings 216
8 Retaining a Tracking Log 219
8.1 Dening the Tracking Log 220
8.2 Queries 222
8.3 Modications 229
8.4 Permitting Insertions 230
8.5 Backlogs 233
8.6 Using After-Images Consistently 235
8.7 Transaction Semantics* 240
8.8 Renements* 243
8.9 Implementation Considerations 244
8.10 Summary 248
8.11 Readings 250
9 Transaction-Time State Tables 253
9.1 Denition 254
9.2 Maintenance 255
x i v CONTENTS
9.3 Queries 259
9.4 Temporal Partitioning* 262
9.5 Vacuuming* 268
9.6 Implementation Considerations 272
9.7 Summary 273
9.8 Readings 275
10 Bitemporal Tables 277
10.1 Denition 278
10.2 Modications 282
10.3 Queries 307
10.4 Integrity Constraints 323
10.5 Temporal Partitioning* 329
10.6 Vacuuming* 337
10.7 Implementation Considerations 339
10.8 Summary 339
10.9 Readings 340
11 Temporal Database Design 343
11.1 Properly Sequencing the Design 343
11.2 Conceptual Design 345
11.3 Logical Design 355
11.4 Physical Design 375
11.5 Advanced Design Aspects* 377
11.6 Benets 382
11.7 Application Development 383
11.8 Implementation Considerations 396
11.9 Summary 397
11.10 Readings 397
CONTENTS xv
12 Language Directions 401
12.1 SQL-92 401
12.2 SQL-92 Limitations 401
12.3 SQL3 402
12.4 Periods 403
12.5 Dening Valid-Time State Tables 406
12.6 Querying State Tables 411
12.7 Modifying State Tables 416
12.8 Retaining a Tracking Log 421
12.9 Transaction-Time State Tables 426
12.10 Bitemporal Tables 427
12.11 Capstone Case 437
12.12 Migration 446
12.13 Additional Constructs of SQL3* 455
12.14 Implementation Considerations 457
12.15 Summary 460
12.16 Readings 465
13 Prospects 469
Glossary 471
Bibliography 479
Author Index 485
Subject Index 487
About the Author 502
About the CD-ROM 503