Relational Algebra Handout

project p

formal definition:

p A(r)={t[A]|te r}

description:

The project operation is a unary operation that returns its argument relation with certain attributes left out.

Relational Algebra example:

p branch-name (loan)

SQL example:

select branch-name

from loan

select s

formal definition:

s F(r)={t|te r and t satisfies F}

description:

The select operation is a unary operation that returns its argument relation with certain tuples left out.

Relational Algebra example:

s branch-name ="Perryville"(loan)

SQL example:

select *

from loan

where branch-name= "Perryville"

natural join | ´ |

formal definition:

r| ´ | s={t|(\$ ue r)(\$ ve s)(t[R]=u and t[S]=v)}

description:

The natural join is a binary operation that allows us to combine certain selections and a Cartesian product into one operation.

Alternate expression:

r| ´ | s=p rÇ s(s F(r´ s))

where F is a selection condition that indicates the tuple values under the common attributes of r and s are equal.

Relational Algebra example:

p customer-name, loan-numbr (borrower| ´ | loan)

SQL example:

select distinct customer-name,borrower.loan-number

from borrower,loan

where borrower.number = loan.loan-number

rename r

formal definition:

r s(r)

description:

The rename operation is a unary operation that returns its argument with a different name. Normally the results of a relational-algebra expression do not have a name that we can use to refer to them.

Relational Algebra example:

p account.balance (s account.balance > d.balance(account´ r d(account)))

SQL example:

select distinct account.balance

from account, account as d

where account.balance > d.balance

intersection Ç

formal definition:

rÇ s={t|te r and te s}

description:

The intersection operation is a binary operation that returns a relation that contains all tuples that are contained in both argument relations.

Alternate expression:

rÇ s=r-(r-s)

Relational Algebra example:

loanÇ borrower

SQL example:

(select distinct customer-name

from depositor)

intersect

(select distinct customer-name

from borrower)

union È

formal definition:

rÈ s={t|te r or te s}

description:

The union operation is a binary operation that returns a relation that contains all tuples that are contained in each of the argument relations.

Relational Algebra example:

loanÇ borrower

SQL example:

(select distinct *

from depositor)

intersect

(select distinct *

from borrower)

difference -

formal definition:

r-s={t|te r and not(te s)}

description:

The difference operation is a binary operation that returns a relation that contains all those tuples in the first relation that are not present in the second relation.

Relational Algebra example:

loan - borrower

SQL example:

(select *

from depositor)

except all

(select *

from borrower)

Cartesian product ´

formal definition:

r´ s={t1.t2|te r and te s}

where t1.t2 is the

concatenation of tuples

t1 and t2 to form a

large tuple

description:

The Cartesian product is a binary operator that takes as input two relations and produces a elation on the schema that is the concatenation of the relation schemes of the inputs. The tuples in the Cartesian product are constructed by concatenating each tuple in the first input relation with each tuple in the second input relation.

Relational Algebra example:

borrower x loan

SQL example:

select *

from branch, loan

division ¸

formal definition:

r¸ s={t|(" ue s)(tue r)}

description:

The division operator takes as input two relations, called the dividend relation (r on schema R) and the divisor relation (s on schema S) such that all the attributes in S also appear in R and s is not empty. The output of the division operation is a relation on schema R with all the attributes common with S discarded. A tuple in t is put in the output of the operation if for all tuples us in s, the tuple tu is in r, where tu is a tuple constructed from tu and u by combining the individual values in these tuples in the proper order to from a tuple in r.

Alternate expression:

r¸ s= p R-S(r)-p R-S((p R-S(r)´ s)-r)

Relational Algebra example:

branch ¸ loan

SQL example:

No SQL equivalent

assignment ¬

formal definition:

r¬ s

description:

The assignment operation is a unary operation that returns its argument with a new name. The argument can be the result of a relational-algebra expression.

Relational Algebra example:

new-branch ß branch

SQL example:

No SQL equivalent