Results 1 to 8 of 8
  1. #1
    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.
    Attached Thumbnails Attached Thumbnails Selection_002.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,518
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    "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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,536
    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. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,943
    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.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,518
    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 on the Tiger 800 or the Norton

  7. #7
    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 11:01.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,943
    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 for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •