Chapter :- 3 (English)

7. Additional Relational Algebraic Operations

Additional Relational Algebraic Operations

1. Projection (π)

Definition:

Projection selects specific columns (attributes) from a relation, removing duplicates.

Notation:

π_{column_list}(Relation)

Example:

π_{Name, Salary}(EMPLOYEE)

Advantages:

• ✔ Reduces the number of columns → smaller and faster intermediate results
• ✔ Helps hide irrelevant or sensitive attributes
• ✔ Simplifies query results

Disadvantages:

• ❌ Removes duplicates automatically → may add extra processing cost
• ❌ Potential loss of information (non-selected attributes gone)

 

2. Selection (σ)

Definition:

Selection retrieves specific rows (tuples) that satisfy a given condition.

Notation:

σ_condition(Relation)

Example:

σ_{Salary > 50000}(EMPLOYEE)

Advantages:

• ✔ Filters rows → smaller data and faster downstream operations
• ✔ Helps enforce conditions (WHERE clause in SQL)
• ✔ Improves query efficiency when used early

Disadvantages:

• ❌ Can be slow if applied on large relations without indexes
• ❌ Incorrect or complex conditions may result in empty or huge outputs

 

3. Division (÷)

Definition:

Division returns rows from relation A that match all rows in relation B.
Used for "for all" queries.

Example:

STUDENT_COURSE ÷ COURSE_REQUIRED
→ students who took all required courses

Advantages:

• ✔ Useful for universal quantification ("for all" conditions)
• ✔ Simplifies complex logic

Disadvantages:

• ❌ Hard to understand and implement
• ❌ Computationally expensive (requires multiple intermediate operations)
• ❌ Rarely used compared to joins

 

 4. Rename (ρ)

Definition:

Rename changes the name of a relation or its attributes.

Notation:

ρ_NewName(Relation)
ρ_{NewName(attr1, attr2,...)}(Relation)

Advantages:

• ✔ Essential for self-joins or eliminating ambiguity
• ✔ Improves readability
• ✔ Helps align attribute names for joins

Disadvantages:

• ❌ Adds extra steps without contributing to actual data
• ❌ Misuse may create confusion

 

5. Join (⨝)

Definition:

Join combines two relations based on a related attribute.

Types:

• Theta join (conditions)
• Equi-join (= only)
• Natural join (automatic match on same attribute names)
• Outer joins (left, right, full — in extended algebra)

Example:

EMPLOYEE ⨝_{EMPLOYEE.DID = DEPARTMENT.ID} DEPARTMENT

Advantages:

• ✔ Brings together related data from multiple tables
• ✔ Powerful tool for complex queries
• ✔ Reduces redundancy when relations are normalized

Disadvantages:

• ❌ Expensive operation on large tables
• ❌ Natural join can cause unexpected results if attribute names overlap incorrectly
• ❌ Requires careful attribute matching

 

Table

Operation	Purpose	Advantages	Disadvantages
Projection (π)	Select columns	Reduces data, hides irrelevant info	Loses discarded data, duplicate elimination cost
Selection (σ)	Select rows	Efficient filtering, reduces dataset	Slow on large data without indexes
Division (÷)	“For all” queries	Handles universal conditions	Hard, slow, rarely used
Rename (ρ)	Rename relations/attributes	Removes ambiguity, essential for self-joins	Adds complexity
Join (⨝)	Combine relations	Powerful, essential for relational model	Computationally expensive