Open In App

Introduction of Relational Algebra in DBMS

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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.

  1. Selection(σ)
  2. Projection(π)
  3. Union(U)
  4. Set Difference(-)
  5. Set Intersection(∩)
  6. Rename(ρ)
  7. Cartesian Product(X)

1. Selection(σ): It is used to select required tuples of the relations.

Example:

A        B        C       
1 2 4
2 2 3
3 2 3
4 3 4

For the above relation, σ(c>3)R will select the tuples which have c more than 3.

A        B        C       
1 2 4
4 3 4

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.
B        C       
2 4
2 3
3 4

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
Student_Name
Ram
Mohan

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
Student_Name
Vivek
Geeta

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

ID      Course     
1 DS
2 DBMS

                                                                                                                            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.

  1. Natural Join(⋈)
  2. 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

  1. Tuple Relational Calculus(TRC)
  2. 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/ 



Last Updated : 18 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads