Relational algebra is a formal system for manipulating relations. Set of operations that can be carried out on a relations are the selection, the projection, the Cartesian product (also called the cross product or cross join), the set union, and the set difference.
Select | σ | selects a subset of tuples from relation |
Project | π | deletes unwanted columns from relation |
Union | ∪ | tuples in relation 1 plus tuples in relation 2 |
Set-difference | − | tuples in relation 1, but not in relation 2 |
Cartesian Product | × | allows to combine two relations |
Selects a subset of tuples from relation
Written as: σP(r)
Now, we will apply select operation on student table.
roll_no |
name |
fees |
dob |
grade |
---|---|---|---|---|
10 | Alex | 7800 | 1998-10-03 | K12 |
11 | Peter | 6700 | 1997-11-15 | K12 |
12 | Alisha | 7800 | 1999-07-03 | K11 |
13 | John | 6900 | 2000-12-13 | K11 |
Retrieve all tuples for students in the K12 grade.
σgrade = “K12” (student)
roll_no | name | fees | dob | grade |
10 | Alex | 7800 | 1998-10-03 | K12 |
11 | Peter | 6700 | 1997-11-15 | K12 |
Retrieve all tuples for students in the K12 grade, with fee under 7000
σgrade=“K12” ∧ fees<7000(student)
roll_no | name | fees | dob | grade |
11 | Peter | 6700 | 1997-11-15 | K12 |
To select vertical subset of a relation
Written as: Πa,b,…(r)
Πroll_no,grade(student)
roll_no | grade |
10 | K12 |
11 | K12 |
12 | K11 |
13 | K11 |
Written as: R1 ∪ R2
Result contains all tuples from R1 and R2
Each tuple is unique, even if it’s in both R1 and R2
R1
A1 | A2 |
a | 1 |
b | 2 |
c | 3 |
R2
A1 | A2 |
b | 2 |
c | 3 |
d | 4 |
R3 = R1 U R2
A1 | A2 |
a | 1 |
b | 2 |
c | 3 |
d | 4 |
Written as: R1 - R2
R1 – R2 returns a relation containing all tuples in R1 but not in R2
R1
A1 | A2 |
a | 1 |
b | 2 |
c | 3 |
R2
A1 | A2 |
b | 2 |
c | 3 |
d | 4 |
R3 = R1 - R2
A1 | A2 |
a | 1 |
It operates on two relations and is denoted by X.
Cartesian product of two relation R1 and R2 is represented by R=R1X R2.
The degree of R is equal to sum of degrees of R1 and R2.
The cardinality of R is product of cardinality of R1 and cardinality of R2
The table R1
Empno | Ename | Dept |
1 | Bill | A |
2 | Sarah | C |
3 | John | A |
The table R2
Dno | Dname |
A | Marketing |
B | Sales |
C | Legal |
R1 X R2
Empno | Ename | Dept | Dno | 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 |