1 / 21

Lecture 5: Relational calculus

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

wfleming
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 5:Relational 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 {<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

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

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

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

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

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

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

  10. Example Find names of sailors who’ve reserved all boats

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

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

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

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

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

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

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

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

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

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

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

More Related