Relational Calculus

1
Lecture 14

• Relational Calculus

2
Relational Calculus

• There is no order of operations to specify how to
  retrieve the query result
• Declarative VS nonprocedural
• Specifies only what information the result should
  contain
• Main distinguishing feature between relational
  algebra and relational calculus
• SQL
• Evaluated using Relational Algebra
• Syntax based on Calculus
• A relational calculus expression creates a new
  relation, which is specified in terms of
  variables that
• range over rows (in tuple relational calculus),
  or
• range over columns (in domain relational calculus)

3
Relational Calculus

• Relational calculus is relationally complete
• Any relational algebra expression can be
  expressed in relational calculus
• We say that relational calculus (in both forms
  tuple and domain) has the same expressive power
  as relational algebra
• From first-order predicate calculus - FOPC
• Predicate is truth-valued function with arguments
• X is a member of staff
• Substituting values for arguments results in
  propositions
• could be true or false
• John Smith is a member of staff
• X has a range of potential values
• X P(X) i.e. the set of all X such that P(X)
  is true

4
Tuple Relational Calculus

• The tuple relational calculus (TRC) is based on
  specifying a number of tuple variables
• A tuple variable ranges over a particular
  database relation
• Tuple relational calculus queries are of the form
  t COND(t)
• t is a tuple variable
• COND (t) (formula or wff) is a conditional
  expression involving t
• To find the tuples of all employees whose salary
  is above 50,000
• t EMPLOYEE(t) AND t.SALARYgt50000

5
Tuple Relational Calculus

• To find the first and last names of all female
  employees whose salary is above 50,000
• t.FNAME, t.LNAME EMPLOYEE(t) AND
  t.SALARYgt50000 AND t.sexFemale
• Every TRC expression has three components
• Requested tuples/attribute(s) (t.FNAME, t.LNAME)
• Range relation(s) (EMPLOYEE(t))
• Specify the domains of the tuple variables
• Condition(s) (t.SALARYgt50000 AND )
• Can be combined using OR, AND and NOT

6
Existential and Universal Quantifiers

• Two special symbols called quantifiers can appear
  in formulas
• Universal quantifier (?)
• Existential quantifier (?)
• A tuple variable t is bound if it is quantified,
• meaning that it appears in an(? t) or (? t)
  clause
• Otherwise, t it is free
• The only free tuple variables in a relational
  calculus expression should be those that appear
  to the left of the bar ( )
• t COND(t)

7
The Existential Quantifier

• Must be true for at least one instance
• Find names of all departments located in
  Chicago
• D.DNAME DEPARMENT(D) AND (( L)
  (DEPT_LOCATIONS(L) AND D.DLOCATION CHICAGO
  AND L.DNUMBER D.DNUMBER))
• D is free while L is bound
• There exists a DEPT_LOCATIONS tuple with same
  DNUMBER as the DNUMBER of the current DEPARMENT
  tuple, D, and is located in Chicago
• Find last names of employees working on Project
  CSI
• E.LNAME EMPLOYEE (E) AND (( W) ( P)
  (WORKS_ON(W) AND PROJECT(P) AND W.ESSNE.SSN AND
  W.PNO P.PNUMBER AND P.PNAME CSI))

8
(No Transcript)
9
The Universal Quantifier

• Statements about every instance, such as
• Find names of departments that are located only
  in PARIS
• or, Find the names of departments which have all
  of their locations in Paris
• D.DNAME DEPARMENT(D) AND ((? L)
  (DEPT_LOCATIONS(L) AND L.DNUMBER D.DNUMBER AND
  D.DLOCATION PARIS))
• D.DNAME DEPARMENT(D) AND (NOT( L)
  (NOT(DEPT_LOCATIONS(L) AND L.DNUMBER D.DNUMBER
  AND D.DLOCATION PARIS)))

10
Transformation Laws

• (? x)(P(x)) NOT( x)(NOT P(x))
• ( x)(P(x)) NOT(? x)(NOT P(x))
• (? x)(P(x) AND Q(x)) NOT( x)(NOT P(x) OR NOT
  Q(x))
