Relational Calculus

15.1 The Roots of SQL

• Relational algebra provides the required base for computing queries of SQL
• Tuple relational calculus (TRC), to a large degree, underlines the appearance of SQL
• Relational algebra is a procedural way for stating queries—concerned with ‘how’
• Relational calculus employs declarative expressions—concerned with ‘what’
• A relational query language is relationally complete if it can express the queries of the relational algebra
• TRC is equivalent to relational algebra in its expressive power
• TRC can be viewed as a formalization of the set notation
  template | condition

  	{i|INTEGER(i) ∧ i > 5}
  =	{6,7,8,…}

• Relational calculus languages are based on first order predicate calculus

15.2 Propositional Logic

• A proposition is a statement which might be true or false
  “3 divides 6”, “5 doesn’t divide 7”

• Propositional logic is concerned with operators which create new propositions from given ones.
  “3 divides 6” and “5 doesn’t divide 7”

• Expressions or sentences of propositional logic rely on:

  Atoms

  q, p

  Logical connectives: ∨, ∧, ¬

  p ∧ q, ¬(¬p ∨¬p)

• Propositions can be true or false, dependent on the interpretation given to their atoms
  The proposition p ∧ q is true when p=“3 divides 6” and q=“5 doesn’t divide 7”

15.3 Predicate Logic

• Predicates are parameterized propositions, allowing references to classes of objects

  propositions	q	3 divides 6
  	p	5 divides 7
  	r	8 divides 2
  predicates	divides(x,y)	q = divides(3,6) p = divides(2,7) r = divides(8,2)

• Predicate logic is an extension of propositional logic interested both in the sentential connectives of the atomic propositions, and in the internal structure of the atomic propositions.
  • Atoms allow functions and relations on variables
  • The variables may be quantified: ∃ (there exists), ∀ (for all)
  ∀c∃s1∃s2(divides(s1,c) ∨¬divides(s2,c))

  For each digit c there exits a digit s1 that divides c or a digit s2 that does not divide c.

  ∃s1∃s2∀c(divides(s1,c) ∨¬divides(s2,c))

  There exist digits s1 and s2 such that every digit c is either divisible by s1 or indivisible by s2.

• Non-quantified variables are said to be free
  ∀c(divides(s1,c) ∨¬divides(s2,c))

15.4 Tuple Relational Calculus

• Motivation: set notation.
  {i | INTEGER(i) ∧ i > 5}

  template:	i
  condition:	INTEGER(i) ∧ i > 5

• The queries employ formulas of predicate logic with free variables
  ∀(c Digit) (divides(s1,c) ∨¬divides(s2,c))

• Set notation is used to highlight the free variables
  {s1,s2 |∀(c Digit) (divides(s1,c) ∨¬divides(s2,c))}

• The domain of a query consists of the tuples which may be assigned to the free variables of the formula

  	s1,s2	|	(s1,s2) Digit × Digit
  			∧
  			∀(c Digit) (divides(s1,c) ∨¬divides(s2,c))

• The selection of a query is defined by the set of assignments to the free variables which satisfy the formula

  • The value of the predicate logic expression in the case of (s1,s2) = (1,2)?

    	∀(c Digit) (divides(s1,c) ∨ ¬divides(s2,c))
    =	∀(c Digit) (divides(1,c) ∨ ¬divides(2,c))
    =	divides(1,0) ∨¬divides(2,0) ∧ divides(1,1) ∨¬divides(2,1) ∧… ∧ divides(1,9) ∨¬divides(2,9)
    =	true

• Each variable is generalized to represent a record in a relation (= a cursor into a table), instead of a single scalar

  s1.V alue,s2.V alue | DIGIT(s1) ∧ DIGIT(s2) ∧ ∀c(DIGIT(c) ∧ (divides(s1.V alue,c.V alue) ∨¬divides(s2.V alue,c.V alue)))

  DIGIT Value 0 1 9

15.5 Translations to SQL

15.6 Examples: Evaluation

Q1 (p 205 (5th ed) 177 (4th ed))

t.FNAME, t.LNAME, t.ADDRESS EMPLOYEE(t) and (∃d) (DEPARTMENT(d) and d.DNAME=’Research’ and d.DNUMBER=t.DNO)

Q2 (p 205 (5th ed) 177 (4th ed))

p.NUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS PROJECT(p) and EMPLOYEE(m) and p.PLOCATION=’Stafford’ and
(∃ d)(
DEPARTMENT(d) and p.DNUM = d.DNUMBER and d.MGRSSN=m.SSN
)

Q3 (p 207 (5th ed) 179 (4th ed))

• e.LNAME, e.FNAME EMPLOYEE(e) and (∀x) (
  not(PROJECT(x))
  or not (x.DNUM=5)
  or (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)
  )

• e.LNAME, e.FNAME EMPLOYEE(e) and (∀x) (
  if PROJECT(x) then
  [
  not (x.DNUM=5)
  or (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)
  ]
  )

