VIEWS: 30 PAGES: 21

• pg 1
```									Relational Calculus

Ameetinder Singh
CS 157A
Tuple Relational Calculus
 non-procedural query language as compared to
relational algebra that is procedural.

 only requires the user to specify what
information is required and not how that
information should be obtained.

 A query in tuple relational calculus expressed as
{t | P(t)}
 i.e. set of all tuples t such that P is true for t
Query Examples

 To find the branch-name, loan-number, and
amount for loans over \$1000:
{t | t  loan  t[amount] > 1000}
 To find only the loan-number attribute, rather
than all the attributes of the loan relation:
{t |  s  loan ( t[loan-number] = s[loan-
number]  s[amount] > 1000)}
This query will retrieve those tuples in loan-
number such that there is a tuple in loan with
the amount attribute > 1000
Examples (Cont’d)
 To find the names of all customers who borrowed a
loan from the Downtown branch.

{t | s  borrower ( t[customer-name] = s[customer-
name]   u  loan (u[loan-number] = s[loan-number] 
u[branch-name] = “Downtown”))}

requires two “there exists” clauses since it involves two
relations

retrieves the set of all (customer-name) tuples for
which the customer has a loan from the Downtown
branch
Examples (Cont’d)
 To find customers who have a loan, an account, or both at the
bank

{t | s  borrower ( t[customer-name] = s[customer-name])   u 
depositor (t[customer-name] = u[customer-name] )}

If a customer has both account and a loan from the bank, the
customer-name only appears once

Changing () to () in the previous query will generate results with
the set of customer-name tuples who have both an account and a
loan at the bank.
Examples (Cont’d)

 To find all the customers who have an account
at the bank but do not have a loan from the
bank, we use the not () symbol in the
previous expression

{t |  s  borrower ( t[customer-name] =
s[customer-name])   u  depositor (
t[customer-name] = u[customer-name] )}
Examples (Cont’d)

Some queries require the use of
implication (). PQ means, “if P is
true, then Q must be true.”

To find all customers who have an
account at all branches located in
Brooklyn, we write a query using “for all”
() construct as follows:
Examples (Cont’d)
{t | r  customer ( r[customer-name] = t[customer-
name])  ( u  branch (u[branch-city] = “Brooklyn”
  s  depositor (t[customer-name] = s[customer-
name]   w  account (w[account-number] =
s[account-number]  w[branch-name] = s[branch-
name]))))}

this is interpreted as “the set of all customers such that,
for all tuples u in the branch relation, if the value of u
on attribute branch-city is Brooklyn, then the customer
has an account at the branch whose name appears in
the branch-name attribute of u.”
Formal Definition

A tuple-relational-calculus expression is
in the form
{t | P(t)}
where P is a formula

The formula in tuple-relational-calculus is
built up out of atoms.
Formal Definition (Cont’d)
An atom can have one of the following forms:

• s  r, where s is a tuple variable in relation r

• s[x]  u[y], where s and u are tuple variables, x is an attribute on
which s is defined, y is an attribute on which u is defined, and  is
a comparison operator (,,,,,); it is required that x and y
have domains whose members can be compared by .

• s[x]  c, where s is a tuple variable, x is and attribute on which s is
defined,  is a comparison operator, and c is a constant in the
domain of attribute x.
Formal Definition (Cont’d)
Formulae can be built up from atoms by using
the following rules:

• an atom is a formula

• If P1 is a formula, then so are  P1 and (P1)

• If P1 and P2 are formulae, then so are P1 
P2, P1 P2, and P1P2
Safety of Expressions
A tuple-relational-calculus expression may generate an
infinite relation.

{t | ( t  loan)}

there are infinitely many tuples that are not in loan.

Domain of P is the set of all values referenced by P.
the expression {t | P(t)} is safe if all values in the result
are in the domain of P
Domain Relational
Calculus
Closely related to tuple-relational-
calculus

Uses domain variables that take on
values from an attributes domain, rather
than values for an entire tuple
Formal Definition

An expression in the domain relational calculus
is in the form

{<x1 , x2 ,……. xn> | P(x1 , x2 ,……. xn)}

where x1 , x2 ,……. xn represent domain
variables. P represents formula composed of
atoms.
Formal Definition (Cont’d)
An atom in the domain relational calculus has one of
the following forms:
• <x1 , x2 ,……. xn>  r, where r is a relation on n
attributes and x1 , x2 ,…xn are domain variables.
• x  y, where x and y are domain variables and  is a
comparison operator (,,,,,); it is required that x
and y have domains that can be compared by 
• x  c, where x is a domain variable,  is a comparison
operator, and c is a constant in the domain of attribute
for which x is a domain variable
Formal Definition (Cont’d)

Formulae can be built up from atoms by
using the following rules:
 an atom is a formula.
 If P1 is a formula, then so are  P1 and
(P1)
 If P1 and P2 are formulae, then so are
P1  P2, P1 P2, and P1P2
Same as tuple-relational-calculus
Example queries

 To find the branch-name, loan-number, and
amount for loans over \$1000:
{<l,b,a> | <l,b,a>  loan  a > 1000}

 To find only the loan-number attribute, rather
than all the attributes of the loan relation:
{< l > |  b,a (<l,b,a>  loan  a > 1000)}
Examples (Cont’d)
 To find the names of all customers who
borrowed a loan from the Downtown branch
and find the loan amount:
{<c,a> |  l (<c,l>  borrower   b (<l,b,a> 
loan  b = “Downtown”))}

 To find all customers who have an account at
all branches located in Brooklyn:
{<c> |  n (<c,n>  customer)  x,y,z(<x,y,z>
 branch  y = “Brooklyn”  a,b (<a,x,b> 
account  <c,a>  depositor)}
Examples (Cont’d)

interpreted the set of all (customer name)
tuples c such that, for all (branch-name,
branch-city, assets) tuples x, y, z, if the branch-
city is Brooklyn, then the following conditions
hold:
 There exists a tuple in the relation account with
account number a and branch name x
 There exists a tuple in the relation depositor
with customer c and account number a
Safety of expressions

Expressions may generate an infinite relation
To overcome this problem we define safety of
domain relational calculus expressions.
An expression such as
{<l,b,a> | <l,b,a>  loan )}
generates result with values that are not in the
domain of the expression.
Safety of expressions
(Cont’d)
An expression {<x1 , x2 ,…xn> | P(x1 , x2 ,…. xn)} is
safe if all of the following conditions hold:
 all values that appear in tuples of the expression are
values from domain of P.
 for every “there exists” sub formula of the form x (P1
(x)) , the subformula is true if and only if there is a
value x in the domain P1 such that P1 (x) is true.
 for every “for all” sub formula of the form x (P1 (x)) ,
the subformula is true if and only if P1 (x) is true for all
values x from domain of P1

```
To top