Task 1 Topic Er Modelling Consider The Following Case Study For Er Modelling We Want To Create A Database For A Local 1 (246.34 KiB) Viewed 45 times
Task 1 Topic: ER modelling Consider the following case study for ER modelling. We want to create a database for a local soccer tournament. The tournament consists of several matches, each of which is identified by the date, the start time and the end time when the match is held. The database also records the names of the two participating teams (denoted as team 1 and team 2) and the score for each match. Each player (first name, last name, and date of birth, which together are unique) belongs to a soccer team which is identified by a team name and is managed by a team coach. The team coach is not a player and is simply identified by his/her name. No two teams are allowed to have the same coach. Each team is led by a captain or team leader who himself/herself is also a player. A team leader can only lead one team. A team needs to have a number of players. Teams participate in a number of matches, but not necessarily all of them as teams could give up some of the matches and it is possible that a team is absent from all matches. It is also possible that there are not any teams participating in a match. For each match that a team participates in, the final result (either "win" or "loss") will be recorded. Matches are divided into either preliminary and final matches. There are no other kinds of matches in the tournament and these two types of matches are disjoint. For a preliminary match, the group of the match will be recorded. For a final match, the database records the stage of the match (e.g., semi-final, final, etc.). Your task: Draw an Entity-Relationship diagram for this database using UML notation. Be sure to include all the entities mentioned above, together with attributes (includ- ing primary key attributes), relationships and multiplicity constraints. Note that generalization/specification will be involved in the ER diagram. Important Notes: Please follow these guidelines carefully: • Please ONLY include the following entities in the ER diagram: Team, Player, Match, Coach, Preliminary Match and Final Match. The last two entities corre- spond to the different types of matches. It is possible that there is more than one relationship between two entities in the diagram; • Use the UML notation to draw the ER diagrams (i.e., the way of ER modeling taught in the lectures and pracs); • Only use the attributes given in the instruction. Please do not make up any other attributes yourself or use other names for the attributes; • Indicate the primary keys for the entities created in the ER diagram. • Do not use any artificial primary key (such as matchnumber). All the attributes for natural primary keys are provided in the description.
Task 2 Topic: Mapping ER to relational schema Your task: Translate the ER diagram created in Task 1 to relations, represented by their schema. Make sure that you apply the correct translation strategies based on the type of relationships in the ER diagram. Separately list Foreign Key constraints. Notes: • The abbreviated schema notation (see Assignment 1), with underlined primary key attributes, will be sufficient for presenting the translated relations; • Produce separate relations for the superclass and subclasses if those were needed in the ER modeling: • Explanations should be provided for the foreign keys added to the translated relations, if any.
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!