Basics of Relational model: Relational Model

Relational Algebra is a procedural query language which takes relations as an input and returns relation as an output. There are some basic operators which can be applied on relations to produce 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_NO |
SPORTS |

1 | Badminton |

2 | Cricket |

2 | Badminton |

4 | Badminton |

** Table 2 : EMPLOYEE**

EMP_NO |
NAME |
ADDRESS |
PHONE |
AGE |

1 | RAM | DELHI | 9455123451 | 18 |

5 | NARESH | HISAR | 9782918192 | 22 |

6 | SWETA | RANCHI | 9852617621 | 21 |

4 | SURESH | DELHI | 9156768971 | 18 |

** ****Table 3 : STUDENT**

ROLL_NO |
NAME |
ADDRESS |
PHONE |
AGE |

1 | RAM | DELHI | 9455123451 | 18 |

2 | RAMESH | GURGAON | 9652431543 | 18 |

3 | SUJIT | ROHTAK | 9156253131 | 20 |

4 | SURESH | DELHI | 9156768971 | 18 |

* Selection operator (σ): *Selection operator is used to select 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 1

σ_{ (AGE>18)}(STUDENT)

**RESULT:**

ROLL_NO |
NAME |
ADDRESS |
PHONE |
AGE |

3 | SUJIT | ROHTAK | 9156253131 | 20 |

* 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_NO |
NAME |

1 | RAM |

2 | RAMESH |

3 | SUJIT |

4 | SURESH |

**Note: **If resultant relation after projection has duplicate rows, it will be removed. For Example: ∏_{(ADDRESS)}(STUDENT) will remove one duplicate row with 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_NO |
NAME |
ADDRESS |
PHONE |
AGE |
ROLL_NO |
SPORTS |

1 | RAM | DELHI | 9455123451 | 18 | 1 | Badminton |

1 | RAM | DELHI | 9455123451 | 18 | 2 | Cricket |

1 | RAM | DELHI | 9455123451 | 18 | 2 | Badminton |

1 | RAM | DELHI | 9455123451 | 18 | 4 | Badminton |

2 | RAMESH | GURGAON | 9652431543 | 18 | 1 | Badminton |

2 | RAMESH | GURGAON | 9652431543 | 18 | 2 | Cricket |

2 | RAMESH | GURGAON | 9652431543 | 18 | 2 | Badminton |

2 | RAMESH | GURGAON | 9652431543 | 18 | 4 | Badminton |

3 | SUJIT | ROHTAK | 9156253131 | 20 | 1 | Badminton |

3 | SUJIT | ROHTAK | 9156253131 | 20 | 2 | Cricket |

3 | SUJIT | ROHTAK | 9156253131 | 20 | 2 | Badminton |

3 | SUJIT | ROHTAK | 9156253131 | 20 | 4 | Badminton |

4 | SURESH | DELHI | 9156768971 | 18 | 1 | Badminton |

4 | SURESH | DELHI | 9156768971 | 18 | 2 | Cricket |

4 | SURESH | DELHI | 9156768971 | 18 | 2 | Badminton |

4 | SURESH | DELHI | 9156768971 | 18 | 4 | Badminton |

* Union (U): *Union on two relations R1 and R2 can only be computed if R1 and R2 are

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

Relation1 U Relation2

Find person who are either student or employee, we can use Union operator like:

STUDENT U EMPLOYEE

**RESULT:**

ROLL_NO |
NAME |
ADDRESS |
PHONE |
AGE |

1 | RAM | DELHI | 9455123451 | 18 |

2 | RAMESH | GURGAON | 9652431543 | 18 |

3 | SUJIT | ROHTAK | 9156253131 | 20 |

4 | SURESH | DELHI | 9156768971 | 18 |

5 | NARESH | HISAR | 9782918192 | 22 |

6 | SWETA | RANCHI | 9852617621 | 21 |

* 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 which are in R1 but not in R2. Syntax:

Relation1 - Relation2

Find person who are student but not employee, we can use minus operator like:

STUDENT - EMPLOYEE

**RESULT:**

ROLL_NO |
NAME |
ADDRESS |
PHONE |
AGE |

2 | RAMESH | GURGAON | 9652431543 | 18 |

3 | SUJIT | ROHTAK | 9156253131 | 20 |

* Rename(ρ): *Rename operator is used to give 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

http://quiz.geeksforgeeks.org/gate-gate-cs-2012-question-50/

http://quiz.geeksforgeeks.org/gate-gate-cs-2012-question-43/

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

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the **DSA Self Paced Course** at a student-friendly price and become industry ready.

## Recommended Posts:

- Extended Operators in Relational Algebra
- Difference between Relational Algebra and Relational Calculus
- Introduction of Relational Algebra in DBMS
- PROJECT Operation in Relational Algebra
- Set Theory Operations in Relational Algebra
- SELECT Operation in Relational Algebra
- Query Optimization in Relational Algebra
- RENAME (ρ) Operation in Relational Algebra
- How to solve Relational Algebra problems for GATE
- Cartesian Product Operation in Relational Algebra
- Difference between Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC)
- Relational Query Evaluation | Set 1
- Properties of Relational Decomposition
- Anomalies in Relational Model
- Relational Model in DBMS
- Relational Query Evaluation | Set 2
- Constraints on Relational database model
- Tuple Relational Calculus (TRC) in DBMS
- Difference between Relational database and NoSQL
- Difference between CouchDB and Relational Database