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.

 
Go Back  dBforums > General > Database Concepts & Design > Difficulty understanding the limitations of a relationship.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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
Difficulty understanding the limitations of a relationship.-selection_002.png  
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
"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
Reply With Quote
  #4 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,389
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?
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,428
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.
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
Quote:
Originally Posted by MCrowley View Post
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..
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 14
Quote:
Originally Posted by MCrowley View Post
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.
Reply With Quote
  #8 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,428
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.
Reply With Quote
Reply

Tags
limits, relationship

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On