Relational Calculus

] > Relational Calculus

[next] [prev] [prev-tail] [tail] [up] [toc]

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 | I N T E G E R (i) \land i > 5}$
= ${6, 7, 8, \dots}$
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: $\lor$ , $\land$ , $\neg$

$p \land q$ , $\neg (\neg p \lor \neg p)$
Propositions can be true or false, dependent on the interpretation given to their atoms
The proposition $p \land 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: $\exists$ (there exists), $\forall$ (for all)
$\forall c \exists s 1 \exists s 2 (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$
For each digit $c$ there exits a digit $s 1$ that divides $c$ or a digit $s 2$ that does not divide $c$ .
$\exists s 1 \exists s 2 \forall c (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$
There exist digits $s 1$ and $s 2$ such that every digit $c$ is either divisible by $s 1$ or indivisible by $s 2$ .
Non-quantified variables are said to be free
$\forall c (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$

15.4 Tuple Relational Calculus

Motivation: set notation.
${i | I N T E G E R (i) \land i > 5}$
template: $i$
condition: $I N T E G E R (i) \land i > 5$
The queries employ formulas of predicate logic with free variables
$\forall (c \in D i g i t) (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$
Set notation is used to highlight the free variables
${s 1, s 2 | \forall (c \in D i g i t) (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))}$
The domain of a query consists of the tuples which may be assigned to the free variables of the formula

$\{$ $s 1, s 2$ $|$ $(s 1, s 2) \in D i g i t \times D i g i t$
$\land$
$\forall (c \in D i g i t) (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, 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)?

	$\forall (c \in D i g i t) (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$
$=$	$\forall (c \in D i g i t) (d i v i d e s (1, c) \lor \neg d i v i d e s (2, c))$
$=$	$d i v i d e s (1, 0) \lor \neg d i v i d e s (2, 0) \land d i v i d e s (1, 1) \lor \neg d i v i d e s (2, 1) \land \dots \land d i v i d e s (1, 9) \lor \neg d i v i d e s (2, 9)$
$=$	$t r u e$

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

$\{$ $s 1 . V a l u e, s 2 . V a l u e$ $|$ $D I G I T (s 1) \land D I G I T (s 2)$
$\land$
$\forall c (D I G I T (c) \land (d i v i d e s (s 1 . V a l u e, c . V a l u e) \lor \neg d i v i d e s (s 2 . V a l u e, c . V a l u e)))$ $\}$

DIGIT
Value
0
1
$\dots$
9

15.5 Translations to SQL

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

${t . N a m e, t . N u m b e r | S t u d e n t s (t) \land t . N u m b e r > 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,
                                 SEX, SALARY, SUPERSSN, DNO)
DEPT_LOC   (DNUMBER, DLOCATION)
DEPARTMENT (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE)
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 (

\exists

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
(

\exists

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 ( $\forall$ x) (
not(PROJECT(x))
or not (x.DNUM=5)
or ( $\exists$ w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)
) $\}$
$\{$ e.LNAME, e.FNAME $|$ EMPLOYEE(e) and ( $\forall$ x) (
if PROJECT(x) then
[
not (x.DNUM=5)
or ( $\exists$ w)(WORKS-ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO)
]
) $\}$
$\{$ e.LNAME, e.FNAME $|$ EMPLOYEE(e) and ( $\forall$ x) (
if PROJECT(x) then
[
if (x.DNUM=5) then
[ ( $\exists$ 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 ( $\exists$ 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
         ( $\exists$ 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   ( $\forall$ x) ( not(PROJECT(x)) or not (x.DNUM=5) or ( $\exists$ 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   ( $\forall$ x) ( if( PROJECT(x) ) then not (x.DNUM=5) or ( $\exists$ 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 ( $\exists$ 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

Find all employees which work in department #5.

EMPLOYEE
Fname Lname $\underline{S S N}$ SupervisorSSN

DEPARTMENT
NAME $\underline{N U M B E R}$ MANAGER-SSN StartDate

WORKS-FOR
EmployeeSSN DeptNumber

$\{$ e.Fname, e.Lname $|$ EMPLOYEE(e) and ( $\exists$ w)(
WORKS-FOR(w) and w.EmployeeSSN = e.SSN and w.DeptNumber = 5
) $\}$
List the managers which have employees in all departments.
$\{$ m.Fname, m.Lname $|$ EMPLOYEE(m) and ( $\forall$ d $\exists$ e $\exists$ 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.

[next] [prev] [prev-tail] [front] [up] [toc]

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)

$\{$	$s 1, s 2$	$\|$	$(s 1, s 2) \in D i g i t \times D i g i t$
			$\land$
			$\forall (c \in D i g i t) (d i v i d e s (s 1, c) \lor \neg d i v i d e s (s 2, c))$	$\}$

	${i \| I N T E G E R (i) \land i > 5}$
=	${6, 7, 8, \dots}$

template:	$i$
condition:	$I N T E G E R (i) \land i > 5$