my sql homework help

Discussion in 'Mac Programming' started by jbrenn, Apr 28, 2009.

  1. jbrenn macrumors 6502a

    Joined:
    Aug 27, 2008
    #1
    I need help. I need to create a one to many relationship. I have a team table and a player table. One team can have one or more players put players can only be on one team. How do i link the two tables.
     
  2. pilotError macrumors 68020

    pilotError

    Joined:
    Apr 12, 2006
    Location:
    Long Island
    #2
    You should really be doing your own homework...

    You need to put a unique key in your team table.
     
  3. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #3
    Since it's for homework, why not create a first go at it and post it then we can give hints to help you out. We don't do homework for people.
     
  4. dbell macrumors member

    Joined:
    Jul 11, 2007
    #4
    You have to create a sub query against the prepared statement and then create a foreign key on a trigger back to the player table when you have that you can then create an index on the inner join and limit the players to one team with a set statement.
     
  5. jbrenn thread starter macrumors 6502a

    Joined:
    Aug 27, 2008
    #5
    This is what i have so far

    Player table

    Number pk
    firstname
    lastname
    fieldposition
    field percentage

    Team table

    Name pk
    City
    PlayerNumber
    datefounded
     
  6. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #6
    I question that a player can only be on one team, but i guess that's to force this design.

    There are many ways to solve this problem. The one that makes the most sense to me is to just have a reference in the player table to the team table. You can't "query" the team table for its members, but finding out the members of a team seems to be pretty trivial at that point.

    You always COULD have an array/vector/what-have-you in the team table with the list of players, but that seems awful.

    Another possibility (that i like less) would be a join table where the player column is unique, so only one entry would be allowed for each player row. You could then have foreign key constraints on both columns. This does give you one place to query for both which team a player is on and all players on a team, but this doesn't seem like a real win to me.

    If you are unfamiliar with primary keys, why you need them, and how you'd use them to make this situation trivial, you should find that out first. There would certainly be ways to implement the ideas above without this, but they are wholly unappealing and sloppy.

    -Lee

    EDIT: Just saw your layout. You have a "Number" that is your primary key in the player table. That seems like a pretty good idea. It's easy to keep track of, and it will never change. hm. I wonder why you wouldn't have something like that in your team table? And once you did... it seems like it would be pretty easy to use that to relate the two. Also, what the heck is PlayerNumber in team? Is that the number of the team captain or something? Does the team only have one player?
     
  7. jbrenn thread starter macrumors 6502a

    Joined:
    Aug 27, 2008
    #7
    I was thinking of making that the fk. it would contain the same info as player number
     
  8. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #8
    For what player? Is there only one on the team?

    -Lee
     
  9. jbrenn thread starter macrumors 6502a

    Joined:
    Aug 27, 2008
    #9
    on this assignment
    there can be one or more than one player on a team.

    In the next assignment
    Is a many to many one player can be on many teams.
     
  10. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #10
    OK, right. So as of now, each team row has a reference to a single player row. Is that what you want? There is also nothing preventing every team from having the same, single player. Again, not sounding like what the assignment wants you to do.

    Perhaps the player should reference the team table? But using what? I wouldn't think the name would be a good idea... that could change. Maybe this week it's the "Juice Devils" but next week it's the "Hoover Dustbuster's Juice Devils". Now all the references from the players are bad! Damn. It would be much easier if there was some value for each team that would never, ever change.

    -Lee
     
  11. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #11
    You seem to be missing proper unique fields (primary key). For the player table you currently have number set for the primary key, but is that a player number (number on their jersey) or a completely unique number, like an increment field for the table? The later is the better setup.

    This also applies for the team table. Using the team name can work, but if you ever need to change the name of a team, you would need to update that name for each player (if you were referencing the player to team relationship that way). By having a unique id for team you could update the team name without editing any of the player table records.

    Hopefully that clears some things up. You're really close now.
     
  12. jbrenn thread starter macrumors 6502a

    Joined:
    Aug 27, 2008
    #12
    no there is nothing preventing every team having the same player. i will manually load data so that will not happen. I was using the pro baseball teams where the name is not likely to change. although it does happen Tampa Bay Devil Rays became the Tampa Bay Rays

    Here is the assignment sheet that might answer questions.
    To be clear for everyone i don't want he physical code done for me. I would like to know how to do it. I have been working on this for about 3 weeks and got almost nowhere.

    Define primary key constraints for both tables to ensure entity integrity.
    Define a foreign key in the child table (or the "many" end of the relationship)
    to enforce referential integrity. (3 pts)

    For the entity classes chosen, include all known instances, or a meaningful
    subset, of the authentic data that can be collected. At least one relationship
    between the "one" and the "many" tables should illustrate a participation
    constraint of "1:M" (one parent row, many children), another "1:1", and "1:0".
    The "many" table should contain at least 15 rows of data. (4 pts)

    Invoke the following SQL commands to demonstrate your design:
    1) Query each of the tables. Project all columns with rows ordered by
    primary key(s); (2 pts)
    2) Query your "many" (aka "child") table to list rows in order by foreign
    key (major sort key) and primary key (minor key); (2 pts)
    3) Issue a query with subquery (nested query) that illustrates a 1:M
    relationship between one parent row and its related child rows (that is,
    show child rows related to a single row from the parent table). (2 pts)

    1 and 2 i can do and understand that completely
    #3 i dont quite understand the nested query. The book i have say dont use nested queries but does not explain anything else about them.
     
  13. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #13
    Alright, for ease i will stop beating around the bush:
    make a numerical, serial, autogenerated unique key for the team table like you have for the player table (unless number is, say, a jersey # that can change. In which case you don't have a real primary key for either table, and you need one on both). Do not use the team name as the primary key, do not relate player to team via the team name.

    You do not want to reference the player table from the team table unless there is some distinct player, i.e. the captain, that you only have one of on the team.

    Now relate the player table in some way to the team table. Once it has a nice key that will never change, this should be easy.

    -Lee

    EDIT: didn't see the specific question about nesting
    The nested query would be something like:
    select x from player where z = (select y from team where name = 'devil rays');
    x would be the attributed of the player you want
    z would be something that relates player to team
    y would be that thing in team

    This is an alternative to join, as join probably hasn't been covered yet.
     
  14. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #14
    Just to elaborate on why we're suggesting not using the team name as a primary key; Yes if you're controlling the data manually, you won't run into issues, but it's bad programming practice. In real world programming you don't know what will happen with your code/data in the future so it's best to things in a way that are least likely to break given possible changes in the near and distant future.
     

Share This Page