University of Sussex Autumn 2018 Informatics
Databases Assignment 1 (Deadline 25.10.18, 4pm)
The coursework assigned below must be submitted online as one single pdf or doc file on Canvas by 4pm on Thursday, 25 October 2018.
You must work on this assignment on your own. The standard Informatics rules for collusion, plagiarism and lateness apply. Any cases of potential misconduct discovered will be reported and investigated.
A decent presentation of your work is expected. Please stick to the notation used in the lectures and produce one file at the end for your entire contri- bution.
All THREE questions must be answered.
1. Provide an Entity-Relationship Model for a motor sport fan, based on the description in Table 1 at the end of this paper.
Present the model in the form of an Entity-Relationship diagram in Chen notation. Make sure to identify special attributes (including keys) in the diagram and include participation and cardinality con- straint annotations for your relationships.
Please list and explain any additional assumptions you have made when you considered the specification incomplete. There should not be (m)any such assumptions though. Do not invent additional features or attributes as those will be marked as wrong.
Marking Criteria
The following criteria will be used to mark your model:
- extent to which your model is correct, minimal (no feature mod- elled twice), complete and expressive
- correct use of Chen notation (including special attributes)
- correct cardinality and participation constraints (including nota- tion)
1
• readability and presentation of your answer. You can design the diagram in any software system you like, as long as you stick to the notation used in the lectures and embed it into your sub- mission. For scanned hand-written diagrams at least 10% points will be deducted depending on the appearance. The Links sec- tion of our Canvas Database site will suggest some dedicated tools which might not support all our notation, so make sure that you find ways to fix this wherever required if you use such a tool.
[53 marks]
- For every of the THREE relationships in the Entity-Relationship Dia- gram in Figure 1 on the next page, explain and justify the cardinality and partiality constraints following the guidelines in lectures and ex- ercises.
Marking Criteria
The following criteria will be used to mark your answer:
- clarity of your explanations
- extent to which your explanations justify the cardinality con- straints used in the diagram
- readability and presentation of your answer.
[24 marks]
- Give a Relational Model that corresponds to the Entity-Relationship Diagram in Figure 1. You must present this model as a Relational Database Schema (see Lecture 6) and not as a diagram. Primary and foreign keys must be declared where required. Please keep the names of attributes and role names where required and do not replace them with different names. Provide NOT NULL constraints only where semantically justified by the Entity-Relationship diagram.
Marking Criteria
The following criteria will be used to mark your Relational Database Schema:
2
• semantically correct schemas
• semantically correct attributes in schemas
• correct schema notation
• semantically correct declaration of primary and foreign keys • semantically correct and justified NOT NULL declarations • readability and presentation of your answer.
[23 marks]
Figure 1: Entity-Relationship Diagram for Questions 2 and 3
3
Specification
A race has a name and must take place at a single race course at a specific date and at a specific starting time. A race has a certain num- ber of laps (around the race course) and a total length in miles. No two races of the same name take place on the same day.
A race course has a unique name, a location, and a length in miles.
For each driver, we keep the name, consisting of first and last name, nationality, date of birth and a unique driver identifier. Drivers always belong to a single racing team. We need to find out which driver drove which car in which race. We also need to store in which place they ar- rived at the finish (non-finishers are recorded as arrived in 0-th place). Drivers drive at most one car in a race but not all drivers do. There are no driver swaps during a race but at different races different drivers may be driving the same car. The type of tyres on the car at the start of the race is recorded as well as the car’s racing number for that particular race.
Cars have a unique identifier and must belong to a racing team. The make of the engine of a car is relevant too. Not every car is necessarily driven in a race.
A car may complete a lap of a race in which case the lap time and fuel consumption are to be recorded. A car may have a pit stop during a lap in a race, in which case the duration of the pit stop and the items that have been changed during the pit stop (tyres, front nose, etc.) are recorded. A car may retire in a lap of a race, in which case the reason for the retirement is to be recorded. In all these cases, we need to know in which lap of which race this occurred.
Important aspects of a racing team are its name and the address of the team’s headquarter consisting of postcode, street name, house num- ber. Racing teams may be entered on the database before any drivers or cars are assigned to them.
Table 1: Description of the motor sport fan’s database for Question 1
4