Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
macOS my sql homework help
- Thread starter jbrenn
- Start date
- Sort by reaction score
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?
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?
I was thinking of making that the fk. it would contain the same info as player number
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.
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
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.
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.
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.
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.
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.
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.