View Full Version : ERD for the NFL

01-14-2013, 10:32 PM
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!

Old Pedant
01-14-2013, 11:06 PM
I don't see any reason for CONFERENCES to be part of the ERD.

And, by the by, you forgot all about DIVISIONS.

But this is *exactly* what ENUM was created for in MySQL:

It would be more than sufficient to simply have something like

teamName VARCHAR(40),
teamCity VARCHAR(40),
conference ENUM ( 'NFC', 'AFC' ),
division ENUM ( 'East', 'West', 'South', 'North' ),
... etc. ...

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.

So... Incomplete.

01-15-2013, 12:16 AM
Well, I don't think it's meant to be. Complete. It's the first week of the class . Tha must be part of the assumption part of it. Why would I include the devsion. And not conference ?

Old Pedant
01-15-2013, 12:33 AM
Once again, you don't seem to have read what I wrote.

Why would I include the devsion. And not conference ?
And yet if you look again, I *CAREFULLY* showed including *BOTH* conference AND division.

You and I seem to have a problem: You don't seem to understand what I write. And on top of that, I forgot you didn't want to hear anything from me.

So do as you want.

01-15-2013, 02:34 AM
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.

Old Pedant
01-15-2013, 03:20 AM
your posts make no sence half the time.
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.

And now you can't say I didn't give you what you wanted. Even though it's clearly in violation of RULE 1.5 in the http://www.codingforums.com/rules.htm

01-15-2013, 08:03 PM
So, me wanting something that isn't sql but an erd the way I created is in violation. Nice. FYI, no one ever reads the rules.

Old Pedant
01-15-2013, 08:17 PM
No. You wanting us to do your homework FOR YOU is a violation of the rules.

And you are wrong about nobody reading the rules. Members have been kicked off this site for continued violation of the rules. Not for one time, maybe. But keep doing it and you'll be gone.