Time and Relational Theory

Temporal Databases in the Relational Model and SQL

C.J. Date, Hugh Darwen, Nikos Lorentzos

• Publisher: Morgan Kaufmann
• Year: 2014
• ISBN:
  9780128006757
• Edition: 2
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Time and Relational Theory provides an in-depth description of temporal database systems, which provide special facilities for storing, querying, and updating historical and future data. Traditionally, database management systems provide little or no special support for temporal data at all. This situation is changing because:

• Cheap storage enables retention of large volumes of historical data in data warehouses
• Users are now faced with temporal data problems, and need solutions
• Temporal features have recently been incorporated into the SQL standard, and vendors have begun to add temporal support to their DBMS products

Based on the groundbreaking text Temporal Data & the Relational Model (Morgan Kaufmann, 2002) and new research led by the authors, Time and Relational Theory is the only book to offer a complete overview of the functionality of a temporal DBMS. Expert authors Nikos Lorentzos, Hugh Darwen, and Chris Date describe an approach to temporal database management that is firmly rooted in classical relational theory and will stand the test of time.

This book covers the SQL: 2011 temporal extensions in depth and identifies and discusses the temporal functionality still missing from SQL.

• Understand how the relational model provides an ideal basis for taming the complexities of temporal databases
• Learn how to analyze and evaluate commercial temporal products with this timely and important information
• Be able to use sound principles in designing and using temporal databases
• Understand the temporal support recently added to SQL with coverage of the new SQL features in this unique, accurate, and authoritative reference
• Appreciate the benefits of a truly relational approach to the problem with this clear, user friendly presentation

Part I

A Review of Relational Concepts

Outline

Part I A Review of Relational Concepts

Chapter 1 Types and Relations

Chapter 2 Relational Algebra

Chapter 3 Relation Variables

Part I A Review of Relational Concepts

This book assumes a basic familiarity with the relational model. Thus, this first part is intended merely as a quick refresher course on that model; in effect, it summarizes, and serves as a reference source for, material you’ll be expected to know when we get to the temporal discussions in Parts II, III, and IV. It consists of three chapters:

1. Types and Relations

2. Relational Algebra

3. Relation Variables

Of course, tutorials on the relational model and related matters can be found in many places (see, e.g., references [28], [42], and [45]); thus, if you’re familiar with any of those references, you probably don’t need to read this part of the book in detail. However, if your knowledge of the relational model derives from other sources—especially ones based on SQL—then you probably do need to read these chapters fairly carefully, because they emphasize numerous important topics that such sources typically don’t. Such topics include:

■ “Possible representations,” selectors, and THE_ operators

■ Relation types

■ Relation values vs. relation variables

■ Predicates and propositions

■ Relation valued attributes

■ Relational assignment

■ The fundamental role of constraints

■ Less well known but important operators such as EXTEND, MATCHING, and NOT MATCHING

and many others.

Note: Chapter 1 also introduces the suppliers-and-shipments database, which serves as the basis for almost all of the examples in later chapters. Even if you decide just to skim this part of the book, therefore, you should at least take a look at that sample database before moving on to Part II.

Chapter 1

Types and Relations

This chapter is the first of three devoted to reviewing the basics of relational theory. It explains relations as such, as well as the supporting notion of types (aka domains), in considerable depth. With regard to types; it discusses values, variables, operators, parameters, arguments, expressions, and polymorphism; equality and assignment; system defined vs. user defined types; scalar vs. nonscalar types; possible vs. physical representations; type generators and generated vs. nongenerated types; type constraints; and selectors, literals, and THE_ operators. With regard to relations, it discusses tuples, attributes, and relation headings and bodies; relation and tuple types; TABLE_DEE and TABLE_DUM; relation valued attributes; predicates and propositions; and The Closed World Assumption. The chapter also introduces the running example (i.e., the suppliers-and shipments-database).

Keywords

