Open In App
Related Articles

Basic Operators in Relational Algebra

Improve Article
Improve
Save Article
Save
Like Article
Like

Basics of Relational model: Relational Model 

Relational Algebra is a procedural query language that takes relations as an input and returns relations as an output. There are some basic operators which can be applied in relation to producing the required results which we will discuss one by one. We will use STUDENT_SPORTS, EMPLOYEE, and STUDENT relations as given in Table 1, Table 2, and Table 3 respectively to understand the various operators. 

Table 1: STUDENT_SPORTS

ROLL_NOSPORTS
1Badminton
2Cricket
2Badminton
4Badminton

 Table 2: EMPLOYEE

EMP_NONAMEADDRESSPHONEAGE
1RAMDELHI945512345118
5NARESHHISAR978291819222
6SWETARANCHI985261762121
4SURESHDELHI915676897118

Table 3: STUDENT 

ROLL_NONAMEADDRESSPHONEAGE
1RAMDELHI945512345118
2RAMESHGURGAON965243154318
3SUJITROHTAK915625313120
4SURESHDELHI915676897118

Selection operator (σ): Selection operator is used to selecting tuples from a relation based on some condition. Syntax: 

σ (Cond)(Relation Name)

Extract students whose age is greater than 18 from STUDENT relation given in Table 3  

σ (AGE>18)(STUDENT)

[Note: SELECT operator does not show any result, the projection operator must be called before the selection operator to generate or project the result. So, the correct syntax to generate the result is: ∏(σ (AGE>18)(STUDENT))]

RESULT: 

ROLL_NONAMEADDRESSPHONEAGE
3SUJITROHTAK915625313120

Projection Operator (∏): Projection operator is used to project particular columns from a relation. Syntax:  

(Column 1,Column 2….Column n)(Relation Name)

Extract ROLL_NO and NAME from STUDENT relation given in Table 3  

(ROLL_NO,NAME)(STUDENT)

RESULT:  

ROLL_NONAME
1RAM
2RAMESH
3SUJIT
4SURESH

Note: If the resultant relation after projection has duplicate rows, it will be removed. For Example  ∏(ADDRESS)(STUDENT) will remove one duplicate row with the value DELHI and return three rows. 

Cross Product(X): Cross product is used to join two relations. For every row of Relation1, each row of Relation2 is concatenated. If Relation1 has m tuples and and Relation2 has n tuples, cross product of Relation1 and Relation2 will have m X n tuples. Syntax: 

Relation1 X Relation2

To apply Cross Product on STUDENT relation given in Table 1 and STUDENT_SPORTS relation given in Table 2,  

STUDENT X STUDENT_SPORTS

RESULT: 

ROLL_NONAMEADDRESSPHONEAGEROLL_NOSPORTS
1RAMDELHI9455123451181Badminton
1RAMDELHI9455123451182Cricket
1RAMDELHI9455123451182Badminton
1RAMDELHI9455123451184Badminton
2RAMESHGURGAON9652431543181Badminton
2RAMESHGURGAON9652431543182Cricket
2RAMESHGURGAON9652431543182Badminton
2RAMESHGURGAON9652431543184Badminton
3SUJITROHTAK9156253131201Badminton
3SUJITROHTAK9156253131202Cricket
3SUJITROHTAK9156253131202Badminton
3SUJITROHTAK9156253131204Badminton
4SURESHDELHI9156768971181Badminton
4SURESHDELHI9156768971182Cricket
4SURESHDELHI9156768971182Badminton
4SURESHDELHI9156768971184Badminton

Union (U): Union on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relations should have the same number of attributes and corresponding attributes in two relations have the same domain). Union operator when applied on two relations R1 and R2 will give a relation with tuples that are either in R1 or in R2. The tuples which are in both R1 and R2 will appear only once in the result relation. Syntax: 

 Relation1 U Relation2

Find the person who is either student or employees, we can use Union operators like: 

STUDENT U EMPLOYEE

RESULT:  

ROLL_NONAMEADDRESSPHONEAGE
1RAMDELHI945512345118
2RAMESHGURGAON965243154318
3SUJITROHTAK915625313120
4SURESHDELHI915676897118
5NARESHHISAR978291819222
6SWETARANCHI985261762121

Minus (-): Minus on two relations R1 and R2 can only be computed if R1 and R2 are union compatible. Minus operator when applied on two relations as R1-R2 will give a relation with tuples that are in R1 but not in R2. Syntax: 

 Relation1 - Relation2

Find the person who is a student but not an employee, we can use minus operator like:  

STUDENT - EMPLOYEE

RESULT:  

ROLL_NONAMEADDRESSPHONEAGE
2RAMESHGURGAON965243154318
3SUJITROHTAK915625313120

Rename(ρ): Rename operator is used to giving another name to a relation. Syntax:  

ρ(Relation2, Relation1)

To rename STUDENT relation to STUDENT1, we can use rename operator like:  

ρ(STUDENT1, STUDENT)

If you want to create a relation STUDENT_NAMES with ROLL_NO and NAME from STUDENT, it can be done using rename operator as:  

ρ(STUDENT_NAMES, ∏(ROLL_NO, NAME)(STUDENT))

Extended Relational Algebra Operators                                        Overview of Relational Algebra Operators

Previous Year Gate Questions 
https://www.geeksforgeeks.org/gate-gate-cs-2012-question-50/ 
https://www.geeksforgeeks.org/gate-gate-cs-2012-question-43/

Article contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.


Last Updated : 16 Sep, 2022
Like Article
Save Article