Aims
To gain experience in designing a database using Entity-Relationship (E-R) Diagram, Normalization, and Relational Database modelling techniques.
Learning Objectives
In the process of this assessment task you will:
• plan, schedule and execute project tasks with a view to improving your personal productivity;
• gain awareness of the typical challenges related to the design of practical databases;
• learn that database design is an iterative process; and
• use the E-R Diagrams, Normalization techniques, and Relational models to develop elegant conceptual and logical models for a database;
Due date: Week 7, Monday, 5pm
Submit the individual work file, named 'a1- Surname_ID .pdf', by Week 7, Monday, 5pm via Moodle.
Late submission:
Any submission after the due date will receive a deduction of 10% per day, this includes weekends.
Marks: This assignment has a total 100 marks and it is worth 20% out of the total assessment.
Extensions: An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor's certificate, with a maximum limit of one week.
Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee.
Designing a Database for Commonwealth Transport Services
Introduction
Commonwealth Transport Services (CTS), a private company, specialises in providing transportation services to various events. Recently, it has been sub-contracted by the Commonwealth Games Federation to transport officials during the Commonwealth Games in 2018. The company now realizes that it needs a modern computerized system for efficient and reliable management and documentation of its services. CTS has hired you as a database professional to design and develop a database system which can meet CTS’s needs as specified below.
Business Requirements
CTS owns a fleet of vehicles, which vary from sedans to vans. The table below shows a small sample of CTS's vehicle assets:
Vehicles require regular maintenance and repair. When a vehicle is maintained or repaired the vehicle's odometer reading, the date when the maintenance or repair is started, the final cost, and a brief description of the action is recorded. CTS also records the nature of the action as Maintenance (M) or Repair (R).
CTS maintains the list of countries participating in the Games and the major languages spoken in those countries. A country may have several spoken languages. Countries are identified by the ISO3166-1 two-character codes. Similarly, CTS uses ISO639-1 two-character codes for languages. For example, the codes for the country Australia and language English (EN) are AU and EN, respectively.
Games officials from all participating countries use CTS's services. CTS records the name of the country that an official is representing, the official's Commonwealth ID (8 characters), his/her name, role at the Games (e. g. head-coach, judge, physician etc.) and the official's preferred language. An official may use CTS's services multiple times (even during a single day); the only limiting factor is whether a suitable vehicle is available at the time they wish to travel.
CTS Vehicles are driven by the company's drivers. The driver's name, licence number (18 characters in length) and the level of clearance granted to the driver are recorded. The clearance levels are digits from 1 to 4 representing the security clearance of the driver (4 is the highest level of clearance). Some drivers (Medical Specialists) have had special first aid training – for these drivers CTS records the first aid level the driver reached (A to F) and the date on which the driver qualified for this level. Some drivers (Security Specialists) have attended special security training for the transport of VIPs. For such drivers the date the training was completed, the certifying authority (e.g., Police, Security Services, and Private Trainer) and the security level reached (S to W) are recorded. A driver may have no special training qualifications, qualifications in one area only (first aid or security) or both. CTS employees are familiar with the meaning of these various codes (1 to 4, A to F and S to W) and thus they do not require any explanation of these codes in the system, just the actual codes. In addition, CTS records (using ISO639-1 language codes) the languages that a driver speaks – some drivers speak several languages. The employee organising a booking will try to match the language of an official with that of the driver.
Before using a vehicle, it must be booked. This booking will take place via a computer program to be created; you are not concerned with this program. However, the back end database needs to support such an activity. Transport bookings require the assignment of a suitable driver to a suitable vehicle matching the needs of the official. The intended start date and time and projected end date and time are recorded when a booking is first placed. The pickup location and drop off location are also recorded. Locations are specified using its address (street number and city) and type (Hotel, Airport, and Aquatic Centre, etc.). Some locations may not have name.
After the completion of a trip, the booking reference number, the actual start date and time and actual end date and time are recorded. CTS also records the start odometer reading and the end odometer reading. To assist vehicle assignment, CTS requires that the new system be capable of indicating whether a vehicle is currently available. The vehicles that are out on a trip or out of service due to maintenance or repair should be flagged as not available.
Assessable Tasks
From the CTS’s business requirements specified above, prepare a document according to the followings:
1. A completed copy of the SITE Assignment Coversheet.
2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.
3. A table of contents and automatically generated page numbers.
4. An entity relation (E-R) diagram using Crow’s Foot notation. The diagram should include:
a) all entities, attributes, and relationships (including names) ;
b) primary keys (underlined) and foreign keys (italic) identified;
c) cardinality and participation (optional / mandatory) symbols; and
d) assumptions you have made, e.g., how you arrived at the cardinality and/or participation for those not mentioned or clear in the business description, etc.
5. Normalization of the relations which identifies:
a) dependency diagram for each relation
b) the level of Normalization achieved for each relation
c) the reasons for any relation that is maintained NOT in 3NF.
6. Relational data structures that translate your E-R diagram which includes:
d) relation (table) names,
e) attribute (column ) names and field types (as required by WAMP),
f) primary and foreign keys identified;
7. A bibliography, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.
Assessment Criteria and Marking Overview
Tasks Marks
1.
Presentation
How clear and well-presented your submission is. 10
2. E-R diagram
Adherence to the standard of the course, assumptions made, inclusion of correct primary and foreign keys, appropriate entities, relationships, and attributes. 50
3.
Normalization
Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 20
4.
Conversion of E-R diagram to relational schema
Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys. 20
Total 100