“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: 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 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; (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. 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: londonessays.com londonessays.com o The assessment will be based on the following · 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. Subject – specific Skills g) Design simple examples of normalised relational metadata. h) Design simple examples of metadata for storing a range of different multimedia data in a relational database. i) Design appropriate metadata for fabricating various simple data structures from relations. j) Given a particular client/server database requirement, design, build and test adequate metadata and/or software components for deployment on a client and/ or server. k) Measure simple examples of performance characteristics of a particular client/server database application. Key Skills l) Learning to learn: recognise major weaknesses and accept the need for further work in those areas m) Communication skills: outline the nature and function of any designed metadata and/or software component n) Numeracy: calculate simple examples of predicted performance of a required application from a simplified prototype.