Database Design and Development: Assignment - ER Diagram & 3NF

This assignment focuses on database design and development, presenting a comprehensive solution for a given case study. The solution includes an Entity Relationship (ER) diagram, which visually represents the database's entities, attributes, and relationships. The ER diagram is designed based on specific business rules and assumptions, providing a logical model for the database's structure. The assignment also includes a logical design, detailing the database's tables and their attributes, and demonstrating the relationships between different entities. The document further provides the database design up to the 3rd Normal Form (3NF), ensuring data integrity and minimizing redundancy. The assignment addresses the core concepts of database design, making it a valuable resource for students studying database management and related fields. The document includes a bibliography of relevant research papers and books. The assignment is based on a car rental business scenario.

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
The ER Diagram has been created on the basis of the business rules and the
assumptions. The dev elopement of an ER diagram is very important for the development of
any type of Database or storage system for the organizations. It is used as the logical model
on which the physical storage system is developed. The ER diagram that is designed above
would be very efficient in providing the details of the entities which provides the type of data
that is to be stored in the system for the organization. The attributes provide the answers
about what data to be stored in the system. In addition to this the ER Diagram also provides
the information about the Super entities and the sub entities. The Super entities are marked in

2
DATABASE DESIGN AND DEVELOPMENT
orange in the diagram (Person and Customers) and the sub entities are marked in green.
However, it is to be noticed that the Customers can also be considered as the weak entity but
there are two sub entities under the super entity Customer and hence the consideration of
customer as the super entity is justified. The weak entities have been marker in white colour
as the weak entities are the ones which are derived from the strong entities. The strong
entities are marked in the in Yellow. The weak entities in the ER Diagram Are Car and
Rental Details. The weak entities are Category and Booking which would help in deriving the
Car and Details.
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 the rental and booking are different entities as the booking is
done online by the customers and the rental details are recorded when the customer
actually takes the ride.
 There would only be a single insurance policy for a single car and a single registration
number.
 It is also assumed that there would be a single rental record which would correspond
to a single booking record.
 There are two type of persons whose details are stored in the system who are
customers and employees where the employees are further sub divided into individual
and special employees.
Logical Design
The logical detail of the database which is fragmented up till 3 NF is provide below:

3
DATABASE DESIGN AND DEVELOPMENT
Persons (PersonId (primary key), PersonCategory, EmployeeID (foreign key), CustomerID
(foreign key))
Car (RegistrationNumber (primary key), Maker, Model, ManufacturingDate, NumberOfSets,
LocationID (foreign key), CategoryID (foreign key))
Category (CategoryID (primary key), CategoryName, DailyPrice)
Boooking (ResrvationNumber (primary key), RegutrationNumber( foreign key), CategoryID
(foreign key), BookingDate, Description, CustomerID (foreign key))
Parking Location (LocationID (primary key), Address, LocatioName)
Person (PersonID (primary key), PersonCategory, EmployeeID (foreign key), CustomerID
(foreign key))
Corporate Customer (CorprateID (primary key), ABN, SpecialDealStatus)
Individual Customer (IndividualID (primary key), IndividualName, LifeInsuaranceDetails)
Customers (CustomerID (primary key), IndividualID (foreign key), CorpotrateID (foreign
key), FullName, LicenseNumber, CustomerAddress, PhoneNumber)
Employee (EmployeeID (primary key), Salary, LastName, FirstName, BirthDate, SkillLevel,
AnnualSalary)
Rental Details (RebtalID (primary key), ReservationNumber (foreign key), PickUpLocation,
DropOfLocation, PickUpTime, DropOfLocation, TotalCost, VehicleImage, EmployeeID
(foreign key))

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.