From Relational Calculus to Relational Algebra

1
From Relational Calculus to Relational Algebra

• Tuple relational calculus,
• domain relational calculus,
• and relational algebra

2
Domain Relational Calculus 1

• .... reminder
• Predicate Calculus query languages ...
• a query finding values satisfying predicate
• Two kinds of predicate calculus language
• primitive objects tuples ? tuple relational
  calculus
• primitive objects domain values
• ? domain relational calculus

3
Domain Relational Calculus 2

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

4
Domain Relational Calculus 3

• Semantics of the atomic clauses
• 1. R(s1s2 sk), where R is relation name,
• and each si is domain variable
• 2. si q uj, where s and u are domain variables,
  and q is
• an arithmetic comparison operator (such as
  lt, etc)
• 3. si q c, where s is domain variable, c is a
  constant
• 1. s1s2 sk represents a tuple in R
• 2. domain value represented by si is in
  relationq to the domain value represented by uj
• 3. domain value represented by si is in
  relationq to the constant c

5
From tuple to domain relational calculus 1

• The definition of safety is expressed in terms of
  constraints on component values of tuples ... so
  generalises directly to domain relational
  calculus
• Theorem 2 For every safe tuple relational
  calculus expression there is an equivalent safe
  domain relational calculus expression.
• Omit formal proof essentially a syntactic
  transformation involving substitution for tuple
  variables

6
From tuple to domain relational calculus 2

• An illustrative example
• Can express RCS in tuple relational calculus as
• w (u)(v)(R(u) Ù S(v) Ù f(u,v))
• where f(u,v) º (u2v1 Ù w1u1 Ù
  w2v2)
• in domain relational calculus, this becomes
• w1w2 (u1)(u2)(v1)(v2)
• (R(u1u2) Ù S(v1v2) Ù F(u1,u2,v1,v2))
• where F(u1,u2,v1,v2) º (u2v1 Ù w1u1 Ù w2v2)

7
From domain relational calculus to algebra 1

• Theorem 3 For every safe expression in domain
  relational calculus, there is an equivalent
  relational algebra expression.
• Proof (sketch only)
• Use induction on number of operators in y to
  construct an algebraic expression for t1t2 ...
  tn y(t1, t2, ..., tn)
• To simplify the induction, begin with two lemmas
• there is a relational algebra exp to represent
  dom(y)
• dont need to consider ? and ? as independent
  cases

8
From domain relational calculus to algebra 2

• Use induction on number of operators in y to
  construct an algebraic expression for t1t2 ...
  tn y(t1, t2, ..., tn)
• By safety, enough to show
• for each subformula w of y of the form
• t1t2 ... tm w(t1, t2, ..., tm)
• a relational algebra expression E whose value
  is
• dom(y) Ç t1t2 ... tm w(t1, t2, ..., tm)
• where dom(y) set of tuples with compts in
  dom(y)
• i.e. can restrict attention to tuples in dom(y)

9
From domain relational calculus to algebra 3

• Lemma A If y is any formula in domain relational
  calculus, there is a relational algebra
  expression to represent the unary relation dom(y)
• Note unary relation º set of 1-tuples º set
• Proof Suppose R has arity k. Let
• D(R) º P1(R) È P2(R) È ... È Pk(R).
• dom(y) is the union of all D(R)'s over relations
  R referred to in y together with the set of all
  constants a1, a2, ..., an referred to in y.
• Thus can take D as an algebraic expression
• D ?R referred to in y D(R) È a1, a2, ...,
  an

10
From domain relational calculus to algebra 4

• Lemma B If y is any formula in domain relational
• calculus, there is a formula y' in domain
  relational
• calculus with no occurrences of Ù or " such that
• t1t2 ... tn y(t1, t2, ..., tn) and t1t2
  ... tn y'(t1, t2, ..., tn)
• are equivalent. This transformation respects
  safety.
• Proof Wherever the operators Ù and " appear in
  y
