I'm stuck on a certain problem on my database homework and was wondering if I could get some guidance. I am not looking for the answer, just a little nudge in the right direction. Here is the context:

Here is the actual problem:
My question: If each game played is identified by the home team, the visiting team and a date/time, why would there be a limit to the number of games that could be played? Shouldn't Team A be able to play Team B any number of times, provided the games don't overlap in space and time (physically impossible).

Attached: Image of the relationship.

just becuase something coudl exist in theory, it doesnt' mean that it must exist in the real world
its called application of business logic

if the football team plays in a league then under the usual system a tema cannot play another team more than twice (once as home, once as away) in any one season. however a team may play another team in another format of match (eg FA Cup, League Cup) so you may need a mechanism to handle that. If so how would you implement that?

in a different game, say Basketball or Ice Hockey you may play several times against the same team both home and away. AS it happens in the Scots premier league they can play more than twice against the same team in the league. however for an assignment you can identify that as a problem but not model it (you could recognise there is an issue but say its not required for your model becuase....)

can two teams play each other on the same day?
if so can two teams play each other at the same time.. obviously not, but how would you ensure that cannot occur in your model

true, but how would you design that into the table(s) so it cannot happen in your model?

"A player also has an age, which is derived from his/her date of birth and a gender."
Gender???
I'm going to have go back through all my sql and fix some calculations....

Didn't you know that women stop aging at 29? Go ahead, ask a few....er...wait a moment, while I put on some body armor, first, then go ask a few.

Strictly speaking from this design, can Team A play against Team A?

Jeesh! You two are worse than I am... One missing comma, and the wheels fly off of the bus!

If they meant that spec as published, then I just want to see how that age calculation works... I'm thinking that mcrowley is on to something, but I'm not quite sure what that might be!

-PatP

must be different over t'pond
here its 21 and holding..

Not in the real world, but there is nothing to prevent that from happening in the model. However, since the model is supposed to keep track of what happens in the real world, this probably wouldn't come up.

Anyway, I'm still not certain that I have a good answer for the question that I originally posed in the first post. What's preventing teams from playing against each other an infinite amount of times?

Based on the image that you provided, there is nothing to prevent two teams from playing each other more than once.

From a homework perspective using relational algebra, you could simply specify a unique constraint on the season and both team attributes. If your use case supports the concept of "home" and "away", then you'd have to add a differentiating sub-attribute to both of the team attributes.

-PatP