For a class, I have to create a crows foot ERD for the NFL. I have created one, but a few things don't make sense to me. here is the case study:
Create an ERD based on the following business rules. If additional assumptions are
made, be sure to document them.
1. The NFL consists of two different conferences: AFC and NFC.
2. Each football team is part of one of the conferences.
3. Each football team has many players who can play multiple positions.
4. A football team has one head coach, but multiple assistant coaches.
5. Each assistant coach is assigned to a sub-team/group of players: offense, defense,
and special teams.
6. Positions are assigned to a sub-team (ie quarterback would be part of the offense)
7. Each sub-team could have multiple assistant coaches.
I have come up with this:
|<- means one or many
nfl ||---|<- conferences(afc nfc) -||---<- teams -||---||- coach - || ---<-assitant coaches -||---||-sub teams -||---<-players
Tell me what you think. Any help will be appreciated!
You have also ignored the "player can play multiple positions" part of your statements. I don't see that represented, at all. Sub-teams are *NOT* the same thing: Even on (say) defense sub-team, it is conceivable that a player might play both linebacker and safety (just for example).
In point of fact, you don't even have a POSITIONS table listed there, at all. Tch.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
First off u didn't make it and erd you made it in sql. Second, your posts make no sence half the time. You're very rude. Please do not reply to my posts. Unless your going to help for real and not insult. I've seen you reply to others basicly treating .them like they are stupid.
Well, actually, they never make "sence". I don't know of anything that makes "sence", because there is no such word.
But assuming you meant to use the word "sense", then I would say the problem seems to be more yours than mine. It's certainly true that my posts must make sense for most people. Or at least to 3688 people.
And you never said "please create an ERD for me". You said
Tell me what you think.
So I did.
But now you say you want an ERD. Okay.
Some would argue that COACHES should not be a separate table, since it is 1-to-1 with TEAMS. But in the NFL, the coach of the Colts this year may be the coach of the Chargers next year, so it makes sense to keep them separate, so that they can easily be moved to new teams.
Note that I am now showing in the diagram what I previously showed by way of the SQL CREATE TABLE: The conference and division fields should simply be ENUMs in the table, not separate tables. Having them as separate tables adds nothing to the clarity of the database. Notice that I have added both POSITIONS and PLAYERS tables. Those are *clearly* tables that you utterly forgot. Finally, note that I show that the link from COACHES to ASSISTANTCOACHES is optional. I, personally, would omit it.