Sopwith Coach Hire Database and ERD

Description

Guidelines

The work consists of the design, implementation and population of a small database system that will form the basis of the report to be handed in.

You will be expected to work on your own and should submit deliverables arrived at independently using your own knowledge and effort.

Sopwith Coach Hire Database

Sopwith Coach 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 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. 

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 name, address, email address and contact number.

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) lists 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.

Booking

The company wishes to record details of all bookings made by their customers. This will include detailed information of each trip a vehicle is booked for.

Each customer is charged the hourly rate for each vehicle (you may assume that no discounts are given for long term hire).

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 a 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 driving in a day.

Licence

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 order to drive a small passenger-carrying vehicle with nine to 16 passenger seats, a driver must have a D1 code on their licence. To drive any bus/coach with more than eight passengers, a driver must have a D code. Some drivers have only a D1 code and are therefore only allowed to drive the minibuses. Other drivers have both codes on their licences and can be used for any of the company’s hired vehicles. 

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.

Sopwith Coach Hire Ltd is required to keep a complete record of all test types, dates and results for each driver.

 

C Tasks and Grading

1. 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). (30 marks)

2. Produce a Data Dictionary for the ER diagram in part (1). (25 marks)

3. Implement the set of relational tables created in part (2) using Oracle.

You must include your CREATE TABLE statements in the report. (10 marks)

4. Populate the tables with relevant data.

A screen shot of the populated tables must be included in the report. (10 marks)

5. A full discussion of the constraints used, covering the three types: entity, referential and column. Explain why you used them and how they ensure that the integrity and consistency of your data is maintained. (10 marks)

6. Write 3 queries to demonstrate that your database meets the data requirements of the system. You have to work out what you consider to be the best 3 queries that demonstrate the database meets the data requirements of the system. In your queries you are expected to use a range of SQL queries. ***

A screen shot of the query output must be included in the report.

*** If you produce 3 queries that are simply SELECT * FROM, then you will receive no marks. (15 marks)