Relational Calculus

15.1 The Roots of SQL

|----------------------------------------------| |         SQL         |-----------------------|| |                     |    Tuple Relational    || |                     |       Calculus        || |                     |----------------------||| |                     |       Relational      ||| |                     ||------Calculus-------||| |                     ||      Predicate       ||| |                     ||        Logic         ||| ||----------------|   |||-------------------|||| ||   Relational    |   |||   Propositional    |||| |||---Algebra----||   |||       Logic        |||| |||              ||   ||||-----------------||||| |-----Algebra-----------------Calculus---------|

15.2 Propositional Logic

15.3 Predicate Logic

15.4 Tuple Relational Calculus

15.5 Translations to SQL

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

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

15.7 Examples: Translations

15.8 Examples: Composing

  1. Find all employees which work in department #5.
    EMPLOYEE
    FnameLnameSSNSupervisorSSN
    DEPARTMENT
    NAMENUMBERMANAGER-SSNStartDate
    WORKS-FOR
    EmployeeSSNDeptNumber

    {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

Q&A

Reference: Ch. 6.6 in textbook.