COIT20247 Database Design And Development

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.
• 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)

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))

References

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.