Chapter 1: Introduction
1.1 Background
Inventory management plays a crucial role in the logistics industry, where timely and accurate
handling of goods determines operational efficiency. Modern logistic warehouses handle vast
quantities of inventory data, often leading to challenges related to data redundancy,
inconsistency, and inefficiency. Inaccurate inventory management can result in delayed
shipments, increased operational costs, and unsatisfied customers.
1.2 Problem Statement
The current inventory management system used by many logistic warehouses suffers from
performance issues stemming from poorly structured data. The system lacks normalization,
leading to anomalies such as data redundancy, update inefficiencies, and inconsistent records.
These inefficiencies hinder the warehouse's ability to process transactions accurately and quickly.
1.3 Research Objectives
The primary objectives of this research are:
1. To identify data anomalies and performance issues in the current system.
2. To prepare sample data that represents real-world warehouse operations.
3. To normalize the dataset to improve data integrity and reduce anomalies.
4. To design an Entity-Relationship Diagram (ERD) for a refined database schema.
5. To implement the refined database and demonstrate its effectiveness through SQL scripts
and sample data.
1.4 Research Scope
This thesis focuses on the normalization of an inventory management system database,
identifying anomalies, and optimizing the data structure for better performance. The study is
limited to a logistic warehouse setting, covering essential operations such as inventory tracking,
supplier management, and order fulfillment.
1.5 Significance of the Study
Optimizing inventory management systems through database normalization is crucial for
improving operational efficiency, minimizing errors, and enhancing data consistency. This study
provides a systematic approach to identifying performance problems and applying normalization
techniques to address them.
Chapter 2: Sample Data Preparation
2.1 Overview of Sample Data
To analyze and improve the inventory management system, sample data reflecting typical
warehouse operations has been prepared. The dataset contains information on inventory items,
suppliers, warehouse locations, and stock levels.
2.2 Un-Normalized Data Structure
The following table represents the dataset in its un-normalized form (UNF):
Tabel UNF (Unnormalized Form)
Ord Produ Product Custo Order_ Orde Suppli Supplie Warehou
er_I ct_Na _Catego mer_Na Quantit r_Dat er_Na r_Conta se_Locati
D me ry me y e me ct on
OR Produc Electroni John 10 2024- Supplie +62 Jakarta
D00 tA cs Doe 01-01 r X 812345
1 678
OR Produc Furniture Jane 5 2024- Supplie +62 Surabaya
D00 tB Smith 01-02 r Y 812987
2 654
OR Produc Electroni Ali 20 2024- Supplie +62 Bandung
D00 tC cs Khan 01-03 r Z 8121112
3 23
OR Produc Electroni Sarah 15 2024- Supplie +62 Jakarta
D00 tA cs Connor 01-04 r X 812345
4 678
OR Produc Clothing Michael 30 2024- Supplie +62 Yogyakart
D00 tD Scott 01-05 r W 812998 a
5 877
2.3 Data Description
1. Item_ID: Unique identifier for each inventory item.
2. Item_Name: Name of the inventory item.
3. Warehouse_ID: Unique identifier for each warehouse.
4. Warehouse_Location: Physical location of the warehouse.
5. Quantity: Number of items available in stock.
6. Supplier_ID: Unique identifier for each supplier.
7. Supplier_Name: Name of the supplier.
8. Supplier_Contact: Contact details of the supplier.
Chapter 3: Data Anomalies and Functional Dependencies
3.1 Identification of Anomalies
In the un-normalized dataset, the following anomalies are observed:
1. Redundancy:
○ Repeated supplier information (e.g., Supplier X appears multiple times).
2. Update Anomaly:
○ If a supplier's contact details change, all rows associated with that supplier must
be updated.
3. Insertion Anomaly:
○ New suppliers cannot be added unless an inventory item is also added.
4. Deletion Anomaly:
○ Deleting an inventory item may result in losing supplier information.
3.2 Functional Dependencies
The following functional dependencies exist in the dataset:
1. Item_ID → Item_Name, Quantity
2. Warehouse_ID → Warehouse_Location
3. Supplier_ID → Supplier_Name, Supplier_Contact
These dependencies suggest opportunities for normalization to improve data structure.
Chapter 4: Normalization Process
4.1 First Normal Form (1NF)
Step 1: Ensure each column contains atomic values, and each row is unique.
Tabel 1NF (First Normal Form)
Menghapus grup berulang dan memastikan nilai atomik.
Ord Produ Product Custo Order_ Orde Suppli Supplie Warehou
er_I ct_Na _Catego mer_Na Quantit r_Dat er_Na r_Conta se_Locati
D me ry me y e me ct on
OR Produc Electroni John 10 2024- Supplie +62 Jakarta
D00 tA cs Doe 01-01 r X 812345
1 678
OR Produc Furniture Jane 5 2024- Supplie +62 Surabaya
D00 tB Smith 01-02 r Y 812987
2 654
OR Produc Electroni Ali 20 2024- Supplie +62 Bandung
D00 tC cs Khan 01-03 r Z 8121112
3 23
OR Produc Electroni Sarah 15 2024- Supplie +62 Jakarta
D00 tA cs Connor 01-04 r X 812345
4 678
OR Produc Clothing Michael 30 2024- Supplie +62 Yogyakart
D00 tD Scott 01-05 r W 812998 a
5 877
4.2 Second Normal Form (2NF)
Step 2: Remove partial dependencies by creating separate tables.
Tabel 2NF (Second Normal Form)
Menghapus ketergantungan parsial dengan membagi tabel.
Tabel Orders
Order_ID Customer_Na Order_Dat
me e
ORD001 John Doe 2024-01-01
ORD002 Jane Smith 2024-01-02
ORD003 Ali Khan 2024-01-03
ORD004 Sarah Connor 2024-01-04
ORD005 Michael Scott 2024-01-05
Tabel Products
Product_I Product_Na Product_Catego Supplier_Na Warehouse_Locati
D me ry me on
P001 Product A Electronics Supplier X Jakarta
P002 Product B Furniture Supplier Y Surabaya
P003 Product C Electronics Supplier Z Bandung
P004 Product D Clothing Supplier W Yogyakarta
Tabel Suppliers
Supplier_Nam Supplier_Conta
e ct
Supplier X +62 812345678
Supplier Y +62 812987654
Supplier Z +62 812111223
Supplier W +62 812998877
Tabel Order_Details
Order_ID Product_I Order_Quantit
D y
ORD001 P001 10
ORD002 P002 5
ORD003 P003 20
ORD004 P001 15
ORD005 P004 30
4.3 Third Normal Form (3NF)
Step 3: Remove transitive dependencies.
Tabel 3NF (Third Normal Form)
Menghapus ketergantungan transitif dengan pemisahan entitas.
Tabel Orders
Order_ID Customer_Na Order_Dat
me e
ORD001 John Doe 2024-01-01
ORD002 Jane Smith 2024-01-02
ORD003 Ali Khan 2024-01-03
ORD004 Sarah Connor 2024-01-04
ORD005 Michael Scott 2024-01-05
Tabel Order_Details
Order_ID Product_I Order_Quantit
D y
ORD001 P001 10
ORD002 P002 5
ORD003 P003 20
ORD004 P001 15
ORD005 P004 30
Tabel Products
Product_I Product_Nam Product_Categor Supplier_I
D e y D
P001 Product A Electronics S001
P002 Product B Furniture S002
P003 Product C Electronics S003
P004 Product D Clothing S004
Tabel Suppliers
Supplier_I Supplier_Nam Supplier_Conta
D e ct
S001 Supplier X +62 812345678
S002 Supplier Y +62 812987654
S003 Supplier Z +62 812111223
S004 Supplier W +62 812998877
Tabel Warehouses
Warehouse_I Warehouse_Locatio
D n
W001 Jakarta
W002 Surabaya
W003 Bandung
W004 Yogyakarta
Chapter 5: Entity-Relationship Diagram and Conclusions
5.1 Entity-Relationship Diagram (ERD)
5.2 Conclusions
This study successfully identified data anomalies in the existing inventory system and applied
normalization techniques to improve database structure. The refined database reduces
redundancy, enhances data integrity, and ensures scalability for warehouse operations.
