The relational algebra and calculus

1 / 6

# The relational algebra and calculus - PowerPoint PPT Presentation

The relational algebra and calculus. … SQL. The big 3:. Selection and projection are unary ops Join is binary Selection is based on a formula and returns a table that contains all tuples from a given table where the formula is valid

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

## PowerPoint Slideshow about 'The relational algebra and calculus' - aminia

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 algebra and calculus

… SQL

The big 3:
• Selection and projection are unary ops
• Join is binary
• Selection is based on a formula and returns a table that contains all tuples from a given table where the formula is valid
• Projection returns a table consisting of a subset of attributes from a given table, with dupes removed
• Join creates tuples with attributes from two given tables, where a specific attribute in one matches a specific attribute in another (often a PK, FK pair)
Algebraic closure
• Any relational algebra operation returns a legal derived table
• The set operators are also part of the algebra
• From a formal perspective, the join operator is not a minimal operator, and is therefore represented as a cross product followed by a selection (where the PK equals the FK)
• Note that joins are symetric
Joins can be generalized
• Complex join conditions
• Non-equi joins
• A “natural” join is based on matching all attributes with equal names in both tables
• “Outer” join creates null-packed tuples when tuples on the left do not match any on the right; there is also a right outer join
The calculus
• It is a tuple calculus, not a domain calculus
• SQL is equivalent
• Select From Where
• The part after the Where is declarative
• A tuple calculus (SQL)
• Notice that the variables are indeed tuples
• Note that set operators often act on tables that are being created in the query
More
• IN operator is “element of”
• EXISTS
• Nesting
• FOR ALL
• FOR SOME
• Putting computations in the SELECT clause
• COUNT, SUM, AVG, MAX, MIN operators