|
The Relational Algebra |
| Theoretical Knowledge |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Practical Knowledge |
|
Select Operation- selects all tuples that satisfy the selection condition from a relation
s <selection condition>(r)
<selection condition> ?
| a | b | c |
| a | b | c |
| d | e | f |
| c | b | a |
| a | b | c |
| a | b | c |
| c | b | a |
Project Operation - produce a new relation with only some of the attributes of r and remove duplicate tuples
p <selection condition>(r)
r
| a | b | c |
| a | b | c |
| d | e | f |
| c | b | a |
| a | c |
| a | c |
| d | f |
| c | a |
r(<attribute list>) ¬ operation
r
| a | b | c |
| a | b | c |
| d | e | f |
| c | b | a |
r1(d) ¬ pc(r)
| d |
| c |
| f |
| a |
Union - Produces a relation that includes all the tuples in r or s or both r and s. r and s must be union compatible - r ? s
Properties:
1. r ?s = s ? r
2. r ? (s ?
t) = (r ? s) ?
t
| r | s | |||||||||||||||||||||
|
|
| a | b | c |
| a | b | c |
| d | e | f |
| c | b | a |
| b | g | a |
Properties:
1. r ? s = s ? r
2. r ? (s ? t) = (r ? s) ? t
| r | s | |||||||||||||||||||||
|
|
| a | b | c |
| c | b | a |
Property:
1. r - s ? s - r
| r | s | |||||||||||||||||||||
|
|
| a | b | c |
| a | b | c |
| d | e | f |
s - r
| a | b | c |
| b | g | a |
| r | s | |||||||||||||||||||||
|
|
| a | b | c | d | e | f |
| a | b | c | b | g | a |
| a | b | c | c | b | a |
| d | e | f | b | g | a |
| d | e | f | c | b | a |
| c | b | a | b | g | a |
| c | b | a | c | b | a |
<join condition>s
<join condition> ?
2. <operations> ? {=, <, ? , >, ? , ? }
3. <logical operations> ? {and}
Properties:
1. r s = s r
2. r (s t) = (r s) t
| r | s | ||||||||||||||||||
|
|
| a | b | c | d | e |
| 1 | 2 | 3 | 3 | 1 |
| 1 | 2 | 3 | 6 | 2 |
| 4 | 5 | 6 | 6 | 2 |
<join condition> ?
2. <operations> ? {=}
3. <logical operations> ? {and}
| r | s | |||||||||||||||
|
|
| a | b | c | e |
| 1 | 2 | 3 | 7 |
{s , p , ? , ? , x} - complete set
r ? s = (r ? s) - ((r - s) ? (s-r))
r <condition>s = s <condition>(rxs)
Division - Produce a relation t(x) that includes all tuples t{x} in r(z) that appear in r in combination with every tuple from s(y), where z = x ? y.
t = r + s;
t1¬ p y(r);
t2¬ p y((sxt1) - r);
t ¬ t1 - t2
r
| a | b |
| a1 | b1 |
| a2 | b1 |
| a3 | b1 |
| a4 | b1 |
| a1 | b2 |
| a3 | b2 |
| a2 | b3 |
| a3 | b3 |
| a4 | b3 |
| a1 | b4 |
| a2 | b4 |
| a3 | b4 |
| s | t | |||||||
|
|
function operation = <grouping attributes> f <function list> (relation name)
function list = sum | average | maximum |
minimum | count
f average_a (r)
r
| a | b | c |
| 5 | 2 | 1 |
| 6 | 1 | 3 |
| 2 | 4 | 7 |
| 3 | 6 | 0 |
| average_a |
| 4 |
Practical Knowledge
Three Primary Functions:
select
project
join
May be combined in different ways and with different syntax in different implementations.
But must be implemented somehow.
Functions extract selected data.
Extracted data put into temporary storage in new relations .
New relations can be further manipulated and/or stored as permanent relations if required.
a) employee(empno,ename,esalary,dept)
employee
| empno | ename | esalary | dept |
b) department(dept,location)
department
| dept | location |
c) job(jname,budget)
job
| jname | budget |
d) assignment(jname,empno,hours)
assignment
| jname | empno | hours |
Extracts tuples from a relation subject to required conditions on attributes in the relation.
Example
select employee where salary > 13000 [giving r1]
[r1]
| empno | ename | esalary | dept |
| 146 | harvey | 15000 | sales |
| 468 | mendoza | 14000 | planning |
project
Extracts columns from a relation in a named order by attribute.
Example
Project employee over ename, dept
| ename | dept |
| smith | sales |
| jones | planning |
join
Combines relations which have a common attribute. generates a temporary relation containing all attributes from both relations.
Example
Join employee and assignment over empno
| empno | ename | esalary | dept | jname | hours |
| 134 | smith | 12000 | sales | projb | 9.0 |
| 146 | harvey | 15000 | sales | proja | 3.4 |
You can do this by nesting operations:
project (
join employee and assignment over empno )
where dept = 'sales')
| ename | jname | hours |
| smith | projb | 9.0 |
| harvey | proja | 3.4 |
The relation is constructed by working from innermost nesting outwards.
Relational algebra is