## 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)
cs1s2(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.

s1s2c(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

 Free variables: select ... Domain: from ... Formula: where ...

• {t.Name,t.Number | Students(t) t.Number > 2000}
select t.Name, t.Number
from Students as t
where t.Number > 2000

### 15.6 Examples: Evaluation

EMPLOYEE   (FNAME, BINIT, LNAME, SSN, BDATE, ADDRESS,
DEPT_LOC   (DNUMBER, DLOCATION)
WORK_ON    (ESSN, PNO, HOURS)
PROJECT    (PNAME, PNUMBER, PLOCATION, DNUM)
DEPENDENT  (ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)

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

FROM   EMPLOYEE as t
WHERE (d) (DEPARTMENT(d) and d.DNAME=’Research’ and d.DNUMBER=t.DNO)

FROM   EMPLOYEE as t
WHERE EXISTS(  d DEPARTMENT(d) and d.DNAME=’Research’ and d.DNUMBER=t.DNO  )

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

Reference: Ch. 6.6 in textbook.