Relational Algebra in DBMS – A Practical Introduction

In the convoluted world of Database Management Systems (DBMS), relational algebra remains a timeless mainstay, providing a formal framework for handling relational data. Emerged from the trailblasing work of Computer Scientist Edgar F. Codd back in the 1970s, relational algebra has since then become an irreplaceable tool for database designers, software engineers, developers, and administrations alike. Relational algebra’s principles are rooted in set theory, empowering individuals to interact with databases efficaciously, revealing the power of data for analysis, decision-making, as well as innovation.

A Glimpse into a Relational Algebra

A procedural query language, relational algebra, is utilised to perform many different operations in relational databases. It consists of a set of operations enabling users to retrieve, manipulate, and handle data stored in relational databases. These operations are parallel to the normal operations of set theory, for example- union intersection and difference, along with more operations personalised for relational databases.

 Basic Operations of Relational Algebra in DBMS

Here are two tables suitable for demonstrating relational algebra operations:

Table 1: PRODUCT

PRODUCT ID	NAME	PRICE
1	Laptop	1500
2	Smartphone	800
3	Tablet	1200
4	Headphones	100
5	Camera	2000

Table 2: ORDER

ORDER ID	CUSTOMER ID	DATE
101	1001	2024-04-25
102	1002	2024-04-26
103	1003	2024-04-27
104	1004	2024-04-28
105	1005	2024-04-29

• Selection (σ)

The selection operation recovers the row from a relation fulfilling a specific condition. For example, consider the table ‘PRODUCT’ having attributes, “PRODUCT ID, NAME, and PRICE. To select products with a price greater than 1000, 

Use the code: σ(PRICE>1000) (PRODUCT). 

Output:

Product ID	NAME	PRICE
1	Laptop	1500
3	Tablet	1200
5	Camera	2000

• Projection (π) 

The projection operation pulls out some specific columns (attributes) from a relation while dumping the rest. For instance, from table, ORDER, with attributes, ORDER ID, CUSTOMER ID, and DATE, to select only the ORDER ID and DATE columns, 

Use the code: π(ORDER ID, DATE) (CUSTOMER ID).

Output:

ORDER ID	DATE
101	2024-04-25
102	2024-04-26
103	2024-04-27
104	2024-04-28
105	2024-04-29

• Cartesian Product (×)

The Cartesian product operation merges every tuple of one relation with each tuple of the other relation, resulting in a new relation with all possible collaborations and mergers. For instance, to produce a relation with each possible pair of tuples from the PRODUCT and ORDER table, 

Use code: PRODUCT × ORDER

Output:

PRODUCT ID	NAME	PRICE	ORDER ID	CUSTOMER ID	DATE
1	Laptop	1500	101	1001	2024-04-25
1	Laptop	1500	102	1002	2024-04-26
1	Laptop	1500	103	1003	2024-04-27
1	Laptop	1500	104	1004	2024-04-28
1	Laptop	1500	105	1005	2024-04-29
2	Smartphone	800	101	1001	2024-04-25
2	Smartphone	800	102	1002	2024-04-26
2	Smartphone	800	103	1003	2024-04-27
2	Smartphone	800	104	1004	2024-04-28
2	Smartphone	800	105	1005	2024-04-29
3	Tablet	1200	101	1001	2024-04-25
3	Tablet	1200	102	1002	2024-04-26

And so on…

• Rename (ρ)

The rename operation is used to rename the attributes of a relation. for example,  to rename the PRICE attribute of the PRODUCT table to PRODUCT PRICE,

Use Code: ρ(PRODUCT PRICE ← PRICE) (PRODUCT) 

Output: 

PRODUCT ID	NAME	PRODUCT PRICE
1	Laptop	1500
2	Smartphone	800
3	Tablet	1200
4	Headphones	100
5	Camera	2000

Now, let’s consider two other tables for understanding the use of Union, Intersection, and Difference operations.

Table 3: NOVEL

NOVEL NO.	NAME	AUTHOR
001	Jaws	Henry
002	Raw	Collins
003	You	Morrison
004	Done	Twain
004	Why	Hemingway

Table 4: DRAMA

DRAMA NO.	NAME	AUTHOR
1	Heaven	Salinger
2	Glory	Collins
3	Death	Morrison
4	Weapon	Henry
5	Hero	Lee

• Union (∪)

The union operation amalgamates tuples from two relations into a single relation while eliminating duplicates. For example, you want all the AUTHOR  from the NOVEL and DRAME tables, 

Use code: ∏ AUTHOR(NOVEL) ∪ ∏ AUTHOR(DRAMA)

Output:

AUTHOR

Henry

Salinger

Collins

Lee

Twain

Morrison

Hemingway

• Intersection (∩)

The intersection operation gives back tuples appearing in both of the relations. For instance, you want the names that are present in NOVEL as well as in DRAMA relation,

Use Code: ∏ NAME(NOVEL) ∩ ∏ NAME(DRAMA)

Output:

AUTHOR

Collins

Morrison

• Difference (−) 

Similar to intersection, the difference operation returns tuples that are in one relation but not in the other. For example, suppose you know the names of AUTHOR who are in DRAMA but not in NOVEL then,

Use Code: ∏ NAME(DRAMA)- ∏ NAME(NOVEL)

Output:

AUTHOR

Salinger

Henry

Lee

 Advanced Operations of Relational Algebra in DBMS

• Aggregate Functions

Relational algebra supports aggregate functions such as SUM, AVG, COUNT, MIN, and MAX, allowing the summary statistics’ calculations on groups of tuples. 

• Nested Queries

Relational algebra enables nested queries, where the outcomes of one query act as an input to another query, providing intricate data retrieval as well as manipulation.

• Join (⨝)

The Join operation extracts and brings information from various tables by matching rows sharing a common field. There are various kinds of joins, including equi join, natural join, theta join, and outer join, and each of them serves specific purposes in database queries.

 Importance of Database Management

Acting as the theoretical foundation for relational databases, relational algebra influences the design, implementation, and optimisation of database systems. Comprehending relational algebra is imperative for:

• Database Design 

Relational algebra supports database designers in normalising data and developing efficient relationships between tables, which leads to well-structured and maintainable databases. 

• Data Manipulation

Database administrators and developers utilise relational algebra to perform and complete data manipulation tasks, incorporating querying, updating, and deleting data from databases.

• Query Optimisation

Database frameworks utilise relational algebra to optimise query execution plans to ensure effective data retrieval and processing.

• Data Integrity

Relational algebra ensures data integrity by enforcing constraints, like primary keys, foreign keys, and referential integrity, balancing out the consistency and accuracy of data in databases.

Relational algebra is pivotal for efficacious data management. Its well-defined approach allows interoperability, and it offers incentives for learning query languages. By utilising relational algebra, you can unlock the full power of your databases.

Conclusion

Oddly enough, relational algebra is the cornerstone of Database Management Systems, which provides a formal system for querying and manipulating relational data. Its operations develop building blocks of database systems, allowing efficient data retrieval, manipulation, and management. By diving deep into relational algebra, database professionals can design and develop vigorous databases, optimising query performance and ensuring data integrity and consistency to utilise the full potential of relational databases in a multitude of applications and industries.

If you are intrigued by relational algebra in Database Management Systems and willing to upscale your skill sets, look no further than Hero Vired’s Certificate Programme in Full Stack Development with Specialization for Web and Mobile.