relational algebra n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Algebra PowerPoint Presentation
Download Presentation
Relational Algebra

Loading in 2 Seconds...

play fullscreen
1 / 28

Relational Algebra - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

Relational Algebra. Lecture 4. Relational Query Languages. Query languages : Allow manipulation and retrieval of data from a database. Relational model supports simple, powerful QLs: Strong formal foundation based on logic. Allows for much optimization.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Algebra' - anana


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
relational query languages
Relational Query Languages
  • Query languages: Allow manipulation and retrieval of data from a database.
  • Relational model supports simple, powerful QLs:
    • Strong formal foundation based on logic.
    • Allows for much optimization.
  • Query Languages != programming languages!
    • QLs not expected to be “Turing complete”.
    • QLs not intended to be used for complex calculations.
    • QLs support easy, efficient access to large data sets.
formal relational query languages
Formal Relational Query Languages

Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation:

  • Relational Algebra: More operational, very useful for representing execution plans.
  • Relational Calculus: Let users describe what they want, rather than how to compute it. (Non-operational, declarative.)
  • Understanding Algebra is key to understanding SQL,
  • and query processing!
algebra preliminaries
Algebra Preliminaries
  • A query is applied to relation instances, and the result of a query is also a relation instance.
    • Schemasof input relations for a query are fixed.
    • The schema for the resultof a given query is also fixed! Determined by definition of query language constructs.
example instances
Example Instances
  • “Sailors” and “Reserves” relations for our examples.

S1

R1

S2

algebra operations
Algebra Operations
  • Look what we want to get from the following table:
projection
Projection
  • Deletes attributes that are not in projection list.
  • Schema of result contains exactly the fields in the projection list, with the same names that they had in the (only one) input relation.
  • Projection operator has to eliminate duplicates ! (Why??)
    • Note: real systems typically don’t do duplicate elimination unless the user explicitly asks for it. (Why not?)
selection
Selection

S2

  • Selects rows that satisfy selection condition.
  • No duplicates in result! (Why?)
  • Schema of result identical to schema of (only one) input relation.
composition of operations
Composition of Operations
  • Result relation can be the input for another relational algebra operation ! (Operatorcomposition.)

S2

what do we want to get from two relations
What do we want to get from two relations?

S1

R1

What about: Who reserved boat 101?

Or: Find the name of the sailor who reserved boat 101.

cross product
Cross-Product
  • Each row of S1 is paired with each row of R1.
  • Result schema has one field per field of S1 and R1, with field names inherited.
  • Renaming operator (because naming conflict) :
why does this cross product help
Why does this cross product help

Query: Find the name of the sailor who reserved boat 101.

* Note my use of “temporary” relation CP.

another example
Another example
  • Find the name of the sailor having the highest rating.

What’s in “Result?” ?

Does it answer our query?

union intersection set difference
Union, Intersection, Set-Difference
  • All of these operations take two input relations, which must be union-compatible:
    • Same number of fields.
    • `Corresponding’ fields have the same type.
  • What is the schema of result?
back to our query
Back to our query
  • Find the name of the sailor having the highest rating.

* Why not project on sname only for Tmp?

relational algebra summary
Relational Algebra (Summary)
  • Basic operations:
    • Selection (  ) Selects a subset of rows from relation.
    • Projection (  ) Deletes unwanted columns from relation.
    • Cross-product(  ) Allows us to combine two relations.
    • Set-difference ( - ) Tuples in reln. 1, but not in reln. 2.
    • Union(  ) Tuples either in reln. 1 or in reln. 2 or in both.
    • Rename(  ) Changes names of the attributes
  • Since each operation returns a relation, operationscan be composed ! (Algebra is “closed”.)
  • Use of temporary relations recommended.
natural join
Natural Join
  • Natural-Join:
  • Result schema similar to cross-product, but only one copy of each field which appears in both relations.
  • Natural Join = Cross Product + Selection on common fields + drop duplicate fields.
query revisited using natural join
Query revisited using natural join

Query: Find the name of the sailor who reserved boat 101.

consider yet another query
Consider yet another query
  • Find the sailor(s) (sid) who reserved all the red boats.

R1

B

start an attempt
Start an attempt
  • who reserved what boat:
  • All the red boats:
division
Division
  • Not supported as a primitive operator, but useful for expressing queries like: Find sailors who have reserved allred boats.
  • Let S1 have 2 fields, x and y; S2 have only field y:
    • S1/S2 =
    • i.e., S1/S2 contains all x tuples (sailors) such that for everyy tuple (redboat) in S2, there is an xy tuple in S1 (i.e, x reserved y).
  • In general, x and y can be any lists of fields; y is the list of fields in S2, and xy is the list of fields of S1.
examples of division a b
Examples of Division A/B

B1

B2

B3

A/B1

A/B2

A/B3

A

find names of sailors who ve reserved a red boat

A more efficient solution:

Find names of sailors who’ve reserved a red boat
  • Information about boat color only available in Boats; so need an extra join:
  • A query optimizer can find this given the first solution!
find sailors who ve reserved a red or a green boat

What happens if is replaced by in this query?

Find sailors who’ve reserved a red or a green boat
  • Can identify all red or green boats, then find sailors who’ve reserved one of these boats:
  • Can also define Tempboats using union! (How?)
find sailors who ve reserved a red and a green boat
Find sailors who’ve reserved a red and a green boat
  • Previous approach won’t work! Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (note that sid is a key for Sailors):
find the names of sailors who ve reserved all boats
Find the names of sailors who’ve reserved all boats
  • Uses division; schemas of the input relations must be carefully chosen:
  • To find sailors who’ve reserved all ‘Interlake’ boats:

.....

summary
Summary
  • The relational model has rigorously defined query languages that are simple and powerful.
  • Relational algebra is more operational; useful as internal representation for query evaluation plans.
  • Several ways of expressing a given query; a query optimizer should choose the most efficient version.