Lecture 5: Relational calculus

1
Lecture 5Relational calculus

• www.cl.cam.ac.uk/Teaching/current/Databases/

2
Relational calculus

• There are two versions of the relational
  calculus
• Tuple relational calculus (TRC)
• Domain relational calculus (DRC)
• Both TRC and DRC are simple subsets of
  first-order logic
• The difference is the level at which variables
  are used for fields (domains) or for tuples
• The calculus is non-procedural (declarative)
  compared to the relational algebra

3
Domain relational calculus

• Queries have the form ltx1,,xngt F(x1,,xn)
  where x1,,xn are domain variables and F is a
  formula with free variables x1,,xn
• Answer all tuples ltv1,,vngt that make F(v1,,vn)
  true

4
Example

• Find all sailors with a rating above 7
• ltI,N,R,Agt ltI,N,R,Agt?Sailors ? Rgt7
• The condition ltI,N,R,Agt?Sailors ensures that the
  domain variables are bound to the appropriate
  fields of the Sailors tuple

5
Example

• Simple projection ltI,Ngt ?R,A.ltI,N,R,Agt?Sailo
  rs
• Simple projection and selection ltI,Ngt
  ?R,A.ltI,N,R,Agt?Sailors ? NJulia

6
DRC formulae

• Atomic formulae a
• ltx1,,xngt?R
• xi binop xj, xi binop c, c binop xj, unop c, unop
  xi
• DRC Formulae P, Q
• a
• ?P, P?Q, P?Q
• ?x.P
• ?x.P
• Recall that ?x and ?x are binders for x

7
Example

• Find the names of sailors rated gt7 whove
  reserved boat 103
• ltNgt ?I,A,R.ltI,N,R,Agt?Sailors ?
• Rgt7 ? ?SI,BI,D.(ltSI,BI,Dgt?Re
  serves ? ISI ?
  BI103)
• Note the use of ? and to simulate join

8
Example

• Find the names of sailors rated gt7 whove
  reserved a red boat
• ltNgt ?I,A,R.ltI,N,R,Agt?Sailors ?
• Rgt7 ? ?SI,BI,D.
  (ltSI,BI,Dgt?Reserves ?
  SII ? ?B,C.
  (ltB,Cgt?Boats ? BBI ?
  Cred))

9
Example

• Find the names of sailors who have reserved at
  least two boats

ltNgt ?I,R,A. ltI,N,R,Agt?Sailors ?
?BI1,BI2,D1,D2.ltI,BI1,D1gt?Reserves ?
ltI,BI2,D2gt?Reserves ?
BI1?BI2
10
Example

• Find names of sailors whove reserved all boats

11
Example

• Find names of sailors whove reserved all boats

ltNgt ?I,R,A. ltI,N,R,Agt?Sailors ? ?B,C.
(?(ltB,Cgt?Boats) ?
(?ltSI,BI,Dgt?Reserves.
ISI ? BIB))
ltNgt ?I,R,A. ltI,N,R,Agt?Sailors ?
?ltB,Cgt?Boats. ?ltSI,BI,Dgt?Reserves.
ISI ? BIB))
12
Tuple relational calculus

• Similar to DRC except that variables range over
  tuples rather than field values
• For example, the query Find all sailors with
  rating above 7 is represented in TRC as follows
• S S?Sailors ? S.ratinggt7

13
Semantics of TRC queries

• In general a TRC query is of the form
• t P
• where FV(P)t
• The answer to such a query is the set of all
  tuples T for which PT/t is true

14
Example

• Find names and ages of sailors with a rating
  above 7
• P ?S?Sailors. S.ratinggt7?
  P.snameS.sname? P.ageS.age

15
Example

• Find the names of sailors who have reserved at
  least two boats

P ?S?Sailors. ?R1?Reserves.
?R2?Reserves. S.sidR1.sid ?
R1.sidR2.sid ? R1.bid ? R2.bid ?
P.snameS.sname
16
Example

• Find the name of sailors who have reserved all
  the boats

17
Equivalence with relational algebra

• This equivalence was first considered by Codd in
  1972
• Codd introduced the notion of relational
  completeness
• A language is relationally complete if it can
  express all the queries expressible in the
  relational algebra.

18
Encoding relational algebra

• Lets consider the first direction of the
  equivalence can the relational algebra be coded
  up in the (domain) relational calculus?
• This translation can be done systematically, we
  define a translation function -
• Simple case
• R ltx1,,xngt ltx1,,xngt?R

19
Encoding selection

• Assume
• e ltx1,,xngt F
• Then
• sc(e) ltx1,,xngt F ? C
• where C is obtained from C by replacing each
  attribute with the corresponding variable

20
Encoding relational calculus

• Can we code up the relational calculus in the
  relational algebra?
• At the moment, NO!
• Given our syntax we can define problematic
  queries such as
• S ? (S?Sailors)
• This (presumably) means the set of all tuples
  that are not sailors, which is an infinite set ?

21
Safe queries

• A query is said to be safe if no matter how we
  instantiate the relations, it always produces a
  finite answer
• Unfortunately, safety (a semantic condition) is
  undecidable ?
• That is, given a arbitrary query, no program can
  decide if it is safe
• Fortunately, we can define a restricted syntactic
  class of queries which are guaranteed to be safe
  ?
• Safe queries can be encoded in the relational
  algebra

22
Summary

• You should now understand
• The relational calculus
• Tuple relational calculus
• Domain relational calculus
• Translation from relational algebra to relational
  calculus
• Safe queries and relational completeness
• Next lecture Basic SQL