Home » DBMS

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

1. Inner Join: The ta join, Equijoin, natural join
2. 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!