Overview

Our final project includes creating a rental business for cars. We made seven tables such as rental table, carmodels table, customers table, staffs table, invoice table, brand table, and category table.

Each table has their primary key which relates to other table as a foreign key. Below are what each table consists of:

1. Carmodels table contains a list of the cars for sale
2. Customers table contains customers data that rents a car or more
3. Staffs table contains a list of staffs and their data that works for the business
4. Brand table contains a list of car brands that exist after normalisation
5. Category table contains a list of categories that each car falls to, also exists after normalisation
6. Rental table contains a list of rental order that each customer made. Each rental order entails the details of the order such as price, total days of rent, and others
7. Invoice table contains the invoice details issued to the customers. Details including rental date and return date

 

• ER diagram

Relational Schema:

• Brand(Brand_ID, Brand_Name)

PRIMARY KEY Brand_ID AUTO_INCREMENT

• Category(Category_ID, Category_Name)

PRIMARY KEY Category_ID AUTO_INCREMENT

• Carmodels(Car_ID, Brand_ID, Category_ID, Car_name, years, Rent_rate_per_day)

PRIMARY KEY Car_ID AUTO_INCREMENT

FOREIGN KEY Brand_ID REFERENCES Brand(Brand_ID)

FOREIGN KEY Category_ID REFERENES Category(Category_ID)

• Customers(Customer_ID, First_Name, Last_Name, Age, Phone, Email, Address, City)

PRIMARY KEY Customer_ID AUTO_INCREMENT

• Staffs(Staff_ID, First_Name, Last_Name, Age, Phone, Email, Address, City)

PRIMARY KEY Staff_ID AUTO_INCREMENT

• Invoice(Invoice_ID, Customer_ID, Staff_ID, Rental_ID, Total_Cost, Total_Days, Status)

PRIMARY KEY Invoice_ID AUTO_INCREMENT

FOREIGN KEY Customer_ID REFERENCES Customers(Customer_ID)

FOREIGN KEY Staff_ID REFERENCES Staffs(Staff_ID)

FOREIGN KEY Rental_ID REFERENCES Rental(Rental_ID)

• Rental(Rental_ID, Car_ID, Customer_ID, Staff_ID, Rental_Date, Return_Date)

PRIMARY KEY Rental_ID AUTO_INCREMENT

FOREIGN KEY Car_ID REFERENCES Carmodels(Car_ID)

FOREIGN KEY Customer_ID REFERENCES Customers(Customer_ID)

FOREIGN KEY Staff_ID REFERENCES Staffs(Staff_ID)

 

Main Menu