I have a table named: project(pId,cId)
and I am trying to find projects ids that only one company is working on them, using relational algebra.
I thought about using join and finding those pIds the occur more than once and then using subtraction but not sure how should I write it.
I cannot use count in my relational algebra and also not the !=
I'll use the variety of Relational Algebra at wikipedia, explanation below, plus assignment to relation variables for intermediate results.
crosscid := project ⋈ ρ<cid2/cid>(project);
multicid := crosscid \ σ<cid = cid2>(crosscid);
result := π<pid>(project) \ π<pid>(multicid);
Where wikipedia shows subscripted components of operators, I show in angle brackets < >.
crosscid is the cross-product of all cids for each pid, obtained by creating a duplicate of the project relation with cid renamed. Note this includes tuples with cid == cid2.
multicid is crosscid filtered to only the pids with multiple cids, obtained by subtracting the tuples in crosscid with cid == cid2. (This is the 'work round' for the limitation that we're not allowed to use !=.)
result is the pids from the original project relation subtract the pids with multiple cids.