0

I have a following table called students. If I want to put a constraint -- for each state, there are no more than 2 students can come from this same state, how could I express such a constraint in relational algebra?

   name   |  hometown      |  gender
 -----------------------------------
   Bob    |  NYC           |  male
   Alice  |  Washington D.C|  female
   Linda  |  London        |  female
   Peter  |  Miami         |  male
   Amy    |  Philadelphia  |  female
   Lucy   |  NYC           |  female
   James  |  Albany        |  male
   Jason  |  Los Angeles   |  male
   Cindy  |  Salt Lake City|  female
   Jackson|  Princeton     |  male
   Judy   |  Seattle       |  female
   Marcia |  San Francisco |  female
   Steve  |  NYC           |  male
   John   |  Miami         |  male
Cœur
  • 37,241
  • 25
  • 195
  • 267
Accordion
  • 53
  • 1
  • 4
  • 1. What version of "relational algebra" are you using? Ie what operators and what kind of relations? 2. See [this answer](http://stackoverflow.com/a/24425914/3404097). – philipxy May 12 '15 at 00:59
  • @philipxy The principle operators are union, intersection, difference, selection, projection, Cartesian product, natural join, theta-join, and renaming. The algebra underlies most query languages for the relational algebra. – Accordion May 12 '15 at 06:31
  • That helps. But 1. Knowing the "principle" operators does not tell us the set of operators allowed. 2. Some algebras allow arbitrary expressions in restriction; others just two attributes and an (in)equality; others also constants. 3. Some algebras have relations with ordered tuples or joins that must pair attributes (but now you have mentioned natural join). 4. Etc. PS: What have you tried? – philipxy May 13 '15 at 22:24
  • Now that you have corrected hometown data to be home towns, where is information on their states? And what is the name of the above relation? – philipxy May 13 '15 at 22:40
  • Does this answer your question? [expressing constraints in relational algebra](https://stackoverflow.com/questions/28457182/expressing-constraints-in-relational-algebra) – philipxy Nov 06 '21 at 02:39

1 Answers1

0

You're going to need a relation providing the state for each town. Join that with this table (on town) and that will give you the state. Then group that result by state and count the occurrences, constraining the count to be less than or equal to two. As you haven't provided the required relation or relation names, I can't give you the algebra, but you should find this helps you get your homework done.

cliffordheath
  • 2,536
  • 15
  • 16