Relational Algebra: Joins, Division Operator, and Assignment

Introduction: Derived Relational Operations

While the six fundamental operators (σ, π, ×, ∪, −, ρ) are theoretically sufficient to express any relational algebra query, writing complex real-world queries exclusively with cross products and selections is cumbersome.

To simplify querying, relational algebra includes powerful Derived Operators—most importantly Joins (⋈) and the Division Operator (÷).

1. Set Intersection (∩)

• Purpose: Finds tuples that are present in both relation R AND relation S.
• Requirement: R and S must be Union-Compatible.
• Equivalence: Can be derived using Set Difference:
  R ∩ S = R − (R − S)
• Example: Find students who are enrolled in BOTH Sports Club AND Music Club:
  π Roll_No (SPORTS) ∩ π Roll_No (MUSIC)

2. The Family of Join Operations (⋈ - Bowtie Symbol)

A Join operation combines related tuples from two different relations into single, unified rows based on a matching condition.

A. Theta Join (θ-Join)

• Notation: R ⋈<condition> S = σ<condition> (R × S)
• Combines Cartesian Product and Selection into a single step.
• Example: Find all employees who earn more than their manager:
  EMPLOYEE ⋈ EMPLOYEE.Salary > MANAGER.Salary MANAGER

B. Equi-Join

• A specific variant of Theta Join where the condition is strictly an Equality comparison (=).
• Example: STUDENT ⋈ STUDENT.Dept_ID = DEPARTMENT.Dept_ID DEPARTMENT
• Drawback: The resulting relation contains the column Dept_ID twice (once from STUDENT and once from DEPARTMENT).

C. Natural Join (⋈)

• The most frequently used join in relational databases.
• Performs an Equi-Join by automatically comparing all columns that share the exact same attribute name in both tables, and keeps only ONE copy of the common column in the output!
• Notation: R ⋈ S
• Example:
  Given STUDENT(Roll_No, Name, Dept_ID) and DEPARTMENT(Dept_ID, Dept_Name).
  STUDENT ⋈ DEPARTMENT automatically joins on Dept_ID and yields:
  [ Roll_No, Name, Dept_ID, Dept_Name ] with zero redundant columns!

3. Outer Joins (Handling Missing / Unmatched Data)

In a standard Natural Join or Inner Join, if a tuple in Table A does not match any tuple in Table B, that row is completely discarded from the output.

An Outer Join preserves unmatched rows by filling missing columns with NULL values:

Detailed Behavior:

1. Left Outer Join (R ⟕ S):
   • Returns all matching tuples from R and S.
   • In addition, returns all tuples from R that have no match in S, filling S attributes with NULL.
   • Example: List all students, including those who have not registered for any courses yet.
2. Right Outer Join (R ⟖ S):
   • Returns all matching tuples.
   • In addition, returns all tuples from S that have no match in R, filling R attributes with NULL.
   • Example: List all courses, including those that have zero enrolled students.
3. Full Outer Join (R ⟗ S):
   • Keeps all rows from both tables, populating missing attributes on either side with NULL.

4. Division Operator (÷)

The Division Operator (÷) is used for queries that include the phrase “FOR ALL” or “EVERY”.

Definition:

Let R have attributes (A, B) and S have attribute (B).
Then R ÷ S produces a relation with attribute (A) consisting of all values of A that are associated with EVERY SINGLE value of B in table S!

Classic Exam Scenario:

Suppose:

• ENROLLMENT table stores [ Student_Name, Course_Name ].

• REQUIRED_COURSES table stores [ Course_Name ] containing:

  • Database Systems
  • Computer Networks

• Query: “Find the names of students who have enrolled in EVERY required course.”
  π Student_Name, Course_Name (ENROLLMENT) ÷ REQUIRED_COURSES

• Output: Only students who take both courses will appear! A student taking only Database Systems will be excluded.

5. Assignment Operator (←)

• Allows complex relational queries to be split into manageable, sequential steps using temporary relation variables.
• Example:

  Temp1 ← σ Dept_Name = 'Computer Science' (DEPARTMENT)
  Temp2 ← STUDENT ⋈ Temp1
  Result ← π Name, Roll_No (Temp2)

• Dramatically enhances readability and matches how real programmers think!

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is Natural Join (⋈)? How is it different from Theta Join and Cartesian Product?”

• Define Natural Join as an equi-join on shared attribute names with automatic elimination of duplicate columns.
• Explain that Cartesian Product combines all rows without any condition (N1 × N2), Theta Join adds a condition, and Natural Join matches identical column names automatically.

10-Mark Question: “Explain Inner Join vs Outer Joins (Left Outer, Right Outer, Full Outer Join) with complete tables and diagrams. What is the Division Operator (÷) and when is it used?”

• Create a small 3-row STUDENT and DEPARTMENT table with one unmatched row on each side.
• Show the outputs for Natural Join, Left Outer Join, Right Outer Join, and Full Outer Join clearly showing NULLs.
• Explain the Division Operator (÷) with the “Student taking ALL courses” example.