Relational Calculus

1
Lecture 14 (10/05/2005)

• 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
• 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 names of employees working on Project X
• NEXT SLIDE

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)) OR e.SSNltgtd.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)))

16
The Domain Relational Calculus

• Another variation of relational calculus called
  the domain relational calculus (DRC)
• Domain calculus differs from tuple calculus in
  the type of variables used in formulas
• the variables range over single values from
  domains of attributes
• To form a relation of degree n for a query
  result, we must have n of these domain
  variablesone for each attribute
• An expression of the domain calculus is of the
  form x1, x2, . . ., xn COND(x1, x2, . . .,
  xn, xn1, xn2, . . ., xnm) where x1, x2, . .
  ., xn, xn1, xn2, . . ., xnm are domain
  variables
• range over domains (of attributes) and COND is a
  condition or formula of the DRC

17
Example Query Using Domain Calculus

• Retrieve the birthdate and address of the
  employee whose name is John B. Smith.
• qrs (? q) (? r) (? s) (? t) (? w) (? x) (? y)
  (? z) (EMPLOYEE(qrstuvwxyz) and qJohn and
  rB and sSmith)
• 10 variables for the employee relation are
  needed, one to range over the domain of each
  attribute in order
• Of the ten variables q, r, s, . . ., z, only u
  and v are free
• Specify the condition for selecting a tuple
  following the bar ( )namely, that the sequence
  of values assigned to the variables qrstuvwxyz be
  a tuple of the employee relation and that the
  values for q (FNAME), r (MINIT), and s (LNAME) be
  John, B, and Smith, respectively.

18
Retrieve the Name of manager of the Accounting
Department
19
Example Query Using Domain Calculus

• Retrieve the Name of manager of the Accounting
  Department.
• qrs (? q) (? r) (? s) (? t) (? w) (? x) (? y)
  (? z) (? a) (? b) (? c) (? d) (EMPLOYEE(qrstuvwxyz
  ) AND DEPARTMENT(abcd) AND a Accounting AND
  tb