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

Chapter 8

SQL and Constraints

A foolish consistency is the hobgoblin of little minds.

—Ralph Waldo Emerson: “Self Reliance” (1841)

I’ve touched on the topic of integrity constraints here and there in previous chapters, but it’s time to get more specific. Here’s a rough definition, repeated from Chapter 1: An integrity constraint (constraint for short) is basically just a boolean expression that must evaluate to TRUE. Constraints in general are so called because they constrain the values that can legally appear as values of some variable; but the ones we’re interested in here are the ones that apply to variables in the database (i.e., relvars) specifically.¹ Such constraints fall into two broad categories, type constraints and database constraints. In essence:

A type constraint defines the values that constitute a given type.
A database constraint further constrains the values that can appear in a given database (where by “further” I mean over and above the constraints already imposed by the pertinent type constraints).

As usual, in what follows I’ll discuss these ideas in both relational and SQL terms.

By the way, it’s worth noting that constraints in general can be regarded as a formal version of what some people call business rules. Now, this latter term doesn’t really have a precise definition (at least, not one that’s universally accepted); in general, however, a business rule is a declarative statement—emphasis on declarative—of some aspect of the enterprise the database ...