Database ER-Diagram

“Johnny Smith” is an East Coast company, with premises in Maine, New Hampshire, Massachusetts and Rhode Island. The company specializes in the hiring of small vans typically for moving household furniture and other similar sized goods. Recently, depot managers have become critical of the software, which the company purchased to cater for the van bookings. Ultimately they would like customers to be able to check for van availability over the Internet and then finalize the hire at one of the company’s four premises. The company you work for has been approached by the director of "Johnny Smith" with a request for the development of a bespoke software system to replace the existing one. Ultimately your company will develop all networked database software for the company. Develop an ER-Diagram to cater for the hiring of vans by new and existing customers. Although the software which you are to develop will be a stand-alone system, it must enable a customer (or one of their designated drivers) to arrange to collect a hired van from any of the four premises at the beginning of the hire period, and return the van again to any of the four premises at the end of the hire period. When a new customer wishes to hire a van he will visit one of 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 van or vans may be hired by an individual customer or by a company for a minimum of one day. - In both of these cases details of the intended drivers must be given prior to the van(s) being hired. - A customer may specify the depot from which each hired van is to be collected. - A customer may specify the depot to which each hired van is delivered after the hire period. - A customer may pay for the hire by means of a recognizable 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 one of 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. However, all vehicles within a hire are to be collected from a single depot and, on or before the agreed termination date, all returned to a single depot. - Each hired vehicle should have a designated driver or drivers. - A hire must be authorized by a named booking clerk. - If a vehicle is not returned in time, a fine (much higher than the daily rental) will be charged. Each vehicle is regularly checked in the service and maintenance workshop next to the Northampton depot. Servicing lasts no more than 2 days. A services/maintenance log is kept for each vehicle. New vans are purchased and then sold on after a period of no more than 3 years. A particular van may have one of several states, e.g. - On hire - Being maintained - Waiting to be collected by a customer - Waiting to be collected by one of the company’s drivers Etc. A particular van may be physically in one of several places, e.g. - On the road. - At a depot. - In the service garage. - At a showroom – having just been purchased. Etc. Your prototype database need not be concerned with: - Purchase of vehicles - Sale of vehicles - Employee details (other than identifying names)