# Thread: Difficulty understanding the limitations of a relationship.

1. Registered User
Join Date
Jan 2012
Posts
14

## Difficulty understanding the limitations of a relationship.

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:

A youth soccer league has hired you to design a database to represent infor-
mation about players, coaches, teams, and games. Each person (player or
coach) has a unique ID, a name, and an e-mail address. A player also has
an age, which is derived from his/her date of birth and a gender. There are
several divisions, each with a unique name. Each team is in one division.
Each team has a number that is unique within its division (e.g. team 3
of the girls age 12 and under division) and a uniform color. A team has
between twelve and fteen players. A player can be on at most one team, a
team has at least one coach, and a coach cannot coach more than one team.
Here is the actual problem:
Suppose we also want to keep track of games between teams, recording
which teams play each other (consider them home team and visitor
team), and the date and time when they play. One way to do this is
with a relationship set plays shown in the attached diagram. This
approach limits the number of times two teams can play against one
another. Explain why. Show how to modify the design so that teams
can play one another any number of times.
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

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).
true, but how would you design that into the table(s) so it cannot happen in your model?

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
"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....

4. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,860
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?

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by MCrowley
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?
must be different over t'pond
here its 21 and holding..

7. Registered User
Join Date
Jan 2012
Posts
14
Originally Posted by MCrowley
Strictly speaking from this design, can Team A play against Team A?
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?
Last edited by HunterDX77M; 02-09-13 at 10:01.

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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