Create a logical ERD for each of the problems on the following pages using the crows foot format discussed in class. 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 foreign keys and other attributes in the bottom of the box. Lines should separate each part of the entity box.

Follow these instructions about each ERD:

• The ERD should not have any M:N relationships.
• Each entity must have a primary key defined. A primary key may consist of one or more attributes.
• Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD.
• Each relationship must have a verb phrase.
• Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.
• Do not differentiate between an identifying or non-identifying relationship.
• All attributes provided in the sample data or data forms must be included in the database.

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. It is possible that the person who makes the reservation request is not the person who intends to use the vehicle or make the trip.
• 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.
• A reservation request is made for a type of vehicle, rather than a specific vehicle. Think of it like a reservation for car with a rental agency you reserve a type of car (like a compact car) rather than a specific car.
• 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. However, sometimes a trip might be related to a reservation request, so the university wants to know which trips were related to which reservation requests.
• 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 make reservations or take trips are no different than employees who check in or out vehicles.

Figure 2. Vehicle Trip Data Vehicle License # Employee ID Destination Driver Employee Name Date/Time Checked Out Employee Check Out Odometer Start Date/Time Returned Odometer Return Employee check In WGN176 17 1/12/2021 Ken Blanchett Berkeley, CA 65,128 Janice Springer 66,099 1/14/2021 5PM Ken Blanchett 8AM 899ULX Marty Brown 34 2/05/2021 Ken Blanchett Sacramento, 14,887 2/05/2021 15,008 Jason Blackman CA 6AM 9PM 172AAX Cora Masters 30 Ken Blanchett Elko, NV 45,515 1/09/2021 46,667 1/09/2021 9:30AM Jason Blackman 2:30PM Figure 1. Reservation Request Data Employee Employee Expected Name ID Departure Date Destination Reason for Trip Expected Return Date Janice Springer 17 1/12/2021 1/14/2021 Vehicle Type Required Van -8 passenger Van - 12 passenger Berkeley, CA Debate Tournament Marty Brown 34 2/05/2021 2/05/2021 Sacramento, CA Tour of Intel Manufacturing Janice Springer 17 2/29/2021 3/04/2021 Van - 12 passenger Bellingham, WA Debate Tournament Fred Martinez 98 3/2/2021 3/2/2021 Sedan Carson City, NV Attend NV State Finance Committee meeting Figure 2. Vehicle Trip Data Vehicle License # Employee ID Destination Driver Employee Name Date/Time Checked Out Employee Check Out Odometer Start Date/Time Returned Odometer Return Employee check In WGN176 17 1/12/2021 Ken Blanchett Berkeley, CA 65,128 Janice Springer 66,099 1/14/2021 5PM Ken Blanchett 8AM 899ULX Marty Brown 34 2/05/2021 Ken Blanchett Sacramento, 14,887 2/05/2021 15,008 Jason Blackman CA 6AM 9PM 172AAX Cora Masters 30 Ken Blanchett Elko, NV 45,515 1/09/2021 46,667 1/09/2021 9:30AM Jason Blackman 2:30PM Figure 1. Reservation Request Data Employee Employee Expected Name ID Departure Date Destination Reason for Trip Expected Return Date Janice Springer 17 1/12/2021 1/14/2021 Vehicle Type Required Van -8 passenger Van - 12 passenger Berkeley, CA Debate Tournament Marty Brown 34 2/05/2021 2/05/2021 Sacramento, CA Tour of Intel Manufacturing Janice Springer 17 2/29/2021 3/04/2021 Van - 12 passenger Bellingham, WA Debate Tournament Fred Martinez 98 3/2/2021 3/2/2021 Sedan Carson City, NV Attend NV State Finance Committee meeting