The Relational Language
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

The Relational Language PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on
  • Presentation posted in: General

The Relational Language. Todd S. Bacastow January 2004. Relational Query Languages. Query languages Allow manipulation and retrieval of data from a database. Query Languages QLs not intended to be used for complex calculations. QLs support easy, efficient access to large data sets.

Download Presentation

The Relational Language

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


The relational language

The Relational Language

Todd S. Bacastow

January 2004


Relational query languages

Relational Query Languages

  • Query languages

    • Allow manipulation and retrieval of datafrom a database.

  • Query Languages

    • QLs not intended to be used for complex calculations.

    • QLs support easy, efficient access to large data sets.

  • Two types of languages

    • Formal (mathematical) query language (ch 4)

      • Relational Algebra

      • Relational Calculas

    • Real (or operational) language --- SQL (ch 5)


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:Lets users describe what they want, rather than how to compute it.


Relational calculus

Relational Calculus

  • Comes in two flavours:

    • Tuple relational calculus (TRC)

    • Domain relational calculus(DRC).

  • Calculus has variables, constants, comparison ops, logical connectives and quantifiers.

    • TRC:Variables range over (i.e., get bound to) tuples.

    • DRC:Variables range over domain elements (= field values).

  • Expressions in the calculus are called formulas.

    • An answer tuple is essentially an assignment of constants to variables that make the formula evaluate to true.


Relational algebra

Relational Algebra

  • Basic operations:

    • Selection ( ) Selects a subset of rows from relation.

    • Projection ( ) Deletes unwanted columns from relation.

    • Cross-product (Cartesian product)( ) Allows us to combine two relations and returns all possible combinations.

    • Union(U) All tuples in reln. 1 and in reln. 2.

    • Intersection ( ) Only tuples common the reln. 1 and reln 2.

    • Set-difference ( ) Tuples in relation 1, but not in reln. 2.

    • Join ( ) Cross-product and a selection.

  • Additional operations:

    • Intersection, division: Not essential, but useful.

  • Since each operation returns a relation, operations can be composed.

U


Relational algebra operations

Relational Algebra Operations

Columns!!

X

Rows!!

Glue together allpossible combinations

Only tuples in both reln 1 and reln 2

Everything in reln 1 take away everything in reln 2

Everything in reln 1 plus reln 2


Consider the following database schema

Consider the following database schema

  • The schema:

    Store (SName, City)

    PriceList (UPC, SName, Price)

    Item (UPC, IName, Warranty)


The tables

The tables


Example

Example

  • Write a relational algebra query to find the namesof all stores that sell items with warranty equal to 24 months.

  • Join PriceList and Item

  • Select those with a warranty equal to 24 months

  • Return the store name


Join pricelist and item

Join PriceList and Item

=


Select all tuples where the warranty 24 months

Select all tuples where the warranty = 24 months


Select the column with the store names

Select the column with the store names


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.

  • SQL is an implementation of relational algebra.

  • There may be several ways of expressing a given query.


  • Login