Introduction to Relational Calculus

1
Introduction to Relational Calculus

• Predicate Calculus over Relations
• and the
• Safety of Relational Calculus Expressions

2
Relational Calculus 1

• Predicate Calculus query languages ...
• a query finding values satisfying predicate
• Two kinds of predicate calculus language
• terms (primitive objects of discourse)
• "tuples ? tuple relational calculus
• "domain values ? domain relational calculus

3
Relational Calculus 2

• Expressions in the tuple relational calculus
• Basic form of such an expression
• t y(t) where t is a tuple variable
• Here t or t(r) denotes a tuple of some fixed
  arity
• (NB not denoting a tuple of fixed type)
• and y is a formula built according to the
  conventional
• first order predicate calculus (FOPC) rules

4
Recall the logical expression for a QUEL query

• Archetypal query in QUEL constructs new relation
  from relations R1, R2, ..., Rk.
• range of t1 is R1
• range of t2 is R2
• ....
• range of tk is Rk
• retrieve (ti(1).Aj(1), ...., ti(r).Aj(r))
• where Y(t1, t2, ..., tk)
• Y(t1, t2, ..., tk) is a (quantifier-free) logical
  constraint on the tuples selected by the range
  variables t1, t2, ..., tk in the construction
  process

5
Recall the logical expression for a QUEL query

• A suitable logical expression for the required
  relation is
• u(r) (? t1) ... (? tk)
• ( R1(t1) ? R2(t2) ? ... ? Rk(tk)
• ? u1 ti(1) j(1)
• ? u2 ti(2) j(2)
• ? ...
• ? ur ti(r) j(r) ,
• ? Y(t1, t2, ..., tk)
• )

6
Relational Calculus 3

• Form of relational calculus expressions
• Formula is built up using operators of FOPC from
• atomic clauses of three types
• 1. R(s), where R is relation name, s is tuple
  variable
• 2. si q uj, where s and u are tuple
  variables, and q is
• an arithmetic comparison operator (such as lt,
  etc)
• 3. si q c, where s is tuple variable, c is a
  constant

7
Relational Calculus 4

• Semantics of the atomic clauses
• 1. R(s), where R is relation name, s is tuple
  variable
• 2. si q uj, where s and u are tuple
  variables, and q is an
• arithmetic comparison operator
• 3. si q c, where s is tuple variable, c is a
  constant
• 1. "s represents a tuple in R"
• 2. "ith component of tuple represented by s is in
  relation
• q to the jth component of tuple represented by
  u
• 3. "ith component of tuple represented by s is in
  relation
• q to the constant c

8
General Relational Calculus Formulae 1

• Combine clauses in standard predicate calculus
  fashion
• A well-formed formula (wff) is defined
  recursively
• 1. Every atomic clause is a wff.
• 2. If y and y' are wffs, then y?y',y?y' and y
  are wffs
• 3. If y is a wff, (s)(y) is a wff
• 4. If y is a wff, ("s)(y) is a wff
• 5. and " are disambiguated by brackets as
  indicated
• in 3. and 4. (NB need extra syntactic
  conventions to
• declare precedence in arithmetic expressions
  etc)
• 6. The class of wffs is the smallest class closed
  wrt
• rules 1,2,3,4,5, and containing all atomic
  clauses.

9
General Relational Calculus Formulae 2

• Basic vocabulary
• 1. and 2. define propositional logic  need more
• 3. and 4. define predicate logic
• (s)(y) is existential quantification
• "there exists s, such that... "
• ("s)(y) is universal quantification
• "for all s, ... "
• and " are quantifiers .
• To interpret wffs need to define free and bound
• variables ... informally, quantifiers introduce
  bound
• variables cf. global / local variables in a PL

10
General Relational Calculus Formulae 3

• Free and bound variables in wff's
• 1. Every atomic clause is a wff.
• All tuple variables mentioned in an atomic wff
  are said to be free in this wff
• 2. If y and y' are wffs, then y?y',y?y' and y
  are wffs
• A variable in a wff is free iff it is not within
  the scope
• of a quantification, so x appears free/bound in
  y?y'
• iff it appears free/bound in y or y' (perhaps
  both) etc.
• Confusing when x denotes both bound and free vars
  ...

11
General Relational Calculus Formulae 4

• Renaming bound vars doesnt affect meaning of wff
• Can rename bound vars in both y and y' to ensure
  that
• x occurs in both y and y'
• iff x occurs freely in both y and y'
• When such renaming has been done, have
• x occurs free in y?y' ( or y?y' )
• iff x occurs free in y or y' (perhaps both)
• x occurs bound in y?y' ( or y?y' )
• iff x occurs bound in y or y' (but not both)

12
General Relational Calculus Formulae 4

