Create a logical ERD for each of the problems on the following pages using the crowsfoot 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 non-key attributes in the bottom of the box. Lines should separate each part of the entity box. Please turn in the designs in a single Word (or .pdf) document it makes grading much faster if everything is in one document. Follow these instructions about each ERD: The ERD should not have any M:N relationships.

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.

Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD.

Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.

Each relationship must have a verb phrase. Do not differentiate between an identifying or non-identifying relationship (dotted line vs. solid line). Feel free to make all your relationship lines solid.

All attributes provided in the sample data or data forms must be included in the database. Submit your assignment on WebCampus by 3:00PM on the day it is due. It is not necessary to turn in any sample data tables, however, I recommend creating sample data tables to validate your design. If you need to add any assumptions for the business rules, please note those on your diagram or in a separate page in the same Word document.

Question 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 a 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 of vehicles.

Fiqure 1. Reservation Request Data Figure 2. Vehicle Trip Data Copyright () Dana Edberg, 2022 Question 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 a 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