Tuple Relational Calculus [199]

{ $t(R) \vert \psi (t)$}

• `find all tuples t over the set of attributes R with property $\psi$ (t)'
• $\psi$ defined relative to a database schema S
• answer depends on a database state, d, of S

Examples:

• $\{i\vert even(i)\}$
• $\{i\vert int(i) \wedge i \bmod 2 =0\}$
• $\{i\vert int(i) \wedge \exists j(int(j) \wedge j*2=i\}$
• $\{i(INTEGER)\vert \exists j(INTEGER)(j*2=i)\}$
• $\{t(S)\vert s(t) \wedge t[S\char93 ] = \lq S1$'$\}$

Tuple Relational Calculus [200]

Find city for supplier S1:

• { $t(CITY) \vert \exists v(S)(s(v) \wedge v[S\char93 ] = \lq S1$' $\wedge v[CITY] = t[CITY])$}

Find S# and SNAME for suppliers in Paris

• { $t(S\char93 ,SNAME) \vert \exists v(S) (s(v) \wedge v[CITY] = \lq paris$' $\\ \wedge v[S\char93 ] = t[S\char93 ] \wedge v[SNAME] = t[SNAME])$}

Find PNAME for parts supplied by supplier S1

• { $t(PNAME) \vert \exists u(P) \exists v(SP)(p(u) \wedge sp(v)$ $\wedge u[P\char93 ] = v[P\char93 ] \\ \wedge v[S\char93 ] = \lq S1$' $\wedge t[PNAME] = u[PNAME])$}

Tuple Relational Calculus [201]

{ $t(R) \vert \psi (t)$} where $\psi$ is a formula:

• R is a set of attributes
• defined relative to a database schema S
• using a set of comparators $\theta(=,<,>,\neq)$
• r(x) means that x is in relation r
• x[A] $\theta$ y[A] where A is an attribute
• x[A] $\theta$ c where c is a constant and in the domain of A
• $\exists$ x(R)(formula)
• $\forall$ x(R)(formula)
• $\neg$ (formula)
• formula $\vee$ formula
• formula $\wedge$ formula
• (formula)

$\exists x(AB) (x[A] > 5 \wedge y[C] = 25 \wedge \forall x(BC)(x[B] \neq x[C]))$

Note that y is free and x is bound twice.

Tuple Calculus: Example [202]

Schema

Muppets(NAME,ANIMAL,COLOR)
Casting(NAME,SHOW,NETWORK)
Ratings(NETWORK,RANK)

Query

Get the names of all frog muppets.

Algebra

$\pi_{NAME}(\sigma_{ANIMAL=''frog''}(muppets))$

QBE

muppets	NAME	ANIMAL	COLOR
	P.	frog

SQL

SELECT name FROM muppets WHERE animal = `frog'

Tuple

{ $n(NAME) \vert \exists t(Muppets) (muppets(t) \wedge$
$t[NAME] = n[NAME] \wedge t[ANIMAL] = \lq frog$'$)$}

Tuple Calculus: Example [203]

Query

Get the shows casting green muppets.

Algebra

$\pi_{SHOW}(\sigma_{COLOR = ''green''}(muppets \Join casting))$

QBE

muppets	NAME	ANIMAL	COLOR
	_kermit		green

casting	NAME	SHOW	NETWORK
	_kermit	P.

SQL

SELECT show FROM muppets,casting
WHERE muppets.name = casting.name
and color = `green'

Tuple

{ $s(SHOW) \vert \exists q(Casting) \exists t(Muppets)$
$(casting(q) \wedge muppets(t) \wedge q[SHOW] = s[SHOW]$
$\wedge q[NAME] = t[NAME] \wedge t[COLOR] = \lq green$'$)$}

Tuple Calculus: Example [204]

Query

Get the rankings of all networks casting frogs.

Algebra

$\pi_{RANK}(\sigma_{ANIMAL = ''frog''}(muppets) \Join casting \Join ratings)$

QBE

muppets	NAME	ANIMAL	COLOR
	_kermit	frog

casting	NAME	SHOW	NETWORK
	_kermit		_ABC

ratings	NETWORK	RANK
	_ABC	P.

SQL

SELECT rank FROM muppets,casting,ratings
WHERE muppets.name = casting.name and
casting.network = ratings.network and muppets.animal = `frog'

Tuple

{ $r(RANK) \vert \exists q(casting) \exists t(muppets) \exists n(ratings)$
$(casting(q) \wedge muppets(t) \wedge ratings(n) \wedge$
$q[NAME] = t[NAME] \wedge t[ANIMAL] = \lq frog$'$\wedge$
$q[NETWORK] = n[NETWORK] \wedge r[RANK] = n[RANK])$}