• e.LNAME, e.FNAME EMPLOYEE(e) and (∀x) (
  if PROJECT(x) then
  [
  if (x.DNUM=5) then
  [ (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)]
  ]
  )

15.7 Examples: Translations

• 1. SELECT t.FNAME, t.LNAME, t.ADDRESS 
     FROM   EMPLOYEE as t 
     WHERE (∃d) (DEPARTMENT(d) and d.DNAME=’Research’ and d.DNUMBER=t.DNO) 
     

  2. SELECT t.FNAME, t.LNAME, t.ADDRESS 
     FROM   EMPLOYEE as t 
     WHERE EXISTS(  d DEPARTMENT(d) and d.DNAME=’Research’ and d.DNUMBER=t.DNO  ) 
     

  3. SELECT t.FNAME, t.LNAME, t.ADDRESS 
     FROM   EMPLOYEE as t 
     WHERE  EXISTS ( 
          SELECT  * 
          FROM DEPARTMENT as d 
          WHERE d.DNAME=’Research’ and d.DNUMBER=t.DNO ) 
     

• 1. SELECT p.NUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS 
     FROM   PROJECT as p, EMPLOYEE as m 
     WHERE  p.PLOCATION=’Stafford’ and 
            (∃ d)( DEPARTMENT(d) and p.DNUM = d.DNUMBER and d.MGRSSN=m.SSN ) 
     

  2. SELECT p.NUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS 
     FROM   PROJECT as p, EMPLOYEE as m 
     WHERE  p.PLOCATION=’Stafford’ and 
            EXISTS(  d DEPARTMENT(d) and p.DNUM = d.DNUMBER and d.MGRSSN=m.SSN  ) 
     

  3. SELECT p.NUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS 
     FROM   PROJECT as p, EMPLOYEE as m 
     WHERE  p.PLOCATION=’Stafford’ and 
        EXISTS ( 
           SELECTS  * 
           FROM     DEPARTMENT as d 
           WHERE    p.DNUM = d.DNUMBER and d.MGRSSN=m.SSN  ) 
     

• 1. SELECT  e.LNAME, e.FNAME 
     FROM    EMPLOYEE as e 
     WHERE   (∀x) ( not(PROJECT(x)) or not (x.DNUM=5) or (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)) 
     

  2. SELECT  e.LNAME, e.FNAME 
     FROM    EMPLOYEE as e 
     WHERE   (∀x) ( if( PROJECT(x) ) then not (x.DNUM=5) or (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)) 
     

  3. SELECT  e.LNAME, e.FNAME 
     FROM    EMPLOYEE as e 
     WHERE NOT EXISTS ( 
              x PROJECT(x)  
          EXCEPT 
              x PROJECT(x) and ((x.DNUM≠5) or (∃w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))  
     ) 
     

  4. SELECT  e.LNAME, e.FNAME 
     FROM    EMPLOYEE as e 
     WHERE NOT EXISTS ( 
              SELECT * 
              FROM PROJECT as x 
            EXCEPT 
              SELECT * 
              FROM PROJECT as x 
              WHERE 
               not (x.DNUM=5) or 
               EXISTS (  { w | WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO} ) 
     ) 
     

  5. SELECT  e.LNAME, e.FNAME 
     FROM    EMPLOYEE as e 
     WHERE   not EXIST ( 
        SELECT * 
        FROM PROJECT as x 
       EXCEPT 
        SELECT * 
        FROM PROJECT as x 
        WHERE 
         not (x.DNUM=5) or 
         EXISTS ( 
           SELECT * 
           FROM   WORKS-ON as w 
           WHERE  w.ESSN=e.SSN and x.PNUMBER=w.PNO ) 
     ) 
     

15.8 Examples: Composing

1. Find all employees which work in department #5.

   EMPLOYEE Fname Lname SSN SupervisorSSN

   DEPARTMENT NAME NUMBER MANAGER-SSN StartDate

   WORKS-FOR EmployeeSSN DeptNumber

   e.Fname, e.Lname EMPLOYEE(e) and (∃ w)(
   WORKS-FOR(w) and w.EmployeeSSN = e.SSN and w.DeptNumber = 5
   )

2. List the managers which have employees in all departments.

   m.Fname, m.Lname EMPLOYEE(m) and (∀ d ∃ e ∃ w)( not(DEPARTMENT(d)) or EMPLOYEE(e) and WORKS-FOR(w) and e.SupervisorSSN = m.SSN and e.SSN = w.EmployeeSSN and w.DeptNumber = d.NUMBER
   )

15.9 Assignment #9

Due: We, Mar 11

Problem 6.24 from the textbook (page 188 4th ed; 218 5th ed). Answer only items a, c, f, and j of Exercise 6.16. Provide the tuple relational calculus queries—don’t give the domain rational calculus queries.

Notes

• If you submit your homework electronically, use the departmental submit utility: submit XXX lab9 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
  • Files are restricted to 1,048,576 bytes
  • The submit program issues error messages to the student’s CSE email account

Q&A

Reference: Ch. 6.6 in textbook.