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
- 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!