Description
Hertz Rental Company provides car rental. To better understand the Hertz car rental process, see
the Hertz reservation website at www.hertz.com. This project case is a simplified version of the car
reservation system for which you need to design a database.
When making a reservation, a customer must provide the name, address, zip code (zipCode), phone,
pickup location code (pickupLocCode), pickupDate, pickupTime, return location code (returnLocCode),
returnDate, returnTime, and car type (carType). Optionally, a customer may choose to indicate airline
(airlineCode), flightNo, discountCode, and extra options (extras).
Car Type refers to the types of cars offered for rental (e.g., Hyundai Accent, Mazda 6, Toyota Corolla,
etc.). The database must have full description for each carType.
A specific car is characterized by its Vehicle Identification Number (vehIdNo), license (carLicense)
and the state (carState) where the license was issued, carType, and car model (carModel). Any car can
be moved from one rental location to another, but at any time it is necessary to know where the car has
been stored or rented from last time (currentLocCode) and the last mileage checked (lastMileage),
which is updated each time the car is returned. A rental location is characterized by locCode, city and
state.
Based on the specified Discount Code (discountCode), Hertz Car Rental determines the applicable
discount.
The airlines (not Hertz Rental) maintain schedules for arriving flights; so, the time of pickup can be
coordinated with the flight arrival based on the airlineCode, flightNo, and the rental location (locCode).
Rental rate for the same car type differs between locations and is determined by carType and the
location (locCode) it has been rented from. There are two types of rate – dailyRate and WeeklyRate.
A customer may choose additional car extra options (extraType) such as NeverLost, SIRIUS Satellite
Radio, Instant Infant Seats. Each extra option requires a different additional fee (extraFee), which
remains the same for all car types. The database must provide for expansion of growing number of extra
options in the future.
Reservation must include carTypeRes and vehIdNo. The carTypeRes indicates the requested type of
the car, which determines the car rate (daily or weekly). But, vehIdNo indicates the actual car which may
be an upgrade (for the same money) to a different car type category. That’s how we can handle car
upgrades.
Here is the list of all attributes (29) to be stored on the database:
Assignment:
1. Analyze data dependencies (define dependencies) among attributes (there are 29 attributes)
2. Create normalized relations with identified PKs and FKs (see the separate example)
3. Design ERD (all N:N relationships must resolved into 1:N relationships)
Partial solution
Data Dependencies (starting point)
Dependencies: Definitions:
customerId name Customer ID and name
customerId address Customer address
customerId zipCode ZIP code
customerId phone Phone
reservId pickupLocCode Car pickup location code
reservId pickupDate Car pickup date
….
Data Relations (starting point)
You will proceed with the assumption that a customer name is not unique.
Customer (customId, name, address, zipCode, phone)
PK: customId
Reservation (reservId, customId, pickupLocCode, pickupDate, … )
PK: reservId
FK: customId
….
ERD (starting point)