SQL and Relational Theory by Date, C.J. -- Read -- Imperial Library of Trantor

Index

SQL and Relational Theory

Preface to the First Edition

Prerequisites Database in Depth Further Remarks on the Text Using Code Examples Comments and Questions Safari® Books Online Acknowledgments

Preface to the Second Edition 1. Setting the Scene

THE RELATIONAL MODEL IS MUCH MISUNDERSTOOD SOME REMARKS ON TERMINOLOGY PRINCIPLES NOT PRODUCTS A REVIEW OF THE ORIGINAL MODEL

Structural Features Integrity Features Manipulative Features The Running Example

MODEL vs. IMPLEMENTATION PROPERTIES OF RELATIONS

All logical differences are big differences. Some Crucial Points

BASE vs. DERIVED RELATIONS RELATIONS vs. RELVARS VALUES vs. VARIABLES CONCLUDING REMARKS EXERCISES

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

3. Tuples and Relations, Rows and Tables

WHAT’S A TUPLE?

Consequences of the Definitions

ROWS IN SQL WHAT’S A RELATION?

Consequences of the Definitions

RELATIONS AND THEIR BODIES RELATIONS ARE n-DIMENSIONAL RELATIONAL COMPARISONS TABLE_DUM AND TABLE_DEE TABLES IN SQL COLUMN NAMING IN SQL CONCLUDING REMARKS EXERCISES

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

5. Base Relvars, Base Tables

UPDATING IS SET LEVEL

Triggered Actions Constraint Checking A Final Remark

RELATIONAL ASSIGNMENT

D_INSERT and I_DELETE Table Assignment in SQL The Assignment Principle

MORE ON CANDIDATE KEYS MORE ON FOREIGN KEYS

Referential Actions

RELVARS AND PREDICATES RELATIONS vs. TYPES EXERCISES

6. SQL and Relational Algebra I: The Original Operators

SOME PRELIMINARIES MORE ON CLOSURE RESTRICTION PROJECTION JOIN

Explicit JOINs in SQL

UNION, INTERSECTION, AND DIFFERENCE

Union Intersection 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

7. SQL and Relational Algebra II : Additional Operators

EXCLUSIVE UNION SEMIJOIN AND SEMIDIFFERENCE EXTEND IMAGE RELATIONS DIVIDE AGGREGATE OPERATORS

Empty Arguments

IMAGE RELATIONS bis SUMMARIZATION SUMMARIZATION bis GROUP, UNGROUP, AND RELATION VALUED ATTRIBUTES

RVAs Make Outer Join Unnecessary RVAs in Base Relvars RVAs Are Necessary for Relational Comparisons Aggregate Operators

“WHAT IF” QUERIES A NOTE ON RECURSION

Cycles

WHAT ABOUT ORDER BY? EXERCISES

8. SQL and Constraints

TYPE CONSTRAINTS

Selectors and THE_ Operators More on 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?

Multiple Assignment

CONSTRAINTS AND PREDICATES MISCELLANEOUS ISSUES EXERCISES

9. SQL and Views

VIEWS ARE RELVARS

The Principle of Interchangeability Relation Constants

VIEWS AND PREDICATES RETRIEVAL OPERATIONS VIEWS AND CONSTRAINTS UPDATE OPERATIONS

The CHECK Option More on SQL London vs. Non London Suppliers Revisited

WHAT ARE VIEWS FOR?

Logical Data Independence

VIEWS AND SNAPSHOTS EXERCISES

10. SQL and Logic

WHY DO WE NEED LOGIC? SIMPLE AND COMPOUND PROPOSITIONS

Connectives A Remark on Commutativity Another Example

SIMPLE AND COMPOUND PREDICATES

Rules of Inference

QUANTIFICATION

Free and Bound Variables

RELATIONAL CALCULUS

More on Range Variables More Sample Queries Sample Constraints