Database Modelling

Question 1.

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 kilometres.

For the information of the Best Climber of Mountain Stage and the Winning

Team of TeamTimeTrial Stage, please model them as attributes instead of relationships.

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

(b) Construct a relational schema for the ER-diagram. Make sure that youcorrectly translate Specialization and Many-to-Many relationships.

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

- Please ONLY include the following entities in the ER diagram: Team, Rider, Stage, Boss, Mountain, Flat and TeamTimeTrial. The last threeentities correspond to the three stage types. It is possible that there aremore than one relationship between two entities in the diagram;
- Please only use the attributes given in the instruction. Please do not makeup any other attributes yourself or use other names for the attributes;
- Please only use the drawing tools (e.g., Microsoft Word or Visio) that areavailable for drawing up the ER diagram. It is not allowed to use thosetools for automatic generation of the ER diagram;
- Always highlight the primary keys for the entities created in the ER diagramand the relations transformed from the ER diagram;
- Please do not use any artificial primary key (such as stage#). All thenatural primary keys are provided in the instruction

Question 2.

Consider the following relation schema for table R:

R(ENo;DNo; PNo; EName; DName; PName; Edob; Dmanager; PCity; PCountry; hours; rate)

Attributes starting with "E" refer to Employees, those starting with "D" refer to Departments, and those with "P" to Projects. Employees, Departments, and Projects are identified by unique numbers. Each department has only one manager, but an employee can be managers for different departments. A department manager is represented by his/her name. The number of hours and hourly rate of pay for an employee to carry out a project are determined by himself/herself and the project. There may be multiple projects that are conducted in a department
and multiple departments can be involved in a single project as well.

Any employee can be affiliated with a few different departments and work in multiple projects at the same time. A project may involve multiple employees. Names for employees, managers, departments and projects are not generally unique. A project will only be conducted in a single city. Multiple cities from the same country may appear in the table, however, cities are uniquely named within and across all countries.

Answer the following questions:

(a) Identify the Functional Dependencies in R. Be sure to only include functional dependencies that satisfy the following 4 rules: 1) Only include non-trivial FDs; 2) Minimize the determinant (LHS), that is, only include full FDs; 3) Maximize the RHS; and 4) Only include FDs that cannot be derived from other FDs using Armstrongs axioms. Please refer to Page 26 of the Module 10 lecture notes for the details of the above requirements.

(b) Identify the super keys of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the keys. Intermediate steps in this process should be summarised. There may be many different super keys of R. It would be great if you can present all of them in a smart way rather than enumerating them one by one.

(c) Assume that R is in 1NF. Now normalise the relation to 2NF, 3NF, and BCNF. Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient.

While it is recommended to answer this question on just one page, you should use no more than two pages for this question.