# Relational Algebra in DBMS

In this article, we will discuss **relational algebra and its number of operations in Database Management System (DBMS)**.

Submitted by Bharti Parmar, on October 29, 2018

In DBMS **relation algebra define mathematical operations to work on relation or table**. Basically, we use all the **relational algebra operations to perform different operations between two different table to solve different types of problems and to find the best result**. So, now today here we are going to discuss these **all basic operations of relational algebra**.

**There are nine operations in relational algebra operations:**

### Union, Intersection, Minus

All these operations are known to **set operation**. These operations work as same as they work in mathematics to solve the problems. These are binary operation; for this operation, the relation must be union-compatible means their table structure must be the same. They must have the same number of the field with same name, data type, size, domain. In union operation, it retrieves all possible rows and columns data present in two or more tables. But, the rows and columns data cannot be the same means if any rows and columns value are same then it can be written at the single time, not multiple time.

**The intersection** is also a set operation; it is similar to the mathematics intersection operation. This operation is used to retrieve different data between two or more table.

**Minus operation** is also similar to the mathematics difference set operation. In this, we find the difference between the two table.

Here, is **some example of union, intersection and minus operations** for knowing how this operation works and how we use this operation to retrieve the data from the multiple tables. Suppose we have these two following table:

**Table 1: (Name "A1")**

S. No. S. Name Course 1 X C# 2 Y Java 3 Z Python

**Table 2: (Name "A2")**

S. No. S. Name Course 1 X C# 2 Y Java 3 A C 4 Z Python

**Union ( A1 U A2) :**

S. No. S. Name Course 1 X C# 2 Y Java 3 Z Python 4 A C

**Intersection ( A1 ∩ A2) :**

S. No. S. Name Course 1 A C

**Minus ( A2 – A1) :**

S. No. S. Name Course 4 A C

### Selection and projection

These are **unary operations**. They work on the single relation.

**Selection** is used to retrieve tuples/row from a relation on the basis of the condition. If the condition is true, then tuple (row) will be retrieved while if the condition is false then the row will not return any value. For each row (tuple) condition will be evaluated individually. Symbol for row query in selection is **'σ'**.

**In projection** we will retrieve columns; we just need to specify a column name. Projection is possible as user’s choice means the projection column may differ from table column order. Symbol for column query in projection **'π'**

Here, is **some example is shown below**. let’s see how these operations are used to retrieve the data. We take same **A1 and A2 tables** for this operation:

**Selection ( σ ) : - Command: - σ Course = 'java' (A1)**

S. No. S. Name Course 2 Y Java

**Projection ( π ) : - Command: - π S. Name (A1)**

S. Name X Y Z

**Combination of Selection ( σ ) and Projection ( π ):**

**Command: - πS.Name,σCourse = 'C#' (A1 U A2)**

S. Name Course X C#

### Product, Join, division, rename

Suppose, we have two relation R1 and R2 having tuple T1 and T2 and attribute A1 and A2 respectively; then perform **Cartesian product** (R1 X R2, T1 X T2, A1 X A2) between them is known as a **product**.

**Join** is the most powerful operation is relational algebra. It allows us to retrieve data from multiple tables by establishing a relationship between them. Join is a projection of selection of product of two table.

**Type of joins**

**Inner Join:**The ta join, Equijoin, natural join**Outer join:**Left outer join, right outer join, full outer join, self-join

**The division** is similarly performing as same as division set operation in mathematics between tuple (row) and attribute (column).

**Rename** as the name specify it is used to rename the tuple or name of the relation **(ρ(R (Ā),A1)**.

Here, is **some example is shown below** related to these operations take a look.

**Table (A1) :**

E. No. E. Name Salary Dept. No. 1 A 5000 10 2 B 4000 10

**Table (A2) :**

Dept. No. D. Name Location 10 IT Mumbai 20 Sales Delhi 30 HR Hyderabad

**Product of two table (A1 X A2) :**

E. No. E. Name Salary Dept. No. (A1) Dept. No. (A2) D. Name Location 1 A 5000 10 10 IT Mumbai 1 A 5000 10 20 Sales Delhi 1 A 5000 10 30 HR Hyderabad 2 B 4000 10 10 IT Mumbai 2 B 4000 10 20 Sales Delhi 2 B 4000 10 30 HR Hyderabad

**Join in two table:**

**Display employees working in city 'Mumbai'.**

Now, we create its query using selection and projection.

**ΠE. Name**

**σA1.DeptNo. =A2.DeptNo. ^ location = 'Mumbai'**

If the value of (A1) Dept. No. and (A2) Dept. No. is equal then the E. Name will be.

**A1 X A2**

E. Name A B

**Division:**

**A1 / A2: **

E. No E. Name Salary D. Name Location 1 A 5000 IT Mumbai 1 A 5000 Sales Delhi 1 A 5000 HR Hyderabad 2 B 4000 IT Mumbai 2 B 4000 Sales Delhi 2 B 4000 HR Mumbai

**Rename: -ρ(R (Ā),A1 )**

Operation Min max A1 U A2 T2 V T1 T1 + T2 (A1 - > T1, A2 -> T2) A1 ∩ A2 0 T1 V T2 A1 – A2 0 T1

Product T1 * T2 T1 * T2 σa=10 0 T1 πa(A1) T1 T1

Here, A1 & A2 are attribute (column) and T1 & T2 are tuple (row).

**Conclusion**

In this article, we have **learned all the different operations of relation algebra** which we are used to retrieving the data from the two or more tables. I hope you all understand the concepts behind these operations. In the next article, we will learn more about the DBMS. Stay tuned for the next article. Have a nice day! HappyLearning!

TOP Interview Coding Problems/Challenges

- Run-length encoding (find/print frequency of letters in a string)
- Sort an array of 0's, 1's and 2's in linear time complexity
- Checking Anagrams (check whether two string is anagrams or not)
- Relative sorting algorithm
- Finding subarray with given sum
- Find the level in a binary tree with given sum K
- Check whether a Binary Tree is BST (Binary Search Tree) or not
- 1[0]1 Pattern Count
- Capitalize first and last letter of each word in a line
- Print vertical sum of a binary tree
- Print Boundary Sum of a Binary Tree
- Reverse a single linked list
- Greedy Strategy to solve major algorithm problems
- Job sequencing problem
- Root to leaf Path Sum
- Exit Point in a Matrix
- Find length of loop in a linked list
- Toppers of Class
- Print All Nodes that don't have Sibling
- Transform to Sum Tree
- Shortest Source to Destination Path

Comments and Discussions

**Ad:**
Are you a blogger? Join our Blogging forum.

Learn PCB Designing: PCB DESIGNING TUTORIAL