Northampton Wedding Cars-Database

Question: (1) "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 companys 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 companys 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. (a) Your final ER diagram. (b) Any assumptions you made and justification/explanation of your ERD. (c) All SQL DDL commands used to create the database: (1)Create table commands (including keys, constraints, etc. (2) Alter table commands (if necessary) (3) Create view commands (if necessary) (d)The screenshots of arbitrary data in each of the tables. (e)The screenshots of the list of SQL DML commands for the sample queries and the outputs. (2)Sample DML commands Listed here are some examples of query transactions that should be supported by wedding car rental database system (a) Populate arbitrary data to the database; (b) List the details of all vehicles in the company and sort the make alphabetically; (c) Add a new customer to the database; (d) Update a vehicles registration number; (e) Show the status of a particular vehicle; (f)Display the drivers name of a particular hire; (g) Delete a damaged vehicle from the database; (h) Display total number of vehicles, total amount of fees to be paid for a particular hire; (i) List all overdue vehicles and the customer contact information; (j) Calculate the total fine for an overdue hire; (k) Find the most popular vehicle for hire so far; Answer: (a) ER Diagram ER Diagram describes the entities, their attributes relationship between various entities. There are four entities: Vehicle, Driver, Customer and Booking. Vehicle and Booking has one to many relationship. Since one vehicle can be booked multiple times on different dates. Similary, Customer can book more than one vehicle and Driver can be hired for multiple bookings on different dates, therefore, Customer and Booking has one to many relationship and Driver and Booking has one to many relationship. DDL Commands (2)DML Command (a) Populating Arbitrary Data (b) Details of vehicles alphabetically sorted by make (c) Adding new customer to database (d)Updating vehicles registration number (e) Displaying status of particular vehicle (f) Displaying the name of driver for particular hire (g)Deleting a damaged vehicle from database (h) Counting vehicle and total fees for particular hire (i) Displaying overdue vehicles and customer contact information (j) Displaying total fine for overdue hire (k) Displaying most popular vehicle