COMSATS University Islamabad

Attock Campus

Department Of Computer Science

Semester Project

Course Database Systems

Instructor Ma’am Mehreen Wahab

Students Details

Registration No. Name

FA24-BCS-024 Laiba Bibi
FA24-BCS-062 Kiran Muskan
 Project Title: “Vehicle Rental System”

Project Description:
The Vehicle Rental System (VRS) is a small-to-medium level project designed to automate
rental operations such as vehicle registration, bookings, payments, and maintenance tracking.
The main entities in the system include Admin, Employee, Customer, Vehicle, Vehicle
Category, Rental, Payment, and Maintenance. The Admin entity, identified by Admin_ID (PK),
has attributes Name, Username, Password, Email, and Contact Number, and manages
Employees, Vehicles, and Customers (1:M). Each Employee, identified by Employee_ID (PK) and
linked to Admin via Admin_ID (FK), has attributes Name, Role, Salary, and Contact Number and
is responsible for registering Customers. Each Customer (Customer_ID, PK) has Name, Address,
Contact Number, Email, and Join Date, and can make multiple Rentals (1:M). Each Rental
(Rental_ID, PK) includes Customer_ID (FK), Vehicle_ID (FK), Rental Date, Return Date, and Total
Cost. Vehicles (Vehicle_ID, PK) have attributes Registration Number, Make, Model, Status, and
Category_ID (FK) linking them to Vehicle Category, which stores Category_ID (PK), Category
Name, Daily Rate, and Weekly Rate. Payments (Payment_ID, PK) are linked to Rentals via
Rental_ID (FK) and include Amount, Payment Date, Method, and Status. Maintenance records
(Maintenance_ID, PK) include Vehicle_ID (FK), Employee_ID (FK), Maintenance Date, Type,
Cost, and Status.
The system enforces cardinalities such as Admin managing many Employees, Vehicles, and
Customers (1:M), Vehicles belonging to one Category (M:1), Rentals linked to one Customer
and Vehicle (M:1), Payments linked to Rentals (1:1 or 1:M), and Maintenance performed on one
Vehicle by one Employee (M:1). In the EER diagram, entities, attributes, primary and foreign
keys, relationships, and cardinalities are represented, and generalization/specialization can be
applied for Employee roles. The system stores all data digitally, ensuring efficient management
of rental resources, customer activities, and financial transactions.

Entities, Attributes and Keys:

1. Admin Table
Attribute Key Description

Admin_ID PK Unique identifier for admin

Name — Admin full name

Username — Admin login username

Email — Admin email address

Contact_No — Admin contact number

2. Employee Table
Attribute Key Description

Employee_ID PK Unique identifier for employee

Name — Employee name

Role — Employee designation

Salary — Employee salary

Contact_No — Employee contact number

Admin_ID FK References Admin(Admin_ID)

3. Customer Table
Attribute Key Description

Customer_ID PK Unique identifier for customer

Name — Customer full name

Address — Customer address

Contact_No — Customer contact number

Email — Customer email

Join_Date — Customer registration date

4. Vehicle Category Table

Attribute Key Description

Category_ID PK Unique vehicle category ID

Category_Name — Category name

Daily_Rate — Daily rental rate

Weekly_Rate — Weekly rental rate

5. Vehicle Table
Attribute Key Description

Vehicle_ID PK Unique identifier for vehicle

Reg_No — Vehicle registration number

Make — Vehicle manufacturer

Model — Vehicle model

Status — Availability status

Category_I FK References Vehicle_Category(Category_ID)

D

6. Rental Table
Attribute Key Description

Rental_ID PK Unique rental transaction ID

Customer_I FK References Customer(Customer_ID)

D

Vehicle_ID FK References Vehicle(Vehicle_ID)

Rental_Date — Rental start date

Return_Date — Rental return date

Total_Cost — Total rental amount

7. Payment Table
Attribute Key Description

Payment_ID PK Unique payment record ID

Rental_ID FK References Rental(Rental_ID)

Amount — Payment amount

Payment_Date — Date of payment

Method — Payment method

Status — Payment status

8. Maintenance Table
Attribute Key Description

Maintenance_ID PK Unique maintenance record ID

Vehicle_ID FK References Vehicle(Vehicle_ID)

Maintenance_Date — Date of maintenance

Type — Maintenance type

Cost — Maintenance cost

Status — Maintenance status

Employee_ID FK References Employee(Employee_ID)

Relationships & Cardinality Constraints:

Entity 1 Relationship Entity 2 Cardinality Explanation

