SQL and Relational Theory, 2nd Edition

SQL and Relational Theory, 2nd Edition

by C.J. Date
Released December 2011
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449316402

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

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 the theory into practice. In this insightful book, author C.J. Date explains relational theory in depth, and demonstrates through numerous examples and exercises how you can apply it directly to your use of SQL.

This second edition includes new material on recursive queries, “missing information” without nulls, new update operators, and topics such as aggregate operators, grouping and ungrouping, and view updating. If you have a modest-to-advanced background in SQL, you’ll learn how to deal with a host of common SQL dilemmas.

  • Why is proper column naming so important?
  • Nulls in your database are causing you to get wrong answers. Why? What can you do about it?
  • Is it possible to 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 the relational model was developed more than 40 years ago. SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of SQL available.

C.J. Date has a stature that is unique within the database industry. A prolific writer well known for the bestselling textbook An Introduction to Database Systems (Addison-Wesley), he has an exceptionally clear style when writing about complex principles and theory.

Publisher resources

View/Submit Errata

Table of contents

  1. SQL and Relational Theory
  2. Dedication
  3. Preface to the First Edition
      1. Prerequisites
      2. Database in Depth
      3. Further Remarks on the Text
      4. Using Code Examples
      5. Comments and Questions
      6. Safari® Books Online
      7. Acknowledgments
  4. Preface to the Second Edition
  5. 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
      1. All logical differences are big differences.
      2. Some Crucial Points
    7. BASE vs. DERIVED RELATIONS
    8. RELATIONS vs. RELVARS
    9. VALUES vs. VARIABLES
    10. CONCLUDING REMARKS
    11. EXERCISES
  6. 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
  7. 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
  8. 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
  9. 5. Base Relvars, Base Tables
    1. UPDATING IS SET LEVEL
      1. Triggered Actions
      2. Constraint Checking
      3. A Final Remark
    2. RELATIONAL ASSIGNMENT
      1. D_INSERT and I_DELETE
      2. Table Assignment in SQL
      3. The Assignment Principle
    3. MORE ON CANDIDATE KEYS
    4. MORE ON FOREIGN KEYS
      1. Referential Actions
    5. RELVARS AND PREDICATES
    6. RELATIONS vs. TYPES
    7. EXERCISES
  10. 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 ONE 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
  11. 7. SQL and Relational Algebra II : Additional Operators
    1. EXCLUSIVE UNION
    2. SEMIJOIN AND SEMIDIFFERENCE
    3. EXTEND
    4. IMAGE RELATIONS
    5. DIVIDE
    6. AGGREGATE OPERATORS
      1. Empty Arguments
    7. IMAGE RELATIONS bis
    8. SUMMARIZATION
    9. SUMMARIZATION bis
    10. GROUP, UNGROUP, AND RELATION VALUED ATTRIBUTES
      1. RVAs Make Outer Join Unnecessary
      2. RVAs in Base Relvars
      3. RVAs Are Necessary for Relational Comparisons
      4. Aggregate Operators
    11. “WHAT IF” QUERIES
    12. A NOTE ON RECURSION
      1. Cycles
    13. WHAT ABOUT ORDER BY?
    14. EXERCISES
  12. 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
  13. 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
      3. London vs. Non London Suppliers Revisited
    6. WHAT ARE VIEWS FOR?
      1. Logical Data Independence
    7. VIEWS AND SNAPSHOTS
    8. EXERCISES
  14. 10. SQL and Logic
    1. WHY DO WE NEED LOGIC?
    2. SIMPLE AND COMPOUND PROPOSITIONS
      1. Connectives
      2. A Remark on Commutativity
      3. Another Example
    3. SIMPLE AND COMPOUND PREDICATES
      1. Rules of Inference
    4. QUANTIFICATION
      1. Free and Bound Variables
    5. RELATIONAL CALCULUS
      1. More on Range Variables
      2. More Sample Queries
      3. Sample Constraints
    6. MORE ON QUANTIFICATION
      1. We Don’t Need Both Quantifiers
      2. Empty Ranges
      3. Defining EXISTS and FORALL
      4. Other Kinds of Quantifiers
    7. SOME EQUIVALENCES
      1. Relational Completeness
      2. The Importance of Consistency
    8. CONCLUDING REMARKS
    9. EXERCISES
  15. 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
  16. 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 SIMPLIFIED BNF GRAMMAR
      1. Table Expressions
      2. Boolean Expressions
    9. EXERCISES
  17. A. The Relational Model
    1. THE RELATIONAL MODEL vs. OTHERS
    2. THE SIGNIFICANCE OF THEORY
    3. THE RELATIONAL MODEL DEFINED
      1. Scalar Types
      2. Relation Types
      3. Relation Variables
      4. Relational Assignment
      5. Relational Operators
    4. DATABASE VARIABLES
    5. OBJECTIVES OF THE RELATIONAL MODEL
    6. SOME DATABASE PRINCIPLES
    7. WHAT REMAINS TO BE DONE?
      1. Implementation
      2. Foundations
      3. Higher Level Abstractions
      4. Higher Level Interfaces
      5. So What about SQL?
  18. B. SQL Departures from the Relational Model
  19. C. A Relational Approach to Missing Information
    1. VERTICAL DECOMPOSITION
    2. HORIZONTAL DECOMPOSITION
    3. WHAT DO THE SHADED ENTRIES MEAN?
    4. CONSTRAINTS
    5. QUERIES
    6. MORE ON PREDICATES
    7. EXERCISES
  20. D. A Tutorial D Grammar
      1. Relational Expressions
      2. Assignments
  21. E. Summary of Recommendations
  22. F. 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 C
  23. G. Suggestions for Further Reading
  24. Index
  25. About the Author
  26. Copyright

Product information

  • Title: SQL and Relational Theory, 2nd Edition
  • Author(s): C.J. Date
  • Release date: December 2011
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449316402