Consider that a journalist wants to create a simple database for covering the 2010 Tour de France cycling race. The race consists of a number of stages identified by a starting and destination city. Each stage is completed on a single date, and goes over a specific number of kilometers. Riders (first name and last name, which together are unique) are employed by a team which is identified by a name and is managed by the team boss. Each team has a sponsor (identified by the unique sponsor name) and each sponsor is only allowed to support one team. The team boss is not a rider and is identified by his/her first and last name together. Each team is led by a team leader who himself/herself is also a rider. A team needs to have at least one rider to enroll in the race. Each rider must belong to one and only one team in the race. No two teams are allowed to have the same boss. Riders participate in a number of stages (not necessarily all, as they could give up after a number of days) and it is possible that a rider is absent from all stages. It is also possible that there are not any riders participating in a stage. For each stage that a rider participates in, his total time spent from start to finish as well as the final position achieved by the rider for the stage are recorded. Stages are either Mountain or Flat, but there may be other kinds of stages for other cycling races. For mountain stages the database keeps track of the grade (= difficulty level), the number of mountains in it, and the best climber (a rider). For a flat stage we record the number of intermediate sprints. For the information of the Best Climber of Mountain Stage, please model it as an attribute instead of a relationship.

Question :

A) Draw an Entity-Relationship diagram for this database using UML notation. Be sure to include all the entities mentioned above, together with attributes (including primary key attributes), relationships and multiplicity constraints. Note that generalisation/specification will be involved in the ER diagram.

B) Please classify the relationships existing in the produced ER diagram into different categories such as 1..1, 1..* , *..* and generalisation/specification relationships, if any. Please list the relationship(s) under each applicable category

Please follow carefully the following guidelines when you finish this question:

Please ONLY include the following entities in the ER diagram: Team, Rider, Sponsor, Stage, Boss, Mountain and Flat. It is possible that there are more than one relationship between two entities in the diagram;

Please only use the attributes given in the instruction. Please do not make up any other attributes yourself or use other names for the attributes;

Please only use the drawing tools (e.g., Microsoft Word or Visio) that are available for drawing up the ER diagram. It is not allowed to use those tools for automatic generation of the ER diagram;

Always highlight the primary keys for the entities created in the ER diagram and the relations transformed from the ER diagram;

Please do not use any artificial primary key (such as stage#). All the natural primary keys are provided in the instruction.

The abbreviated schema description, with highlighted primary keys, will be sufficient for presenting the translated relations.

Explanations should be provided for the foreign keys added to the relations if any.

Please follow the UML approach used in the slides and the textbook to draw the ER diagram.