CS 430 Database Theory

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