Introduction

Extended Relational Algebra introduces advanced operations like generalized projection, aggregate functions, and grouping, essential for practical database queries. Unlike basic relational algebra, these operations enable computations, aggregations, and string manipulations.

Key Extensions in Extended Relational Algebra

• Generalized Projection: Allows arithmetic operations on attributes during projection.
• String Operations: Enables concatenation and other string manipulations.
• Aggregate Functions: Includes operations like SUM, COUNT, AVG, MAX, and MIN.
• Group By: Groups tuples based on attributes and applies aggregate functions to each group.

Examples

1. Generalized Projection

Query: Calculate monthly salary from annual salary.
Relational Algebra Expression:
Π(salary / 12)(Employee)
            

2. Aggregate Functions

Query: Calculate the total salary and count employees.
Relational Algebra Expression:
SUM(salary), COUNT(SSN)(Employee)
            

3. Group By

Query: Group employees by department and calculate average salary.
Relational Algebra Expression:
GROUP_BY(department, COUNT(SSN), AVG(salary))(Employee)
            

Comparison with Basic Relational Algebra

• Basic Relational Algebra: Includes operations like selection, projection, union, set difference, and Cartesian product. Limited to tuple-level manipulations.
• Extended Relational Algebra: Adds aggregate and grouping functions for higher-level data analysis.

GATE Question Insights

Question 1:

Problem: Given relations R (M tuples) and S (N tuples), what are the maximum and minimum sizes of the result after a join operation?

Solution:

• Maximum Size: M × N (if all tuples match).
• Minimum Size: 0 (if no tuples match).

Question 2:

Problem: Which of the following cannot be expressed in basic relational algebra?

• Finding the sum of all employee salaries requires SUM, an aggregate function only available in extended relational algebra.

Conclusion

• Extended Relational Algebra provides powerful tools for advanced query processing, including arithmetic and aggregate operations.
• Understanding its features is essential for solving complex database problems and excelling in competitive exams like GATE and UGC NET.