For an improved design of the database model, the company would like to keep additional information on all staff who actually worked for each trip. For each coach trip, the main driver is an essential staff involved but a number of other employees also joined the trip to conduct various jobs - including assistant drivers, mechanics, baggage handlers, etc. Some sample records including such information are as below:

[Instruction]

 

All tables in the database should be at least in 3NF and without multi-value attributes. You need to use Crow's foot notation. Your diagram should show all relevant attributes including primary and foreign keys. Indicate all connectivity, cardinalities, and optionality. (Feel free to make your own assumptions for the choice of optionality in your ERD. You are not required to write all assumptions explicitly).

 

You can use one of the following ways to present/draw your ERD for this question.

 

1. Draw your ERD on a piece of paper, use your mobile phone to take a photo, transfer the photo to your computer as an image file, and attach the file here using the 'Insert Local Files' menu.

Attribute Name TRIP-CODE TRIP-DATE TRIP-CITY TRIP-MILES MAX-PAX (Capacity) DRIVER ASSISTANT DRIVER MECHANIC BAGGAGE HANDLER CUST-NUM CUST-NAME BUS-REG-NO BUS-MODEL-CODE BUS-MODEL- SEATS Sample Value Sample Value Sample Value | Sample Value 38633 38635 38636 38637 18-July-2020 Perth 1300 28 Thomson 8133 Sally 5101H BMW-3000 60 BUS-MODEL-CHG-MILE $13.63 18-July-2020 Melbourne 1500 30 Donny Stewart Ched Trevor 3818 Janet 5231J CV-580 50 $18.33 19-July-2020 Sydney 8000 28 Stewart Thomson 3318 Selene 5101H BMW-3000 60 $13.63 20-July-2020 Sydney 6830 50 Thomson 8133 Sally 8763P BMW-3000 60 $13.63