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 7

SQL and Relational Algebra II: Additional Operators

Algebra is the part of advanced mathematics that is not calculus.

—John Derbyshire: Unknown Quantity: A Real and Imaginary History of Algebra (2006)

As I’ve said several times already, a relational algebra operator is an operator that (a) takes one or more relations—or possibly no relations at all, in the case of the n-adic versions of operators such as join (see Chapter 6)—as input and (b) produces another relation as output. As I observed in Chapter 1, however, any number of operators can be defined that conform to this simple characterization. The previous chapter described the original operators (join, project, etc.); by contrast, the present chapter describes some of the many additional operators that have been defined since the relational model was first invented. It also considers how those operators might best be realized in SQL.

Note: By its nature, this chapter is necessarily something of a miscellany. Thus, you might just want to skim it lightly on a first pass, and come back to it later if you need to gain a deeper understanding of one or more of the topics discussed. Perhaps it would help to say up front that, from a practical point of view at least, the most important topics are probably these: