Loading in 5 sec....

Lecture 5: Relational calculusPowerPoint Presentation

Lecture 5: Relational calculus

- 245 Views
- Updated 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

Lecture 5: Relational calculus

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

Lecture 5:Relational calculus

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

- 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

- 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

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

- 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’}

- Atomicformulae: a ::=
- <x1,…,xn>R
- xibinop xj, xibinopc, cbinop xj, unopc, unop xi

- DRC Formulae: P, Q ::=
- a
- P, PQ, PQ
- x.P
- x.P

- Recall that x and x are binders for x

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

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’))}

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

BI1BI2 }

Find names of sailors who’ve reserved all boats

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)) }

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

- 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

Recall P ranges

over tuple values

Find names and ages of sailors with a rating above 7

{P | SSailors. S.rating>7 P.sname=S.sname P.age=S.age}

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

{ P | SSailors.

R1Reserves. R2Reserves. S.sid=R1.sid R1.sid=R2.sid

R1.bid R2.bid

P.sname=S.sname}

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

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

- 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

- 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 | (SSailors)}

- This (presumably) means the set of all tuples that are not sailors, which is an infinite set…

- 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

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