The TSQL2 Temporal Query Language

674 Pages·1995·35.76 MB·English

by  Richard T. Snodgrass

THE TSQL2 TEMPORAL
QUERY LANGUAGE
THE KLUWER INTERNATIONAL SERIES
IN ENGINEERING AND COMPUTER SCIENCE
THE TSQL2 TEMPORAL
QUERY LANGUAGE
edited by
Richard T. Snodgrass
University of Arizona
Tucson, AZ
The TSQL2 Language Design Committee
SPRINGER SCIENCE+BUSINESS MEDIA, LLC
Library of Congress Cataloging-in-Publication Data
A C.I.P. Catalogue record for this book is available
from the Library of Congress.
ISBN 978-1-4613-5966-1 ISBN 978-1-4615-2289-8 (eBook)
DOI 10.1007/978-1-4615-2289-8
Copyright @ 1995 Springer Science+Business Media New York
Originally published by Kluwer Academic Publishers in 1995
Softcover reprint ofthe hardcover Ist edition 1995
AII rights reserved. No part of this publication may be reproduced, stored in
a retrieval system or transmitted in any form or by any means, mechanical,
photo-copying, recording, or otherwise, without the prior written permission of
the publisher, Springer Science+Business Media, LLC.
Printed on acid-free paper.
CONTENTS
LIST OF FIGURES xiii
LIST OF TABLES xvii
PREFACE xxi
Part I OVERVIEW 1
1 RATIONALE FOR A TEMPORAL
EXTENSION TO SQL 3
1.1 Introduction 3
1.2 Temporal Applications 4
1.3 Temporal Data Types and Literals 5
1.4 Valid Time Support 8
1.5 Implementation Aspects 17
1.6 Summary 18
2 INTRODUCTION TO TSQL2 19
2.1 Scope 19
2.2 Desired Features 21
2.3 Concepts 23
2.4 SQL-92 Compatibility 30
2.5 Implementation 30
3 TSQL2 TUTORIAL 33
3.1 The Context 33
3.2 Recording the Media Plan in a Temporal Database 35
v
VI THE TSQL2 TEMPORAL QUERY LANGUAGE
3.3 Evaluating the Media Plan 38
3.4 Accommodating Indeterminacy 42
3.5 Querying Multiple Versions 43
3.6 Changing the Schema 44
3.7 Vacuuming 45
3.8 Summary 46
4 A SECOND EXAMPLE 49
4.1 Introduction 49
4.2 A Database Schema for the Test Suite 50
4.3 The Test Suite Data 53
4.4 The Test Suite Queries 57
Part II LANGUAGE DESIGN 74
5 THE BASELINE CLOCK 77
5.1 Overview 77
5.2 Clocks 78
5.3 The Baseline Clock 83
5.4 Clocks Reexamined 89
6 THE DATA MODEL FOR TIME 97
6.1 Introduction 97
6.2 Summary 101
7 SUPPORTING MULTIPLE CALENDARS 103
7.1 Introduction 103
7.2 Timestamps, Calendars, and Calendric Systems 104
7.3 Calendar Support 108
7.4 Summary 120
8 TEMPORAL DATA TYPES 123
8.1 Introduction 123
8.2 Related Work 123
8.3 SQL Language Extensions 125
8.4 Summary and Evaluation 147
Contents vii
9 THE SURROGATE DATA TYPE 153
10 THE TSQL2 DATA MODEL 157
10.1 Introduction 157
10.2 Dimensions of Time 159
10.3 Previous Data Models 160
10.4 Context 185
10.5 A New Data Model 186
10.6 Representation Schemes 194
10.7 Semantic Equivalence 219
10.8 Algebras and Equivalence 226
10.9 Summary 239
11 SCHEMA SPECIFICATION 241
11.1 Introduction 241
11.2 Informal Definition 242
11.3 Syntactic Considerations 243
12 THE FROM CLAUSE 245
12.1 Introduction 245
12.2 Informal Definition 246
12.3 Expressive Power 249
13 VALID-TIME SELECTION AND
PROJECTION 251
13.1 Introduction 251
13.2 Survey 252
13.3 Design Criteria 282
13.4 Valid-time Selection in TSQL2 284
13.5 Valid-time Projection in TSQL2 291
13.6 Summary 297
14 MODIFICATION 299
14.1 The Insert Statement 299
14.2 The Delete Statement 301
14.3 The Update Statement 303
Vlll THE TSQL2 TEMPORAL QUERY LANGUAGE
15 CURSORS 305
16 EVENT TABLES 311
16.1 Introduction 311
16.2 The Data Model 311
16.3 Schema Specification 317
16.4 From Clause 317
16.5 Valid-time Selection 317
16.6 Valid-time Projection 318
16.7 Conclusion 318
17 TRANSACTION TIME SUPPORT 319
17.1 Introduction 319
17.2 Transaction-time Table Definition 321
17.3 Restructuring 321
17.4 Generation of Transaction-time Values 322
17.5 Transaction-time Selection 322
17.6 Transaction-time Projection 324
18 TEMPORAL INDETERMINACY 327
18.1 Overview 327
18.2 Motivating Example 328
18.3 Extending the Data Model with Indeterminacy 331
18.4 Syntactic Extensions 335
18.5 Semantic Extensions 336
18.6 Implementation Overview 345
18.7 Summary 346
19 TEMPORAL GRANULARITY 347
19.1 Introduction 347
19.2 Motivation 348
19.3 Model of Time 350
19.4 Accommodating Mixed Granularities 358
19.5 Implementation 375
19.6 Related Work 381
19.7 Summary 382
Contents IX
20 "NOW" 385
20.1 Introduction 385
20.2 Now in User-defined and Valid Time 386
20.3 Now at Run-time 389
20.4 Now in Transaction Time 391
20.5 Summary 394
21 AGGREGATES 395
21.1 Introduction 395
21.2 Taxonomy of Temporal Aggregates 395
21.3 Aggregates in TSQL2 406
21.4 Comparison with Other Languages 414
21.5 Possible Complaints 423
22 SCHEMA VERSIONING 427
22.1 Introduction 427
22.2 Principles 429
22.3 Interaction with the Data Model 430
22.4 Schema Versioning Considerations 433
22.5 Summary 447
23 VACUUMING 451
23.1 Introduction 451
23.2 Motivation 452
23.3 Using Cut-off Points 456
Part III IMPLEMENTATION ISSUES 463
24 AN ARCHITECTURAL FRAMEWORK 465
24.1 Conventional Architecture 465
24.2 Data Dictionary and Data Files 469
24.3 DDL Compiler 470
24.4 Query Compiler 470
24.5 Run-time Evaluator 471
24.6 Transaction and Data Manager 472
24.7 Summary 473
x THE TSQL2 TEMPORAL QUERY LANGUAGE
25 A TIMESTAMP REPRESENTATION 475
25.1 Overview 475
25.2 Existing Timestamp Representations 477
25.3 Timestamp Formats 481
25.4 Indeterminate Timestamp Formats 487
25.5 Execution Efficiency 490
25.6 Summary 498
26 SQL-92 COMPATIBILITY ISSUES 501
26.1 Introduction 501
26.2 Primary Compatibility Considerations 501
26.3 The SQL92 Calendric System 503
26.4 Properties 503
27 AN ALGEBRA FOR TSQL2 505
27.1 Introduction 505
27.2 Conceptual Algebra 506
27.3 Extended Operators of the Conceptual Algebra 522
27.4 Expressive Power 523
27.5 Implementation 537
27.6 Summary and Future Work 546
Part IV LANGUAGE SPECIFICATION 547
28 LANGUAGE SYNTAX 549
29 SECTION 5 LEXICAL ELEMENTS 551
29.1 Section 5.2 <token> and <separator> 551
29.2 Section 5.3 <literal> 552
29.3 Section 5.4 Names and identifiers 561
30 SECTION 6 SCALAR EXPRESSIONS 563
30.1 Section 6.1 <data type> 563
30.2 Section 6.2 <value specification> 566
30.3 Section 6.3 <table reference> 568
30.4 Section 6.5 <set function specification> 570