by Jasleen Chhabra | Updated on 29 September 2024

Understanding Relational Algebra in DBMS: Operations and Examples

Relational Algebra is a fundamental aspect of the relational database model. It is a procedural query language used to retrieve information from databases, guiding users through a step-by-step process to achieve query results. The operations in relational algebra act on relations (tables) and return new relations as results.

Below is a detailed explanation of the basic operations in Relational Algebra, along with updated examples in tables.

1. Select Operation

The select operation is used to extract rows (tuples) from a relation (table) that satisfy a specific condition or predicate. This operation helps filter data based on a condition. It is denoted by the Greek letter σ (sigma).

• Notation: σ condition (relation)

• Example Table: Employee

EMP_ID	EMP_NAME	DEPT_NO	SALARY
101	Alice	D01	60000
102	Bob	D02	55000
103	Charlie	D01	70000
104	David	D03	45000
105	Emma	D02	50000

• Query: Select all employees from Department D01.

  • Relational Algebra Expression: σ DEPT_NO = 'D01' (Employee)

• Result:

EMP_ID	EMP_NAME	DEPT_NO	SALARY
101	Alice	D01	60000
103	Charlie	D01	70000

2. Project Operation

The project operation selects certain columns from a relation and eliminates duplicates in the result. It is useful for retrieving specific attributes from a table. The operation is denoted by π (pi).

• Notation: π attribute1, attribute2, ..., attributeN (relation)

• Example Table: Product

PROD_ID	PROD_NAME	CATEGORY	PRICE
201	Laptop	Electronics	80000
202	Phone	Electronics	50000
203	Chair	Furniture	7000
204	Table	Furniture	10000
205	TV	Electronics	60000

• Query: Retrieve the PROD_NAME and PRICE from the Product table.

  • Relational Algebra Expression: π PROD_NAME, PRICE (Product)

• Result:

PROD_NAME	PRICE
Laptop	80000
Phone	50000
Chair	7000
Table	10000
TV	60000

3. Union Operation

The union operation merges the tuples of two relations, eliminating any duplicates. Both relations must have the same number of attributes, with matching data types. It is denoted by ∪.

• Notation: Relation1 ∪ Relation2

• Example Tables:

Customers1

CUST_ID	CUST_NAME
301	John
302	Jane
303	Adam

Customers2

CUST_ID	CUST_NAME
303	Adam
304	Sarah
305	Mark

• Query: Find all unique customers from both Customers1 and Customers2.

  • Relational Algebra Expression: π CUST_NAME (Customers1) ∪ π CUST_NAME (Customers2)

• Result:

CUST_NAME

John

Jane

Adam

Sarah

Mark

4. Set Intersection Operation

The set intersection operation returns tuples that appear in both relations. It is denoted by ∩.

• Notation: Relation1 ∩ Relation2

• Example Tables:

Sales1

SALE_ID	PRODUCT
401	Laptop
402	Phone
403	Chair

Sales2

SALE_ID	PRODUCT
403	Chair
404	Table
405	Phone

• Query: Find products sold in both Sales1 and Sales2.

  • Relational Algebra Expression: π PRODUCT (Sales1) ∩ π PRODUCT (Sales2)

• Result:

PRODUCT

Chair

Phone

5. Set Difference Operation

The set difference operation returns tuples that exist in one relation but not in the other. It is denoted by the minus (-) sign.

• Notation: Relation1 - Relation2

• Example Tables:

Employees1

EMP_ID	EMP_NAME
501	Adam
502	Blake
503	Charles

Employees2

EMP_ID	EMP_NAME
502	Blake
504	Donna

• Query: Find employees in Employees1 who are not in Employees2.

  • Relational Algebra Expression: π EMP_NAME (Employees1) - π EMP_NAME (Employees2)

• Result:

EMP_NAME Adam Charles

6. Cartesian Product Operation

The Cartesian product combines every row from one relation with every row from another. It is denoted by the X (cross) symbol.

• Notation: Relation1 X Relation2

• Example Tables:

Department

DEPT_ID DEPT_NAME D01 Marketing D02 Sales

Manager

MGR_ID MGR_NAME M01 Alice M02 Bob

• Query: Perform a Cartesian product of Department and Manager.

  • Relational Algebra Expression: Department X Manager

• Result:

DEPT_ID DEPT_NAME MGR_ID MGR_NAME D01 Marketing M01 Alice D01 Marketing M02 Bob D02 Sales M01 Alice D02 Sales M02 Bob

7. Rename Operation

The rename operation is used to assign a new name to a relation. It is denoted by ρ (rho).

• Notation: ρ (NewName, OldName)

• Example:

If we want to rename the relation Students to Learners, we can use the following operation:

• Relational Algebra Expression: ρ(Learners, Students)

By doing this, we assign the new name Learners to the relation.

---

Conclusion

Relational algebra plays a key role in structuring queries in databases. Each operation, whether it’s selecting specific rows, projecting columns, or combining datasets, has its own significance. The examples provided here offer a practical understanding of how each operation works. This makes relational algebra a powerful tool for querying and managing relational databases efficiently.

---