Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2011
    Posts
    10

    Unanswered: Entity Relationship Diagrams (ERD)s

    Not 100% where i needed to put this but since im creating the database on Access thought id post it in here, Move if necessary.
    Part of an assignment i have to create an ERD, using this scenario below.

    Football League Competition where PLAYERS play in MATCHES between CLUBS from a particular LEAGUE (1st, 2nd etc)

    The capital letters are the entities so Players, Matches, Clubs and League.
    Before i implement this into a Database i have to do a number of things which is why i need the HELP please.

    - Identify/Create an identifier (primary key) for each entity type plus a number of appropriate
    attributes (normally at least 3 or 4) that you would reasonably expect to find.

    - Identify the relationship details (Cardinality Ratios & Participation Constraints) between
    your entity types.

    - Translate the E-R model into an appropriate linked relational schema using the relational mapping rules

    Sorry for all that but would anyone be able to help me with this giving me any knowledge whatsoever? as i struggling which this and could do with some help Thanks

    ANY1 tell me if im on the right track here towards relationships below.

    Relationships
    Players play for Club
    Players play in Matches
    Clubs play in Matches
    Clubs compete in League

    Possible Relationships
    Players play in a League
    Last edited by PublicViewer; 11-21-11 at 10:01.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PublicViewer View Post
    Sorry for all that but would anyone be able to help me with this giving me any knowledge whatsoever?
    what's your favourite team sport? try to think of how that sport is operated, and apply the concepts to your assignment

    you are sort of on the right track, but some corrections at this early stage are definitely advisable...

    Players play for Club
    correct

    Players play in Matches
    not directly, no, they don't

    you never hear anything like "today, tom&bill&fred&andy&jock will play against andy&ted&fritz&jack&todd"

    it's always "today, manu will play mancity"

    see the difference? the clubs participate in matches, not the players

    apply this same idea to all your relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    clubs compete in leagues
    players are employed by club
    leagues have fixtures
    clubs play in fixtures
    clubs select a team to play in fixtures


    the fixtures is possibly a red herring......
    depends on how you want to model the league structure, its composition and its clubs.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2011
    Posts
    23
    I must admit Im not a golf fan but do you have team matches in Golf?

    I thought the structure would be something similar to:-

    1 League has many Clubs
    1 Club has many Players
    1 Player plays many Matches

    Dont know if thats any help at all.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, fixtures = matches

    on this side of the pond we usually call them games
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Random Invert View Post
    I must admit Im not a golf fan but do you have team matches in Golf?

    I thought the structure would be something similar to:-

    1 League has many Clubs
    1 Club has many Players
    <persoanlly I think you need some form of entity such as team which defines what players are used in the team for a specific match>
    1 Player plays many Matches

    Dont know if thats any help at all.
    ryder cup, solheim cup, regional team matches......

    you need to understand the wrinkles of what you are trying to model before you start the ERD. the ERD is a tool to help you flesh out that model, but you need a good grasp first..
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Posts
    10
    thanks for all of the replies everyone very helpful, as i have to create idenifity 4 relationships im going to consider this and then refer back to this thread for more feedback. Ill draw up my ideas of the entities and there relationships such as 1:1 1:M M:M so hopefully some of you can reply back to the thread and give me some feedback Thanks

  8. #8
    Join Date
    Nov 2011
    Posts
    10

    Help!

    So i decied on this now and want some more feedback please

    PLAYERS play for CLUB - Many to 1 Relationship
    CLUBS compete in LEAGUE - Many to 1 Relationship
    CLUBS play MATCHES - Many to Many? not sure about this one
    LEAGUE has MATCHES - 1 to Many Relationship

    Want some feedback on the above please

    Also for each entity i need 5 attributes so far ive come up with some and looking for help again.

    PLAYERS - Player Name, Player ID-Primary Key, Club Name, Position. NEED ANOTHER

    CLUB - Club Name - Primary Key, Stadium, Location. NEED 2 MORE

    MATCHES - Next Match, Games Played, Stadium, League. ANOTHER or changes to these?

    LEAGUE - League Name - Primary Key, Wins, Lost, Draw, Total Points

    So any feedback on these will be very helpful Thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you propose to handle the situation where a player may leave for another club, and or a player may be loaned to another club.
    how does your model support a situation where a club may play in multiple comeptitions in any one period (eg thinking of football in Europe, you have national leagues, national competitions. ferinstance in England there is the league structure, the FA cup (open to any registered team), the League cup (open to any team in the football league structure (EPL, Championship, Divisions 1 & 2), and European competitions such as CL and Europa League), let alone the one off "team world cup", and so called friendlies
    simialrly ho do you expect a national team to be registered / recorded
    does your structure support a single (year) snapshot, or can it record the history of the clubs, ferinstance how would you know in which league which club played in in what period
    simiarly does your model support a club that enters multiple teams in different competitions. EG EPL clubs may also enter teams into Elite Development Squad, Seniors, Ladies and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2011
    Posts
    10
    Quote Originally Posted by healdem View Post
    how do you propose to handle the situation where a player may leave for another club, and or a player may be loaned to another club.
    how does your model support a situation where a club may play in multiple comeptitions in any one period (eg thinking of football in Europe, you have national leagues, national competitions. ferinstance in England there is the league structure, the FA cup (open to any registered team), the League cup (open to any team in the football league structure (EPL, Championship, Divisions 1 & 2), and European competitions such as CL and Europa League), let alone the one off "team world cup", and so called friendlies
    simialrly ho do you expect a national team to be registered / recorded
    does your structure support a single (year) snapshot, or can it record the history of the clubs, ferinstance how would you know in which league which club played in in what period
    simiarly does your model support a club that enters multiple teams in different competitions. EG EPL clubs may also enter teams into Elite Development Squad, Seniors, Ladies and so on.
    Thanks for the reply but thats looking into it to much, this is just a simple ERD. Im just after the entities which i have been given thats way to deep yet. Can you offer any help from my previous post with the attributes i mentioned and the relationships?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by PublicViewer View Post
    Thanks for the reply but thats looking into it to much, this is just a simple ERD. Im just after the entities which i have been given thats way to deep yet. Can you offer any help from my previous post with the attributes i mentioned and the relationships?
    nope, its your homework, its up to you to decide what is or what isn't included
    there's lots of good reasons to decide which way to develop your modle. thats part of your learning process.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2011
    Posts
    10
    Quote Originally Posted by healdem View Post
    nope, its your homework, its up to you to decide what is or what isn't included
    there's lots of good reasons to decide which way to develop your modle. thats part of your learning process.
    ok thanks but from the work which i have done in my other post does it seem ok so far?

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its OK in a general sense
    but its not myself, Rudy or anyone else who can look at the detail and give you an authorotative answer. talk it through with your peers or your lecturer or teacher.. its what they are there for. asking us to validate / approve your model is cheating on your learning process. the risk you run asking other to vvlaidate is that they will look at this as a 'real world' problem and not see the reasons why you have a naiive simplified model.

    you need to work through your model to make certain you understand it, to make certain that you take ownership of it so that you understand why somethings were ruled out, somethings were included

    FWIW I think your League entity is wrong you are mixing the league with the clubs that comprise the league. ferisntance 'the' league is say EPL, Champ, Div1, Div2 etc, where as the positions within the league are the performance of clubs in matches. arguably the 'Wins, Lost, Draw, Total Points' can be derived from the matches entity. a match happens on a specific day in a specific place and will have a result. so you could record the score in the match and then aggregate the results when you need to present your league position. you should not store aggregated data in a table, if it already exists elsewhere (unless you have a good reason, usually for performance, legislative reasosn, to do so).

    Bear in mind that one of the guiding principles behind realtional databases is that you shouldn't duplicate data I suspect you may need an entity for stadium
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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