Pre-Requisite: Relational Model in DBMS
Relational Algebra is a procedural query language. Relational algebra mainly provides a theoretical foundation for relational databases and SQL. The main purpose of using Relational Algebra is to define operators that transform one or more input relations into an output relation. Given that these operators accept relations as input and produce relations as output, they can be combined and used to express potentially complex queries that transform potentially many input relations (whose data are stored in the database) into a single output relation (the query results). As it is pure mathematics, there is no use of English Keywords in Relational Algebra and operators are represented using symbols.
Fundamental Operators
These are the basic/fundamental operators used in Relational Algebra.
- Selection(σ)
- Projection(π)
- Union(U)
- Set Difference(-)
- Set Intersection(∩)
- Rename(ρ)
- Cartesian Product(X)
1. Selection(σ): It is used to select required tuples of the relations.
Example:
For the above relation, σ(c>3)R will select the tuples which have c more than 3.
Note: The selection operator only selects the required tuples but does not display them. For display, the data projection operator is used.
2. Projection(π): It is used to project required column data from a relation.
Example: Consider Table 1. Suppose we want columns B and C from Relation R.
π(B,C)R will show following columns.
Note: By Default, projection removes duplicate data.
3. Union(U): Union operation in relational algebra is the same as union operation in set theory.
Example:
FRENCH
| Student_Name | Roll_Number |
|---|
| Ram | 01 |
| Mohan | 02 |
| Vivek | 13 |
| Geeta | 17 |
GERMAN
| Student_Name | Roll_Number |
|---|
| Vivek | 13 |
| Geeta | 17 |
| Shyam | 21 |
| Rohan | 25 |
Consider the following table of Students having different optional subjects in their course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN
| Student_Name |
|---|
| Ram |
| Mohan |
| Vivek |
| Geeta |
| Shyam |
| Rohan |
Note: The only constraint in the union of two relations is that both relations must have the same set of Attributes.
4. Set Difference(-): Set Difference in relational algebra is the same set difference operation as in set theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is used as follows
π(Student_Name)FRENCH - π(Student_Name)GERMAN
Note: The only constraint in the Set Difference between two relations is that both relations must have the same set of Attributes.
5. Set Intersection(∩): Set Intersection in relational algebra is the same set intersection operation in set theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection is used as follows
π(Student_Name)FRENCH ∩ π(Student_Name)GERMAN
Note: The only constraint in the Set Difference between two relations is that both relations must have the same set of Attributes.
6. Rename(ρ): Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.
7. Cross Product(X): Cross-product between two relations. Let’s say A and B, so the cross product between A X B will result in all the attributes of A followed by each attribute of B. Each record of A will pair with every record of B.
Example:
A
| Name | Age | Sex |
|---|
| Ram | 14 | M |
| Sona | 15 | F |
| Kim | 20 | M |
B
A X B
| Name | Age | Sex | ID | Course |
|---|
| Ram | 14 | M | 1 | DS |
| Ram | 14 | M | 2 | DBMS |
| Sona | 15 | F | 1 | DS |
| Sona | 15 | F | 2 | DBMS |
| Kim | 20 | M | 1 | DS |
| Kim | 20 | M | 2 | DBMS |
Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘ n*m ‘ tuples.
Derived Operators
These are some of the derived operators, which are derived from the fundamental operators.
- Natural Join(⋈)
- Conditional Join
1. Natural Join(⋈): Natural join is a binary operator. Natural join between two or more relations will result in a set of all combinations of tuples where they have an equal common attribute.
Example:
EMP
| Name | ID | Dept_Name |
|---|
| A | 120 | IT |
| B | 125 | HR |
| C | 110 | Sales |
| D | 111 | IT |
DEPT
| Dept_Name | Manager |
|---|
| Sales | Y |
| Production | Z |
| IT | A |
Natural join between EMP and DEPT with condition :
EMP.Dept_Name = DEPT.Dept_Name
EMP ⋈ DEPT
| Name | ID | Dept_Name | Manager |
|---|
| A | 120 | IT | A |
| C | 110 | Sales | Y |
| D | 111 | IT | A |
2. Conditional Join: Conditional join works similarly to natural join. In natural join, by default condition is equal between common attributes while in conditional join we can specify any condition such as greater than, less than, or not equal.
Example:
R
| ID | Sex | Marks |
|---|
| 1 | F | 45 |
| 2 | F | 55 |
| 3 | F | 60 |
S
| ID | Sex | Marks |
|---|
| 10 | M | 20 |
| 11 | M | 22 |
| 12 | M | 59 |
Join between R and S with condition R.marks >= S.marks
| R.ID | R.Sex | R.Marks | S.ID | S.Sex | S.Marks |
|---|
| 1 | F | 45 | 10 | M | 20 |
| 1 | F | 45 | 11 | M | 22 |
| 2 | F | 55 | 10 | M | 20 |
| 2 | F | 55 | 11 | M | 22 |
| 3 | F | 60 | 10 | M | 20 |
| 3 | F | 60 | 11 | M | 22 |
| 3 | F | 60 | 12 | M | 59 |
Relational Calculus
As Relational Algebra is a procedural query language, Relational Calculus is a non-procedural query language. It basically deals with the end results. It always tells me what to do but never tells me how to do it.
There are two types of Relational Calculus
- Tuple Relational Calculus(TRC)
- Domain Relational Calculus(DRC)
In-depth articles:
Basic-operators-in-relational-algebra
Extended Relational Algebra Operators
Following are the Previous Year’s Gate Questions
https://www.geeksforgeeks.org/gate-gate-cs-2012-question-50/
https://www.geeksforgeeks.org/gate-gate-cs-2012-question-43/