domain; relation; tuple; attribute; relation heading; relation body; type theory; relation predicate; The Closed World Assumption

What type of relation do you mean?

—Anon.:

Where Bugs Go

The relational model is, of course, defined in terms of relations, and relations in turn are defined in terms of types. This first chapter explains these two constructs in detail. First, however, it introduces the running example.

The Running Example

Most of the examples in this book are based on the well known suppliers-and-parts database—or, more precisely, on a simplified version of that database that we refer to as suppliers and shipments. Fig. 1.1 shows a set of sample values for that simplified database (and please note that we’ll be assuming those specific sample values in examples throughout this part of the book, barring explicit statements to the contrary). Note: In case you happen to be familiar with the more usual version of this database as discussed in, e.g., reference [45], we explain here briefly what the simplifications consist of:

■ First of all, we’ve dropped the parts relvar P entirely (hence the revised name “suppliers and shipments”). Note: The term relvar is short for relation variable. Relation variables are discussed in detail in Chapter 3.

■ Second, we’ve removed attribute QTY (“quantity”) from the shipments relvar SP, leaving just attributes SNO and PNO.

■ Third, we (re)interpret that revised SP relvar thus: “Supplier SNO is currently able to supply part PNO. ” In other words, instead of standing for actual shipments of parts by suppliers, as it did in, e.g., reference [45], relvar SP now stands for what might be called potential shipments—i.e., the ability of certain suppliers to supply certain parts.

Fig. 1.1 The suppliers-and-shipments database (first version)–sample values

Overall, then, the database is meant to be interpreted as follows:

■ Relvar S represents suppliers under contract. Each such supplier has a supplier number (SNO), unique to that supplier; a supplier name (SNAME), not necessarily unique (though the sample SNAME values shown in Fig. 1.1 do happen to be unique); a status or rating value (STATUS); and a location (CITY). Note: In the rest of this book we’ll refer to “suppliers under contract,” most of the time, as just suppliers for short.

■ Relvar SP represents potential shipments—it shows which suppliers are capable of supplying, or shipping, which parts. Obviously, the combination of SNO and PNO values for a given “potential shipment” is unique to that shipment. Note: In the rest of this book we’ll take the liberty of referring to “potential shipments”—somewhat inaccurately, but very conveniently—as just shipments for short. Also, we’ll assume it’s possible for a given supplier to be under contract at some time and yet not actually be able to supply any parts at that time (supplier S5 is a case in point, given the sample values of Fig. 1.1).

Here for purposes of reference are formal definitions for the example, expressed in a language called Tutorial D—or, rather, in a version of that language that’s been tailored slightly in order to suit the purposes of this book. Tutorial D is a computationally complete programming language with fully integrated database functionality and, we believe, more or less self-explanatory syntax. It was introduced in the first (1998) edition of reference [52] as a vehicle for teaching database concepts. All coding examples in this book are expressed in that language or certain specified extensions thereto (except, of course, for the SQL-specific examples in Part IV).

TYPE SNO … ;

TYPE PNO … ;

TYPE NAME … ;

VAR S BASE

 RELATION

 { SNO SNO , SNAME NAME, STATUS INTEGER , CITY CHAR }

 KEY { SNO } ;

VAR SP BASE

 RELATION

 { SNO SNO , PNO PNO }

 KEY { SNO , PNO }

 FOREIGN KEY { SNO } REFERENCES S ;

To elaborate briefly (note, however, that all of the technical issues illustrated by this example and discussed in outline below will be explained in more detail either later in this chapter or somewhere in the next two):

■ The first three lines are type definitions (shown here in outline only;...

Table of contents

• Cover image
• Title page
• Table of Contents
• Copyright
• Dedication
• About the Authors
• Preface
• Part I: A Review of Relational Concepts
• Part II: Laying the Foundations
• Part III: Building on the Foundations
• Part IV: SQL Support
• Appendixes
• Index