Admin manages Employee 1:M One Admin can manage many
Employees, but each Employee
is managed by one Admin.

Admin manages Vehicle 1:M One Admin manages many

Vehicles, while each Vehicle is
controlled by one Admin.

Admin manages Customer 1:M One Admin manages many

Customers, and each Customer
belongs to one Admin.
Vehicle classifies Vehicle 1:M One Category can include many
Category Vehicles, but each Vehicle
belongs to only one Category.

Employee registers Customer 1:M One Employee can register

many Customers, while each
Customer is registered by one
Employee.

Customer makes Rental 1:M One Customer can make

multiple Rentals, but each
Rental is made by one
Customer.

Vehicle is used in Rental 1:M One Vehicle can be rented

multiple times over time, but
each Rental includes only one
Vehicle.

Rental has Payment 1:1/1:M One Rental can have one or

multiple Payments, but each
Payment is linked to one Rental.

Vehicle undergoes Maintenanc 1:M One Vehicle can have many

e Maintenance records, but each
Maintenance is for one Vehicle.

Employee performs Maintenanc 1:M One Employee can perform

e many Maintenance tasks, but
each Maintenance is handled by
one Employee.

Transform EER Model to Relation:

1. Admin
Admin_I Name Username Email Contact_No
D
2. Employee
Employee_ID Name Role Salary Contact_N Admin_ID
o

3. Customer
Customer_ID Name Address Contact_N Email Join_Date
o

4. Vehicle Category
Category_ID Category_Name Daily_Rate Weekly_Rate

5. Vehicle
Vehicle_I Reg_No Make Mode Status Category_ID
D l

6. Rental
Rental_ID Customer_ID Vehicle_I Rental_Date Return_Date Total_Cost
D

7. Payment
Payment_ID Rental_I Amount Payment_Date Method Status
D

8. Maintenance
Maintenance_I Vehicle_ID Maintenance_Date Type Cost Statu Employee_ID
D s
Normalization Process:

 Unnormalized Form (UNF)

1. Administration
AdminI AdminName Username EmployeeID EmployeeName Role ContactNo
D
A001 Alex alex01 E001 Sam Clerk 8888888888
A001 Alex alex01 E002 Mike Manager 7777777777

2. Customers
Custom Customer Addr Contact Email Renta Vehicl Rental Return TotalC
erID Name ess No lID eID Date Date ost
C001 John Doe City 666666 john@emai R001 V001 2025- 2025- 150
A 6666 [Link] 12-01 12-04
C002 Jane Smith City 555555 jane@emai R002 V002 2025- 2025- 400
B 5555 [Link] 12-05 12-10

3. Vehicles
Vehicl Reg Mak Mo Categ DailyR Weekly Maintenanc Maintenanc Maintenanc
eID No e del ory ate Rate eDate eType eStatus
V001 ABC Toy Coro Sedan 50 300 2025-11-20 Service Completed
123 ota lla
V002 XYZ7 Hon CR- SUV 80 500 2025-12-10 Repair Scheduled
89 da V

4. Payments
RentalI CustomerNa VehicleReg PaymentAmo PaymentDa PaymentMeth PaymentSta
D me No unt te od tus
R001 John Doe ABC123 150 2025-12-01 Card Paid
R002 Jane Smith XYZ789 400 2025-12-05 Cash Pending
Issues in un normalized tables:
 Multiple entities in one table
 Redundant data
 Category & pricing repeated
 Rental, payment, and maintenance mixed
 Update / Insert / Delete anomalies

 First Normal Form (1NF)

First Normal Form (1NF) removes all repeating groups and ensures that each
attribute contains only atomic (single) values. The data is organized into tables
where each row is unique.

1.Administration_Employee
AdminI AdminName Username EmployeeID EmployeeName Role ContactNo
D
A001 Alex alex01 E001 Sam Clerk 8888888888
A001 Alex alex01 E002 Mike Manager 7777777777

2. Customer_Rental
Custom Customer Addr Contact Email Renta Vehicl Rental Return TotalC
erID Name ess No lID eID Date Date ost
C001 John Doe City 666666 john@emai R001 V001 2025- 2025- 150
A 6666 [Link] 12-01 12-04
C002 Jane Smith City 555555 jane@emai R002 V002 2025- 2025- 400
B 5555 [Link] 12-05 12-10

