Relational Algebra Operations

Below are fundamental operations that are "complete".  That is, this set of operations alone can define any retrieval.

• Select
• Project
• Rename
• Union
• Set Difference
• Cartesian Product

Convenient, natural additions to the set of operations makes

• Set Intersection
• Natural Join
• Division
• Assignment

Select Operation

σ_condition(relation)

Project Operation

• Produce a subset of attributes from a relation
• Unselected columns are eliminated
• Duplicate rows are eliminated
• Result is a relation

Notation is lower case pi

π_{attribute- list}(relation)

• Both r and s have same number of columns
• Domains of attributes match or are compatible in sequence in both r and s
• The names may not necessarily match up; helpful if they do, however.

Union compatible relations can be combined using union, intersection ∩, and set difference.

Cartesian Product

If R and S are two relations, R × S is the set of all concatenated tuples , where x is a tuple in R and y is a tuple in S

• (R and S need not be union compatible)

R × S is expensive to compute:

• The sum of the number of attributes of R and S is the number of attributes of the Cartesian product
• The product of the sizes of R and S in the number of rows

Renaming

Result of an expression evaluation is a relation, called the result set.

Attributes of relation must have distinct names. This is not guaranteed with Cartesian product. Suppose in the previous example attributes a and c have the same name. 

Renaming operator tidies this up. To assign the names A₁, A₂,… A_n to the attributes of the n column relation produced by

expression expr, use the form expr [A₁, A₂, … A_n]

Join

This is a derived operation, i.e., it is based on the basic operations of the relational algebra.  It is a convenience operation because it is done so much.

A (general or theta θ) join of R and S is the expression 
R  _{join-condition} S

where join-condition is a conjunction of terms A_i oper B_i in which A_i is an attribute of R and B_i is an attribute of S and oper is one of =, <, >, ≤, ≥, ≠

The meaning is essentially:

σ_{join.-condition}(R X S)

where join-condition in both case are the same, except for possible renamings of attributes.

Join and Renaming

Problem: R and S might have attributes with the same name – in which case the Cartesian product is not defined

Solution:

• Rename attributes prior to forming the product and use new names in join-condition?.
• Common attribute names are qualified with relation names in the result of the join