Scenario For each courier the company keeps track of the co

Scenario:

- For each courier, the company keeps track of the courier\'s name (first name and last name), courier\'s address (street, city, state, postal code), courier\'s driver license number, and mobile telephone number.

- For each motorbike, the company keeps track of a unique motorbike ID number, motorbike license number, and motorbike model.

- A courier can be assigned to more than one motorbike. A motorbike will have multiple couriers but only one courier at a time.

Based on the scenario,

A. Identify and list the entities and attributes of those entities.

B. Identify and list the functional dependencies.

C. Create a set of a 3NF (normal form) relations using the shorthand notation. Be sure to identify all primary and foreign keys appropriatley.

Solution

A) Here the entities are Courier and motorbike . Attributes for those entities are :

Courier - Courier name, Courier driver address , Couriers driving licence number , mobile telephne number .

Motorbike : motorbike id number , motobike licence number , motorbike model

B)Functionala dependencies are - one to many depenmdency ( a courier can be assigned to more than one motorbike . many to one ( a motorbike can have multiple couriers but only one motorbike at a time

c) As per definition , 3NF states that all column reference in referenced data that are not dependent on the primary key should be removed.

primary keys is driver licence number and foreign key is motorbike id and motorbike licence number which references primary key driver licence number in the courier table .

In the motorbike table, we have motorbike model attribute which is not useful for courier functions . it is irrespective of it . So, motorbike mode lhas to be removed

courier-->courier name, address, driver licence number , telephone number

motorbike-->motorbike id, motorbike licence id ( We removed motorbike model attribute using 3NF)