Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Did I do this E-R diagram correctly?

    Hey guys, I'm a student in an intro to databases class and I'd like to make sure I've done this problem correctly. Here is the problem text:

    When discussing candidate keys in lecture #1 I used an example of profes-
    sional sports teams, where the entities are teams, each of which has a name,
    a sport, and a city. This problem expands on that example.
    In addition to the \Teams" entity set, we'd like to model a relationship set
    giving information about which teams play each other during a given year
    and the results of those games. The data will represent a single year. The
    ordered pair (t1, t2) will be in the relationship set \Plays" if team t1 plays
    team t2, where t1 is the home team and t2 is the visting team (i.e. the game
    is played at t1's home stadium). We'd like to model the date on which the
    teams play one another and (once the game has taken place) each team's
    score in that game. We will initially assume that t1 plays t2 at most once
    at t1's home stadium. In addition to the name, sport, and city, we are
    interested in the total number of wins and total number of losses for each
    team.
    Draw an E-R diagram representing the Team entity set and the Plays re-
    lationship set. You may select either candidate key as the primary key of
    Teams.
    How do things change if there can be more than one game between t1 and
    t2 at t1's home stadium?
    This is the E-R Diagram I came up with using name and city as the candidate key for "Team":

    http://img5.imageshack.us/img5/8348/hw1m.jpg
    I've also attached my solution in-case image shack doesn't work.

    Also, if things are changed where t1 and t2 and can play multiple times, I'd simply have to make "home_score", "visitor_score", and "date" all multi-valued...right?

    I appreciate you guys looking over my solution, want to make sure I hand it in correct
    Attached Thumbnails Attached Thumbnails HW1.jpeg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dgutie01 View Post
    This is the E-R Diagram I came up with using name and city as the candidate key for "Team":
    everything looks okay except for the arrows between Team and Plays

    Quote Originally Posted by dgutie01 View Post
    Also, if things are changed where t1 and t2 and can play multiple times, I'd simply have to make "home_score", "visitor_score", and "date" all multi-valued...right?
    NOOOOOOOOOOOooooooooooo.......

    maybe your class hasn't covered first normal form yet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    Quote Originally Posted by r937 View Post
    everything looks okay except for the arrows between Team and Plays

    NOOOOOOOOOOOooooooooooo.......

    maybe your class hasn't covered first normal form yet
    Thanks for the quick reply! Looking over the arrows again, I think I need to make the lines between Team and Plays have arrow heads on BOTH ends. To indicate that this is a one to one relationship, and not a one to many. Is this correct?

    And you're right, we haven't covered first normal form yet. But there is another lecture before this homework is due so I'll hold out till then for that part of the question.

    Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dgutie01 View Post
    Looking over the arrows again, I think I need to make the lines between Team and Plays have arrow heads on BOTH ends. To indicate that this is a one to one relationship, and not a one to many. Is this correct?
    what exactly does the arrowhead ~mean~ in your relationships?

    and yes, both of these are one-to-many -- one team plays many games as home team, and one team plays many games as away team

    Quote Originally Posted by dgutie01 View Post
    And you're right, we haven't covered first normal form yet. But there is another lecture before this homework is due so I'll hold out till then for that part of the question.
    first normal form basically says never to use multiple values in a single attribute, all attributes must be atomic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    Quote Originally Posted by r937 View Post
    what exactly does the arrowhead ~mean~ in your relationships?

    and yes, both of these are one-to-many -- one team plays many games as home team, and one team plays many games as away team

    The cardinality is confusing me. I think because, this relationship set only includes one type of entity. It's not a relationship between a team and a game, but a relationship between a team and a team.

    Since, for the initial condition of the problem, a team can only plan another time at most once in its home stadium...argh...the solution is in my head I just can't put together

    For reference as you asked, the arrows are defined as follows:

    One To Many: E1 <------ Relation ------ E2
    Many to One: E1 ------> Relation ------- E2
    Many to Many: E1 ------- Relation ------ E2

    There is another, "Total participation" which is defined by double lines. It means that, at the very least EVERY entity in a set must participate in the relationship. As opposed to the three above, where an entity could possibly not be partaking in the relationship.
    Last edited by dgutie01; 01-27-10 at 00:07.

  6. #6
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Change the diamond Plays to a square since it will be a table. Give it attributes of HomeTeamKey, VisitorTeamKey, HomeTeamScore, VisitorTeamScore, and DatePlayed.

    I have never seen a diagram like yours, but the relationship between Team and Plays is many to many. A team would play multiple times, either as the home team or the visitor team. Based on your description of the arrows above that means you would have two lines between Team and Plays: -----Home----- and -----Visitor-----

Posting Permissions

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