0

Say I have a Personstable with attributes {name, pet}. How do I select the names of people where they have one of each kind of pet (dog, cat, bird), but a person only has one of each kind of pet if they pet is in the table.

Example: Bob, Dog and Bob, Cat are the only rows in the table. Therefore, Bob has one of each kind of pet. But the moment Lynda, Bird are added, Bob doesn't have one of each type of pet anymore.

I think the first step to this is to π(pet). You get a list of all kinds of pets since relational algebra removes duplicates. Not sure what to do after this, but I have think I need to join π(pet) and Persons.

I've tried a few things like Natural Join and Cross products but I haven't arrived at a result yet and I'm out of ideas.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Extreme112
  • 95
  • 1
  • 2
  • 9
  • Your description is not clear. Maybe you mean "where they have one of each kind of pet that appears in the table". If you don't force yourself to be clear in natural language, how do you expect to be clear in RA? Or to justify claiming that a particular RA expression says what you mean? What was the exact assignment wording you were given? (Note that if there were more columns, "one" would have to be either "exactly one" or "at least one".) – philipxy Oct 19 '16 at 21:22

1 Answers1

0

The answer to the question can be found with the Division operator:

Persons ÷ πpet(Persons)

This relational algebra expression returns a relation with only the column name, containing all the names of the persons that have all the different kind of pets currently present in the Persons table itself.

The division is an operator that, in some sense, is the inverse of the product operator (the name is derived exactly from this fact). It is a derived operator that can be defined in terms of projection, set difference and product (see for instance this answer).

Community
  • 1
  • 1
Renzo
  • 26,848
  • 5
  • 49
  • 61