Intersection operation on two database relation is:

Question

Accepted Answer

The correct option is (3):
The Intersection operation ($\cap$) in Relational Algebra, which is derived directly from mathematical Set Theory, is fundamentally both Associative and Commutative. **Commutativity** means that the order of the operands does not affect the result. For any two relations $R$ and $S$, $R \cap S$ is identical to $S \cap R$; the set of tuples common to $R$ and $S$ is the same regardless of which relation is named first. **Associativity** means that when three or more relations are involved, the grouping of the operations does not matter: $(R \cap S) \cap T$ yields the exact same result as $R \cap (S \cap T)$, producing the set of tuples common to $R$, $S$, and $T$. These algebraic properties are crucial in query optimization, allowing the database management system (DBMS) to reorder operations for faster execution without changing the final output.

The incorrect option is (1):
Associative but not Commutative is incorrect because the Intersection operation absolutely satisfies the Commutative property. This property is inherent to the concept of finding common elements between two sets, where the arrangement of the sets being compared is irrelevant to their common elements. Operations that are Associative but not Commutative, such as matrix multiplication, involve an ordered process where operand position significantly alters the outcome, which is not the case for set intersection. Therefore, ruling out Commutativity is factually incorrect for this operation.

The incorrect option is (2):
Commutative but not Associative is incorrect because, as an essential set operation, Intersection is guaranteed to be Associative. Any operation that is Commutative in set theory, which includes Union ($\cup$) and Intersection ($\cap$), is also Associative. An example of an operation that is Commutative but not Associative could be subtraction of the sum from a number, but in Relational Algebra, the fundamental set operations adhere to both properties. The nature of finding shared elements across multiple sets dictates that the grouping of these sets does not change the final collection of shared members.

The incorrect option is (4):
Neither Associative nor Commutative is incorrect because both properties hold true for Intersection. If an operation lacked both properties, its result would be highly dependent on the strict sequence of execution, which is characteristic of the **Set Difference** ($R - S$) operation in Relational Algebra, which is neither commutative nor associative. However, since the Intersection operation is simply designed to find the common elements, both changing the order of two relations (Commutative) and changing the grouping of three or more relations (Associative) will yield the same final set of common tuples.