1 / 22

Introduction to Relational Calculus: Formulating Queries Effectively

This document explains the principles of relational calculus, a foundational concept in database querying. It outlines how queries are structured using formulas, atomic formulas, free and bound variables, and quantifiers. Through examples, it demonstrates how to construct simple queries to retrieve data, including filtering sailors by their ratings and movie stars associated with Kevin Bacon. The discussion also touches on the expressive power of relational calculus versus relational algebra and concludes with the importance of formal query languages in database management.

abia
Télécharger la présentation

Introduction to Relational Calculus: Formulating Queries Effectively

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. Relational Calculus  Logic, like whiskey, loses its beneficial effect when taken in too large quantities. --Lord Dunsany

  2. Relational Calculus • Query has the form: {T | p(T)} • p(T)is a formula containing T • Answer = tuples T for which p(T)= true.

  3. Formulae • Atomic formulae: T  Relation T.aopT.b T.aopconstant … op is one of • Aformula can be: • an atomic formula

  4. Free and Bound Variables • Quantifiers:  and  • Use of or bindsX. • A variable that is not bound is free. • Recall our definition of a query: • {T | p(T)} • Important restriction: • Tmust be the only free variable in p(T). • all other variables must be bound using a quantifier.

  5. Simple Queries {S |S Sailors S.rating > 7} • Find all sailors with rating above 7 • Find names and ages of sailors with rating above 7. • Note: S is a variable of 2 fields (i.e. S is a projection of Sailors) {S | S1 Sailors(S1.rating > 7 S.sname = S1.sname S.age = S1.age)}

  6. Joins Find sailors rated > 7 who’ve reserved boat #103 { S | SSailors  S.rating > 7  R(RReserves  R.sid = S.sid  R.bid = 103) }

  7. Joins (continued) Find sailors rated > 7 who’ve reserved a red boat • This may look cumbersome, but it’s not so different from SQL! { S | SSailors  S.rating > 7  R(RReserves  R.sid = S.sid  B(BBoats  B.bid = R.bid  B.color = ‘red’)) }

  8. Universal Quantification Find sailors who’ve reserved all boats { S | SSailors  BBoats (RReserves (S.sid = R.sid B.bid = R.bid)) }

  9. A trickier example… Find sailors who’ve reserved all Red boats in existence… { S | SSailors  B  Boats ( B.color = ‘red’  R(RReserves  S.sid = R.sid B.bid = R.bid)) } Alternatively… { S | SSailors  B  Boats ( B.color  ‘red’  R(RReserves  S.sid = R.sid B.bid = R.bid)) }

  10. a  b is the same as a  b b T F T F T a T T F

  11. A Remark: Unsafe Queries • syntactically correct calculus queries that have an infinite number of answers! Unsafe queries. • e.g., • Solution???? Don’t do that!

  12. Your turn … • Schema: Movie(title, year, studioName) ActsIn(movieTitle, starName) Star(name, gender, birthdate, salary) • Queries to write in Relational Calculus: • Find all movies by Paramount studio • … movies whose stars are all women • … movies starring Kevin Bacon • Find stars who have been in a film w/Kevin Bacon • Stars within six degrees of Kevin Bacon* • Stars connected to K. Bacon via any number of films** * Try two degrees for starters ** Good luck with this one!

  13. Answers … • Find all movies by Paramount studio {M | MMovie  M.studioName = ‘Paramount’}

  14. Answers … • Movies whose stars are all women {M | MMovie  AActsIn((A.movieTitle = M.title)  SStar(S.name = A.starName  S.gender = ‘F’))}

  15. Answers … • Movies starring Kevin Bacon {M | MMovie  AActsIn(A.movieTitle = M.title  A.starName = ‘Bacon’))}

  16. S: A: movie name movie star … star ‘Bacon’ A2: Answers … {S | SStar  AActsIn(A.starName = S.name  A2ActsIn(A2.movieTitle = A.movieTitle  A2.starName = ‘Bacon’))} • Stars who have been in a film w/Kevin Bacon

  17. two Answers … • Stars within six degrees of Kevin Bacon {S | SStar  AActsIn(A.starName = S.name  A2ActsIn(A2.movieTitle = A.movieTitle  A3ActsIn(A3.starName = A2.starName  A4ActsIn(A4.movieTitle = A3.movieTitle  A4.starName = ‘Bacon’))}

  18. A: A2: movie movie name movie movie star star star … star ‘Bacon’ Two degrees: S: A3: A4:

  19. Answers … • Stars connected to K. Bacon via any number of films • Sorry … that was a trick question • Not expressible in relational calculus!! • What about in relational algebra? • We will be able to answer this question shortly …

  20. Expressive Power • Expressive Power (Theorem due to Codd): • Every query that can be expressed in relational algebra can be expressed as a safe query in relational calculus; the converse is also true. • Relational Completeness: Query language (e.g., SQL) can express every query that is expressible in relational algebra/calculus. (actually, SQL is more powerful, as we will see…)

  21. Question: • Can we express query #6 in relational algebra? • A: If we could, then by Codd’s theorem we could also express it in relational calculus. However, we know the latter is not possible, so the answer isno.

  22. Summary • Formal query languages — simple and powerful. • Relational algebra is operational • used as internal representation for query evaluation plans. • Relational calculus is “declarative” • query = “what you want”, not “how to compute it” • Same expressive power --> relational completeness. • Several ways of expressing a given query • a queryoptimizer should choose the most efficient version.

More Related