Relational Calculus

1
Relational Calculus

• Ameetinder Singh
• CS 157A

2
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

3
Query Examples

• To find the branch-name, loan-number, and amount
  for loans over 1000
• t t ? loan ? tamount gt 1000
• To find only the loan-number attribute, rather
  than all the attributes of the loan relation
• t ? s ? loan ( tloan-number
  sloan-number ? samount gt 1000)
• This query will retrieve those tuples in
  loan-number such that there is a tuple in loan
  with the amount attribute gt 1000

4
Examples (Contd)

• To find the names of all customers who borrowed a
  loan from the Downtown branch.
• t ?s ? borrower ( tcustomer-name
  scustomer-name ? ? u ? loan (uloan-number
  sloan-number ? ubranch-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

5
Examples (Contd)

• To find customers who have a loan, an account, or
  both at the bank
• t ?s ? borrower ( tcustomer-name
  scustomer-name) ? ? u ? depositor
  (tcustomer-name ucustomer-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.

6
Examples (Contd)

• 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 ( tcustomer-name
  scustomer-name) ? ? u ? depositor (
  tcustomer-name ucustomer-name )

7
Examples (Contd)

• 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

8
Examples (Contd)

• t ?r ? customer ( rcustomer-name
  tcustomer-name) ? (? u ? branch
  (ubranch-city Brooklyn ? ? s ? depositor
  (tcustomer-name scustomer-name ? ? w ?
  account (waccount-number saccount-number ?
  wbranch-name sbranch-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.

9
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.

10
Formal Definition (Contd)

• An atom can have one of the following forms
• s ? r, where s is a tuple variable in relation r
• sx ? uy, 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 ?.
• sx ? 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.

11
Formal Definition (Contd)

• 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

12
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

13
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

14
Formal Definition

• An expression in the domain relational calculus
  is in the form
• ltx1 , x2 ,. xngt P(x1 , x2 ,. xn)
• where x1 , x2 ,. xn represent domain
  variables. P represents formula composed of atoms.

15
Formal Definition (Contd)

• An atom in the domain relational calculus has
  one of the following forms
• ltx1 , x2 ,. xngt ? 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

16
Formal Definition (Contd)

• 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

17
Example queries

• To find the branch-name, loan-number, and amount
  for loans over 1000
• ltl,b,agt ltl,b,agt ? loan ? a gt 1000
• To find only the loan-number attribute, rather
  than all the attributes of the loan relation
• lt l gt ? b,a (ltl,b,agt ? loan ? a gt 1000)

18
Examples (Contd)

• To find the names of all customers who borrowed a
  loan from the Downtown branch and find the loan
  amount
• ltc,agt ? l (ltc,lgt ? borrower ? ? b (ltl,b,agt ?
  loan ? b Downtown))
• To find all customers who have an account at all
  branches located in Brooklyn
• ltcgt ? n (ltc,ngt ? customer) ? ?x,y,z(ltx,y,zgt ?
  branch ? y Brooklyn ?? a,b (lta,x,bgt ?
  account ? ltc,agt ? depositor)

19
Examples (Contd)

• 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

20
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
• ltl,b,agt ?ltl,b,agt ? loan )
• generates result with values that are not in the
  domain of the expression.

21
Safety of expressions (Contd)

• An expression ltx1 , x2 ,xngt 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