Please read the following scenario;


A country bus company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several buses. Each route passes through a number of towns. One or more of the drivers are allocated to each stage of a route which corresponds to a journey through some or all of the towns on a route.Some of the towns have a garage where buses are kept. Each of the buses is identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route.Drivers have an employee number, name, address and sometimes a telephone number.



Below is a preliminary entity relationship diagram for this scenario.




The optional relationship(s) have not been identified in the above diagram. Identify an optional relationship that has not been identified in the diagram.


 

  • Each town does not always have a garage – so this is an optional relationship. The candidate may state the relationship or produce a diagram. Allocate 2 marks where the correct relationship has been clearly identified.


 

Using bracketing notation, list the attributes for each of the entities. Clearly identify the primary keys and foreign keys.


 

Bus (registrationNumber, make, size, numberOfPassengers, routeNumber, garageNumber)

Route (routeNumber, averageNumberOf Passengers)

Stage (stageNumber, routeNumber)

Town (townName, stageNumber)

Driver (employeeNumber, name, address, contactNumber, stageNumber)

Garage (garageNumber, name, address)