Database Design

1

The Need for Normalization

Christina Peacock

ISM 641 Database Design and Management

Professor Xu Ashton

10/09/23

2

The Need for Normalization

It is fundamental to guarantee that the conceptual model adheres to the principles of

normalization before translating the Entity-Relationship Diagram (ERD) into a physical database

using Structured Query Language (SQL). The normalization process is an essential step in database

design pointed toward arranging information to limit overt repetitiveness and keep up with data

integrity. In this context, I will explore the purpose of normalization and give portrayals of each of

the five normal forms (1NF, 2NF, 3NF, 4NF, and 5NF) while offering models for better

comprehension.

Purpose of the Normalization Process

Minimizing Data Redundancy

Normalization assumes a critical part in limiting data redundancy by organizing data

efficiently (Servant et al., 2018). Redundant data alludes to the pointless duplication of data within a

database. At the point when information is excess, it consumes extra room and builds the gamble of

irregularities. For example, in a denormalized database, where redundant data is common, on the off

chance that one occurrence of an information point is refreshed while others are not, it prompts data

inconsistencies.

Enhancing Data Integrity

Update Anomalies: In a denormalized database, where information is copied across numerous

records, refreshing one event of an information point might leave others conflicting. For instance, in

the event that the telephone number of a client is changed in one spot but not in another, it results in

an update anomaly.

Insertion Anomalies: In some cases, adding new data to a denormalized database can be

challenging, as it might require embedding information into different spots (Ouafiq, Saadane &

Chehri, 2022). This intricacy can prompt inclusion abnormalities, where data insertion becomes

error-prone.

3

Deletion Anomalies: Deletion anomalies occur when deleting data from a denormalized database

inadvertently removes related data that ought to be held. For example, if erasing a client's

organization likewise eliminates their contact data, it leads to a deletion anomaly.

Improving Query Performance

Reduced Data Volume: Normalization eliminates data redundancy and stores data in a smaller

structure. This decrease in information volume implies that less data must be scanned or transferred

during query execution, leading to faster query performance.

Optimized Indexing: Normalized databases often have distinct essential keys and records. These

lists are more modest and more proficient, making it easier for the database management system to

locate and retrieve specific data quickly.

Structured Joins: Normalization often involves breaking data into multiple related tables, which

empowers organized joins between tables (Kasica, Berret & Munzner, 2020) Effective joins are

fundamental for complex questions that include recovering information from different related

elements. Very much planned joins result in quicker query execution.

Simplified Query Logic: In normalized databases, the inquiry rationale will be more clear and

natural. This straightforwardness makes it simpler for engineers to write efficient queries, reducing

the chances of performance bottlenecks.

1NF - First Normal Form

Scenario Violation of 1NF Resolution (1NF)

Table:

EmployeePhoneNumbers

Attributes:

EmployeeID,

PhoneNumbers

Attributes: EmployeeID, PhoneNumber

Description: In this table, there

is a single attribute

To bring this table into 1NF, create a new row for

each phone number and associate them with a

unique identifier, in this case, EmployeeID.

Before 1NF (Example Row): EmployeeID PhoneNumbers

4

101 123-456-7890, 987-654-3210

101

101

2NF - Second Normal Form

Scenario Violation of 2NF Resolution (2NF)

Table:

OrderDetails

Attributes: OrderID, ProductID,

ProductName

Table: Products

Attributes: ProductID (Primary Key),

ProductName

Description: In this table, we have attributes To achieve 2NF, we create a new table

"OrderID," "ProductID," and called "Products" with "ProductID" as

"ProductName." A violation of 2NF the primary key. We then include

occurs because "ProductName"

depends

"ProductName" in the "Products" table

on "ProductID" but not "OrderID." with a reference to "ProductID" as a

foreign key. This separation of data

ensures that non-key attributes are fully

functionally dependent on the primary key,

adhering to the principles of the Second

Normal Form (2NF).

Before 2NF (Example

Rows): OrderID ProductID ProductName

101 501 Laptop

102 502 Smartphone

103 501 Laptop

104 503 Tablet

5

3NF - Third Normal Form

Scenario Violation of 3NF Resolution (3NF)

Table:

EmployeeDetails

Attributes: EmployeeID, Department,

Manager

Table: Departments

Attributes: Department (Primary Key),

Manager

Description: In this table, we have attributes To achieve 3NF, we create a new table

called

"EmployeeID," "Department," and "Departments" with "Department" as the

primary

"Manager." A violation of 3NF key. We then include "Manager" in the

occurs because "Manager" depends "Departments" table with a reference to

on "Department" but not "Department" as a foreign key. This

separation

"EmployeeID." of data resolves the transitive

dependency.

Before 3NF (Example Rows): EmployeeID Department

101 HR

102 IT

103 HR

104 IT

Normalization of the ERD

Table

Meets

1NF

Meets

2NF

Meets

3NF Issues & Resolution

Students Yes Yes Yes No issues found.

Courses Yes Yes Yes No issues found.

Grade_Report Yes Yes No (3NF

Issue)

"DepartmentName" transitive dependency.

Create a separate "Courses" table with

"DepartmentName."

6

Departments Yes Yes Yes No issues found.

In conclusion, while most of the tables in the ERD already meet 1NF, 2NF, and 3NF

criteria, the "Grade_Report" table requires a slight change to accomplish 3NF by separating

"DepartmentName" into a new "Courses" table. This adjustment guarantees that the ERD lines up

with the principles of database normalization, optimizing data organization and integrity.

7

References

Ouafiq, E. M., Saadane, R., & Chehri, A. (2022). Data management and integration of low power

consumption embedded devices IoT for transforming smart agriculture into actionable

knowledge. Agriculture, 12(3), 329.

Kasica, S., Berret, C., & Munzner, T. (2020). Table scraps: an actionable framework for multi-table

data wrangling from an artifact study of computational journalism. IEEE Transactions on

visualization and computer graphics, 27(2), 957-966.

Servant, N., Varoquaux, N., Heard, E., Barillot, E., & Vert, J. P. (2018). Effective normalization for

copy number variation in Hi-C data. Bmc Bioinformatics, 19(1), 1-16.