3. Vehicle_Maintenance
Vehicl Reg Mak Mo Categ DailyR Weekly Maintenanc Maintenanc Maintenanc
eID No e del ory ate Rate eDate eType eStatus
V001 ABC Toy Coro Sedan 50 300 2025-11-20 Service Completed
123 ota lla
V002 XYZ7 Hon CR- SUV 80 500 2025-12-10 Repair Scheduled
89 da V
4. Payment
RentalI CustomerNa VehicleReg PaymentAmo PaymentDa PaymentMeth PaymentSta
D me No unt te od tus
R001 John Doe ABC123 150 2025-12-01 Card Paid
R002 Jane Smith XYZ789 400 2025-12-05 Cash Pending

After applying 1NF, the data is atomic and repeating groups are eliminated,
forming the base for higher normalization.

 Second Normal Form (2NF)

Second Normal Form (2NF) builds on 1NF by removing partial dependencies.
All non-key attributes fully depend on the whole primary key, but transitive
dependencies may still exist.
1. Admin
Admin_ID (PK) Name Username Email Contact_No
A001 Alex alex01 alex@[Link] 9999999999

2. Employee
Employee_ID Name Role Salar Contact_No Admin_ID (FK)
(PK) y
E001 Sam Clerk 3000 8888888888 A001
0
E002 Mike Manager 5000 7777777777 A001
0

3. Customer
Customer_ID Name Address Contact_No Email Join_Date
(PK)
C001 John Doe City A 6666666666 john@[Link] 2025-01-10
C002 Jane Smith City B 5555555555 jane@[Link] 2025-02-15

4. Vehicle
Vehicle_ID Reg_No Make Model Status Categor Daily_Rate Weekly_Rate
 (PK) y
V001 ABC123 Toyot Corolla Available Sedan 50 300
a
V002 XYZ789 Honda CR-V Rented SUV 80 500

5. Rental
Rental_ID Customer_ID (FK) Vehicle_ID (FK) Rental_Date Return_Date Total_Cost
(PK)
R001 C001 V001 2025-12-01 2025-12-04 150
R002 C002 V002 2025-12-05 2025-12-10 400

6. Payment
Payment_ID (PK) Rental_ID (FK) Amount Payment_Dat Method Status
e
P001 R001 150 2025-12-01 Card Paid
P002 R002 400 2025-12-05 Cash Pending

7. Maintenance
Maintenance_ID Vehicle_ID Maintenance_Date Type Cost Status Employee_ID
(PK) (FK) (FK)
M001 V001 2025-11-20 Servic 100 Completed E001
e
M002 V002 2025-12-10 Repair 200 Scheduled E002

In 2NF, partial dependencies are removed, reducing redundancy and preparing

the database for further refinement in 3NF.

 Third Normal Form (3NF)

Third Normal Form (3NF) further removes transitive dependencies. All non-key
attributes depend only on the primary key, making the tables free from
redundancy and update anomalies.

1. Admin
Admin_ID Name Username Email Contact_No
A001 Alex alex01 alex@[Link] 9999999999
2. Employee
Employee_ID Name Role Salary Contact_No Admin_ID
E001 Sam Clerk 30000 8888888888 A001
E002 Mike Manager 50000 7777777777 A001

3. Customer
Customer_I Name Address Contact_No Email Join_Date
D
C001 John Doe City A 6666666666 john@[Link] 2025-01-10
C002 Jane Smith City B 5555555555 jane@[Link] 2025-02-15

4. Vehicle Category
Category_I Category_Nam Daily_Rate Weekly_Rate
D e
CAT01 Sedan 50 300
CAT02 SUV 80 500

5. Vehicle
Vehicle_I Reg_No Make Model Status
Category_I
D D
V001 ABC123 Toyota Corolla Available CAT01
V002 XYZ789 Honda CR-V Rented CAT02

6. Rental
Rental_I Customer_ID Vehicle_ID Rental_Dat Return_Date Total_Cost
D e
R001 C001 V001 2025-12-01 2025-12-04 150
R002 C002 V002 2025-12-05 2025-12-10 400

7. Payment
Payment_ID Rental_ID Amoun Payment_Date Metho Status
t d
P001 R001 150 2025-12-01 Card Paid
P002 R002 400 2025-12-05 Cash Pending

8. Maintenance
 Maintenance_ID Vehicle_ID Maintenance_Dat Type Cost Status Employee_ID
e
M001 V001 2025-11-20 Service 100 Completed E001
M002 V002 2025-12-10 Repair 200 Scheduled E002

3NF ensures that the database is fully normalized, eliminating redundancy and
maintaining data integrity, making it ready for efficient storage and operations.

 Writing SQL (DDL) Queries:

CUSTOMER TABLE:
SUPPLIER TABLE:

Product Table:
Admin Table:
Purchase Order:

