SQL and relational theory : how to write accurate SQL code

446 Pages·2012·6.61 MB·English

by  Date

SQL and
Relational Theory

How to Write Accurate SQL Code
SECOND EDITION

C. J. Date
sql_final.pdf 1 12/8/11 2:33:04 PM
SQL and Relational Theory: How to Write Accurate SQL Code (2nd edition)

by C. J. Date

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

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

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 [email protected].

Printing History:
January 2009: First Edition.
December 2011: Second Edition.

Revision History:
2011-12-08 First release
See http://oreilly.com/catalog/errata.csp?isbn= 9781449316402 for release details.

Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks 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.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a
trademark claim, the designations have been printed in caps or initial caps.

While every precaution has been taken in the preparation of this book, the publisher and authors assume
no responsibility for errors or omissions, or for damages resulting from the use of the information contained
herein.

ISBN: 978-1-449-31640-2

[LSI]
sql_final.pdf 2 12/8/11 2:33:05 PM
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
sql_final.pdf 3 12/8/11 2:33:05 PM
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 850,000 copies at the time of writing and is used by several hundred colleges and
universities worldwide. He is also the author of many other books on database management, including most
recently:

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

 From Apress: Date on Database: Writings 2000–2006 (2006)

 From Trafford: Logic and Databases: The Roots of Relational Theory (2007)

 From Apress: The Relational Database Dictionary, Extended Edition (2008)

 From Trafford: Database Explorations: Essays on The Third Manifesto and Related Topics (coauthored with
Hugh Darwen, 2010)

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

Another book, Normal Forms and All That Jazz: A Database Professional’s Guide to Database Design Theory (a
companion to the present book), is also due for publication in the near future.
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.

sql_final.pdf 4 12/8/11 2:33:05 PM
C o n t e n t s

Preface to the First Edition xi

Preface to the Second Edition xvi

Chapter 1 Setting the Scene 1

The relational model is much misunderstood 1
Some remarks on terminology 2
Principles not products 4
A review of the original model 5
Model vs. implementation 12
Properties of relations 14
Base vs. derived relations 18
Relations vs. relvars 19
Values vs. variables 21
Concluding remarks 22
Exercises 23

Chapter 2 Types and Domains 25

Types and relations 25
Equality comparisons 26
Data value atomicity 31
What’s a type? 34
Scalar vs. nonscalar types 37
Scalar types in SQL 39
Type checking and coercion in SQL 40
Collations in SQL 42
Row and table types in SQL 43
Concluding remarks 45
Exercises 46

Chapter 3 Tuples and Relations, Rows and Tables 49

What’s a tuple? 49
Rows in SQL 53
What’s a relation? 55
Relations and their bodies 57
Relations are n-dimensional 58
Relational comparisons 58
TABLE_DUM and TABLE_DEE 59
Tables in SQL 60
sql_final.pdf 5 12/8/11 2:33:05 PM
vi Contents

Column naming in SQL 62
Concluding remarks 64
Exercises 64

Chapter 4 No Duplicates, No Nulls 67

What’s wrong with duplicates? 67
Duplicates: further issues 72
Avoiding duplicates in SQL 72
What’s wrong with nulls? 74
Avoiding nulls in SQL 77
A remark on outer join 79
Concluding remarks 80
Exercises 80

Chapter 5 Base Relvars, Base Tables 85

Updating is set level 86
Relational assignment 88
More on candidate keys 92
More on foreign keys 94
Relvars and predicates 97
Relations vs. types 99
Exercises 101

Chapter 6 SQL and Relational Algebra I: The Original Operators 105

Some preliminaries 105
More on closure 108
Restriction 110
Projection 111
Join 112
Union, intersection, and difference 116
Which operators are primitive? 119
Formulating expressions one step at a time 119
What do relational expressions mean? 121
Evaluating SQL table expressions 122
Expression transformation 123
The reliance on attribute names 125
Exercises 127

Chapter 7 SQL and Relational Algebra II: Additional Operators 131

Exclusive union 131
Semijoin and semidifference 132
Extend 133
Image relations 135
Divide 138
sql_final.pdf 6 12/8/11 2:33:05 PM
Contents vii

Aggregate operators 139
Image relations bis 144
Summarization 146
Summarization bis 150
Group, ungroup, and relation valued attributes 152
“What if” queries 157
A note on recursion 159
What about ORDER BY? 163
Exercises 164

Chapter 8 SQL and Constraints 169

Type constraints 169
Type constraints in SQL 173
Database constraints 174
Database constraints in SQL 178
Transactions 180
Why database constraint checking must be immediate 180
But doesn’t some checking have to be deferred? 182
Constraints and predicates 185
Miscellaneous issues 186
Exercises 188

Chapter 9 SQL and Views 193

Views are relvars 194
Views and predicates 197
Retrieval operations 198
Views and constraints 199
Update operations 203
What are views for? 211
Views and snapshots 212
Exercises 213

Chapter 10 SQL and Logic 215

Why do we need logic? 216
Simple and compound propositions 217
Simple and compound predicates 222
Quantification 223
Relational calculus 227
More on quantification 234
Some equivalences 241
Concluding remarks 244
Exercises 244

sql_final.pdf 7 12/8/11 2:33:05 PM
viii Contents

Chapter 11 Using Logic to Formulate SQL Expressions 247

Some transformation laws 247
Example 1: Logical implication 250
Example 2: Universal quantification 251
Example 3: Implication and universal quantification 252
Example 4: Correlated subqueries 254
Example 5: Naming subexpressions 255
Example 6: More on naming subexpressions 258
Example 7: Dealing with ambiguity 259
Example 8: Using COUNT 261
Example 9: Join queries 262
Example 10: UNIQUE quantification 263
Example 11: ALL or ANY comparisons 265
Example 12: GROUP BY and HAVING 269
Exercises 270

Chapter 12 Miscellaneous SQL Topics 273

SELECT * 273
Explicit tables 274
Name qualification 274
Range variables 275
Subqueries 277
“Possibly nondeterministic” expressions 280
Empty sets 281
A simplified BNF grammar 281
Exercises 285

Appendix A The Relational Model 287

The relational model vs. others 288
The significance of theory 291
The relational model defined 293
Database variables 298
Objectives of the relational model 299
Some database principles 300
What remains to be done? 301

Appendix B SQL Departures from the Relational Model 305

Appendix C A Relational Approach to Missing Information 307

Vertical decomposition 308
Horizontal decomposition 309
What do the shaded entries mean? 311
Constraints 313
sql_final.pdf 8 12/8/11 2:33:05 PM