Here, we will learn about the DBMS Relational Algebra in detail with its operations. The DBMS relational algebra is a concept used for maintaining the databases. Relational Algebra is a ‘Procedural Query Language’ defined by Edgar Frank (E.F. Codd) for the relational databases.
This query language accepts one or more relation from the database and performs some specific operation on them, and gives the desired result in ‘Relation’ to the database users. Relational Algebra has various operators for performing queries on the databases. It provides a single query language for retrieving multiple data from the database without using loops.
Following are the various operations of Relational Algebra for maintaining the relational databases:
- Select Operation
- Project Operation
- Union Operation
- Intersection Operation
- Set Difference Operation
- Cartesian Product Operation
- Rename Operation
Select Operation(σ)
This operation shows those records or tuples in the output, which satisfy the specific condition in the relation. This operation is indicated by the Sigma (σ) symbol.
Following is an expression which selects the tuples from the relation:
σp(r)
Where σ denotes the selection predicate, p is the prepositional logic formula, and r is the relation or table whose data we want to access. The prepositional logic may use relational operators like AND, OR, NOT.
Example:
The following Employee table has Five attributes, which show the employee details.
If we want to access the details of employees whose Employee_Id is greater than and equals to 103. Then we have to type the following expression:
σ Emp_ID >= “103” (Employee)
Project Operation(∏)
This operation shows only the values of those attributes of the table in the output, which are mentioned by the user in the operation. This operation is indicated by the Pie (∏) symbol.
It eliminates or discards all the attributes of the relation which are not specified. Following is an expression which shows the values of selected attributes:
∏ A1, A2,….., An (r)
Where, r is the relation and A1, A2, A3, ……, An, are the attributes of the existing table or relation.
Example:
The following Employee table has Five attributes, whose names are Emp_ID, Emp_Name, Emp_Age, Emp_EmailID, and Emp_Salary.
If we want to access the Emp_Name and Emp_Salary attributes from the employee table, then we have to type the following expression using Project operation:
∏ Emp_Name, Emp_Salary (Employee)
Output:
Union Operation(U)
This operation shows all the records of both the relations (tables) specified in the operation. This operation automatically discarded the duplicate tuples or records.
It is a binary operation because it requires two operands. This operation is indicated by U symbol. Specified relations in the union operation must have the same number of attributes.
Following is an expression of Union operation:
(Relation 1) U (Relation 2)
where Relation1 and Relation 2 are the two relations (tables) of the same relational database.
Example:
Following are the two tables: Coder and Tester.
If we want to fetch the name of employees who work either as Coder or Tester or both, then we have to use the following expression:
(∏ Name (Coder) ) U ( ∏ Name (Tester) )
Intersection Operation(∩)
This operation shows all the records which are common in both the specified tables.
This operation is indicated by the (∩) symbol.
Following is an expression of Intersection operation:
(Relation 1) ∩ (Relation 2)
where Relation1 and Relation 2 are the two relations (tables) of the same relational database.
Example:
Following are the two tables of the Department database. The names of these relations are Coder and Tester. If we want to fetch the name of employees who work only in both the department, then we have to use the following expression:
(∏ Name (Coder) ) ∩ ( ∏ Name (Tester) )
Set Difference Operation(-)
This operation shows all the records which are present in Relation 1 but not present in Relation 2.This operation is indicated by the (-) symbol.
Following is an expression of Set Difference operation:
(Relation 1) – (Relation 2)
whereRelation1 and Relation 2are the two relations (tables) of the same relational database.
Example:
Following are the two relations of the Department database. The names of these two relations are Coder and Tester. If we want to fetch the name of employees who work only in coding but not in testing, then we have to use the following expression:
(∏ Name (Coder) ) – ( ∏ Name (Tester) )
Cartesian Product Operation(x)
This operation combines all the records of one relation with all the records of another relation.
It is indicated by the (x) symbol.
This operation of Relational Algebra is also known as cross-product or cross join.
Following is an expression of Cartesian Product operation:
(Relation 1) x (Relation 2)
where Relation1 and Relation 2 are the two relations (tables) of the same relational database.
Example:
Following are the two tables of the Department database. The names of these two relations are Coder and Tester.
If we want to combine both the relations, then we have to use the following expression:
(Coder) x ( Tester)
Output:
Rename Operation(ρ)
This operation allows the database users to rename the output relation.
This operation is indicated by the small Rho (ρ) symbol.
Following is an expression of Rename operation:
ρ(New_Relation_Name, Old_Relation_Name)
Example:
If any table has a employee name, and we want to change its name, then we have to type the following statement:
ρ(Employee_details, Employee)