Domain Relational Calculus [205]

tuple variable $\rightarrow$ domain variables

• r(a1,a2,...an) where a's are variables or constants
• x $\theta$ y where x,y are variables or constants
• (formula)
• $\neg$ formula
• $formula \wedge formula$
• $formula \vee formula$
• $\exists x(A) formula$ where A is single attribute
• $\forall x(A) formula$

Domain Relational Calculus: Example [206]

Query

Get the names of all all frog muppets.

QBE

muppets	NAME	ANIMAL	COLOR
	P.	frog

Domain

{ $n(NAME) \vert \exists c(COLOR) (muppets(n,\lq frog$'$,c))$}

Query

Get shows casting green muppets.

QBE

muppets	NAME	ANIMAL	COLOR
	_kermit		green

casting	NAME	SHOW	NETWORK
	_kermit	P.

Domain

{ $s(SHOW) \vert \exists n(NAME) \exists a(ANIMAL) \exists z(NETWORK)$
$muppets(n,a,\lq green$' $) \wedge casting(n,s,z)$}

Domain Relational Calculus: Divide [207]

certified
PILOT	EQUIPMENT
joe	707
joe	727
joe	747
moe	707
doe	707
doe	727
doe	747
doe	1011

$\div$

equip

EQUIPMENT

707

727

747

=

PILOT

joe

doe

certified $\div$ equip says the pilot is certified on ALL equipment

Tuple Relational Calculus: Divide [208]

Query

Get muppets acting in ALL shows on ABC.

Algebra

$\pi_{NAME,SHOW}(casting) \div \pi_{SHOW}(\sigma_{NET = ABC}(casting))$

Tuple

{$m(NAME) \vert$
$\exists q(casting) (casting(q) \wedge q[NET] = \lq ABC$'$) \wedge$
$\forall t(casting)((casting(t) \wedge t[NET] = \lq ABC$'$) \Rightarrow$
$\exists p(casting)(casting(p) \wedge p[NAME] = m[NAME] \wedge$
$p[SHOW] = t[SHOW]))$}

Tuple Relational Calculus: Minus [209]

Query

Get muppets acting ONLY on shows on ABC.

Algebra

$\pi_{NAME}(casting) - \pi_{NAME}(\sigma_{NETWORK \neq ABC}(casting))$

Tuple

{ $n(NAME) \vert \exists c(casting) (casting(c) \wedge c[NAME] = n[NAME]) \wedge$
$\forall t(casting)(casting(t) \wedge t[NAME] = n[NAME] \Rightarrow$
$t[NET] = \lq ABC$')}

Domain

{ $n(NAME) \vert \exists s(SHOW) \exists z(NET)(casting(n,s,z) \wedge$
$\forall p(SHOW) \forall q(NET) (casting(n,p,q) \Rightarrow q = \lq ABC$'))}

Tuple Relational Calculus: Minus [210]

Query

Get the highest rank (join with different names).

Algebra

$\pi_{RANK}(ratings) -$
$\pi_{RANK}(\sigma_{RANK < RANK1}$
$(\pi_{RANK}(ratings) \Join \delta_{RANK \rightarrow RANK1}(\pi_{RANK}(ratings))))$

QBE

RATINGS	NETWORK	RANK
		P. _x
$\neg$		$>$ _x

SQL

SELECT r.rank FROM ratings r WHERE r.rank NOT IN
SELECT r1.rank FROM ratings r1,ratings r2
WHERE r1.rank $<$ r2.rank

Tuple

{ $r(Ratings) \vert ratings(r) \wedge \neg \exists r1(Ratings)$
$(ratings(r1) \wedge r1[RANK] > r[RANK])$}

Tuple

{ $r(Ratings) \vert ratings(r) \wedge \forall r1(Ratings)$
$(ratings(r1) \Rightarrow r1[RANK] \leq r[RANK])$}

Domain Relational Calculus: Negate [211]

Query

Get muppets acting ONLY on networks not casting Kermit.

Domain

{ $m(NAME) \vert \exists k(NET) (casting(m,k)) \wedge$
$\forall n(NET)(casting(m,n) \Rightarrow \neg casting(\lq Kermit$'$,n))$}

QBE

CASTING	NAME	NET
	_m	_n
	`Kermit'	_n

TEMP	NAME
	I. _m

TEMP	NAME
$\neg$	_m

CASTING	NAME	NET
	_m

RESULT	NAME
I.	_m

SQL

SELECT c.name FROM casting c WHERE c.name NOT IN
SELECT d.name FROM casting d, casting e
WHERE d.net = e.net and e.name = `Kermit'

Relational Calculus: Examples [212]

Schema

frequents(DRINKER,BAR) serves(BAR,BEER)
likes(DRINKER,BEER)

Query

Get all drinkers who frequent at least one bar that serves a beer they like.

Algebra

$\pi_{DR}(frequents \Join serves \Join likes)$

Tuple

{ $d(DR) \vert \exists f(frequents) \exists s(serves) \exists l(likes)$
$(frequents(f) \wedge serves(s) \wedge likes(l) \wedge$
$l[DR] = f[DR] \wedge f[BAR] = s[BAR] \wedge$
$s[BEER] = l[BEER] \wedge l[DR] = d[DR])$}

Domain

{ $d(DR) \vert \exists b(BAR) \exists x(BEER)$
$(likes(dx) \wedge frequents(db) \wedge serves(bx))$}

Relational Calculus: Examples [213]

Query

Get the drinkers that frequent no bar that serves a beer they like.

Algebra

$\pi_{DR}(frequents) - \pi_{DR}(frequents \Join serves \Join likes)$

Domain

{ $d(DR) \vert \exists b(BAR) (frequents(db)) \wedge$
$\forall b(BAR)(frequents(db) \Rightarrow$
$\neg \exists x(BEER) (serves(bx) \wedge likes(dx)))$}

SQL

SELECT f.drinker FROM frequents f
WHERE f.drinker NOT IN
SELECT g.drinker FROM frequents g, serves s, likes l
WHERE g.drinker = l.drinker and s.beer = l.beer and
s.bar = g.bar

Relational Calculus: Examples [214]

Query

Get the drinkers that frequent only bars that serve some beer they like.

Domain

{ $d(DR) \vert \exists b(BAR) (frequents(db)) \wedge$
$\forall b(BAR)(frequents(db) \Rightarrow \exists x(BEER)(serves(bx) \wedge likes(dx)))$}

Algebra

$([DR,BAR] - frequents \cup \pi_{DR,BAR}(serves \Join likes)) \div [BAR]$

Algebra

$\pi_{DR}(freq) - \pi_{DR}(freq- \pi_{DR,BAR}(freq \Join serves \Join likes))$