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