Lecture 5 relational calculus
This presentation is the property of its rightful owner.
Sponsored Links
1 / 21

Lecture 5: Relational calculus PowerPoint PPT Presentation


  • 203 Views
  • Uploaded on
  • Presentation posted in: General

Lecture 5: Relational calculus. www.cl.cam.ac.uk/Teaching/current/Databases/. 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

Download Presentation

Lecture 5: Relational calculus

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lecture 5 relational calculus

Lecture 5:Relational calculus

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


Relational calculus

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


Domain relational calculus

Domain relational calculus

  • Queries have the form {<x1,…,xn>| F(x1,…,xn)}where x1,…,xn are domain variables and F is a formula with free variables {x1,…,xn}

  • Answer: all tuples <v1,…,vn> that make F(v1,…,vn) true


Example

Example

Find all sailors with a rating above 7

{<I,N,R,A> | <I,N,R,A>Sailors  R>7}

  • The condition <I,N,R,A>Sailors ensures that the domain variables are bound to the appropriate fields of the Sailors tuple


Example1

Example

  • Simple projection:{<I,N> | R,A.<I,N,R,A>Sailors}

  • Simple projection and selection:{<I,N> | R,A.<I,N,R,A>Sailors  N=`Julia’}


Drc formulae

DRC formulae

  • Atomicformulae: a ::=

    • <x1,…,xn>R

    • xibinop xj, xibinopc, cbinop xj, unopc, 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


Example2

Example

Find the names of sailors rated >7 who’ve reserved boat 103

{<N> | I,A,R.<I,N,R,A>Sailors 

R>7  SI,BI,D.(<SI,BI,D>Reserves  I=SI  BI=103)}

  • Note the use of  and = to ‘simulate’ join


Example3

Example

Find the names of sailors rated >7 who’ve reserved a red boat

{<N> | I,A,R.<I,N,R,A>Sailors 

R>7  SI,BI,D. (<SI,BI,D>Reserves  SI=I  B,C. (<B,C>Boats  B=BI  C=‘red’))}


Example4

Example

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

{<N> | I,R,A. <I,N,R,A>Sailors  BI1,BI2,D1,D2.<I,BI1,D1>Reserves  <I,BI2,D2>Reserves 

BI1BI2 }


Example5

Example

Find names of sailors who’ve reserved all boats


Example6

Example

Find names of sailors who’ve reserved all boats

{<N> | I,R,A. <I,N,R,A>Sailors  B,C. ((<B,C>Boats)  (<SI,BI,D>Reserves.

I=SI  BI=B)) }

{<N> | I,R,A. <I,N,R,A>Sailors  <B,C>Boats. <SI,BI,D>Reserves.

I=SI  BI=B)) }


Tuple relational calculus

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.rating>7}


Semantics of trc queries

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 P[T/t] is true


Example7

Recall P ranges

over tuple values

Example

Find names and ages of sailors with a rating above 7

{P | SSailors. S.rating>7 P.sname=S.sname P.age=S.age}


Example8

Example

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

{ P | SSailors.

R1Reserves. R2Reserves. S.sid=R1.sid  R1.sid=R2.sid 

R1.bid  R2.bid 

P.sname=S.sname}


Equivalence with relational algebra

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.


Encoding relational algebra

Encoding relational algebra

  • Let’s 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] = {<x1,…,xn> | <x1,…,xn>R}


Encoding selection

Encoding selection

  • Assume

    [e] = {<x1,…,xn> | F }

  • Then

    [sc(e)] = {<x1,…,xn> | F  C’}

    where C’ is obtained from C by replacing each attribute with the corresponding variable


Encoding relational calculus

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… 


Safe queries

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


Summary

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


  • Login