QRentals Database Design and Development Assignment - COIT20247

This assignment solution focuses on the database design and development for a car rental system, specifically the QRentals case study. The solution includes an Entity Relationship (ER) diagram representing the conceptual data model, detailing entities such as cars, customers, employees, and rental activities, along with their attributes and relationships. The document then proceeds with a logical design, breaking down the database into 3NF relations, including tables for car categories, bookings, locations, cars, persons, customers, employees, and rental details. The ER diagram incorporates assumptions and business rules, such as multiple cars per location and different customer and employee categories. The logical design provides table structures with primary and foreign keys to ensure data integrity and efficient query processing. The assignment demonstrates a comprehensive approach to database design, ensuring that the database can effectively track vehicles, customers, employees, and rental activities for the QRentals business.

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
Assumptions and Business Rules
For the construction of the ER Diagram the assumptions and the business rules stated
below has been followed:
 It has been assumed that there can be many cars from a single location.
 The person category is sub divided into two categories and customers and employees.
In addition to this, the entity customers have been divided into Individual Customers
and Corporate Customers.

2
DATABASE DESIGN AND DEVELOPMENT
 The employee’s details are required only for the purpose of rentals and not for the
booking. This is because the car services are provided by the employees only during
the rental period and the booking is done by the customer himself.
 Also it has been assumed that there are several categories in which a car can be
classified and the charges would be assumed on the basis of the daily charges for a
particular category.
 It is assumed that for any type of car with a particular registration number there would
only be a single insurance with a unique policy number.
Logical Design
The logical detail of the database which is fragmented up till 3 NF is provide below:
Car Category (Category_Number (pk), Category_Name, Daily_Price)
Car Booking (Reservation_Number (pk), Registration_Number (fk), Category_ID (fk),
Booking_Date, Description, Customer_ID (fk))
Parking Location (Location_ID (pk), Location_Address, Location_Name)
Car (Registration_Number (pk), Maker, Model, Manufacturing_Date, Number_Of_Sets,
Location_ID (fk), Category_ID (fk), Vehicle_Image)
Persons (Person_Id (pk), Person_Category, Employee_ID (fk), Customer_ID (fk))
Customers (Customer_ID (pk), IndividualID (fk), CorpotrateID (fk), Full_Name,
License_Number, Customer_Address, Phone_Number)
Employee (Employee_Id (pk), Salary, Last_Name, First_Name, DOB, Skill_Level,
Annual_Salary)
Corporate Customer (Corprate_ID (pk), ABN, Special_Deal_Status)

3
DATABASE DESIGN AND DEVELOPMENT
Individual Customer (Individual_ID (pk), Individual_Name, Life_Insuarance_Details)
Rental Details (Rebtal_ID (pk), Reservation_Number (fk), PickUp_Location,
DropOf_Location, PickUp_Time, DropOf_Location, Total_Cost, Vehicle_Image,
Employee_ID (fk))

4
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.