Database Design and Implementation: ER Diagram, Normalization to 3NF

This assignment solution focuses on database design principles, specifically addressing the creation of an Entity-Relationship (ER) diagram for a hotel booking system. The solution details the entities, attributes, and relationships within the system, visually represented in the ER diagram. Furthermore, the assignment covers the process of mapping the ER diagram to the Third Normal Form (3NF), explaining the steps involved in normalization to reduce data redundancy and ensure data integrity. The solution includes the application of business rules, functional dependencies, and the separation of tables to achieve 3NF. References to academic sources support the design and normalization processes presented in the assignment. The assignment is a practical example of database design, illustrating how to create a well-structured and efficient database for managing hotel booking data. The solution includes mapping the ER diagram to 3NF and explaining the process of normalization, functional dependencies and key attributes.

Assignment 2
1

PART A......................................................................................................................................3
1. Draw the ER diagram.........................................................................................................3
PART B......................................................................................................................................4
1. Map the ER diagram to 3 NF.............................................................................................4
2. Normalisation.....................................................................................................................6
REFERENCES...........................................................................................................................7
2

PART A
1. Draw the ER diagram
An entity relationship diagram is based on the ERD model in which consider as
graphical representation that create a strong relation among people, place, concept and
objects within IT system. In this section, it has been created an entity relationship model for
hotel booking systems. It shows all kind of visual in the form of database tables and creating
a strong relationship between themselves.
Figure: 1 Entity Relationship Diagram
In Figure-1, it has been explained the entity relationship diagram of hotel booking
management system. On the basis of evaluation, it can be divided the entities such as
payment, customer, and hotel, staff, booking, Room and service (Al-Fedaghi, 2021). Every
entities are showing a proper attributes in which develop a strong relationship between two
database tables.
 Entities- An entity refers to the real world object which is distinguishable from one
object to another in the real basis.
 Attributes- it is based on the conceptual termed to represent a characteristics of
entities. In the context of DBMS, it refers to the concept of database component like
DB tables. It can be referred to the field and explain the multiple column of database.
3

4

Business Rule
 Each and every customer can book a room in the hotel
 Each booking can only made by one customer
 Every employee can check the booking of consumer
 Customer can make payment
 Each payment can be done by one customer
 Payment can depend on the room booking
 Each room can be booked by customer and assigned on the specific deadline.
 Employee allows to store the room booking information into the system.
PART B
1. Map the ER diagram to 3 NF
First Normal Form-
Second Normal Form-
5

Since the 2nd normal form, it has been separated two tables, but it is necessary where both key
are showing in the same time. So, it can be used the compound key and considering primary
key as conjoined to represent each other.
 Customer id and booking id - composite primary key.
 Booking id  Customer_name, Customer_phone no, Customer_email
Third Normal Form-
It is defined the database schema design approach in terms of relational databases,
which uses a normalizing the effectiveness of principles to minimise the level of duplication
of data (Mortezaee, 2021). It is also helping to avoid any kind of anomalies and maintaining
the level of referential integrity in proper manner.
Separating these two tables into individual representation and makes all type of column
dependent on the particular key in the table, no other column. In this way, it has been created
a third normal form.
6

 Booking id  payment id
 Payment id  booking_status
 Non-key attributes Booking title, booking type, booking status and booking dates
Non-key attributes are dependent on the primary key.
 Booking title, booking type, booking date  booking id
 Booking status payment id
2. Normalisation
Normalisation is based on the process or method that helps to organise, manage large
amount of data or information in the form of database. It should be creating a particular
tables, establishing a strong relationship between tables on the basis of business rules (Pham,
Liu and Hoi, 2022). It would be designed the database and needs to be protected or secured
data, make a database which has become more flexible by eliminating inconsistency and
redundancy.
Functional Dependencies
A functional dependency is defined the relationship between two attributes, which is
typically representing a relation in the primary key, non-key attributes within database table/
3 NF relations
Booking id Booking title Payment id Payment amount
Functional dependencies-
Booking Booking title
Booking id  booking title payment
Booking id Booking title Booking type Payment id Payment amount
Booking id  payment
Key booking id
Payment id Payment status
7

Payment id  payment status
Key payment id
8

REFERENCES
Book and Journals
Al-Fedaghi, S., 2021. Conceptual Data Modeling: Entity-Relationship Models as Thinging
Machines. arXiv preprint arXiv:2109.14717.
Mortezaee, K., 2021. Normalization in tumor ecosystem: opportunities and challenges. Cell
Biology International. 45(10). pp.2017-2030.
Pham, Q., Liu, C. and Hoi, S., 2022. Continual normalization: Rethinking batch
normalization for online continual learning. arXiv preprint arXiv:2203.16102.
9