Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8

Thread: ERD for the NFL

  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    121
    Thanks
    0
    Thanked 3 Times in 3 Posts

    ERD for the NFL

    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!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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
    Code:
    CREATE TABLE team (
        teamid INT AUTO_INCREMENT PRIMARY KEY,
        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.
    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.

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    121
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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 ?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    clickable grid on top of canvas

    So do as you want.
    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.

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Posts
    121
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

    Here.


    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
    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.

  • #7
    Regular Coder
    Join Date
    Jun 2010
    Posts
    121
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    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.


  •  

    Posting Permissions

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