1. Background

"Northampton Wedding Cars" is a Northampton based company, which specialises in the hiring of driven limousines for weddings and other celebrations. Recently, the booking staffs have become critical of the database software, which the company purchased to cater for booking vehicles and drivers. Ultimately they would like customers to be able to check for limousine availability over the Internet and then finalise the hire at the company's premises.

The company you work for has been approached by the director of "Northampton Wedding Cars" with a request for the development of a bespoke software system to replace the existing one. As part of a feasibility study, you have been asked to develop a database to cater for the hiring of limousines and their drivers by new and existing customers.

When a new customer wishes to hire a limousine s(he) visits the company's premises. An existing customer might instead book over the telephone. A booking clerk will then search for an appropriate vehicle, or vehicles, which will be available between the required hire dates.

At a recent meeting with the company's representative the following list of minimal requirements was agreed:

• A limousine or limousines may be hired by an individual customer or by a company for a minimum of one day.
• There are a few different models of limousine owned by the company. They have different daily hire charges.
• The cost of hiring a limousine is calculated by adding the daily charge to an amount for the number of miles travelled.
• A customer must specify the starting location for the hire and an approximate distance to be travelled.
• A customer may pay for the hire by means of a recognisable and valid credit/debit cards. The customer may only use a single method of payment for a particular hire (that is, a customer may not pay part of the hire price by, say, Visa and part by, say, Master Card).
• Although hiring a vehicle may be done at a distance (e.g. by telephone), for the hiring to be complete the customer, or the customer's representative, must sign appropriate documents at the company's premises.
• A customer may hire more than one vehicle at a time provided each vehicle is required for the same hire period. The system to be built should identify a single hire as being a contract between the company and the customer concerning one or more vehicles.
• Each hired vehicle will have a driver employed by "Northampton Wedding Cars".
• The cost of hiring a driver is included in the price for hiring a particular vehicle.
• A hire must be authorized by a named booking clerk.

2. Sample DML commands

Listed here are some examples of query transactions that should be supported by wedding car rental database system:

1) Populate arbitrary data to the database;
2) List the details of all vehicles in the company and sort the make alphabetically;
3) Add a new customer to the database;
4) Update a vehicle's registration number;
5) Show the status of a particular vehicle;
6) Display the driver's name of a particular hire;
7) Delete a damaged vehicle from the database;
8) Display total number of vehicles, total amount of fees to be paid for a particular hire;
9) List all overdue vehicles and the customer contact information;
10) Calculate the total fine for an overdue hire;
11) Find the most popular vehicle for hire so far;

2. Student activities

(It is taken for granted that you will make appropriate backups of any code/data within this assessment.)

o Investigate the data, which you believe to be appropriate to support the business requirements as outlined above. You are encouraged to investigate similar on-line systems and to discuss requirements with classmates (with the knowledge however that the work which you submit must be your own).

o Design and refine Entity-Relationship (ER) models, which represent the identified interrelated data. This will require you to consider the entities, attributes and relationships. You are expected to use the MySQL Workbench software for this assignment.

o Create database schema either manually or with the help of MySQL Workbench generate SQL DDL commands to build a MySQL database corresponding to your final ER model; modify the generated schema when necessary.

o Populate the table records with token records (arbitrary data).

o Answer and explain all the queries in section 2.1. Your answers need to be included in the report.

3. Deliverables:

Assignment work is marked electronically. The feedback will be given electronically in your report, too. Your submitted report will consist of at least the following elements:

o Your final ER diagram.
o Any assumptions you made and justification/explanation of your ERD.
o All SQL DDL commands used to create the database:

• Create table commands (including keys, constraints, etc.)
• Alter table commands (if necessary)
• Create view commands (if necessary)
• Etc.

o The screenshots of arbitrary data in each of the tables.
o The screenshots of the list of SQL DML commands for the sample queries and the outputs.
o Immediately after each screenshot of the DML commands, explain your answer.
o In addition to the report, you are required to have a video demo of your assignment. The demo should be about 5-15 minutes in length (no longer than 20 minutes), and should cover all your work in a logical way; your voice needs to be clear for the marker to hear.
o The video recording software is of your choice. The following link lists some good ones for Windows and Mac:

http://download.cnet.com/windows/video-capture-software/?licenseType=Free

http://download.cnet.com/mac/video-capture-software/3150-13633_4-0.html?licenseType=Free

o If you, for some reasons, can't make the video demo, you can request in advance for a face-to-face demo of your work during the assessment week. If your request is granted, due to limited time, you will only be given up to 5 minutes for a face-to-face demo.

4. Submission and Assessment Details

The assessment will be based on the following two parts:

• The final report, which has to be submitted via Turnitin on NILE
• A video demonstration, which explain your work in a logic and clear way.
• A good way to submit the video demo is to upload it on YouTube as private video and share with me.