Relational Calculus Relational Model Topic 2 Introduction to

Relational Calculus Relational Model : Topic 2 Introduction to Database Systems 1

Relational Calculus Comes in two flavors: Tuple relational calculus (TRC) and Domain relational calculus (DRC). v Calculus has variables, constants, comparison ops, logical connectives and quantifiers. v – TRC: Variables range over (i. e. , get bound to) tuples. – DRC: Variables range over domain elements (= field values). – Both TRC and DRC are simple subsets of first-order logic. Calculus expressions called formulas. v Answer tuple: assignment of constants to variables that makes formula evaluate to true. v Introduction to Database Systems 2

Domain Relational Calculus v Query has the form: v Answer includes all tuples make the formula v Formula is recursively defined, starting with simple atomic formulas and building bigger and better formulas using logical connectives. Introduction to Database Systems that be true. 3

DRC Formulas v Atomic formula: – – op is one of v , or X op Y, or X op constant Formula: – an atomic formula, or – , where p and q are formulas, or – , where variable X is free in p(X) Introduction to Database Systems 4

Example Instances v v “Sailors”, “Reserves”, and “Boats” relations Sailors(Id, Name, Tech. Rating, Age) Boats(Id, Name, Color) Reserves(Sailor. Id, Boat. Id, Date) Introduction to Database Systems 5

Find all sailors with a rating above 7 The condition ensures that the domain variables I, N, T and A get bound to fields of the same Sailors tuple. v The term to the left of `|’ says that every tuple that satisfies T>7 is in the answer. v Modify this: v – find all sailors who are older than 18 or have a rating under 9, and are called ‘Aaron’. Introduction to Database Systems 6

Find sailors rated > 7 who’ve reserved boat #103 v We have used Introduction to Database Systems as a shorthand for 7

Find sailors rated > 7 who’ve reserved a red boat Introduction to Database Systems 8

Find sailors who’ve reserved all boats v Find all sailors I such that for each 3 -tuple either it is not a tuple in Boats or there is a tuple in Reserves showing that sailor I has reserved it. Introduction to Database Systems 9

Find sailors who’ve reserved all boats (again!) Simpler notation, same query. (Much clearer!) v To find sailors who’ve reserved all red boats: v . . . Introduction to Database Systems 10

Unsafe Queries v Unsafe query: Legal calculus, but an infinite number of answers! – e. g. , Introduction to Database Systems 11