ERD assignment

csc3400 { Database Systems Assignment 3 Page 1 Database Modelling Answer the following questions in a word processing document, export the document as a PDF file, and submit via the course web site. Question 1. 12 marks 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, last name, and date of birth, which together are unique) are employed by a team which is identified by a name and is managed by the team boss. The team boss is not a rider and is identified by his/her name. 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. A team also assigns one specific rider who acts as the team leader in the race. 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. For each stage that a rider participates in, his total time spent from start to finish is recorded. Stages are either Mountain, Flat, or Team Time Trial stages. There are no other kinds of stages. 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 a team time trial we record which team won the stage. (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 generalization/specification will be involved in the ER diagram. (b) Construct a relational schema for the ER-diagram. Make sure that you...