Report on Database Design and Development for Car Rental System

This report provides a comprehensive overview of the database design and development process for a car rental system. It includes an ER diagram illustrating the entities, relationships, and attributes within the database, such as car categories, car locations, insurance policies, bookings, customers, and employees. The report outlines the assumptions and business rules considered during the design phase, such as insurance policy assignments for each car, unique identification of employees and customers, and the relationship between bookings and rentals. Furthermore, the logical design of the database is presented, detailing the tables and their attributes up to the 3NF normalization level, including primary and foreign key relationships. The bibliography lists relevant sources used in the design process. Desklib offers a wealth of similar solved assignments and past papers for students.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database design and development
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND DEVELOPMENT
ER Diagram

2
DATABASE DESIGN AND DEVELOPMENT
The ER Diagram is very important step to be taken before the physical design of the
database is done. In addition to this, the it is important that the designing of the ER Diagram
is done according to the requirements of the organization for whom the construction of the
Database is to be done. In the Diagram presented in this report there are different type of
entities which are to be considered for the design of the logical model of the structure for the
database. There are strong entities in the ER Diagram which are denoted in Yellow colour
and weak entities denoted in white colour. The weak entities are derived from the strong
entities for example, Enquiry and Rental Details have been derived from Car Booking and
Car has been derived from Car category. In addition to this there are super entities and sub
entities. In the Diagram provided above the QR People, Employees and Customers denoted in
red and the sub entities are Individual Employee, Corporate Customers, Booking Staffs and
Rental Staffs denoted in green.
Assumptions and Business Rules
For the development of the Entity Relationship diagram the following assumptions
are taken into consideration:
 It has been assumed that each and every car would have an insurance policy assigned
to their registration number.
 The employees can be identified by their unique identification numbers however the
employees are categorised as booking staffs and rentals staffs who have their own
identification number.
 The customers can be identified by their unique identification numbers however the
customers are categorised as individual staffs and corporate staffs who have their own
identification number.
 It has also been assumed that the enquiry is done for a particular booking done by the
customers and hence a particular booking is done for any Id which has been Booked.

3
DATABASE DESIGN AND DEVELOPMENT
In addition to this, the Rental ID is also single for any particular type of Booking or
for a particular booking records.
 The categorization of the cars is also done accordingly and hence, for any particular
type of category there can be a number of cars which have the same daily rates and
the charges are calculated as the total Price of the rental service.
Logical Design
The logical detail of the database which is fragmented up till 3 NF is provide below:
Car Category (Category_ID (pk), Category_Name, Daily_Price)
Car Location (Location_ID (pk), Location_Address)
Car (Registration_Number (pk), Maker, Model, Manufactured_Year, NumberOf_Sets,
Category_ID (fk), Location_ID (fk), PolicyNumber (fk))
Car Insurance Policy (Policy_Number (pk), Premium_Number, Insurer_Name, Start_Date,
Expiry_Date)
Car Booking (Reservation_Number (pk), Registration_Number (fk), Category_ID (fk),
Booking_Date, Description, Customer_ID (fk))
QR People (Person_Id (pk), Employee_ID (fk), Customer_ID (fk))
Customers (Customer_ID (pk), Full_Name, Customer_Address, Phone_Number,
CorpotrateID (fk), IndividualID (fk))
Employee (Employee_ID (pk), Last_Name, First_Name, Birth_Date, Skill_Level,
Annual_Salary, Working_Location, Booking_Staff_ID (fk), Rental_Staff_ID (fk))
Corporate Customer (Corprate_ID (pk), ABN, Organization_Name Special_Deal_Status)
Individual Customer (Individual_ID (pk), Personal_Insuarance)

4
DATABASE DESIGN AND DEVELOPMENT
Booking Staff (Booking_Staff_ID (pk), Last_Name, First_Name)
Rental Staff (Rental_Staff_ID (pk), Last_Name, First_Name)
Rental Details (Rebtal_ID (pk), Reservation_Number (fk), PickUp_Location,
DropOf_Location, PickUp_Time, DropOf_Location, Total_Cost, Vehicle_Image,
Employee_ID (fk))
Enquiry (Enquiry_ID (pk), Customer_ID (fk), Description, Reservation_Number (fk))

5
DATABASE DESIGN AND DEVELOPMENT
Bibliography
Bugiotti, F., Cabibbo, L., Atzeni, P. and Torlone, R., 2014, October. Database design for
NoSQL systems. In International Conference on Conceptual Modeling (pp. 223-231).
Springer, Cham.
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.