×

Close

Type:
**Note**Institute:
**
Dr. A.P.J. Abdul Kalam Technical University
**Course:
**
B.Tech
**Specialization:
**Computer Science Engineering**Views:
**35**Uploaded:
**10 months ago**

Touch here to read

Page-1

Topic:

Relational Algebra
What? Why?
•
•
•
•
•
•
Similar to normal algebra (as in 2+3*x-y), except we use relations as values
instead of numbers, and the operations and operators are different.
Not used as a query language in actual DBMSs. (SQL instead.)
The inner, lower-level operations of a relational DBMS are, or are similar to,
relational algebra operations. We need to know about relational algebra to
understand query execution and optimization in a relational DBMS.
Some advanced SQL queries requires explicit relational algebra operations,
most commonly outer join.
Relations are seen as sets of tuples, which means that no duplicates are allowed.
SQL behaves differently in some cases. Remember the SQL keyword distinct.
SQL is declarative, which means that you tell the DBMS what you want, but
not how it is to be calculated. A C++ or Java program is procedural, which
means that you have to state, step by step, exactly how the result should be
calculated. Relational algebra is (more) procedural than SQL. (Actually,
relational algebra is mathematical expressions.)
Set operations
Relations in relational algebra are seen as sets of tuples, so we can use basic set
operations.
Review of concepts and operations from set theory
•
•
•
•
•
•
•
•
•
•
•
set
element
no duplicate elements (but: multiset = bag)
no order among the elements (but: ordered set)
subset
proper subset (with fewer elements)
superset
union
intersection
set difference
cartesian product

Projection
Example: The table E (for EMPLOYEE)
nr name salary
1 John 100
5 Sarah 300
7 Tom 100
SQL
Result
Relational algebra
salary
select salary
from E
100
PROJECTsalary(E)
300
nr salary
select nr, salary
from E
1 100
5 300
PROJECTnr, salary(E)
7 100
Note that there are no duplicate rows in the result.
Selection
The same table E (for EMPLOYEE) as above.
SQL
Result
Relational algebra
select *
from E
where salary < 200
select *
from E
where salary < 200
and nr >= 7
nr name salary
1 John 100
SELECTsalary < 200(E)
7 Tom 100
nr name salary
7 Tom 100
SELECTsalary < 200 and nr >= 7(E)
Note that the select operation in relational algebra has nothing to do with the SQL
keyword select. Selection in relational algebra returns those tuples in a relation that
fulfil a condition, while the SQL keyword select means "here comes an SQL
statement".

Relational algebra expressions
SQL
Result
select name, salary
from E
where salary < 200
Relational algebra
PROJECTname, salary (SELECTsalary < 200(E))
name salary or, step by step, using an intermediate result
John 100
Tom 100
Temp <- SELECTsalary < 200(E)
Result <- PROJECTname, salary(Temp)
Notation
The operations have their own symbols. The symbols are hard to write in HTML that
works with all browsers, so I'm writing PROJECT etc here. The real symbols:
Operation
My HTML
Projection
Operation
My HTML
PROJECT
Cartesian
product
X
Selection
SELECT
Join
JOIN
Renaming
RENAME
Left outer join
LEFT OUTER
JOIN
Union
UNION
Right outer join
RIGHT OUTER
JOIN
Full outer join
FULL OUTER
JOIN
Semijoin
SEMIJOIN
Intersection INTERSECTION
Assignment
<-
Symbol
Example: The relational algebra expression which I would here write as
PROJECTNamn ( SELECTMedlemsnummer < 3 ( Medlem ) )
should actually be written
Symbol

Cartesian product
The cartesian product of two tables combines each row in one table with each row in
the other table.
Example: The table E (for EMPLOYEE)
enr
1
2
3
ename
Bill
Sarah
John
dept
A
C
A
Example: The table D (for DEPARTMENT)
dnr
A
B
C
dname
Marketing
Sales
Legal
SQL
Result
Relational algebra
enr ename dept dnr
select *
from E, D
•
•
•
dname
1
Bill
A
A
Marketing
1
Bill
A
B
Sales
1
Bill
A
C
Legal
2
Sarah C
A
Marketing
2
Sarah C
B
Sales
2
Sarah C
C
Legal
3
John
A
A
Marketing
3
John
A
B
Sales
3
John
A
C
Legal
Seldom useful in practice.
Usually an error.
Can give a huge result.
EXD

## Leave your Comments