A merchant shipping company wishes to use a computer database to help with its

operations. There are three distinct areas in which such assistance is desired.

First, it is required that details of the company fleet be recorded. The fleet

comprises a number of types of ship: cargo vessels have a deadweight $($cargo capacity$),$

class of cargo carried $($e$.$g$.$ dry, container, grain$)$ and number of onboard loading derricks;

oil tankers also have a deadweight, and a class of petroleum for which they are equipped;

and passenger vessels have a maximum passenger capacity for each of $$first$$ and

$$tourist$$ classes, and a number of staterooms. All ships have a crew, which comprises, a

master, a number of officers and seamen. They have length, beam, draught and

displacement, and burn particular types of fuel. They also have a maximum speed and

endurance. Each ship has a home port, which may or may not be the same as its port of

registration.

Secondly, the officers and crew have to be paid! Their salary depends on their

qualifications and current appointment $$ to which ship and in what capacity. There is

also a $$bounty$$ payment made in respect of each trip made, which depends on the length

of the trip and on the individual$$s length of service with the company. In addition, ship$$s

officers who are sailing in less than their customary capacity $($e$.$g$.$ a master sailing as a

first mate$)$ receive an extra payment.

Finally, the company is required by its insurers to maintain a record of the

position of each vessel; its port and date of departure, destination and estimated date of

arrival; number of officers, total number on board, and total value of cargo carried, if any.

Ships currently in dock for repairs must also be included in this catalogue.

Required

$1.$ Construct an ER diagram from the description above. Include required attributes

to make the data storage meaningful. $[40$ marks$]$

$2.$ Use your research initiative to determine three other entities $($and their attributes$)$

that are of importance to this model.

a$.$ Write a paragraph explaining their importance and relationship with the

other entities. $[10$ marks$]$

b$.$ In a separate diagram show how they should added to your ER diagram.

$[10$ marks$]$

$3.$ Based on the final E$-$R model developed part $2$ above define the file structure

using relational notation. $[20$ marks$]$