Data Requirements
The Car Rental System Database involves around three main entities Car, User, and Reservation
The car can be reserved from a rental location with a specific address. It has a unique Rental_Location_ID, a phone number, and a contact email. A rental location keeps track of the address where the car belongs using the street name, state, and zip code.
A rental location has several cars for rental. Each car is described by VIN, Registration Number, Color, Model, Manufactured year, Seating capacity of the car, whether it has Disabled friendly feature and its Status marked ‘Available’ or ‘Unavailable’
The car has a parameter Car Type. It can be ‘Economy’, ‘Standard’, ’SUV’, ‘Premium’ and ‘Minivan’. Car Type defines the rental price per day. A user can take Insurance per day for the rental car. There are different types of Insurance each having different medical coverage, collision coverage, and bodily coverage. Insurance Types are ‘Liability’ and ‘Comprehensive’. Car type and Insurance Type drives the Insurance price per day.
A user can reserve a car for several days. He can use any valid promotional code which is maintained by status. When a user books a car he mentions the start date and end date for which he needs the car. The end date will be hypothetical at the time of reservation and updated with the actual end date when the car is returned. The total amount and net amount are calculated based on start date, end date, rental price per day, insurance price per day, and promotional code if any
A user is categorized as a guest or customer. The user can continue reserving the car as a guest as long as he has not registered as a a customer. A user is uniquely identified by his/her license number. User information consists of his name as first name, middle name and last name, email, address, date of birth, and contact number
A registered customer will be provided with a login id and password. A customer can save his credit/debit card details for future payment
Partial payment can also be made at the time of reservation and the balance must be paid by the user during car return when the actual end date is known. If the user is a customer, he/she can pay through saved debit/credit card details
A user can add any accessories as part of his/her reservation. Accessories can be ‘Car Seat’ and ‘GPS’. A user can add as many accessories he/she needs as part of the reservation
The additional driver can be added as part of his/her reservation. For each additional driver, there will be an additional charge
A user can cancel a reserved car before the car has been rented. A reservation can have status as ‘Reserved’, ‘Completed’, and ‘Cancelled’. When the car is reserved, the status will be in ‘Reserved’ Status. Once the car is returned and the total amount is paid, the status will be ‘Completed’
Functional Dependencies
In Rental car location, Rental_Location_ID is the primary key
Rental_Location_ID —> {Phone, Email, Street_Name, State, Zip_Code}
Type of the car defines the rental price of the car per day
Car_Type —> Price_Per_Day
Type of the insurance defines the insurance coverage
Insurance_Type —> {Bodily_Coverage, Medical_Coverage, Collision_Coverage}
Insurance Type and Car Type defines the Insurance price per day
{Car_Type, Insurance_Type} —> {Insurance_Price}
A user is defined by his/her License_No
{License_No} —> {FName, Mname, Lname, Email,Address, Phone,DOB, User_Type}
In a user credential, Login_ID defines the rest of the attributes in the entity
{Login_ID} —> {Password, Year_Of_Membership, License_No}
Login_ID and Card_No in Card_Details defines complete card information
{Login_ID, Card_No} —> {Name_On_Card, Expiry_Date, CVV, Billing_Address}
Reservation_ID drives all the other attributes in Reservation relation
Reservation_ID —> {Start_Date, End_Date, Meter_Start, Meter_End, Rental_Amount, Insurance_Amount, Status, License_No, VIN, Promo_Code, Additional_Amount, Tot_Amount, Insurance_Type}
Payment_ID is the primary key of Payment relation
Payment_ID —> {Amount_Paid, Card_No, Expiry_Date, Name_On_Card, CVV, Billing_Address, Reservation_ID, Login_ID, Saved_Card_No, Paid_By_Cash}
Promo_Code defines other attributes in Offer_Details relation
Promo_Code —> {Description, Promo_Type, Is_One_Time, Percentage, Discounted_Amount}
Additional_Drivers relation
{Reservation_ID,Name} —> DOB
In Accessories relation, Accessory_ID drives the type and amount of the accessory
Accessory_ID —> {Type, Amount}
Download the Car Rental System Database Design Project Code, Report, and Presentation.