Relational Calculus - Download Now PowerPoint - PowerPoint

Document Sample
Relational Calculus - Download Now PowerPoint - PowerPoint Powered By Docstoc
					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