Relational Algebra: The 6 Fundamental Operations

What is Relational Algebra? (In Simple Words)

Relational Algebra is a formal Procedural Query Language. It takes one or two relations (tables) as input and produces a brand-new relation as output.

• Procedural means it tells the computer HOW to fetch data step-by-step by specifying an exact sequence of mathematical operations.
• While software developers write high-level SQL queries (declarative: “give me this data”), the internal DBMS Query Optimizer immediately translates SQL into an optimized tree of Relational Algebra operations to execute on the hard drive!

1. The Six Fundamental Relational Algebra Operations

Every query in relational algebra can be constructed using just six fundamental algebraic operators:

2. Selection Operator (σ - Greek letter Sigma)

• Purpose: Selects a subset of tuples (rows) that satisfy a specified predicate (condition).
• Notation: σ<condition>(Relation_Name)
• Comparison Operators allowed: =, !=, <, <=, >, >=
• Logical Operators allowed: AND (∧), OR (∨), NOT (¬)

Practical Example:

Table: STUDENT(Roll_No, Name, Branch, Marks, City)

1. Query 1: “Find all students from Indore.”
   σ City = 'Indore' (STUDENT)
2. Query 2: “Find students in BCA branch with marks greater than 80.”
   σ Branch = 'BCA' ∧ Marks > 80 (STUDENT)

Output: A table containing the exact same number of columns as STUDENT, but containing only the filtered rows!

3. Projection Operator (π - Greek letter Pi)

• Purpose: Selects specific columns (attributes) from a relation, discarding all other columns.
• Important Note: Relational algebra is based on mathematical set theory. Therefore, Projection automatically eliminates duplicate rows from the resulting table!
• Notation: π<attribute_list>(Relation_Name)

Practical Example:

1. Query 1: “Display the names and marks of all students.”
   π Name, Marks (STUDENT)
2. Query 2: “List all distinct cities where students live.”
   π City (STUDENT)
   (If 10 students live in Indore, ‘Indore’ appears only once in the output set).

4. Combining Selection and Projection (Composition)

In real database queries, Selection and Projection are chained together:

• Selection filters the rows first.
• Projection filters the desired columns from that result.

Practical Example:

• Query: “Find the names of all students whose marks are greater than 75.”
  π Name (σ Marks > 75 (STUDENT))

5. Cartesian Product (Cross Product - ×)

• Purpose: Combines every single row of relation R with every single row of relation S.
• Properties:
  • If R has N1 tuples and degree D1, and S has N2 tuples and degree D2:
  • Resulting Degree = D1 + D2
  • Resulting Cardinality = N1 × N2
• Notation: R × S

Practical Example:

• If CUSTOMER has 4 rows and ORDERS has 10 rows:
• CUSTOMER × ORDERS produces a giant table of 4 × 10 = 40 rows.
• Note: A cross product by itself produces mostly meaningless cross-combinations. It becomes useful when followed immediately by a Selection condition (which forms a Join!).

6. Set Operations: Union (∪) and Set Difference (−)

Concept of Union Compatibility:

Two relations R and S can only participate in Union, Intersection, or Set Difference if they are Union-Compatible:

1. Both relations must have the exact same degree (same number of columns).
2. The domains of corresponding columns must be type-compatible (e.g., Column 1 in both must be an integer, Column 2 must be a string).

A. Union (R ∪ S):

• Returns all tuples present in R, or S, or in both (duplicates removed).
• Example: Find all persons who are either a Student or an Employee:
  π Name (STUDENT) ∪ π Name (EMPLOYEE)

B. Set Difference (R − S):

• Returns tuples that are in R but NOT in S.
• Example: Find all students who have NOT paid their fees:
  π Roll_No (STUDENT) − π Roll_No (FEES_PAID)

7. Rename Operator (ρ - Greek letter Rho)

• Purpose: Renames a relation or changes the names of its attributes.
• Essential when performing self-joins on the same table.
• Notation: ρ S (R) renames table R to S.
• Notation: ρ S(A1, A2, ...) (R) renames table R to S and changes its column names to A1, A2, ....

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is Relational Algebra? Differentiate between Selection (σ) and Projection (π) operators with examples.”

• Define Relational Algebra as a procedural query language.
• Contrast Selection (horizontal row filter, does not eliminate duplicates) vs Projection (vertical column filter, eliminates duplicate rows).
• Write small examples using the STUDENT schema.

10-Mark Question: “Explain the six fundamental operations of Relational Algebra with syntax, mathematical symbols, and illustrations. What is Union Compatibility?”

• Detail all 6 operators: Selection (σ), Projection (π), Cross Product (×), Union (∪), Set Difference (−), and Rename (ρ).
• Define Union Compatibility (same degree and matching column domains).
• Provide clear tabular examples for each operation.