Q87.Marks: +2.0UGC NET Paper 2: Computer Science and Application 26th June 2025 Shift 1
Consider the following three relations
Employee (eid, eName), Comp(cid, cName), Own(eid, cid). Which of the following relational algebra expression return the set of eids who own all brands:
The correct answer is option 1) πeid( πeid,cid(Own) ÷ πcid(Comp) )
Key Points
Relational Division: ✅ The expression πeid( πeid,cid(Own) ÷ πcid(Comp) ) returns exactly those eids for which every brand cid in Comp appears with that employee in Own. This is the canonical way to express “owns all brands”.
Why the other options are wrong:
Option 2: ❌ πeid( πeid(Own) × πcid(Comp) ) is a Cartesian product of all eids with all cids; projecting eid just gives all employees that appear in Own, not only those who own every brand.
Option 3: ❌ πeid( πeid,cid(Own) × πcid(Comp) ) is also a Cartesian product; after projection it again returns all eids in Own, ignoring the “for all brands” requirement.
Option 4: ❌ πeid( πeid(Own) × ( πcid,cName(Own) ÷ πcid(Comp) ) ) is ill-typed (the relation Own doesn’t have cName), and even if fixed, the product with πeid(Own) does not enforce the universal condition per employee.
Additional Information
Division-free equivalent (anti-join idea): Let A = πcid(Comp) and E = πeid(Own). Compute the “missing pairs” M(eid,cid) = (E × A) − πeid,cid(Own). Then result is E − πeid(M) (employees for whom no brand is missing).
Group-by intuition: Count distinct cid owned by each eid and keep those whose count equals |πcid(Comp)| (this is how SQL mirrors division).
