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.
Chapter 1Setting the Scene
The relational model is much misunderstood
Some remarks on terminology
Principles not products
A review of the original model
Model vs. implementation
Properties of relations
Base vs. derived relations
Relations vs. relvars
Values vs. variables
Chapter 2Types and Domains
Types and relations
Data value atomicity
What’s a type?
Scalar vs. nonscalar types
Scalar types in SQL
Type checking and coercion in SQL
Collations in SQL
Row and table types in SQL
Chapter 3Tuples and Relations, Rows and Tables
What’s a tuple?
Rows in SQL
What’s a relation?
Relations and their bodies
Relations are n-dimensional
TABLE_DUM and TABLE_DEE
Tables in SQL
Column naming in SQL
Chapter 4No Duplicates, No Nulls
What’s wrong with duplicates?
Duplicates: further issues
Avoiding duplicates in SQL
What’s wrong with nulls?
Avoiding nulls in SQL
A remark on outer join
Chapter 5Base Relvars, Base Tables
Updating is set level
More on candidate keys
More on foreign keys
Relvars and predicates
Relations vs. types
Chapter 6SQL and Relational Algebra I: The Original Operators
More on closure
Union, intersection, and difference
Which operators are primitive?
Formulating expressions one step at a time
What do relational expressions mean?
Evaluating SQL table expressions
The reliance on attribute names
Chapter 7SQL and Relational Algebra II: Additional Operators
Semijoin and semidifference
Image relations revisited
Group, ungroup, and relation valued attributes
“What if” queries
A note on recursion
What about ORDER BY?
Chapter 8SQL and Constraints
Type constraints in SQL
Database constraints in SQL
Why database constraint checking must be immediate
But doesn’t some checking have to be deferred?
Constraints and predicates
Chapter 9SQL and Views
Views are relvars
Views and predicates
Views and constraints
What are views for?
Views and snapshots
Chapter 10SQL and Logic
Why do we need logic?
Simple and compound propositions
Simple and compound predicates
More on quantification
Chapter 11Using Logic to Formulate SQL Expressions
Some transformation laws
Example 1: Logical implication
Example 2: Universal quantification
Example 3: Implication and universal quantification
Example 4: Correlated subqueries
Example 5: Naming subexpressions
Example 6: More on naming subexpressions
Example 7: Dealing with ambiguity
Example 8: Using COUNT
Example 9: Another variation
Example 10: UNIQUE quantification
Example 11: ALL or ANY comparisons
Example 12: GROUP BY and HAVING
Chapter 12Miscellaneous SQL Topics
“Possibly nondeterministic” expressions
A simplified BNF grammar
Appendix The Relational Model
The relational model vs. others
The significance of theory
The relational model defined
Objectives of the relational model
Some database principles
What remains to be done?
Appendix SQL Departures from the Relational Model
Appendix A Relational Approach to Missing Information
C.J. Date has a stature that is unique within the database industry. C.J. is a prolific writer, and is well-known for his best-selling textbook: An Introduction to Database Systems (Addison Wesley). C.J. is an exceptionally clear-thinking writer who can lay out principles and theory in a way easily understood by his audience.