Database Normalization
Database normalization is organizing the data in a database to minimize data redundancy and improve data integrity. The main goal of normalization is to create a database design that avoids data anomalies and inconsistencies, by reducing or eliminating duplicate data, and ensuring that data dependencies are correctly enforced.
Normalization is typically carried out in a series of steps, called normal forms. There are several normal forms, each building on the previous one, and each with its own set of rules and requirements.
The most commonly used normal forms are:
- First Normal Form (1NF): A table is in 1NF if it contains only atomic values, that is, values that cannot be further decomposed. In other words, each cell in the table should contain only a single value.
- Second Normal Form (2NF): A table is in 2NF if it is in 1NF and every non-key attribute (i.e., an attribute that is not part of the primary key) is functionally dependent on the entire primary key, not just a part of it.
- Third Normal Form (3NF): A table is in 3NF if it is in 2NF and every non-key attribute is functionally dependent on the primary key, not on any other non-key attributes.
- Boyce-Codd Normal Form (BCNF): A table is in BCNF if every determinant (i.e., a set of attributes that determines the values of other attributes) is a candidate key.
Normalization is important for several reasons. First, it helps to reduce data redundancy, which in turn reduces storage requirements and makes updates and deletions easier. Second, it improves data integrity by ensuring that data dependencies are properly enforced. Finally, it can make querying the database more efficient, as it eliminates the need to search through redundant data.
However, it’s important to note that normalization is not always necessary or desirable. Over-normalization can lead to more complex database designs and can hurt performance. Therefore, it’s important to strike a balance between normalization and performance, based on the specific requirements of the application.
- An example of how to apply First Normal Form (1NF) to a database table.
Suppose we have a table that stores information about books, with the following columns:
In this table, the data is not organized according to any specific rules or requirements. It contains multiple values in a single column, and there is no way to uniquely identify each row. This violates the first normal form (1NF).
To apply 1NF, we need to ensure that each column contains atomic values (i.e., indivisible values) and that each row is unique and can be identified by a primary key. We can do this by splitting the table into multiple tables, each with a single theme, and adding a primary key to uniquely identify each row.
Here’s an example of how we can apply 1NF to the book table by splitting it into two tables:
Table: Books
| Book ID | Book Name | Publisher ID | Genre ID |
|---------|---------------------|--------------|------------|
| 1 | The Great Gatsby | 1 | 1 |
| 2 | The Catcher in the Rye | 2 | 1 |
| 3 | To Kill a Mockingbird | 3 | 1 |
Table: Publishers
| Publisher ID | Publisher Name |
|--------------|------------------|
| 1 | Scribner |
| 2 | Little, Brown and Company |
| 3 | J.B. Lippincott |
Table: Genres
| Genre ID | Genre Name |
|----------|---------------|
| 1 | Fiction |In this normalized schema, the Books table contains only the book ID, name, and foreign keys to the Publishers and Genres tables. The Publishers and Genres tables contain unique values for each publisher and genre, respectively.
By splitting the original table into three smaller tables, each with a single theme, we’ve eliminated the multi-valued column and ensured that each row is uniquely identified by a primary key. This satisfies the first normal form (1NF).
2. How to apply Second Normal Form (2NF) to a database table
Suppose we have a table that stores information about customer orders, with the following columns:
In this table, the primary key is the Order ID column and the other columns are not fully dependent on the primary key. Specifically, Customer Name is dependent on Order ID, while Product Name, Price, and Quantity are dependent on both Order ID and Product Name. This violates the second normal form (2NF).
Get Murat Aslan’s stories in your inbox
Join Medium for free to get updates from this writer.
To apply 2NF, we need to remove the partial dependencies by splitting the table into multiple tables. We can create a separate table for customers and products, and then link them to the orders table using foreign keys. Here’s an example of how we can apply 2NF to the order table:
Table: Orders
| Order ID | Customer ID | Product ID | Price | Quantity |
|----------|--------------|--------------|----------|-------------|
| 1 | 1 | 1 | 1099.00 | 1 |
| 2 | 2 | 2 | 399.00 | 2 |
| 3 | 1 | 3 | 1999.00 | 1 |
Table: Customers
| Customer ID | Customer Name |
|--------------|----------------|
| 1 | John Smith |
| 2 | Sarah Johnson |
Table: Products
| Product ID | Product Name |
|-------------|----------------------|
| 1 | iPhone 12 Pro Max |
| 2 | Apple Watch Series 6 |
| 3 | MacBook Pro |In this normalized schema, the Orders table contains only the order ID, foreign keys to the Customers and Products tables, and the price and quantity for each order. The Customers and Products tables contain unique values for each customer and product, respectively.
By splitting the original table into three smaller tables and removing the partial dependencies, we’ve ensured that each non-key column is fully dependent on the primary key, and that all data is stored without redundancy. This satisfies the second normal form (2NF).
3. An example of how to apply Third Normal Form (3NF) to a database table.
Suppose we have a table that stores information about employees and their projects, with the following columns:
In this table, the primary key is the combination of Employee ID and Project Name, and the other columns are not fully dependent on the primary key. Specifically, the columns Project Manager, Manager Department, and Manager Email are dependent only on Project Name, and not on Employee ID. This violates the third normal form (3NF).
To apply 3NF, we need to remove the transitive dependencies by splitting the table into multiple tables. We can create a separate table for projects and managers, and then link them to the employee-project table using foreign keys. Here’s an example of how we can apply 3NF to the employee-project table:
Table: Employee_Project
| Employee ID | Project Name |
|-------------|-----------------|
| 1 | Project A |
| 2 | Project B |
| 3 | Project C |
Table: Projects
| Project Name | Project Manager |
|----------------|-----------------------|
| Project A | Jane Doe |
| Project B | John Doe |
| Project C | Jane Doe |
Table: Managers
| Manager Name | Department | Email |
|-----------------|---------------|-----------------------|
| Jane Doe | Marketing | jane.doe@example.com |
| John Doe | Sales | john.doe@example.com |In this normalized schema, the Employee_Project table contains only the employee and project IDs, and serves as a link between the Projects and Managers tables. The Projects table contains unique values for each project, and the Managers table contains unique values for each manager. The Employee_Project table references these tables using foreign keys.
By splitting the original table into three smaller tables and removing the transitive dependencies, we’ve ensured that each non-key column is fully dependent on the primary key, and that all data is stored without redundancy. This satisfies the third normal form (3NF).
4. An example of how to apply Boyce-Codd Normal Form (BCNF) to a database table.
Suppose we have a table that stores information about students and their courses, with the following columns:
In this table, the primary key is the combination of Student ID and Course Name, and the other columns are not fully dependent on the primary key. Specifically, the columns Instructor Name and Department are dependent only on Instructor ID, and not on Student ID or Course Name. This violates the Boyce-Codd Normal Form (BCNF).
To apply BCNF, we need to identify the functional dependencies in the table and ensure that each determinant is a candidate key. In this case, we can see that the functional dependencies are:
- Student ID, Course Name → Instructor ID
- Instructor ID → Instructor Name, Department
We can create two separate tables for students-courses and instructors, and then link them using foreign keys. Here’s an example of how we can apply BCNF to the student-course table:
Table: Student_Course
| Student ID | Course Name | Instructor ID |
|------------|---------------|-----------------|
| 1 | Math | 100 |
| 1 | Physics | 101 |
| 2 | Physics | 101 |
Table: Instructor
| Instructor ID | Instructor Name | Department |
|-----------------|----------------------|--------------|
| 100 | John Smith | Mathematics |
| 101 | Sarah Johnson | Physics |In this normalized schema, the Student_Course table contains only the student and course IDs, and serves as a link between the Students and Courses tables. The Instructor table contains unique values for each instructor, and the Student_Course table references this table using foreign keys.
By splitting the original table into two smaller tables and ensuring that each determinant is a candidate key, we’ve satisfied the Boyce-Codd Normal Form (BCNF). This ensures that the data is stored without redundancy and that each non-key column is fully dependent on the primary key.
