DOC PREVIEW
Berkeley COMPSCI 186 - Relational Calculus

This preview shows page 1-2-3-27-28-29 out of 29 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Relational CalculusAdministriviaReviewReview: Where have we been?Review: Where have we been? Where are we going next?Where are we going next?Review – Why do we need Query Languages anyway?Relational Algebra ReviewSlide 9Slide 10IntermissionToday: Relational CalculusRelational Calculus Building BlocksSlide 14Tuple Relational CalculusTRC FormulasFree and Bound VariablesUse of  (For every)a  b is the same as a  bQuantifier ShortcutsSelection and ProjectionJoinsJoins (continued)DivisionMore Calculus exercises on the web site…Unsafe Queries, Expressive PowerRelational Completeness means…Now we can study SQL!SummaryRelational CalculusCS 186, Spring 2007, Lecture 6R&G, Chapter 4Mary RothWe will occasionally use thisarrow notation unless there is danger of no confusion.Ronald Graham Elements of Ramsey TheoryAdministrivia•Homework 1 due in 1 week –Thursday, Feb 8 10 p.m. •New syllabus on web site•Questions?Review•Database Systems have both theory and practice•It’s a systems course, so we are heavy on the practice•But our practice has to have theory to back it up 8-)•…so we will be looking at both of them in parallelReview: Where have we been?Query Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBPracticeTheoryLecture 2Relational AlgebraRelational ModelLecture 5Lectures 3 &4Review: Where have we been?Where are we going next?Query Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBPracticeTheoryLecture 2Relational AlgebraRelational ModelLecture 5Lectures 3 &4Relational CalculusTodayWhere are we going next?Query Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBPracticeSQLOn Deck:Practical ways of evaluating SQLReview – Why do we need Query Languages anyway?•Two key advantages–Less work for user asking query–More opportunities for optimization•Relational Algebra–Theoretical foundation for SQL–Higher level than programming language•but still must specify steps to get desired result•Relational Calculus–Formal foundation for Query-by-Example–A first-order logic description of desired result–Only specify desired result, not how to get itAdditional operations:•Intersection ()•Join ( ) •Division ( / )Relational Algebra Reviewbid bnamecolor101InterlakeBlue102InterlakeRed103ClipperGreen104MarineRedsidbidday2210110/10/965810311/12/96Reserves Sailors BoatsBasic operations:•Selection ( σ ) •Projection ( π ) •Cross-product (  ) •Set-difference ( — ) •Union (  ) :tuples in both relations.:like  but only keep tuples where common fields are equal.:tuples from relation 1 with matches in relation 2: gives a subset of rows.: deletes unwanted columns.: combine two relations.: tuples in relation 1, but not 2 : tuples in relation 1 and 2.Query Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBPrediction: These relational operators are going to look hauntingly familiar when we get to them…!Additional operations:•Intersection ()•Join ( ) •Division ( / )Relational Algebra Reviewbid bnamecolor101InterlakeBlue102InterlakeRed103ClipperGreen104MarineRedsidbidday2210110/10/965810311/12/96Reserves Sailors BoatsBasic operations:•Selection ( σ ) •Projection ( π ) •Cross-product (  ) •Set-difference ( — ) •Union (  ) Find names of sailors who’ve reserved a green boatσ ( color=‘Green’Boats) ( Sailors)π( sname ) ( Reserves)Relational Algebra Reviewbid bnamecolor101InterlakeBlue102InterlakeRed103ClipperGreen104MarineRedsidbidday2210110/10/965810311/12/96Reserves Sailors BoatsFind names of sailors who’ve reserved a green boatGiven the previous algebra, a query optimizer would replace it with this!σ ( color=‘Green’Boats) ( Sailors)π( sname ) ( Reserves)π( bid )π( sid )Or better yet:Intermission•Some algebra exercises for you to practice with are out on the class web site•Algebra and calculus exercises make for good exam questions!Today: Relational Calculus•High-level, first-order logic description–A formal definition of what you want from the database•e.g. English: “Find all sailors with a rating above 7” In Calculus:{S |S  Sailors  S.rating > 7} “From all that is, find me the set of things that are tuples in the Sailors relation and whose rating field is greater than 7.”•Two flavors: –Tuple relational calculus (TRC) (Like SQL)–Domain relational calculus (DRC) (Like QBE)Relational Calculus Building Blocks•VariablesTRC: Variables are bound to tuples.DRC: Variables are bound to domain elements (= column values)•Constants7, “Foo”, 3.14159, etc.•Comparison operators=, <>, <, >, etc.•Logical connectives - not– and- or- implies - is a member of•QuantifiersX(p(X)): For every X, p(X) must be trueX(p(X)): There exists at least one X such that p(X) is trueRelational Calculus•English example: Find all sailors with a rating above 7–Tuple R.C.:{S |S Sailors  S.rating > 7}“From all that is, find me the set of things that are tuples in the Sailors relation and whose rating field is greater than 7.”–Domain R.C.: {<S,N,R,A>| <S,N,R,A> Sailors  R > 7}“From all that is, find me column values S, N, R, and A, where S is an integer, N is a string, R is an integer, A is a floating point number, such that <S, N, R, A> is a tuple in the Sailors relation and R is greater than 7.”sidsnameratingage28yuppy935.031lubber855.544guppy535.058rusty1035.0Tuple Relational Calculus•Query form: {T | p(T)}–T is a tuple and p(T) denotes a formula in which tuple variable T appears. •Answer:–set of all tuples T for which the formula p(T) evaluates to true.•Formula is recursively defined:–Atomic formulas get tuples from relations or compare values–Formulas built from other formulas using logical operators.•An atomic formula is one of the following:R  Rel R.a op S.bR.a op constant, where op is one of•A formula can be:– an atomic formula– where p and q are formulas– where variable R is a tuple variable– where variable R is a tuple variable TRC Formulas  , ,


View Full Document

Berkeley COMPSCI 186 - Relational Calculus

Documents in this Course
Load more
Download Relational Calculus
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Relational Calculus and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Relational Calculus 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?