SQL and Relational Theory: How to Write Accurate SQL Code

428 Pages·2009·1.92 MB·English

by  C. J. Date

Download at WoweBook.Com
SQL and Relational Theory
How to Write Accurate SQL Code
Download at WoweBook.Com
Other resources from O’Reilly
Related titles The Art of SQL Essential SQLAlchemy
Database In Depth SQL in a Nutshell
oreilly.com oreilly.com is more than a complete catalog of O’Reilly
books. You’ll also find links to news, events, articles,
weblogs, sample chapters, and code examples.
oreillynet.comistheessentialportalfordevelopersinterested
in open and emerging technologies, including new plat-
forms, programming languages, and operating systems.
Conferences O’Reilly brings diverse innovators together to nurture the
ideas that spark revolutionary industries. We specialize in
documenting the latest tools and systems, translating the
innovator’s knowledge into useful skills for those in the
trenches. Visit conferences.oreilly.com for our upcoming
events.
Safari Bookshelf (safari.oreilly.com) is the premier online
reference library for programmers and IT professionals.
Conduct searches across more than 1,000 books. Sub-
scribers can zero in on answers to time-critical questions
inamatterofseconds.ReadthebooksonyourBookshelf
from cover to cover or simply flip to the page you need.
Try it today for free.
Download at WoweBook.Com
SQL and Relational Theory
How to Write Accurate SQL Code
C. J. Date
Beijing • Cambridge • Farnham • Köln • Sebastopol • Taipei • Tokyo
Download at WoweBook.Com
SQL and Relational Theory: How to Write Accurate SQL Code
by C. J. Date
Copyright © 2009 Chris 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 (safari.oreilly.com). For more information, contact our
corporate/institutional sales department: [phone] or [email protected].
Editor: Andy Oram Cover Designer: Mark Paglietti
Production Editor: Adam Witwer Interior Designer: Marcia Friedman
Indexer: C.J. Date Illustrators: Jessamyn Read
Printing History:
January 2009: First Edition.
The O’Reilly logo is a registered trademark of O’ReillyMedia, Inc.SQL and Relational Theory 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-0-596-52306-0
[V]
Download at WoweBook.Com
Toallthosewhothinkanexerciselikethis
one is worthwhile, and in particular
to the memory of Lex de Haan,
who is very much missed.
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
Unfortunately, the gap between theory and
practice is not as wide in theory
as it is in practice.
Anonymous
Download at WoweBook.Com
Download at WoweBook.Com
CO NTENTS
PREFACE xiii
1 SETTING THE SCENE 1
The Relational Model Is Much Misunderstood 2
Some Remarks on Terminology 3
Principles, Not Products 4
A Review of the Original Model 5
Model vs. Implementation 13
Properties of Relations 17
Base vs. Derived Relations 19
Relations vs. Relvars 21
Values vs. Variables 23
Concluding Remarks 24
Exercises 25
2 TYPES AND DOMAINS 27
Types and Relations 27
Equality Comparisons 28
Data Value Atomicity 34
What’s a Type? 37
Scalar vs. Nonscalar Types 40
Scalar Types in SQL 42
Type Checking and Coercion in SQL 43
Collations in SQL 45
Row and Table Types in SQL 47
Concluding Remarks 49
Exercises 50
3 TUPLES AND RELATIONS, ROWS AND TABLES 53
What’s a Tuple? 53
Rows in SQL 57
What’s a Relation? 60
Relations and Their Bodies 61
Relations Are n-Dimensional 62
Relational Comparisons 63
TABLE_DUM and TABLE_DEE 64
Tables in SQL 65
vii
Download at WoweBook.Com
Column Naming in SQL 66
Concluding Remarks 68
Exercises 69
4 NO DUPLICATES, NO NULLS 71
What’s Wrong with Duplicates? 71
Duplicates: Further Issues 76
Avoiding Duplicates in SQL 77
What’s Wrong with Nulls? 79
Avoiding Nulls in SQL 82
A Remark on Outer Join 84
Concluding Remarks 85
Exercises 86
5 BASE RELVARS, BASE TABLES 89
Data Definitions 90
Updating Is Set Level 90
Relational Assignment 92
More on Candidate Keys 94
More on Foreign Keys 97
Relvars and Predicates 100
Relations vs. Types 102
Exercises 105
6 SQL AND RELATIONAL ALGEBRA I:
THE ORIGINAL OPERATORS 107
Some Preliminaries 107
More on Closure 110
Restriction 113
Projection 114
Join 115
Union, Intersection, and Difference 118
Which Operators Are Primitive? 120
Formulating Expressions a Step at a Time 121
What Do Relational Expressions Mean? 122
Evaluating SQL Table Expressions 123
Expression Transformation 124
The Reliance on Attribute Names 127
Exercises 129
viii CONTENTS
Download at WoweBook.Com