SQL and Relational Theory, 3rd Edition
How to Write Accurate SQL Code
Publisher: O'Reilly Media
Release Date: November 2015
Pages: 582
Read on O'Reilly Online Learning with a 10day trial
Start your free trial now Buy on AmazonWhere’s the cart? Now you can get everything with O'Reilly Online Learning. To purchase books, visit Amazon or your favorite retailer. Questions? See our FAQ or contact customer service:
18008898969 / 7078277019
support@oreilly.com
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.
Table of Contents

Chapter 1 Setting 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

Concluding remarks

Exercises

Answers


Chapter 2 Types and Domains

Types and relations

Equality comparisons

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

Concluding remarks

Exercises

Answers


Chapter 3 Tuples and Relations, Rows and Tables

What’s a tuple?

Rows in SQL

What’s a relation?

Relations and their bodies

Relations are ndimensional

Relational comparisons

TABLE_DUM and TABLE_DEE

Tables in SQL

Column naming in SQL

Concluding remarks

Exercises

Answers


Chapter 4 No 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

Concluding remarks

Exercises

Answers


Chapter 5 Base Relvars, Base Tables

Updating is set level

Relational assignment

More on candidate keys

More on foreign keys

Relvars and predicates

Relations vs. types

Exercises

Answers


Chapter 6 SQL and Relational Algebra I: The Original Operators

Some preliminaries

More on closure

Restriction

Projection

Join

Union, intersection, and difference

Which operators are primitive?

Formulating expressions one step at a time

What do relational expressions mean?

Evaluating SQL table expressions

Expression transformation

The reliance on attribute names

Exercises

Answers


Chapter 7 SQL and Relational Algebra II: Additional Operators

Exclusive union

Semijoin and semidifference

Extend

Image relations

Divide

Aggregate operators

Image relations revisited

Summarization

Summarization revisited

Group, ungroup, and relation valued attributes

“What if” queries

A note on recursion

What about ORDER BY?

Exercises

Answers


Chapter 8 SQL and Constraints

Type constraints

Type constraints in SQL

Database constraints

Database constraints in SQL

Transactions

Why database constraint checking must be immediate

But doesn’t some checking have to be deferred?

Constraints and predicates

Miscellaneous issues

Exercises

Answers


Chapter 9 SQL and Views

Views are relvars

Views and predicates

Retrieval operations

Views and constraints

Update operations

What are views for?

Views and snapshots

Exercises

Answers


Chapter 10 SQL and Logic

Why do we need logic?

Simple and compound propositions

Simple and compound predicates

Quantification

Relational calculus

More on quantification

Some equivalences

Concluding remarks

Exercises

Answers


Chapter 11 Using 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

Exercises

Answers


Chapter 12 Miscellaneous SQL Topics

SELECT *

Explicit tables

Dot qualification

Range variables

Subqueries

“Possibly nondeterministic” expressions

Empty sets

A simplified BNF grammar

Exercises

Answers


Appendix The Relational Model

The relational model vs. others

The significance of theory

The relational model defined

Database variables

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

Vertical decomposition

Horizontal decomposition

What do the shaded entries mean?

Constraints

Queries

More on predicates

Exercises

Answers


Appendix A Tutorial D Grammar

Appendix Summary of Recommendations

Appendix NoSQL and Relational Theory

Functional segmentation

Sharding

Eventual consistency

The Fernandez interview


Appendix Suggestions for Further Reading