Relational Algebra

What is Relational Algebra?

1.    The relation algebra is a procedure query language.

2.    It consists of set of operation that takes one or two relation as input and produced a new relation as result.

3.    The select, project, and rename operation are called unary operation because they operate on one relation.

4.    Other operations operate on more than one relation and therefore it’s called binary operation.

Six basic operators:

·       Unary Operators

1)   Select    -  σ  (Sigma)

2)   Project   -  ∏ (PI)

3)    Rename -  ρ  (rho)

·       Binary Operators

1)    Union    -  U

2)    Set Difference - –

3)    Cartesian Product - X

Four Additional operators:

1)    Set intersection - ∩

2)    Natural join      -  

                    I.        Left Outer Join    -  

                  II.        Right Outer Join -  

                 III.        Full Outer Join   -

3)    Division - ÷

4)    Assignment - ←

Three Database Modification operators:

1)    Insert Operator  r ← r U E

2)    Delete Operator  r ← r – E

3)    Update Operator r ← ∏ F1,F2….Fn  (r)

Projection

Example:   (Table) EMPLOYEE

nr	name	salary
1	John	100
5	Sarah	300
7	Tom	100

SQL	Result	Relational algebra
select salary from Employee	salary 100 300	∏salary(Employee)
select nr, salary from Employee	nr salary 1 100 5 300 7 100	∏nr, salary(E)

Note that there are no duplicate rows in the result.

Selection

SQL	Result	Relational algebra
select * from Employee where salary < 200	nr name salary 1 John 100 7 Tom 100	σsalary < 200(Employee)
select * from Employee where salary < 200 and nr >= 7	nr name salary 7 Tom 100	σsalary < 200 and nr >= 7(Employee)

Cartesian product

The cartesian product of two tables combines each row in one table with each row in the other table.
Example: The table  (for EMPLOYEE)

enr	ename	dept
1	Parag	A
2	Kapil	C
3	Deven	A

(DEPARTMENT)

dnr	dname
A	Marketing
B	Sales
C	Legal

SQL	Result	Relational algebra
select * from E, D	enr ename dept dnr dname 1 Parag A A Marketing 1 Parag A B Sales 1 Parag A C Legal 2 Kapil C A Marketing 2 Kapil C B Sales 2 Kapil C C Legal 3 Deven A A Marketing 3 Deven A B Sales 3 Deven A C Legal	Employee X Department

• Seldom useful in practice.
• Usually an error.
• Can give a huge result.

The Natural join Operation:

•   The Natural join is a binary operation that allows us to combine certain selections and Cartesian product into one operation.
•   It is denoted by the join symbol ( ).
•   The natural join operation forms a Cartesian product of its two arguments, performs a selection forcing equality on those attribute that appear in both relation, and removes the duplicate attributes.

Example

                                       EMP

Emp No	Emp Name	City	Salary	Deptno
1	Parag	Snagar	17000	10
2	Kapil	Sidhpur	18000	20
3	Deven	Junagadh	20000	10
4	Rahul	Rajkot	22000	30
5	Saurav	Jamnagar	35000	Null

    DEPT

Deptno	Deptname	Location
10	Computer	Ahmedabad
20	Account	Surat
30	Finance	Banglore
40	Research	Bombay
50	Marketing	Rajkot

∏ Emp.Empno,Empname,Salary,Deptname

 

(σEmp.deptno=dept.deptno ^{(EMP                                DEPT)})

Output:

Empno	Empname	Salary	Deptname
1	Parag	17000	Computer
2	Kapil	18000	Account
3	Deven	20000	Computer
4	Rahul	22000	Finance

•   The result of this expression is shown in above table. Notice that we have lost the record about Saurav because he is not working in any department.
•   Similarly, we have lost the record of deptno 40 and 50 from Dept because there is no employee working in deptno 40 and 50.
•   In short, it returns record on base of deptno.