Overview of Relational Algebra Operations

• Selection (σ): Filters rows based on a condition.
• Projection (π): Selects specific columns, reducing the degree of the relation.
• Union (∪): Combines two relations while eliminating duplicates.
• Commutativity: Certain operations, like selection, can be reordered without affecting the result.
• Distributivity: Operations such as selection distribute over others, like union.

Analysis of Query Transformations

Option A: Incorrect Transformation

Left-hand side: Applies two selection operations sequentially: σ_{C2}(R1) and σ_{C1}.

Right-hand side: Applies only σ_{C2}(R1).

Issue: The condition C1 is missing on the right-hand side, leading to an incorrect result. Selection is commutative, but all conditions must be applied.

Example:
C1: salary > 12000
C2: dept_id = 1

Left-hand result: Empty
Right-hand result: Retains rows with dept_id = 1 but ignores salary condition.
            

Option B: Correct Transformation

Left-hand side: Applies σ_{C1} first and then projects A1.

Right-hand side: Projects A1 first and then applies σ_{C1}.

Condition: This transformation is valid only if C1 involves attributes in A1. Otherwise, it is invalid.

Example:
C1: name = 'Alice'
A1: name

Both sides yield the same result: {Alice}.
            

Option C: Correct Transformation

Left-hand side: Performs R1 ∪ R2 first, then applies σ_{C1}.

Right-hand side: Applies σ_{C1} to both R1 and R2, then performs R1 ∪ R2.

Validation: Selection is distributive over union, making this transformation valid.

Example:
C1: salary > 12000
Both sides yield the same filtered result.
            

Option D: Incorrect Transformation

Left-hand side: Applies σ_{C1} to R1, then projects A1.

Right-hand side: Projects A2 first, then applies σ_{C1}.

Issue: Attributes required by C1 may be removed by projecting A2, making the transformation invalid.

Example:
A1: emp_id
A2: name
C1: emp_id > 100

Left-hand side: Retains emp_id for filtering.
Right-hand side: Removes emp_id, making selection impossible.
            

Conclusion

• Correct Transformations: Option B and Option C.
• Incorrect Transformations: Option A and Option D.