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:

a) relation (table) names,

b) attribute (column ) names and field types (as required by WAMP),

c) 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.