Question

Consider the following three relations in a relational database. 

Employee(\(eId\), Name), Brand(\(bId\), bName), Own(\(eId\), \(bId\)) 

Which of the following relational algebra expressions return the set of \(eId\)'s who own all the brands?

• A. \(\Pi_{eId} (\Pi_{eId,bld} (Own) / \Pi_{bld} (Brand))\)
• B. \(\Pi_{eId} (Own) - \Pi_{eId} ((\Pi_{eId} (Own) \times \Pi_{bld} (Brand)) - \Pi_{eId,bld} (Own)))\)
• C. \(\Pi_{eId} (\Pi_{eId,bld} (Own) / \Pi_{bld} (Own))\)
• D. \(\Pi_{eId} ((\Pi_{eId} (Own) \times \Pi_{bld} (Own)) / \Pi_{bld} (Brand))\)

Hint:

The division operation in relational algebra is a powerful tool for finding rows in one relation that match all values in another relation, which is perfect for queries like "who owns all the brands?"

Answer 1

The Correct Option is A, B

To answer this, we need to determine the relational algebra expression that correctly identifies employees (\(eId\)) who own all the brands. Let's analyze the options:

Option (A) - Correct:
This expression involves a division operation, which is ideal for the problem of selecting rows from one relation based on matching values in another relation. The division \( \Pi_{eId,bld} (Own) / \Pi_{bld} (Brand) \) essentially returns the set of \(eId\)'s who own all the brands listed in the Brand table. The projection \( \Pi_{eId} \) on top then selects only the eId's. This approach correctly identifies employees who own all the brands.

Option (B) - Correct:
This expression first calculates the cross product of employees who own any building and the list of all brands (\(\Pi_{eId} (Own) \times \Pi_{bld} (Brand)\)), and then subtracts the set of employees who own specific buildings (\(\Pi_{eId,bld} (Own)\)). The difference operation removes those who do not own every brand, leaving only the employees who own all brands. This option works but is more complex than (A).

Option (C) - Incorrect:
This expression would select all employees who own buildings and then divide it by the buildings, which does not account for ownership of all brands. It incorrectly attempts to find employees who own a specific set of brands, which doesn't solve the problem of ownership of all brands.

Option (D) - Incorrect:
This expression attempts to find employees who own any building and cross-multiply it with all the buildings, followed by a division by the brands. It is conceptually flawed, as it does not correctly select employees based on the ownership of all brands. The division should apply to the ownership relation with the set of brands, not to the entire ownership relation.

Thus, the correct answers are (A) and (B).