1

CS 430Database Theory

Winter 2005

Lecture 6: Relational Calculus

2

What is the Relational Calculus? Answer: A non-procedural way to specify

relational queries Relational algebra provides a procedural way to

specify queries Why both?

History SQL resembles Relational Calculus

Relational Calculus came first Way to compare the power of various query

languages

3

Tuple and Domain Relational Calculus Two forms of Relational Calculus:

Tuple: Predicates on tuples Domain: Predicates on attributes

Relational Calculus (without qualification) refers to the Tuple Relational Calculus

4

Tuple Relational Calculus

Example: { t.FNAME, t.LNAME |

EMPLOYEE(t) AND t.SALARY > 50000 } General:

{ t1.A1, … , tn.An | COND(t1, … , tn+k) }

ti are tuple variables

Ai are attributes COND is a condition or formula

5

Formulas

Atoms R(t) where R is a relation and t is a tuple variable

True if the tuple t is a tuple of the relation R x y, where x and y are either t.Ai, an attribute, or

c, a constant, and is a comparison operator Conditions (aka Formulas)

Atoms Boolean combinations (AND, OR, NOT) of

formulas Existential and Universal Quantifiers

6

Existential and Universal Quantifiers (t) (F)

“For all t, F is True” True if F is True for any possible tuple assigned to

a free occurrence of t in F (t) (F)

“There exists t such that F is True” True if F is True for some tuple assigned to a free

occurrence of t in F t is a tuple variable F is a formula

7

Safe Expressions

A safe expression is one guaranteed to yield a finite number of tuples

Non-safe expression: { t | NOT( EMPLOYEE(t)) }

Domain of an expression: All values that exist in any tuple in the relations referenced in the expression and all constants used in the expression

An expression is safe if all resulting values are from the domain of the expression Note that by definition this is a finite collection

Usual use: All tuples t are qualified by an atom of the form R(t)

8

Domain Relational Calculus

{ x1, … , xn | COND(x1, … , xn+k) } xi are value variables COND is a condition or formula

Same structure as formula for Tuple Relational Calculus, except: R(x1, … , xn), where R is a relation, is True if <x1, … ,xn> is a

tuple of R Example:

{ q, s | (r) (t) (u) (v) (w) (x) (y) (z) EMPLOYEE(qrstuvwxyz) AND x > 50000 }

Shorthand { qs | EMPLOYEE(qrstuvwxyz) AND x > 50000 }

9

Relationally Complete

A query language is “Relationally Complete” if it has the power of the Relational Calculus

The Basic Relational Algebra (one with the original operations) is equivalent in power to the Relational Calculus Any expression in the Basic Relational Algebra has a

corresponding expression in either Relational Calculus Any Safe expression in Relational Calculus has a

corresponding expression in the Relational Algebra Note that the Relational Calculus (as given here) doesn’t

include equivalents to EXTEND and AGGREGATION

10

Examples