• Free bound variables in wffs (cont)
• 3. If y is a wff, (s)(y) is a wff
• Occurrences of s which are bound in y can be
• eliminated by renaming as necessary.
• When this has been done, occurrences of s which
  are free in y are bound in (s)(y).
• 4. If y is a wff, ("s)(y) is a wff
• Similar conventions operate for bound and free
• variables to those in case of 3. above
• ... use concepts of free and bound variables to
  give
• formal interpretation to wffs ...

13
General Relational Calculus Formulae 5

• Semantics of wff's
• A tuple relational calculus expression y with
  free tuple
• variables t1, t2, ..., tn is satisfied by
  assigning specific
• tuples to t1, t2, ..., tn so that formula y
  evaluates to true.
• Denote such a y by y(t1, t2, ..., tn) when we
  want to
• emphasise the free variables in it.
• NB in the process of defining a set of tuples
• t y(t) where t is a single tuple variable
• may need to define "tuples of tuples" by logical
  formulae
• with more than one free tuple variable in them.

14
General Relational Calculus Formulae 6

• Semantics of wff's (cont.)
• 1. Every atomic clause is a wff.
• Semantics explained above. For example
• (s,u) s1 u2
• defines all pairs of tuples (s0, u0) such that
• first component of s0 second component of u0
• 2. If y and y' are wffs, then y?y',y?y' and y
  are wffs
• Semantics (y ? y')(t) means y(t) and y'(t) etc

15
General Relational Calculus Formulae 7

• Semantics of quantified expressions
• 3. If y is a wff, (s)(y) is a wff
• ((s)(y))(t1, t2, ..., tn) ? (s)(y(t1, t2, ...,
  tn))
• Take n1 obvious extension of semantics to
  cases where there are more free variables.
• Semantics The predicate (s)(y(t)) is true for
  the
• tuple tt0 if, on substituting t0 for each free
  occurrence of t in y, there exists a tuple s0 of
  the appropriate arity such that substituting s0
  for all free occurrences of s in y ensures that
  the formula y evaluates to true.

16
General Relational Calculus Formulae 8

• Semantics of quantified expressions (cont.)
• 4. If y is a wff, ("s)(y) is a wff
• (("s)(y))(t1, t2, ..., tn) ? ("s)(y(t1, t2, ...,
  tn))
• Take n1 obvious extension of semantics to
  cases where there are more free variables.
• Semantics The predicate ("s)(y(t)) is true for
  the
• tuple tt0 if, on substituting t0 for each free
  occurrence of t in y, the substitution of all
  tuples s0 of the appropriate arity for all free
  occurrences of s in y
• ensures that the formula y evaluates to true.

17
General Relational Calculus Formulae 9

• An illustrative example
• Express composition of binary relations R S in
  ISBL
• RCS (R S) BC A, D
• or using the syntax of relational algebra
• RCS ?1,4 s23 (R ? S)
• Express RCS in tuple relational calculus as
• w (u)(v)(R(u) ? S(v) ? F(u,v))
• where F(u,v) ? (u2 v 1 ? w1 u1 ?
  w2 v2)
• Note evaluating the predicate requires a finite
  search
• need only inspect tuples from the finite
  relations R S

18
Safety of relational expressions 1

• Without any restriction on a logical expression
  can define an infinite collection of tuples.
• Need to restrict to sets of tuples that are
  finite to take account of storage and
  computation.
• For example
• what is t ?y(t) ?
• ... very ill-defined collection of tuples
• how do we compute t ( s)(y(s,t)) ?
• ... when have we considered every possible s?

19
Safety of relational expressions 2

• Essential to know when it is safe to evaluate
  expression
• cant have non-terminating behaviour in a
  database
• Solution need to set limits on the values for
  tuples under consideration to eliminate endless
  searches
• ... motivates safety rules for expressions

20
Safety of relational expressions 3

• Safe relational calculus expressions
• When can we evaluate t y(t) ?
• In computational terms, want to be able to
  evaluate truth or falsehood of expression after
  making a finite set of substitutions
• Logical expression means context-independent
  interpretation
• For context-independence basis for restricting a
  search is what can be inferred about domain of
  values of interest from the expression to be
  evaluated.

21
Safety of relational expressions 4

• For context-independence basis for restricting a
  search is what can be inferred about domain of
  values of interest from the expression to be
  evaluated.
• Motivates definition of Dom(y), viz the set of
  components of tuples in relations mentioned in y
  together with all constants referenced by y
• Note that Dom(y) is always a finite set

22
Safety of relational expressions 5

• An illustrative example
• RCS w (u)(v)(y(u,v))
• where y(u,v) ? R(u) ? S(v) ? F(u,v)
• and F(u,v) ? (u2v1 ? ... )
• The relations mentioned in the formula RCS are R
  and S, and there are no constants. Hence
• Dom(y) set of components of tuples in R and S
• a subset of the elements of X where
• R and S are binary relations on X

23
Safety of relational expressions 6

• An illustrative example (cont.)
• RCS w (u)(v)(y(u,v))
• where y(u,v) ? R(u) ? S(v) ? F(u,v)
• and F(u,v) ? (u2v1 ? ... )
• Dom(y) set of components of tuples in R and S
• a subset of the elements of X where
• R and S are binary relations on X
• To evaluate RCS, enough to consider possible
  values of u and v from finite set X ? X Dom(y)
  ? Dom(y).
• Thus w y(w) is a safe relational calculus
  expression

24
Safety of relational expressions 7

• Relational calculus expression t y(t) is safe
  if
• 1. t satisfies y(t) gt each component of t is in
  Dom(y)
• 2. each well-defined subformula of y of the form
  (u)(y(u)) satisfies the condition
• if u is a tuple with a component not in Dom(y),
  then y(u, t1, t2, ..., tn) is false under every
  assignment of values to the free variables t1,
  t2, ..., tn in y
• 3. each well-defined subformula of y of the form
  ("u)(y(u)) satisfies the (dual) condition
• if u is a tuple with a component not in Dom(y),
  then y(u, t1, t2, ..., tn) is true under every
  assignment of values to the free variables t1,
  t2, ..., tn in y

25
Safety of relational expressions 8

• More about the definition of safety
• Safety is a condition that is strong enough to
  ensure that we can decide in finitely many steps
  whether a quantified subformula of the predicate
  y is true.
• We wish to restrict our search procedure to the
  range over tuples of arity k from the finite set
  Dom(y) k
• How can we give a valid yes / no answer to the
  question
• Does there exist a value to satisfy a logical
  condition?
• when we only inspect a finite set of values?

26
Safety of relational expressions 9

• Let F denote the finite set Dom(y)k to be
  inspected.
• Let U denote the universe of all possible
  k-tuples.
• Want to ensure that (u?F)(y(u)) ? (u?U)(y(u))
• This will be true provided y(u) is false for all
  u in U\F.
• Want to ensure that ("u?F)(y(u)) ? ("u?U)(y(u))
• This will be true provided y(u) is true for all u
  in U\F.
• NB These conditions are actually stronger than we
  need, but are the sort of conditions that are
  most useful in practice, when we need to
  interpret our predicates in real-world terms

27
From Relation Algebra to Calculus 1

• Overall aim of the next section prove that
  algebraic and logical characterisations of
  relational queries have equivalent expressive
  power
• First show that safe relational expressions can
  mimic the expressive power of relational algebra
  
• Theorem 1 If E is a relational algebra
  expression, then there is a safe expression in
  tuple relational calculus equivalent to E.

28
From Relation Algebra to Calculus 2

• Proof of Theorem 1
• Use induction on the number N of operators in E.
• Base of induction (N0)
• E is a constant relation t1, t2, ..., tn
• ? E t y(t) where y(t)? (tt1 ? tt2 ?
  ... ? ttn)
• E is specified by a relational variable R
• ? E t y(t) where y(t) ? R(t)
• Both of these are safe expressions
• values of t that satisfy y(t) are within dom(y)
• dom(y) set of tuples with compts in dom(y)

29
From Relation Algebra to Calculus 3

• Induction Step
• Assume that number of operators in E is Ngt0
• Can assume that E is derived from relational
  algebra expressions F and G via one of the five
  basic relational algebra operators, and that
• F t f(t) , G t r(t)
• are safe tuple relational calculus exps for F
  G
• There are 5 cases to be considered, corresponding
  to the 5 basic operators of relational algebra ...

30
From Relation Algebra to Calculus 4

• Induction Step (cont.)
• one case for each basic operator of relational
  algebra
• 1. Union E F ? G
• 2. Set Difference E F - G
• 3. Cartesian Prod E F ? G
• 4. Projection E ?i(1), i(2), ..., i(k)(F)
• 5. Selection
• E sC(F), where C condition on the tuples in F
  
• Cases 1, 2 and 3 dom(y) dom(f) ? dom(r).
• Cases 4 and 5 dom(y) dom(f).

31
From Relation Algebra to Calculus 5

• Induction Step (cont.)
• Cases 1, 2 and 3 dom(y) dom(f) ? dom(r).
• Cases 4 and 5 dom(y) dom(f).
• Generally straightforward to specify the
  predicate y
• NB In each case, have 3 safety conditions to
  check
• satisfying tuples are within dom(y)
• condition on quantified subformulae ( ? / ? )

32
From Relation Algebra to Calculus 6

• Representing the 5 basic operations union (?)
• 1. E F ? G
• ? E t y(t) where y(t) ? (f ? r)(t)
  f(t) ? r(t)
• Safe relational calculus expression
• t satisfies (f ? r)(t)
• ? t satisfies f(t) or t satisfies r(t)
• ? t ? dom(f) or t ? dom(r)
• ? t ? dom(f) ? dom(r) ? dom(y)
• Also because - by inductive hypothesis - f and
  r are both safe, any ? / ? subformulae within f
  or r is safe.

33
From Relation Algebra to Calculus 7

• Representing the 5 basic operations
  difference(-)
• 2. E F - G
• ? E t y(t) where y(t) ? (f ??r)(t)
  f(t) ??r(t)
• Safe expression
• t satisfies (f ??r)(t)
• ? t satisfies f(t)
• ? t ? dom(f)
• ? t ? dom(f) ? dom(r) ? dom(y)
• Other part of safety check similar to the
  previous case

34
From Relation Algebra to Calculus 8

• Representing the 5 basic operations product (?)
• 3. E F ? G
• Assume F and G have arity f and g respectively.
• E t(fg) y(t) where
• y(t) ? (u) (v) (f(u) ? r(v) ?
• t1u1 ? t2u2 ? ... ? tfuf ?
• tf1v1 ? tf2v2 ? ... ? tfgvg
  )
• Must now check the safety conditions ...

35
From Relation Algebra to Calculus 9

• Representing the 5 basic operations product (?)
• y? (u)(v)(f(u)?r(v)? ...) is safe RC
  expression
• First safety condition values are within dom(y)
• t satisfies y(t)
• ? t1u1 ? t2u2 ? ... ? tfuf
• where u satisfies f(u)
• ? t1,t2,...,tf ? dom(f) ? dom(r) ?
  dom(y)
• tf1, tf2, ... , tfg ? dom(y) similarly

36
From Relation Algebra to Calculus 10

• Second safety condition for product (?)
• Consider the existential subformula
• (u) ((v) (f(u) ? r(v) ?
• t1u1 ? t2u2 ? ... ? tfuf ?
• tf1v1 ? tf2v2 ? ... ? tfgvg
  ))
• - one of two introduced in y(t) not in f(u) and
  r(v).
• Has form (u)(F(u)) where dom(F) dom(f) ?
  dom(r)
• ui ? dom(F) for some i
• ? ui ? dom(f) ? dom(r) for some i
• ? ui ? dom(f)
• ? u does not satisfy f(u) since f is safe
• ? u does not satisfy F(u) ? f(u) ? ... .

37
From Relation Algebra to Calculus 11

• Representing the 5 basic operations ?
• 4. E ?i(1), i(2), ..., i(k)(F)
• ? E t(k) y(t) where
• y(t)? (u)(f(u) ?
• t1u i(1) ? t2ui(2) ? ... ?
  tkui(k))
• Safe relational calculus expression (1st
  condition)
• t satisfies y(t)
• ? t1ui(1) ? t2ui(2) ? ... ?
  tkui(k)
• where u satisfies f(u) and f is safe
• ? t1, t2, ... , tk ? dom(f) dom(y)

38
From Relation Algebra to Calculus 12

• Second safety condition for projection (?)
• Consider the existential subformula
• (u) (f(u) ? t1u i(1) ? t2ui(2) ? ...
  ? tkui(k))
• introduced in constructing y(t) from f(u)
• Has form (u)(F(u)) where dom(F) dom(f).
• ui ? dom(F) for some i
• ? ui ? dom(f) for some i
• ? u does not satisfy f(u) since f is safe
• ? u does not satisfy F(u) ? f(u) ? ... .

39
From Relation Algebra to Calculus 13

• Representing the 5 basic operations sC
• 5. E sC(F) where C is a condition on tuples in
  F.
• C is expressed in terms of primitive relations of
  the form
• i q j
• combined using propositional logical connectives
• where i and j are indices of components of tuples
  in F
• and q is a relational operator (lt,?,,?,gt)
• Transform C into C' in tuple relational calculus
  syntax
• by substituting ti for i throughout then
• E t y(t) where y(t) ? f(t) ? C'.

40
From Relation Algebra to Calculus 14

• Safety condition for selection (sC)
• Selection is represented using y(t) ? f(t) ? C'.
• t satisfies y(t) ? t satisfies f(t)
• ? t ? dom(f)
• ? t ? dom(y)
• since dom(y) dom(f) any constants in C'.
• There is no need to check the other safety
  conditions
• as no new quantifiers are introduced in
  constructing y.

41
From Relation Algebra to Calculus 15

• This completes the proof of theorem 1
• Theorem 1 If E is a relational algebra
  expression, then there is a safe expression in
  tuple relational calculus equivalent to E.
• This shows that tuple relational calculus is at
  least as expressive as relational algebra.
• It remains to prove the converse that relational
  algebra is as expressive as relational calculus

42
To follow From (domain) relational
calculus to algebra