# The relational algebra and calculus - PowerPoint PPT Presentation

1 / 6

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.

The relational algebra and calculus

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

## 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