Create a logical ERD for each of the problems on the following pages using the crowsfoot format. Be sure that each entity has the entity name at the top of the box, the primary key attribute or attributes in the middle of the box, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box. The ERD should not have any M:N relationships and all attributes should be placed within an entity. Each entity must have a primary key defined. A primary key may consist of one or more attributes. Please include all required foreign keys and denote the foreign key(s) with the notation (FK) on the ERD. You do not have to differentiate between an identifying or non-identifying relationship. It would be very helpful to include verb phrases for each relationship, but verb phrases are not required on the ERD. If you need to add any assumptions for the business rules, please note those on your diagram.

2. The purpose of this database is to keep track of vehicle reservations and actual use of vehicles in the motor pool of a university. The university owns a group of vehicles that are available to employees for officially sanctioned travel. The vehicles may be used for traveling to off-campus meetings and events. Every time an employee uses a vehicle, it is considered a trip. Here is some information about the application:

Employees (uniquely identified by an employeeID) are encouraged to make reservation requests for vehicles. An example of the data required for a reservation request is provided in the spreadsheet on the next page as Figure 1. Figure 1 shows data for four different reservation requests. An employee may have multiple reservation requests for vehicles, but a given reservation request is filled out by one and only one employee.

Assume for this application that the only pieces of data stored about an employee (other than the employee ID) are the employee name and phone number.

Vehicles (uniquely identified by vehicle license #) in the university motor pool are of a particular type. Samples of vehicle types are shown in Figure 1. The university standardizes the type (description) of vehicles available. Each vehicle can be of only one type, but there are potentially many vehicles of the same type in the motor pool. For example, vehicle # WGN176 is a Van 8 Passenger vehicle type. There are at least six other actual vehicles in the motor pool that are the type Van 8 Passenger. Assume for this application that the only pieces of data stored about a vehicle (other than the vehicle #) are the vehicle type and vehicle year.

It is possible for an employee to use a vehicle (make a trip) without making a reservation request; a trip is not always related to a reservation request.

When an employee takes a vehicle from the motor pool, this is considered a trip and data must be recorded about the trip. A given trip is represented by one row in the spreadsheet shown in Figure 2. At the start of the trip, some of the data in Figure 2 is recorded: The vehicle license #, the name and ID of the employee who will be driving the vehicle for the trip, the date and time that the vehicle was checked out, the employee name of the employee who checked out the vehicle to the employee who was actually driving the vehicle, the intended destination of the trip, and the number of miles on the odometer at the start of the trip. It is safe to assume that a given vehicle can start only one trip at a given date and time. You can also assume that the date and time can be stored together in one attribute.

When an employee returns a vehicle to the motor pool at the end of the trip, then the rest of the data in Figure 2 is recorded: The date and time that the vehicle was returned, the number of miles on the odometer when it was returned, and the employee who checked in the vehicle (may be different than the employee who checked out the vehicle, and will definitely be different than the employee who actually drove the vehicle).

An employee is an employee - employees who take trips are no different than employees who check in or out vehicles.

Each employee (except the president) is managed by a maximum of one other employee. Some employees are managers and some arent managers. The database should keep track of the manager of each employee.

Fiqure 1. Reservation Request Data Employee Name EmployeeExpectedExpectedVehicle Destination Reason for Trip ID Departure Return Date 9/14/2017 Type Required Date Janice Springer Marty Brown Janice Springer Fred Martinez 17 34 17 98 Berkeley, CA Debate Tournament 9/12/2017 9/05/2017 9/29/2017 10/2/2017 an- ssen Van- 12 sseng Tour of Intel Manufacturin Sacramento, CA Bellingham, Debate Tournament WA Carson City, NV 9/05/2017 10/04/2017 Van 12 sseng Attend NV State Finance Committee meetin 10/2/2017 Sedan Figure 2. Vehicle Trip Data Vehicle DriverEmployee ID Date/Time EmployeeDestination Odometer Date/Time Odometer Employee License # | Employee WGN176 Janice 899ULX Marty Brown 172AAX Cora M Checked Out Check Out Start Returned Return check In Name 9/14/201766,099 5PM 9/05/2017 15,008 9PM 9/09/201746,667 2:30PM 17 9/12/2017 8AM 9/05/2017 6AM 9/09/2017 9:30AM Ken Blanchett Berkeley, CA 65,128 Ken Blanchett Sacramento, 14,887 Ken Blanchett Elko, NV Ken Blanchett Springer ason CA Blackman asters 30 45,515 ason Blackman