SQL and Relational Theory: How to Write Accurate SQL Code

584 Pages·2015·7.04 MB·English

by  C.J. Date

SQL and
Relational Theory
How to Write Accurate SQL Code
C. J. Date
3r
d
E
ditio
n
/ theory / in / practice
SQL and Relational Theory
SQL is full of difficulties and traps for the unwary. You can avoid them if you understand relational
theory, but only if you know how to put that theory into practice. In this book, Chris Date explains
relational theory in depth, and demonstrates through numerous examples and exercises how you
can apply it to your use of SQL.
This third edition has been revised, extended, and improved throughout. Topics whose treatment
has been expanded include data types and domains, table comparisons, image relations, aggregate
operators and summarization, view updating, and subqueries. A special feature of this edition is a
new appendix on NoSQL and relational theory.
Could you write an SQL query to find employees who have worked at least once in every
■
programming department in the company? And be sure it’s correct?
Why is proper column naming so important?
■
Nulls in the database cause wrong answers. Why? What you can do about it?
■
How can image relations help you formulate complex SQL queries?
■
SQL supports “quantified comparisons,” but they’re better avoided. Why? And how?
■
Database theory and practice have evolved considerably since Codd first defined the relational
model, back in 1969. This book draws on decades of experience to present the most up to date
treatment of the material available anywhere. Anyone with a modest to advanced background in
SQL can benefit from the insights it contains. The book is product independent.
Chris Date has a stature that is unique in the database industry. He is best known for his textbook
An Introduction to Database Systems (Addison-Wesley). He enjoys a reputation that is second to
none for his ability to explain complex technical issues in a clear and understandable fashion.
US $39.99 CAN $45.99
ISBN: 978-1-491-94117-1
Twitter: @oreillymedia
facebook.com/oreilly
oreilly.com
Databases
SQL and
Relational Theory

How to Write Accurate SQL Code

THIRD EDITION

C. J. Date
SQL and Relational Theory: How to Write Accurate SQL Code (3rd edition)

by C. J. Date

Copyright © 2015 C. J. Date. All rights reserved.
Printed in the United States of America.

Published by O’Reilly Media, Inc.,
1005 Gravenstein Highway North, Sebastopol, CA 95472

O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also
available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/institutional
sales department: [phone] or [e-mail].

Printing History:
January 2009: First Edition.
December 2011: Second Edition.
October 2015: Third Edition.

Revision History:
2015-09-30 First release.
See http://www.oreilly.com/catalog/errata.csp?isbn=0636920046158 for release details.

The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. SQL and Relational Theory: How to Write
Accurate SQL Code and related trade dress are trademarks of O’Reilly Media, Inc.

While the publisher and the author have used good faith efforts to ensure that the information and instructions
contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions,
including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the
information and instructions contained in this work is at your own risk. If any code samples or other technology this
work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your
responsibility to ensure that your use thereof complies with such licenses and/or rights.

ISBN: 978-1-491-94117-1
[LSI]
Those who are enamored of practice without theory are like a
pilot who goes into a ship without rudder or compass
and never has any certainty where he is going.
Practice should always be based upon
a sound knowledge of theory.
—Leonardo da Vinci (1452-1519)

The trouble with people is not that they don’t know
but that they know so much that ain’t so.
—Josh Billings (1818-1885)

Languages die ...
mathematical ideas do not.
—G. H. Hardy (1877-1947)

Unfortunately, the gap between theory and practice
is not as wide in theory as it is in practice.
—Anon.

These are my principles.
If you don’t like them, I have others.
—Groucho Marx (1890-1977)

There is no royal road to geometry.
—Euclid (c. 365-275 BCE), attrib.

——— ♦♦♦♦♦ ———

To all those who think an exercise like this one is worthwhile,
and in particular to the memory of Lex de Haan,
who is very much missed
A b o u t t h e A u t h o r

C. J. Date is an independent author, lecturer, researcher, and consultant, specializing in relational
database technology. He is best known for his book An Introduction to Database Systems (8th
edition, Addison-Wesley, 2004), which has sold some 900,000 copies at the time of writing and
is used by several hundred colleges and universities worldwide. He is also the author of
numerous other books on database management, including most recently:

n From Ventus: Go Faster! The TransRelationalTM Approach to DBMS Implementation
(2002, 2011)