Supply Order:
Report:
Inserting data:

CUSTOMER TABLE:

INSERT INTO Customer VALUES (1, 'Ali Khan', '03123456789', 'ali@[Link]', 'Karachi');
INSERT INTO Customer VALUES (2, 'Sara Iqbal', '03214567890', 'sara@[Link]', 'Lahore');
INSERT INTO Customer VALUES (3, 'Bilal Ahmad', '03335678901', 'bilal@[Link]',
'Islamabad');
INSERT INTO Customer VALUES (4, 'Nida Farooq', '03001234567', '[Link]@[Link]',
'Rawalpindi');
INSERT INTO Customer VALUES (5, 'Usman Tariq', '03451112223', 'usman@[Link]',
'Peshawar');
INSERT INTO Customer VALUES (6, 'Hira Sajjad', '03111223344', 'hira@[Link]', 'Multan');

PRODUCT TABLE:

INSERT INTO Product VALUES (101, 'Wireless Mouse', 'Electronics', 1200.00, 150);
INSERT INTO Product VALUES (102, 'Mechanical Keyboard', 'Electronics', 3500.00, 100);
INSERT INTO Product VALUES (103, 'Laptop Stand', 'Accessories', 2200.00, 75);
INSERT INTO Product VALUES (104, 'USB-C Hub', 'Accessories', 1800.00, 90);
INSERT INTO Product VALUES (105, 'Webcam HD', 'Electronics', 3000.00, 60);
INSERT INTO Product VALUES (106, 'Bluetooth Speaker', 'Audio', 4500.00, 40);
INSERT INTO Product VALUES (107, 'LED Monitor 24"', 'Displays', 17000.00, 30);

SUPPLIER TABLE:

INSERT INTO Supplier VALUES (1, 'Tech Supply Co.', '03211234567', 'supply@[Link]',
'Lahore');
INSERT INTO Supplier VALUES (2, 'Office Solutions', '03456789012', 'office@[Link]',
'Faisalabad');
INSERT INTO Supplier VALUES (3, 'Pak IT Distributors', '03118889900', 'sales@[Link]',
'Quetta');
INSERT INTO Supplier VALUES (4, 'Galaxy Supplies', '03229997766',
'contact@[Link]', 'Sialkot');

ADMIN TABLE:

INSERT INTO Admin VALUES (1, 'Ayesha Ahmad', 'ayesha_admin', 'admin123', 'Manager');
INSERT INTO Admin VALUES (2, 'Zain Raza', 'zain_admin', 'admin456', 'Supervisor');
INSERT INTO Admin VALUES (3, 'Faizan Malik', 'faizan_admin', 'admin789', 'Data Analyst');
INSERT INTO Admin VALUES (4, 'Mehwish Noor', 'mehwish_admin', 'mehwish321', 'Inventory
Manager');
PURCHASE ORDER TABLE:

INSERT INTO PurchaseOrder VALUES (201, 1, DATE '2024-06-01', 2400.00);

INSERT INTO PurchaseOrder VALUES (202, 2, DATE '2024-06-05', 3500.00);
INSERT INTO PurchaseOrder VALUES (203, 3, DATE '2024-06-08', 4000.00);
INSERT INTO PurchaseOrder VALUES (204, 4, DATE '2024-06-10', 7500.00);
INSERT INTO PurchaseOrder VALUES (205, 5, DATE '2024-06-11', 12000.00);
INSERT INTO PurchaseOrder VALUES (206, 6, DATE '2024-06-12', 2000.00);

SUPPLYORDER TABLE:

INSERT INTO SupplyOrder VALUES (301, 1, DATE '2024-05-25', 60000.00);

INSERT INTO SupplyOrder VALUES (302, 2, DATE '2024-06-02', 45000.00);
INSERT INTO SupplyOrder VALUES (303, 3, DATE '2024-06-03', 55000.00);
INSERT INTO SupplyOrder VALUES (304, 4, DATE '2024-06-04', 30000.00);

REPORT TABLE:

INSERT INTO Report VALUES (401, 1, DATE '2024-06-01', 'Purchase', 'Purchase order report for
June 1st');
INSERT INTO Report VALUES (402, 2, DATE '2024-06-05', 'Supply', 'Supply order from Office
Solutions on June 2nd');
INSERT INTO Report VALUES (403, 3, DATE '2024-06-10', 'Purchase', 'Customer purchase log
generated by Faizan');
INSERT INTO Report VALUES (404, 4, DATE '2024-06-12', 'Supply', 'June supply overview
generated by Mehwish');