-1

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 !=

Cœur
  • 37,241
  • 25
  • 195
  • 267
Hawk_Y
  • 5
  • 4
  • This is likely a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. See [ask] & the voting arrow mouseover texts. PS There are many relational algebras. They differ in operators & even what a relation is. Give a reference and/or definition for yours. Eg textbook name, edition & page. PS Ids present 1 time are all ids minus ids present 2 or more times. PS Show parts you can do & ask re being stuck. [mre] – philipxy Nov 29 '19 at 20:23
  • Possible duplicate of [Relational Algebra “Only Once” or “Exists once”](https://stackoverflow.com/q/26324811/3404097) [How to find all tuples in a table if and only if the tuple appears once?](https://stackoverflow.com/q/19482046/3404097) [Relational algebra, Find unique Names](https://stackoverflow.com/q/50901458/3404097) – philipxy Nov 29 '19 at 20:47
  • Hmm a faq @philipxy? Note the last para: cannot use count (nor presumably other aggregate operators), nor a theta-join with `≠` -- which I presume is what "not the !=" is saying -- strange because `≠` is allowed by every variety of RA, back to Codd 1972. – AntC Nov 30 '19 at 03:34
  • @AntC I don't understand your comment. I said how to do it with minus & self-join in my first comment then my next comment gave 3 links. What operators are not allowed doesn't mean much when they don't say what operators are allowed, but I didn't mention count or unequijoin anyway. – philipxy Nov 30 '19 at 07:54
  • philipxy actually before posting I checked all links you provided and was not able to find an answer for my question. but I'll take a note of the other things said. – Hawk_Y Nov 30 '19 at 18:58

1 Answers1

0

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.

AntC
  • 2,623
  • 1
  • 13
  • 20
  • might be my mistake about not using the ≠ , need to check it again. either way, you helped a lot and I know what to change. Thank you :) – Hawk_Y Nov 30 '19 at 19:04