Creation of a database system for Berwyn Buses Hire Ltd
Berwyn Buses Hire Ltd Database
Berwyn Buses Hire Ltd is a leading provider of coach and minibus hire in West London. The company was founded in 1982 and it has continued with steady expansion over the past 30 years. The company operates a fleet of vehicles which are available for hire. The majority of the company’s clients are local schools and colleges within West London that use the vehicles for school trips. However, vehicles can also be booked for private functions.
The Director has requested the creation of a database system to assist in the more effective and efficient running of the company. The Director has provided the following description of the current system.
- Create an entity-relationship model (using UML notation). Specify the cardinality ratio and participation constraint of each relationship type. State any assumptions you make when creating the ER model (if necessary).
System Overview
Customers
A vehicle can be booked by customers and a customer can book many vehicles. Details of customers need to be recorded, which include company name, contact name, address, email address and contact number.
Details of each booking must also be stored. For example, trip details, date required, destination and number of passengers. Details of each individual passengers do not need to be recorded.
Vehicles
The company has a large number of vehicles that range from a standard six seater MPV to a 49 seater coach.
Table 1 (appendix A) summarises the company’s fleet of vehicles. The company has more than one model type of vehicle. The hourly rate for hiring each vehicle is also displayed in this table.
Details of all vehicles will need to be stored including make, model, date of registration, registration number and colour.
Employees
The company employs a large number of employees and details of each member of staff need to be recorded. These details include name, address, home telephone number, date of birth, gender, job description, national insurance number and current annual salary. The majority of employees are drivers but the company also has a number of administrative staff that takes the bookings.
The database must be able to handle driver assignments. Each hired vehicle requires at least one driver. A second driver is required in the following situations: (1) If the driver cannot have a break of not less than 45 minutes after each 4.5 hours of driving, or (2) if the driver cannot have a 10 hour break after driving a maximum of 9 hours in a day.
In short, some bookings may require more than one driver.
Licence / Certificate
Each driver must have a valid driver’s licence with no penalty points. Details of each driver’s licence needs to be recorded. This will include the licence number and the expiry date.
In addition to a full driver’s licence, each driver must also hold a Passenger Carrying Vehicle (PCV) licence and Driver Certificate of Professional Competence (Driver CPC). The expiry dates and unique serial number for the PVC and CPC for each driver must also be stored.
Tests
The company operates a program that involves random alcohol or drug testing. Any driver may be required to take either the alcohol or drug test or both.
Berwyn Buses Hire Ltd is required to keep a complete record of all test types, dates and results for each driver.
Appendix A
Table 1