• ( x)(P(x) AND Q(x)) NOT(? x)(NOT P(x) OR NOT
  Q(x))
• (? x)(P(x) OR Q(x)) NOT( x)(NOT P(x) AND NOT
  Q(x))
• ( x)(P(x) OR Q(x)) NOT(? x)(NOT P(x) AND NOT
  Q(x))

11
Example Queries using ? and ?

• Retrieve the name and address of employees who
  work for the Research department
• t.FNAME, t.LNAME, t.ADDRESS EMPLOYEE(t) and
  ((? d) (DEPARTMENT(d) and d.DNAMEResearch and
  d.DNUMBERt.DNO))
• The conditions EMPLOYEE (t) and DEPARTMENT(d)
  specify the range relations for t and d
• The condition d.DNAME Research is a selection
  condition and corresponds to a SELECT operation
  in the relational algebra, whereas the condition
  d.DNUMBER t.DNO is a JOIN condition

12
Example Queries using ? and ?

• Find the names of employees who have at least one
  dependent
• e.LNAME, e.FNAME EMPLOYEE(e) AND ((? d)
  (DEPENDENT(d) AND d.ESSN e.SSN))
• Find the names of managers who have at least one
  dependent
• e.LNAME, e.FNAME EMPLOYEE(e) AND ((? d) (? p)
  (DEPARTMENT (d) AND DEPENDENT(p) AND
  e.SSNd.MGRSSN AND p.ESSN e.SSN))
• Find the names of employees who have no
  dependents
• e.LNAME, e.FNAME EMPLOYEE(e) AND (NOT(? d)
  (DEPENDENT(d) AND e.SSNd.ESSN)) OR
• e.LNAME, e.FNAME EMPLOYEE(e) AND ((? d)
  (NOT(DEPENDENT(d) AND e.SSNd.ESSN)

13
Other Example Queries

• For every employee, retrieve the employees first
  and last name and the first and last name of his
  or her immediate supervisor
• e.FNAME, e.LNAMR, s.FNAME, s.LNAME EMPLOYEE(e)
  AND EMPLOYEE(s) AND e.SUPERSSN s.SSN
• For every project located in Stafford, list the
  project number, the controlling department
  number, and the departments managers last name,
  birth date and address
• p.PNUMBER, p.DNUM, e.LNAME, e.BDATE, e.ADDRESS
  PROJECT(p) AND EMPLOYEE (e) AND p.PLOCATION
  Stafford AND ((? d)(DEPARMENT(d) AND
  d.DNUMBERp.DNUM AND d.MGRSSN e.SSN))

14
Other Example Queries

• Find the name of each employee who works on some
  project controlled by department 5
• e.FNAME, e.LNAME EMPLOYEE(e) AND ((? w) (?
  p)(WORKS_ON(w) AND PROJECT(P) AND p.DNUM 5 AND
  e.SSN w.ESSN AND p.PNUMBER w.PNO))
• Find the names of employees who work on ALL
  projects controlled by department number 5
• e.LNAME, e.FNAME EMPLOYEE(e) AND ((? p) (? w)
  (PROJECT(p) AND p.DNUM5 AND WORKS_ON(w) AND
  w.ESSNe.SSN AND p.PNUMBERw.PNO))

15
Example Queries using ? and ?

• Make a list of project numbers for projects that
  involve an employee whose last name is Smith,
  either as a worker or as manager of the
  controlling department for the project
• p.PNUMBER PROJECT(p) AND (((? e)(?
  w)(EMPLOYEE(e) AND WORKS_ON(w) AND e.LNAME
  Smith AND e.SSN w.ESSN AND p.PNUMBER
  w.PNO))OR ((? e)(? d)(EMPLOYEE(e) AND
  DEPARMENT(d) AND e.LNAME Smith AND e.SSN
  d.MGRSSN AND p.DNUM d.DNUMBER)))