What is Relational Algebra?
Relational Algebra is considered a procedural query language. It has a group of operators that work on relations or tables. It takes relations as an input and also gives relation as an output. It is the basic foundation for Structured Query Languages. It consists of different operators.
Operators in Relational Algebra
- Projection (π)
- Selection (σ)
- Cross Product (X)
- Union (U)
- Set difference (-)
- Rename (ρ)
- Natural Join (⋈)
- Conditional Join
- Intersection (⋂)
Projection (π)
It is used to retrieve data from a column of a table.
Eg.
| students | ||
|---|---|---|
| roll_no | name | stream |
| 7 | Ashish | CSE |
| 8 | Ankit | IT |
| 9 | Ashish | CSE |
In the above relation named students, data of students are stored. There are three attributes roll_no, name, and stream.
πName(students) - The output of this statement is the ‘name’ column from the ‘students’ Table.
| name |
|---|
| Ashish |
| Ankit |
Here, it can be seen that there are two Ashish in the original table but in the output, there is only one Ashish. This is because the projection never shows the duplicate data. All the duplicate rows automatically get removed.
Selection (σ)
It is used to select the required tuple/row from the table.
Eg.
| students | ||
|---|---|---|
| roll_no | name | stream |
| 7 | Ashish | CSE |
| 8 | Ankit | IT |
| 9 | Ashish | CSE |
σroll_no>7(students)
The above statement will select the tuples having roll_no greater than 7. The rows which will be selected are given below.
| roll_no | name | stream |
|---|---|---|
| 8 | Ankit | IT |
| 9 | Ashish | CSE |
Cross Product (X)
Cross Product is done between two relations. It takes two relations as an input and gives one relation the cross product as the output. If there are X attributes in 1st relation and Y in 2nd relation then the final relation will have X+Y attributes. The number of tuples in the Final relation will be X*Y.
| students | ||
|---|---|---|
| roll_no | name | stream |
| 7 | Ashish | CSE |
| 8 | Ankit | IT |
| 9 | Ashish | CSE |
| enrollment | |
|---|---|
| courseid | course |
| 1 | OS |
| 2 | DBMS |
By doing the cross product of the above two tables students and enrollment. The output will be
| roll_no | name | stream | courseid | course |
|---|---|---|---|---|
| 7 | Ashish | CSE | 1 | OS |
| 7 | Ashish | CSE | 2 | DBMS |
| 8 | Ankit | IT | 1 | OS |
| 8 | Ankit | IT | 2 | DBMS |
| 9 | Ashish | CSE | 1 | OS |
| 9 | Ashish | CSE | 2 | DBMS |
Union (U)
It is the same as the general Union operation in the set theory. It eliminates the duplicate tuples and selects the tuples which have appeared at least once in any table. For Union operation, both the tables must have the same set of attributes.
| students | |
|---|---|
| name | course |
| Ashish | OS |
| Ankit | DBMS |
| Prashant | OS |
| record | |
|---|---|
| name | stream |
| Ashish | CSE |
| Karan | IT |
(πname(students) U πname(record))
The output of the above statement will be
| name |
|---|
| Ashish |
| Ankit |
| Prashant |
| Karan |
Set Difference (-)
It is also the same as the set difference in set theory. It is denoted by “ - ”. If there are two tables A and B then A-B means all the tuples which are present in A but not in B. Both the tables must have the same set of attributes.
| students | |
|---|---|
| name | course |
| Ashish | OS |
| Ankit | DBMS |
| Prashant | OS |
| record | |
|---|---|
| name | stream |
| Ashish | CSE |
| Karan | IT |
πname(students) - πname(record)
The output of the above statement will be
| name |
|---|
| Ankit |
| Prashant |
Rename (ρ)
Rename is denoted by “ρ” (rho). It is used to rename the attributes of a relation. ρ(A/B)(R) will rename the attribute ‘B’ of relation R by ‘A’.
Natural Join (⋈)
If there are two relations A and B then Natural join between A and B will show the set of all the tuples in which they have the equal common attribute.
| students | ||
|---|---|---|
| Roll | name | course |
| 1 | Ashish | OS |
| 2 | Ankit | DBMS |
| 3 | Prashant | CN |
| professor | |
|---|---|
| teacher | course |
| Shantanu | DBMS |
| Mukherjee | OS |
students ⋈ professor
The output of the above statement will be
| roll | name | course | teacher |
|---|---|---|---|
| 1 | Ashish | OS | Mukherjee |
| 2 | Ankit | DBMS | Shantanu |
Conditional Join
It is also similar to natural join. The only difference between Natural Join and Conditional Join is in natural join the attributes must be equal by default but in conditional join, we can specify the conditions like greater than, less than, etc.
Intersection (⋂)
If there are two relations A and B then the output of A ⋂ B will be the set of tuples that are common in both A and B.
| students | |
|---|---|
| name | course |
| Ashish | OS |
| Ankit | DBMS |
| Prashant | OS |
| enrollment | ||
|---|---|---|
| roll_no | name | stream |
| 8 | Ankit | IT |
| 9 | Ashish | CSE |
πname(students) ⋂ πname(enrollment)
The output of the above statement will be
| name |
|---|
| Ashish |
| Ankit |
