book

SQL and Relational Theory, 3rd Edition

by C.J. Date

November 2015

Intermediate to advanced

582 pages

16h 19m

English

O'Reilly Media, Inc.

Read now

Unlock full access

The relational model is much misunderstoodSome remarks on terminologyPrinciples not productsA review of the original modelModel vs. implementationProperties of relationsBase vs. derived relationsRelations vs. relvarsValues vs. variablesConcluding remarksExercisesAnswers

Types and relationsEquality comparisonsData value atomicityWhat’s a type?Scalar vs. nonscalar typesScalar types in SQLType checking and coercion in SQLCollations in SQLRow and table types in SQLConcluding remarksExercisesAnswers
What’s a tuple?Rows in SQLWhat’s a relation?Relations and their bodiesRelations are n-dimensionalRelational comparisonsTABLE_DUM and TABLE_DEETables in SQLColumn naming in SQLConcluding remarksExercisesAnswers
What’s wrong with duplicates?Duplicates: further issuesAvoiding duplicates in SQLWhat’s wrong with nulls?Avoiding nulls in SQLA remark on outer joinConcluding remarksExercisesAnswers
Updating is set levelRelational assignmentMore on candidate keysMore on foreign keysRelvars and predicatesRelations vs. typesExercisesAnswers
Some preliminariesMore on closureRestrictionProjectionJoinUnion, intersection, and differenceWhich operators are primitive?Formulating expressions one step at a timeWhat do relational expressions mean?Evaluating SQL table expressionsExpression transformationThe reliance on attribute namesExercisesAnswers
Exclusive unionSemijoin and semidifferenceExtendImage relationsDivideAggregate operatorsImage relations revisitedSummarizationSummarization revisitedGroup, ungroup, and relation valued attributes“What if” queriesA note on recursionWhat about ORDER BY?ExercisesAnswers
Type constraintsType constraints in SQLDatabase constraintsDatabase constraints in SQLTransactionsWhy database constraint checking must be immediateBut doesn’t some checking have to be deferred?Constraints and predicatesMiscellaneous issuesExercisesAnswers
Views are relvarsViews and predicatesRetrieval operationsViews and constraintsUpdate operationsWhat are views for?Views and snapshotsExercisesAnswers
Why do we need logic?Simple and compound propositionsSimple and compound predicatesQuantificationRelational calculusMore on quantificationSome equivalencesConcluding remarksExercisesAnswers
Some transformation lawsExample 1: Logical implicationExample 2: Universal quantificationExample 3: Implication and universal quantificationExample 4: Correlated subqueriesExample 5: Naming subexpressionsExample 6: More on naming subexpressionsExample 7: Dealing with ambiguityExample 8: Using COUNTExample 9: Another variationExample 10: UNIQUE quantificationExample 11: ALL or ANY comparisonsExample 12: GROUP BY and HAVINGExercisesAnswers
SELECT *Explicit tablesDot qualificationRange variablesSubqueries“Possibly nondeterministic” expressionsEmpty setsA simplified BNF grammarExercisesAnswers
The relational model vs. othersThe significance of theoryThe relational model definedDatabase variablesObjectives of the relational modelSome database principlesWhat remains to be done?
Vertical decompositionHorizontal decompositionWhat do the shaded entries mean?ConstraintsQueriesMore on predicatesExercisesAnswers
Functional segmentationShardingEventual consistencyThe Fernandez interview

Content preview from SQL and Relational Theory, 3rd Edition

Footnotes

Preface to the First Edition

¹ In response to reader requests, in the third edition I’ve moved the answers that are specific to a given chapter to the end of the chapter in question and deleted the old Appendix F.

Preface to the Second Edition

² It was published in 2012.

³ In this connection, I’d like to acknowledge the contribution of a reader of the first edition, Thomas Uhren, who found an embarrassingly large number of errors. I’ll try harder in future. I promise.

⁴ Note that the name Tutorial D is always set in boldface.

Chapter 1: Setting the Scene

¹ There’s at least one pundit who doesn’t. The following is a direct quote from a document purporting (like this book!) to offer advice to SQL users: “Don’t use joins ... Oracle and SQL Server have fundamentally different approaches to the concept ... You can end up with unexpected result sets ... You should understand the basic types of join clauses ... Equijoins are formed by retrieving all the data from two separate sources and combining it into one large table ... Inner joins are joined on the inner columns of two tables. Outer joins are joined on the outer columns of two tables. Left joins are joined on the left columns of two tables. Right joins are joined on the right columns of two tables.” Your comment here.

² The standard has been through several versions, or editions, over the years. The version current at the time of writing is SQL:2011 (a formal reference for which can be found in Appendix G); the previous ...