If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Difficulty understanding the limitations of a relationship.

 HunterDX77M 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:

Quote:
 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:
Quote:
 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.
Attached Thumbnails

 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
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

Quote:
 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?
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
 blindman World Class Flame Warrior Join Date: Jun 2003 Location: Ohio Posts: 12,561
 "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.... __________________ If it's not practically useful, then it's practically useless. blindman www.chess.com: "sqlblindman" www.LobsterShot.blogspot.com
 MCrowley Registered User Join Date: Jan 2003 Location: Massachusetts Posts: 5,477
 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?
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,815
 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 __________________ In theory, theory and practice are identical. In practice, theory and practice are unrelated.
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
Quote:
 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..
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
 HunterDX77M Registered User Join Date: Jan 2012 Posts: 14
Quote:
 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.
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,815
 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 __________________ In theory, theory and practice are identical. In practice, theory and practice are unrelated.

 Tags limits, relationship