Understanding the Basics of Database Normalization

Harini C 02 Mar, 2023 • 6 min read

Introduction

Data normalization is the process of building a database according to what is known as a canonical form, where the final product is a relational database with no data redundancy. More specifically, normalization involves organizing data according to attributes assigned as part of a larger data model. The main goals of database normalization are eliminating redundant data, minimizing data modification errors, and simplifying the query process.

Database normalization is the process of restructuring a relational database according to a set of so-called paradigms to reduce data redundancy and establish data integrity. First introduced by Edgar F. Codd is an integral part of his relational model.

In database design, the normal form is a set of guidelines for ensuring that data is organized efficiently and without redundancy. There are several normal forms that we are going to discuss in this article. If you want to cover the basics of database normalization, click here.

This article was published as a part of the Data Science Blogathon.

First Normal Form (1NF)

The 1NF (First Normal Form) rule is a fundamental principle of relational database design, all tables must have a primary key, and all columns must contain atomic values. This means that a table cannot contain repeating groups or arrays of values in a single column; instead, in a smaller table, each column contains only a single value that must be split.

Source: Medium

Before proceeding, we need to know about some important things :

What is a Key in SQL?

A key is a column or group of columns in a table that uniquely identifies each row or record in the table. Keys are used to enforce data integrity, ensure that data is unique and not duplicated, and establish relationships between tables in a relational database.

What is the Primary Key?

A primary key is a column or set of columns in a table that uniquely identifies each row or record in the table. Primary keys are used to enforce data integrity, ensure that data is unique and not duplicated, and are often used as the basis for establishing relationships between tables in relational databases.

Here are some important points about primary keys:

A primary key must contain a unique value for each row in the table and cannot contain NULL values.
A table must have only one primary key. Primary key column values should be stable and should not change.
Used as a basis for establishing relationships between tables using foreign keys.
A primary key can be defined using the CREATE TABLE statement with the PRIMARY KEY constraint.

Source: Javapoint

What is a Composite Key?

A composite key is the combination of two or more columns in a table that uniquely identifies each row or record in the table. Composite keys are used when a single column cannot uniquely identify a row in a table, and additional columns are required to ensure uniqueness.

Here are some important points about composite keys:

A composite key consists of two or more table columns uniquely identifying each row.
Each composite key column can contain NULL values as long as the combination of values is unique.
Composite keys are typically used in tables that model many-to-many relationships between other tables.
The CREATE TABLE statement with the PRIMARY KEY or UNIQUE constraint can define a composite key.

Source: guru99

Second Normal Form (2NF)

This requires that the table is in 1NF, and all non-key columns depend on the table’s primary key.

Source: eduCBA

Database Foreign Key:

In the above Project table, PROJECT_ID is Foreign Key.

Source: eduCBA

Here are Some Key Points About Foreign Keys:

A foreign key in one table references a primary key in another table. The purpose of foreign keys is to enforce referential integrity between tables and maintain the consistency and correctness of data within tables.
Foreign keys can be used to create a one-to-many relationship between two tables. In this case, one table (the “child” table) contains a foreign key that references the primary key of another table (the “parent” table).
Foreign key column values must match the referenced primary or null values.
A foreign key can be defined using the CREATE TABLE statement with the FOREIGN KEY constraint.

Third Normal Form (3NF)

This requires that the table is 2NF and has no transitive dependencies. That is if A depends on B, and B depends on C, then A must directly depend on C.

Source: giffa.info

The 3NF rule can be summarized as follows:

The table must already be in second normal form (2NF). No non-key columns must depend on another non-key column (all non-key columns depend on the primary key).
Any non-key column that depends on another non-key column should be dropped and placed in its own table.

Boyce-Codd Normal Form (BCNF)

This requires the table to be 3NF and each determinant in the table to be a candidate key. That is, there should be no functional dependencies between non-key attributes.

Interpretation of the Table:

Students can enroll in multiple courses.

Example: A student with ID 101 is enrolled in Java and C++. Professors are assigned to students of a particular subject, and there is always the possibility of more than one professor teaching a particular subject.

To meet the BCNF requirements, we decompose the table into a student table and a professor table.

Fourth Normal Form (4NF)

This requires that the table is BCNF and has no multivalued dependencies.

Fifth Normal Form (5NF)

This requires that the table is 4NF and has no significant join dependencies.

Note that normalization may involve a trade-off between reducing verbosity and simplifying queries. Finding the right balance based on your application’s specific needs is important.

Benefits of Database Normalization

Minimize Data Redundancy: Normalization reduces data redundancy by storing each data item only once, reducing disk space requirements for storage.
Eliminate Update Anomalies: By reducing redundancy and dependencies, normalization eliminates update anomalies that can occur when updating a data item requires updating multiple records, resulting in inconsistencies.
Leads to Improve Data Consistency: Normalization reduces redundancies and dependencies and ensures data consistency across databases. This maintains data integrity.
Better Data Relationships: Normalization improves data relationships by ensuring that related data is stored in the same table, making data easier to query and analyze.

Overall, normalization is a critical process for creating efficient, consistent, and maintainable databases.

Conclusion

Thus, producing clean data is commonly referred to as data normalization. Nevertheless, when you delve deeper, the meaning or goal of data normalization is twofold:

Data normalization is organizing data to seem consistent across all records and fields.
It improves entry-type cohesiveness, resulting in cleansing, lead creation, segmentation, and higher-quality data.

Some of the key takeaways from the article are stated below:

Data normalization is essential for optimizing database efficiency, consistency, and maintainability.
It helps minimize data redundancy, eliminate update anomalies, and improve data relationships and consistency. Normalization simplifies database design, makes data easier to manage and query and improves database performance.
Ultimately, normalization ensures that data is stored in a way that maximizes its value and usability and is an important step in the database development process.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.