SQL and Relational Theory

SQL and Relational Theory

by C.J. Date
Released January 2009
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596523060

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Understanding SQL's underlying theory is the best way to guarantee that your SQL code is correct and your database schema is robust and maintainable. On the other hand, if you're not well versed in the theory, you can fall into several traps. In SQL and Relational Theory, author C.J. Date demonstrates how you can apply relational theory directly to your use of SQL. With numerous examples and clear explanations of the reasoning behind them, you'll learn how to deal with common SQL dilemmas, such as:

  • Should database access granted be through views instead of base tables?
  • Nulls in your database are causing you to get wrong answers. Why? What can you do about it?
  • Could you write an SQL query to find employees who have never been in the same department for more than six months at a time?
  • SQL supports "quantified comparisons," but they're better avoided. Why? How do you avoid them?
  • Constraints are crucially important, but most SQL products don't support them properly. What can you do to resolve this situation?

Database theory and practice have evolved since Edgar Codd originally defined the relational model back in 1969. Independent of any SQL products, SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of the material available anywhere. Anyone with a modest to advanced background in SQL will benefit from the many insights in this book.

Publisher resources

View/Submit Errata

Table of contents

  1. Dedication
  2. Preface
    1. Prerequisites
    2. Database in Depth
    3. Further Remarks on the Text
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Comments and Questions
    7. Safari® Books Online
    8. Acknowledgments
  3. 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
      1. Structural Features
      2. Integrity Features
      3. Manipulative Features
      4. The Running Example
    5. Model vs. Implementation
    6. Properties of Relations
    7. Base vs. Derived Relations
    8. Relations vs. Relvars
    9. Values vs. Variables
    10. Concluding Remarks
    11. Exercises
  4. 2. Types and Domains
    1. Types and Relations
    2. Equality Comparisons
    3. Data Value Atomicity
    4. What’s a Type?
    5. Scalar vs. Nonscalar Types
    6. Scalar Types in SQL
    7. Type Checking and Coercion in SQL
    8. Collations in SQL
    9. Row and Table Types in SQL
    10. Concluding Remarks
    11. Exercises
  5. 3. Tuples and Relations, Rows and Tables
    1. What’s a Tuple?
      1. Consequences of the Definitions
    2. Rows in SQL
    3. What’s a Relation?
      1. Consequences of the Definitions
    4. Relations and Their Bodies
    5. Relations Are n-Dimensional
    6. Relational Comparisons
    7. TABLE_DUM and TABLE_DEE
    8. Tables in SQL
    9. Column Naming in SQL
    10. Concluding Remarks
    11. Exercises
  6. 4. No Duplicates, No Nulls
    1. What’s Wrong with Duplicates?
    2. Duplicates: Further Issues
    3. Avoiding Duplicates in SQL
    4. What’s Wrong with Nulls?
    5. Avoiding Nulls in SQL
    6. A Remark on Outer Join
    7. Concluding Remarks
    8. Exercises
  7. 5. Base Relvars, Base Tables
    1. Data Definitions
    2. Updating Is Set Level
      1. Constraint Checking
      2. Triggered Actions
      3. A Final Remark
    3. Relational Assignment
      1. Table Assignment in SQL
      2. The Assignment Principle
    4. More on Candidate Keys
    5. More on Foreign Keys
      1. Referential Actions
    6. Relvars and Predicates
    7. Relations vs. Types
    8. Exercises
  8. 6. SQL and Relational Algebra I: The Original Operators
    1. Some Preliminaries
    2. More on Closure
    3. Restriction
    4. Projection
    5. Join
      1. Explicit JOINs in SQL
    6. Union, Intersection, and Difference
      1. Union
      2. Intersection
      3. Difference
    7. Which Operators Are Primitive?
    8. Formulating Expressions a Step at a Time
    9. What Do Relational Expressions Mean?
    10. Evaluating SQL Table Expressions
    11. Expression Transformation
    12. The Reliance on Attribute Names
    13. Exercises
  9. 7. SQL and Relational Algebra II: Additional Operators
    1. Semijoin and Semidifference
    2. Extend
    3. Image Relations
    4. Divide
    5. Aggregate Operators
    6. Image Relations bis
    7. Summarization
    8. Summarization bis
    9. Group and Ungroup
    10. “What If” Queries
    11. What About ORDER BY?
    12. Exercises
  10. 8. SQL and Constraints
    1. Type Constraints
      1. Selectors and THE_ Operators
      2. More on Type Constraints
    2. Type Constraints in SQL
    3. Database Constraints
    4. Database Constraints in SQL
    5. Transactions
    6. Why Database Constraint Checking Must Be Immediate
    7. But Doesn’t Some Checking Have to Be Deferred?
      1. Multiple Assignment
    8. Constraints and Predicates
    9. Miscellaneous Issues
    10. Exercises
  11. 9. SQL and Views
    1. Views Are Relvars
      1. The Principle of Interchangeability
      2. Relation Constants
    2. Views and Predicates
    3. Retrieval Operations
    4. Views and Constraints
    5. Update Operations
      1. The CHECK Option
      2. More on SQL
    6. What Are Views For?
      1. Logical Data Independence
    7. Views and Snapshots
    8. Exercises
  12. 10. SQL and Logic
    1. Simple and Compound Propositions
      1. Connectives
      2. A Remark on Commutativity
    2. Simple and Compound Predicates
    3. Quantification
      1. Free and Bound Variables
    4. Relational Calculus
      1. More on Range Variables
      2. More Sample Queries
      3. Sample Constraints
    5. More on Quantification
      1. We Don’t Need Both Quantifiers
      2. Empty Ranges
      3. Defining EXISTS and FORALL
      4. Other Kinds of Quantifiers
    6. Some Equivalences
      1. Relational Completeness
      2. The Importance of Consistency
    7. Concluding Remarks
    8. Exercises
  13. 11. Using Logic to Formulate SQL Expressions
    1. Some Transformation Laws
    2. Example 1: Logical Implication
    3. Example 2: Universal Quantification
    4. Example 3: Implication and Universal Quantification
    5. Example 4: Correlated Subqueries
    6. Example 5: Naming Subexpressions
    7. Example 6: More on Naming Subexpressions
    8. Example 7: Dealing with Ambiguity
    9. Example 8: Using COUNT
    10. Example 9: Join Queries
    11. Example 10: UNIQUE Quantification
    12. Example 11: ALL or ANY Comparisons
    13. Example 12: GROUP BY and HAVING
    14. Exercises
  14. 12. Miscellaneous SQL Topics
    1. SELECT *
    2. Explicit Tables
    3. Name Qualification
    4. Range Variables
    5. Subqueries
    6. “Possibly Nondeterministic” Expressions
    7. Empty Sets
    8. A BNF Grammar for SQL Table Expressions
    9. Exercises
  15. A. The Relational Model
    1. The Relational Model vs. Others
    2. The Relational Model Defined
      1. Scalar Types
      2. Relation Types
      3. Relation Variables
      4. Relational Assignment
      5. Relational Operators
    3. Objectives of the Relational Model
    4. Some Database Principles
      1. The Golden Rule
        1. The Golden Rule
    5. What Remains to Be Done?
      1. Implementation
      2. Foundations
      3. Higher Level Abstractions
      4. Higher Level Interfaces
      5. So What About SQL?
  16. B. Database Design Theory
    1. The Place of Design Theory
    2. Functional Dependencies and Boyce/Codd Normal Form
      1. Nonloss Decomposition
      2. But Isn’t It All Just Common Sense?
      3. 1NF, 2NF, 3NF
    3. Join Dependencies and Fifth Normal Form
      1. The Significance of 5NF
      2. More on 5NF
    4. Two Cheers for Normalization
    5. Orthogonality
    6. Some Remarks on Physical Design
    7. Concluding Remarks
    8. Exercises
  17. C. Answers to Exercises
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Chapter 10
    11. Chapter 11
    12. Chapter 12
    13. Appendix B
  18. D. Suggestions for Further Reading
  19. Index
  20. About the Author
  21. Colophon
  22. Copyright

Product information

  • Title: SQL and Relational Theory
  • Author(s): C.J. Date
  • Release date: January 2009
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596523060