Lesson 5: Normalization

Objectives

By the end of this lesson, you will be able to:

• Understand the concept of normalization and its importance in database design.
• Explain the principles of normalization.
• Apply normalization techniques to refine the database schema.

Introduction

As we continue our journey in developing the inventory management system, it’s crucial to explore the concept of database normalization. Normalization is a fundamental principle in database design, aimed at organizing data efficiently and minimizing redundancy. Let’s delve into the intricacies of normalization and its significance in creating robust database schemas.

What is Database Normalization?

Database normalization is the process of structuring a relational database in accordance with a series of rules designed to reduce data redundancy and dependency. The primary goal of normalization is to ensure that each table within the database contains only related data and that data integrity is preserved.

Different Normal Forms

Normalization is typically divided into several normal forms, each addressing specific aspects of data organization and dependency. The most commonly discussed normal forms include:

1. First Normal Form (1NF): Ensures that each column in a table contains atomic values, with no repeating groups or arrays. It eliminates redundant data by breaking down complex attributes into their atomic components.

2. Second Normal Form (2NF): Builds upon 1NF by ensuring that all non-key attributes are fully functionally dependent on the primary key. It eliminates partial dependencies, where attributes depend on only part of the primary key.

3. Third Normal Form (3NF): Extends the principles of 2NF by removing transitive dependencies, where non-key attributes depend on other non-key attributes. It ensures that each attribute directly depends on the primary key, rather than indirectly through another attribute.

Applying Normalization to Our Inventory Management System

Now that we have implemented the Entity-Relationship Diagram (ERD) for our inventory management system, we can apply normalization principles to refine the database schema and ensure optimal data organization. We’ll start by examining each entity and its attributes to identify potential normalization issues and then proceed to normalize the schema to at least Third Normal Form (3NF).

Normalization of Entities

• Products Entity: We’ll ensure that each attribute contains atomic values (non-decomposable values) and there are no repeating groups. Attributes like Product ID, Name, Description, Price, and Quantity are already atomic, so we’ll focus on eliminating any repeating groups or composite attributes.

• Suppliers Entity: Like the Products entity, we’ll ensure atomicity and eliminate repeating groups. Attributes such as Supplier ID, Name, Contact Information, and Address are already atomic.

• Orders Entity: We’ll examine the attributes to ensure atomicity and eliminate any partial dependencies. Attributes like Order ID, Customer ID, Order Date, Total Price, and Status appear to be atomic, but we’ll verify if any composite attributes or repeating groups exist.

• Customers Entity: We’ll ensure atomicity and eliminate any repeating groups in the attributes. Attributes such as Customer ID, Name, Contact Information, and Address are already atomic.  

• Inventory Entity: We’ll review the attributes to ensure atomicity and eliminate any repeating groups. Attributes like Product ID, Quantity on Hand, Reorder Level, and Location should be atomic.

Normalization of Relationships

We’ll also normalize the relationships between entities to ensure that they adhere to normalization principles. This may involve decomposing many-to-many relationships into separate tables or resolving any transitive dependencies.

Outcome

By applying normalization techniques to our inventory management system project, we’ll achieve a database schema that is well-structured, free from data redundancy and anomalies, and optimized for efficient data storage and manipulation. This ensures data integrity and reliability, laying a solid foundation for the development of the inventory management system.

Example

Inventory Management Table (Before Normalization)

Here’s the initial structure of our inventory table:

Table: Inventory (Before Normalization)

This table contains information about electronic gadgets, including their unique IDs, names, categories, supplier IDs, supplier names, prices, and quantities in stock. However, it’s evident that there’s redundancy in the Supplier information

Transition to 1NF

In First Normal Form (1NF), we ensure that each cell in the table holds a single value, and there are no repeating groups or arrays. To achieve 1NF, we need to separate the Supplier information into its own table.

Inventory Table (1NF)

Supplier Table (1NF)

Now, the data is organized into two tables, adhering to the principles of 1NF. The Supplier information is separated from the Inventory table, eliminating redundancy and ensuring data integrity.

Transition to 2NF

In Second Normal Form (2NF), we ensure that there are no partial dependencies, meaning each non-key attribute is fully functionally dependent on the entire primary key.

Looking at our current structure, we observe that the Price attribute is fully dependent on the Product_ID, which is the primary key. However, the Supplier_Name and Location attributes in the Inventory table are dependent only on the Supplier_ID, which is a part of the composite primary key. To achieve 2NF, we need to remove these partial dependencies.

Inventory Table (2NF)

Supplier Table (2NF)

Now, we’ve separated the Supplier information into two tables: Supplier and Supplier_Location. Each table contains attributes that are fully functionally dependent on the primary key, adhering to the principles of 2NF.

In Third Normal Form (3NF), we aim to remove transitive dependencies, ensuring that no non-key attribute depends on another non-key attribute.

Looking at our current structure, we observe that the Location attribute in the Supplier_Location table is functionally dependent on the Supplier_ID, which is the primary key. However, the Location attribute does not directly relate to the Supplier_ID, but rather to the Supplier_Name. To achieve 3NF, we need to eliminate this transitive dependency.

Supplier Table (3NF)

Supplier_Location Table (3NF)

Summary

This lesson introduces the concept of database normalization, highlighting its importance in creating well-structured and efficient database schemas. By understanding the different normal forms and applying normalization techniques, learners gain insight into how to design databases that optimize data organization, minimize redundancy, and enhance data integrity.