Database Normalization: Applying 1NF, 2NF, 3NF Using Bottom-up Method

This assignment solution demonstrates the process of database normalization using a bottom-up approach, specifically focusing on transforming relations into First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Several tables, including Bank, Manager, Branch, Customer, Employees, Deliveries, Orders, and Products, are analyzed and normalized. The solution identifies and eliminates repeating groups, partial dependencies, and transitive dependencies to achieve 3NF compliance for each table. The Orders table requires the creation of an additional Order_Line table to properly address repeating product entries. The document provides a step-by-step breakdown of the normalization process for each entity, ensuring data integrity and minimizing redundancy. Desklib offers this document to aid students in understanding and applying database normalization principles.

Bottom-up Approach: Normalization
Normalization of the Bank
UN 1NF 2NF 3NF
Bank_ID (PK)
Bank_Name
Bank_TelNo
Bank_Lodation
Bank_AC
Bank_Sort-code
Bank_ID
Bank_Name
Bank_TelNo
Bank_Lodation
Bank_AC
Bank_Sort-code
Bank_ID
Bank_Name
Bank_TelNo
Bank_Lodation
Bank_AC
Bank_Sort-code
Bank_ID
Bank_Name
Bank_TelNo
Bank_Lodation
Bank_AC
Bank_Sort-code
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.
Normalization of the Manager
UN 1NF 2NF 3NF
Manager_No
(PK)
Manager_Name
Manager_TelNo
Manager_Addres
s
Manager_JobTitl
e
Bank_ID (FK)
Manager_No
Manager_Name
Manager_TelNo
Manager_Addres
s
Manager_JobTitl
e
Bank_ID
Manager_No
Manager_Name
Manager_TelNo
Manager_Addres
s
Manager_JobTitl
e
Bank_ID
Manager_No
Manager_Name
Manager_TelNo
Manager_Addres
s
Manager_JobTitl
e
Bank_ID
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.
Normalization of the Branch
UN 1NF 2NF 3NF
BranchNo (PK)
Branch_Street
Branch_City
Branch_Telepho
ne
Branch_Email_ID
Manager_No
((FK)
BranchNo
Branch_Street
Branch_City
Branch_Telepho
ne
Branch_Email_ID
Manager_No
BranchNo
Branch_Street
Branch_City
Branch_Telepho
ne
Branch_Email_ID
Manager_No
BranchNo
Branch_Street
Branch_City
Branch_Telepho
ne
Branch_Email_ID
Manager_No
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.

Normalization of the Customer
UN 1NF 2NF 3NF
Cutomer_ID (PK)
Customer_Name
Customer_Surna
me
Customer_Meas
urements
Customer_Addre
ss
Customer_Email
_ID
BranchNo (FK)
Bank_ID
Cutomer_ID
Customer_Name
Customer_Surna
me
Customer_Meas
urements
Customer_Addre
ss
Customer_Email
_ID
BranchNo
Bank_ID
Cutomer_ID
Customer_Name
Customer_Surna
me
Customer_Meas
urements
Customer_Addre
ss
Customer_Email
_ID
BranchNo
Bank_ID
Cutomer_ID
Customer_Name
Customer_Surna
me
Customer_Meas
urements
Customer_Addre
ss
Customer_Email
_ID
BranchNo
Bank_ID
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.
Normalization of the Employees
UN 1NF 2NF 3NF
Emp_No (PK)
Emp_FullName
Emp_Position
Emp_Salary
BranchNo (FK)
Emp_No
Emp_FullName
Emp_Position
Emp_Salary
BranchNo
Emp_No
Emp_FullName
Emp_Position
Emp_Salary
BranchNo
Emp_No
Emp_FullName
Emp_Position
Emp_Salary
BranchNo
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.
Normalization of the Deliveries
UN 1NF 2NF 3NF
Delivery_No (PK)
Delivery_Day
Delivery_Date
Delivery_ToACust
omer
Delivery_ToALoca
tion
Emp_No (FK)
Order_No(FK)
Delivery_No
Delivery_Day
Delivery_Date
Delivery_ToACust
omer
Delivery_ToALoca
tion
Emp_No
Order_No
Delivery_No
Delivery_Day
Delivery_Date
Delivery_ToACust
omer
Delivery_ToALoca
tion
Emp_No
Order_No
Delivery_No
Delivery_Day
Delivery_Date
Delivery_ToALoc
ation
Emp_No
Order_No
There exists transitive dependency because the order_no determines the
customer thus this can be eliminated for the relation to remain in 3NF.
Normalization of the Orders
UN 1NF 2NF 3NF
Order_No (PK) Order_No Order_No Order_No

Order_Name
Order_Deadline
Order_Collection
Date
Delivery_Date
Customer_ID(FK)
To_Address
Product_ID
Order_Name
Order_Deadline
Order_Collection
Date
Delivery_Date
Customer_ID
To_Address
Order_Name
Order_Deadline
Order_Collection
Date
Delivery_Date
Customer_ID
To_Address
Order_Name
Order_Deadline
Order_Collection
Date
Delivery_Date
Customer_ID
The relation has repeating groups because an order can consist of one or
more products thus this can eliminated for the relation to meet all the
conditions of 3NF.
Elimination of the repeating groups results to a table order_line table.
UN 1NF 2NF 3NF
Order_No
(PK,FK)
Product_ID(PK,F
K)
Order_No
Product_ID
Order_No
Product_ID
Order_No
Product_ID
Normalization of the Products
UN 1NF 2NF 3NF
Product_ID (PK)
Product_Name
Product_Type
Quantity
Product_ID
Product_Name
Product_Type
Quantity
Product_ID
Product_Name
Product_Type
Quantity
Product_ID
Product_Name
Product_Type
Quantity
This relation is already in 3NF since no repeating groups, no partial
dependencies and no transitive dependencies exist in the relation.