• replace f Ù r by ?(?f ? ?r).
• replace ("v)(f(v)) by ?(v)(?f(v)).
• Need to show that safety is preserved ...

11
From domain relational calculus to algebra 5

• Proof of Lemma B Wherever the operators Ù and "
  appear in y
• replace f Ù r by ?(?f ? ?r).
• replace ("v)(f(v)) by ?(v)(?f(v)).
• To show that safety is preserved ...
• Observe that dom(y) dom(y')
• this takes care of the first safety condition.
• Note also that if ("v)(f(v)) safe
• v Ï dom(f) Þ f(v) true Þ ?f(v) false
• Hence (v)(?f(v)) is also safe

12
From domain relational calculus to algebra 6

• Proof of Theorem 3 (cont.)
• Consider relation defined by t1t2...tn y(t1,
  t2, ..., tn)
• where y is a safe relational calculus expression
• By Lemmata
• can assume neither Ù or " occurs in y
• enough to show for each subformula w of y of form
• t1t2...tm w(t1, t2, ..., tm)
• a relational algebra expression E whose value
  is
• dom(y) Ç t1t2...tm w(t1, t2, ..., tm)
• where dom(y) set of tuples with compts in
  dom(y)
• Prove this by induction on the number of
  operators in y.

13
From domain relational calculus to algebra 7

• I.e. prove for all subformulae w of y ? in
  particular for y itself ? by induction on number
  of operators N in w.
• N0 consider the relation defined by
• dom(y) Ç t1t2 ... tm w(t1, t2, ..., tm)
• where w is an atomic formula.
• Let D be relational algebra expression for
  dom(y).
• Two cases
• 1. w(ti, tj) ti q tj or w(ti) ti q c
• where q is an arithmetic comparison operator
• 2. w(t1, t2, ..., tm) R(ti(1)ti(2) ... ti(k))

14
From domain relational calculus to algebra 8

• Proof of Theorem 3 Base of induction (cont.)
• 1. w(ti, tj) ti q tj or w(ti) ti q c
• where q is an arithmetic comparison operator
• 2. w(t1, t2, ..., tm) R(ti(1)ti(2) ... ti(k))
• For case 1 use expression E º siqj(DD).
• For case 2 have w(t1, t2, ..., tm) R
  (ti(1)ti(2) ... ti(k))
• By safety, every index r, where 1?r?m, must be an
• index i(j) for some j. Define algebraic
  expression
• E º Õj(1), j(2), ..., j(m)(sC(R))
• where C is conjunction of relations rs over
  pairs (r,s)
• such that i(r)i(s) and j(r) is an index such
  that i(j(r))r.

15
From domain relational calculus to algebra 9

• Illustrative example for Case 2
• Take domain relational calculus expression
• t1t2t3 R(t3t2t1t2)
• Consider indices j for which i(j) r this
  defines a pattern
• j1 j2 j3 j4
• r1
• r2
• r3
• Suitable expression is E º Õ3, 4,1(s24(R))

16
From domain relational calculus to algebra 10

• Proof of Thm 3 The induction step
• Three cases to consider in the induction step ...
• Assume form of w(t1, t2, ..., tm) is
• 1. f(u1, u2, ..., up) Ú r(v1, v2, ..., vr)
• 2. ?f(t1, t2, ..., tm)
• 3. (t) (f(t1, t2, ..., tm, t))

17
From domain relational calculus to algebra 11

• Proof of Thm 3 The induction step
• Case 1 Assume form of w(t1, t2, ..., tm) is
• f(u1, u2, ..., up) Ú r(v1, v2, ..., vr)
• Can assume
• (by safety) the variables u1, u2, ..., up, v1,
  v2, ..., vr
• include all the variables t1, t2, ..., tm
• variables in u1, u2, ..., up are distinct
• variables in v1, v2, ..., vr are distinct

18
From domain relational calculus to algebra 12

• Proof of Thm 3 The induction step for operator Ú
  
• Illustrative example shows principle
• w(t1, t2, ..., tm) ? f(u1, u2, ..., up) Ú r(v1,
  v2, ..., vr)
• where, in particular case of m 4, p3, r2
• w(t1, t2, t3, t4) º f(t1, t3, t4) Ú r(t2, t4)
• Let F and G be relational algebra expressions for
• t1t2t3 f(t1, t2, t3) and t1t2 r(t1, t2)
  respectively ....
• Need to write down a relational algebra
  expression for
• dom(y) Ç t1t2t3t4 w(t1, t2, t3, t4) which
  is also
• dom(y) Ç t1t2t3t4 f(t1, t3, t4) Ú r(t2, t4)
  
• ... to do this, must use expression D for dom(y)

19
From domain relational calculus to algebra 13

• Proof of Thm 3 The induction step for operator Ú
  (cont.)
• Need a relational algebra expression for
• dom(y) Ç t1t2t3t4 f(t1, t3, t4) Ú r(t2, t4)
  
• Set of tuples t1t2t3t4 satisfying f(t1, t3, t4)
  is constrained
• so that t1t3t4 is a tuple in the relation defined
  by
• algebraic expression F. If D is the algebraic
  expression
• for dom(y) then FD defines tuples t1t3t4t2
  satisfying
• f(t1, t3, t4) within dom(y). Hence Õ1, 4, 2,
  3(FD) defines
• tuples t1t2t3t4 satisfying f(t1, t3, t4) within
  dom(y).

20
From domain relational calculus to algebra 14

• Proof of Thm 3 The induction step for operator Ú
  (cont.)
• Need a relational algebra expression for
• dom(y) Ç t1t2t3t4 f(t1, t3, t4) Ú r(t2,
  t4)
• Õ1, 4, 2, 3(FD) defines tuples t1t2t3t4
  satisfying f(t1, t3, t4) in dom(y).
• Similarly G D D defines tuples t2t4t1t3
  satisfying
• r(t2, t4) within dom(y) and Õ3, 1, 4, 2(G D
  D) defines
• tuples t1t2t3t4 satisfying r(t2, t4) within
  dom(y).
• Hence can take E º Õ1, 4, 2, 3(FD)ÈÕ3, 1, 4,
  2(GDD)

21
From domain relational calculus to algebra 15

• Case 2 w(t1, t2, ..., tm) is ?f(t1, t2, ..., tm)
  
• If F is an algebraic expression for
• dom(y) Ç t1t2 ... tm f(t1, t2, ..., tm)
• and D is an algebraic expression for dom(y) then
• D D ... D - F
• m times
• represents the relation
• dom(y) - t1t2 ... tm f(t1, t2, ..., tm)
• dom(y) - (dom(y) - t1t2 ... tm ?f(t1, t2,
  ..., tm) )
• dom(y) Ç t1t2 ... tm ?f(t1, t2, ..., tm)

22
From domain relational calculus to algebra 16

• Case 3
• w(t1, t2, ..., tm) is (?t) (f(t1, t2, ..., tm,
  t))
• By induction, have an algebraic expression F for
• dom(y) Ç t1t2 ... tmtm1 f(t1, t2, ..., tm,
  tm1)
• Since y is safe
• t satisfies f(t1, t2, ..., tm, t) Þ t Î dom(y)
• Hence Õ1, 2, ..., m(F) represents the required
  relation
• dom(y) Ç t1t2 ... tm (?t) (f(t1, t2, ...,
  tm, t))

23
From domain relational calculus to algebra 17

• Have proved the equivalence of relational algebra
  and domain / tuple relational calculus ...
• Theorems 1, 2 and 3 together prove
• relational algebra
• domain relational calculus
• tuple relational calculus
• all have the same expressive power. Thus
• A query language is complete if and only if it
  has the expressive power of one of these
  formalisms.

24
To follow Mathematical foundations and
features of SQL