Home » DBMS

Relational Algebra in DBMS

In this tutorial, we will learn about the relational algebra and its various operations in Database Management System (DBMS). By Pratishtha Saxena Last updated : May 27, 2023

What is Relational Algebra in DBMS?

In DBMS, the relation algebra define mathematical operations to work on relation or table. 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.

In other words, we can say that relational algebra is a theoretical framework and a formal language for expressing operations on relational databases. It provides a set of operations that can be used to manipulate and retrieve data from relational database tables. Relational algebra forms the foundation of the relational model, which is the basis for most modern relational database management systems (DBMS).

Relational Algebra Operations

The following are the relational algebra operations:

1. Selection (σ): Selects rows from a table that satisfy a given condition.
2. Projection (π): Extracts specified columns from a table, discarding the others.
3. Union (∪): Combines rows from two tables, removing duplicates.
4. Set Difference (-): Retrieves rows from one table that are not present in another table.
5. Cartesian Product (×): Combines every row from one table with every row from another table, resulting in a new table with all possible combinations.
6. Join (⨝): Combines rows from two tables based on a common attribute or condition.
7. Intersection (∩): Retrieves rows that are common to both tables.
8. Division (÷): Retrieves rows from one table that have a one-to-one relationship with rows in another table.

This was an overview of what is relational algebra and its basic operations. Now, let's discuss some of the important operations in detail.

1) Select Operation

The selection operation selects rows from a table that satisfy a given condition. It is denoted by the Greek letter sigma (σ). The condition is specified using predicates that involve column values.

Example: σ (age > 30) (Employees) selects all rows from the Employees table where the age is greater than 30.

2) Projection Operation

The projection operation extracts specified columns from a table, discarding the others. It is denoted by the Greek letter pi (π).

Example: π (name, age) (Employees) retrieves only the name and age columns from the Employees table.

3) Union Operation

The union operation combines rows from two tables, removing duplicate rows. The two tables being combined must have the same number of columns and compatible data types.

Example: Table1 ∪ Table2 combines the rows from Table1 and Table2 into a single result set.

4) Set Difference

The set difference operation retrieves rows from one table that are not present in another table. It is denoted by the minus sign (-). The two tables being compared must have the same schema.

Example: Table1 - Table2 retrieves rows from Table1 that do not exist in Table2.

5) Cartesian Product

The cartesian product operation combines every row from one table with every row from another table, resulting in a new table with all possible combinations. It is denoted by the multiplication symbol (×). The resulting table will have a number of rows equal to the product of the number of rows in the two input tables.

Example: Table1 × Table2 generates a new table with all combinations of rows from Table1 and Table2.

6) Set Intersection

The intersection operation retrieves rows that are common to both tables. It is denoted by the intersection symbol (∩). The two tables being compared must have the same schema.

Example: Table1 ∩ Table2 retrieves rows that exist in both Table1 and Table2.

7) Join Operation

The join operation combines rows from two tables based on a common attribute or condition. It is denoted by the join symbol (⨝). Joins can be performed based on different types such as inner join, outer join, left join, right join, etc. The common attribute or condition is specified using predicates.

Example: Table1 ⨝ Table2 on Table1.id = Table2.id performs an inner join on the id column of Table1 and Table2.

8) Division Operation

The division operation retrieves rows from one table that have a one-to-one relationship with rows in another table. It is denoted by the division symbol (÷). The division operation is a more complex operation and requires careful understanding and usage. It is commonly used in database normalization and relational algebra theory.

Conclusion

Overall, these operations can be combined and nested to perform more complex queries and operations on databases. Relational algebra provides a formal and mathematical foundation for understanding and reasoning about the operations and properties of relational databases.