Open In App
Related Articles

Introduction of Relational Algebra in DBMS

Improve
Improve
Improve
Like Article
Like
Save Article
Save
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       
124
223
323
434

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

A       B       C       
124
434

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       
24
23
34

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    
Ram01
Mohan02
Vivek13
Geeta17

                                                                                                                          GERMAN

Student_Name    Roll_Number    
Vivek13
Geeta17
Shyam21
Rohan25

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      
Ram14M
Sona15F
Kim20M

                                                                                                                             B

ID     Course     
1DS
2DBMS

                                                                                                                            A X B

Name     Age     Sex     ID      Course     
Ram14M1DS
Ram14M2DBMS
Sona15F1DS
Sona15F2DBMS
Kim20M1DS
Kim20M2DBMS

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    
A120IT
B125HR
C110Sales
D111IT

                                                                                                                   DEPT

Dept_Name     Manager     
SalesY
ProductionZ
ITA

Natural join between EMP and DEPT with condition :

EMP.Dept_Name = DEPT.Dept_Name           

                                                                                                   EMP ⋈ DEPT

Name     ID         Dept_Name     Manager     
A120ITA
C110SalesY
D111ITA

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    
1F45
2F55
3F60

                                                                                                                               S

ID     Sex     Marks    
10M20
11M22
12M59

Join between R and S with condition  R.marks >= S.marks

R.ID   R.Sex   R.Marks   S.ID   S.Sex   S.Marks   
1F4510M20
1F4511M22
2F5510M20
2F5511M22
3F6010M20
3F6011M22
3F6012M59

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