n From Addison-Wesley: Databases, Types, and the Relational Model: The Third Manifesto
(3rd edition, with Hugh Darwen, 2007)

n From Trafford: Logic and Databases: The Roots of Relational Theory (2007) and
Database Explorations: Essays on The Third Manifesto and Related Topics (with Hugh
Darwen, 2010)

n From Apress: Date on Database: Writings 2000-2006 (2007)

n From O’Reilly: Database Design and Relational Theory: Normal Forms and All That Jazz
(2012); View Updating and Relational Theory: Solving the View Update Problem (2013);
Relational Theory for Computer Professionals: What Relational Databases Are Really All
About (2013); and The New Relational Database Dictionary (2015, to appear)

Mr. Date was inducted into the Computing Industry Hall of Fame in 2004. He enjoys a
reputation that is second to none for his ability to explain complex technical subjects in a clear
and understandable fashion.
C o n t e n t s

Preface to the First Edition xi

Preface to the Second Edition xv

Preface to the Third Edition xvii

Chapter 1 Setting the Scene 1

The relational model is much misunderstood 1
Some remarks on terminology 3
Principles not products 4
A review of the original model 6
Model vs. implementation 14
Properties of relations 18
Base vs. derived relations 22
Relations vs. relvars 24
Values vs. variables 27
Concluding remarks 28
Exercises 29
Answers 31

Chapter 2 Types and Domains 41

Types and relations 41
Equality comparisons 43
Data value atomicity 48
What’s a type? 52
Scalar vs. nonscalar types 56
Scalar types in SQL 59
Type checking and coercion in SQL 61
Collations in SQL 63
Row and table types in SQL 65
Concluding remarks 67
Exercises 68
Answers 71
vi Contents
Chapter 3 Tuples and Relations, Rows and Tables 81

What’s a tuple? 81
Rows in SQL 86
What’s a relation? 88
Relations and their bodies 90
Relations are n-dimensional 92
Relational comparisons 92
TABLE_DUM and TABLE_DEE 94
Tables in SQL 95
Column naming in SQL 97
Concluding remarks 100
Exercises 100
Answers 102

Chapter 4 No Duplicates, No Nulls 111

What’s wrong with duplicates? 111
Duplicates: further issues 116
Avoiding duplicates in SQL 118
What’s wrong with nulls? 120
Avoiding nulls in SQL 124
A remark on outer join 126
Concluding remarks 127
Exercises 128
Answers 133

Chapter 5 Base Relvars, Base Tables 141

Updating is set level 142
Relational assignment 145
More on candidate keys 149
More on foreign keys 152
Relvars and predicates 156
Relations vs. types 158
Exercises 161
Answers 164
Contents vii
Chapter 6 SQL and Relational Algebra I: The Original Operators 173

Some preliminaries 173
More on closure 177
Restriction 180
Projection 181
Join 182
Union, intersection, and difference 187
Which operators are primitive? 191
Formulating expressions one step at a time 191
What do relational expressions mean? 193
Evaluating SQL table expressions 194
Expression transformation 196
The reliance on attribute names 199
Exercises 201
Answers 205

Chapter 7 SQL and Relational Algebra II: Additional Operators 217

Exclusive union 218
Semijoin and semidifference 219
Extend 220
Image relations 223
Divide 227
Aggregate operators 228
Image relations revisited 235
Summarization 237
Summarization revisited 243
Group, ungroup, and relation valued attributes 245
“What if” queries 252
A note on recursion 254
What about ORDER BY? 259
Exercises 260
Answers 264

Chapter 8 SQL and Constraints 281

Type constraints 282
Type constraints in SQL 286
Database constraints 287
Database constraints in SQL 293
viii Contents
Transactions 295
Why database constraint checking must be immediate 296
But doesn’t some checking have to be deferred? 299
Constraints and predicates 301
Miscellaneous issues 304
Exercises 306
Answers 310

Chapter 9 SQL and Views 323

Views are relvars 324
Views and predicates 328
Retrieval operations 329
Views and constraints 331
Update operations 336
What are views for? 349
Views and snapshots 350
Exercises 351
Answers 354

Chapter 10 SQL and Logic 363

Why do we need logic? 364
Simple and compound propositions 365
Simple and compound predicates 372
Quantification 374
Relational calculus 379
More on quantification 387
Some equivalences 394
Concluding remarks 398
Exercises 399
Answers 401