Define the five basic relational algebra operations

1. Define the five basic relational algebra operations. Define the Join, Intersection, and Division operations in terms of these five basic operations.
2. Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join, Outer join, and Semijoin. Give examples to illustrate your answer.

The Correct Answer and Explanation is :

1. Five Basic Relational Algebra Operations:

Relational algebra is a procedural query language used in databases. The five basic operations are:

• Selection (σ): Extracts rows (tuples) that satisfy a given condition.
• Example: σ_age>30(Employees) retrieves employees older than 30.
• Projection (π): Selects specific columns (attributes) from a relation.
• Example: π_name, salary(Employees) retrieves only names and salaries.
• Union (∪): Combines tuples from two relations, removing duplicates.
• Example: Employees ∪ Managers gives a list of employees and managers.
• Set Difference (-): Returns tuples in one relation but not in another.
• Example: Employees – Managers lists employees who are not managers.
• Cartesian Product (×): Combines each tuple of the first relation with all tuples of the second relation.
• Example: Employees × Departments pairs every employee with every department.

Join, Intersection, and Division using Basic Operations:

• Join (⨝): A join between two relations can be defined using Cartesian product and selection.
• Example: R ⨝ S = σ_condition(R × S)
• Intersection (∩): The intersection of two relations can be defined using set difference.
• Example: R ∩ S = R – (R – S)
• Division (÷): Used when we need to find tuples in one relation that are associated with all tuples in another.
• Example: A ÷ B can be expressed as π_A(R) – (π_A(R) × B – R)

---

2. Differences Between Join Operations:

1. Theta Join (⨝θ):

• Joins tuples satisfying a general condition.
• Example: σ_Employees.salary > Managers.salary(Employees × Managers)
• Output: Employees earning more than a manager.

1. Equijoin:

• A Theta Join where the condition is equality (=).
• Example: Employees ⨝ Employees.manager_id = Managers.manager_id Managers
• Output: Employees paired with their managers.

1. Natural Join (⨝):

• An equijoin that automatically matches common attribute names.
• Example: Employees ⨝ Managers (if both have manager_id).
• Output: Removes duplicate columns.

1. Outer Join:

• Includes unmatched tuples by padding with NULL.
• Left Outer Join (⟕): Keeps all tuples from the left relation.
• Right Outer Join (⟖): Keeps all tuples from the right relation.
• Full Outer Join (⟗): Keeps all tuples from both relations.

1. Semijoin (⋉):

• Returns only tuples from the first relation that have matches in the second.
• Example: Employees ⋉ Managers (returns only employees who have a manager).

These operations optimize database queries and improve retrieval efficiency.