Database Normalization

Process of Organizing Data into the Database to Avoid Data Anomalies.

Database normalization is the process of reorganizing attributes and relations in a database for ensuring data integrity. The primary purpose of the normalization technique is to reduce data redundancy or to reduce data anomalies in a relation (table) with the help of a formal rules named as normal forms.

Anomalies in Database

Anomalies in a database refer to that the inconsistency occurred in the relational tables during transactional operations. There are various reasons for anomalies. If a table is not constructed very well and contains a lot of redundant or duplicated data causes data anomalies. This leads to data integrity problems. So the common problems that happen without proper normalization are given below.

1. Difficult to maintain data (Insertion, Deletion, and Updation of data in a table becomes problematic).
2. Frequent formation of insertion, deletion, and updation anomalies (For e.g. if there are a number of redundant data in the database and if we need to change or modify anything in the data, then it needs to update everywhere to keep consistency of data).
3. Data redundancy takes up an inordinate amount of space on the disk. That means having an abundant resource without any application.

Database Normalization came into existence to resolve these problems. There are several levels of normalization, each with its own set of guidelines, and that are given below.

Normal Forms

There are four normal forms given below, and each one has its own constraints to achieve normalization.

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)

First Normal Form (1NF)

The first normal form requires that a table satisfies the following conditions:

Only have single(atomic) valued attributes/columns.

Rows and columns are not ordered.

Row-and-column intersections always have a unique value.

All columns are “regular” with no hidden values.

Here the first table contains the rollno, name, course, and age of the student. The course attribute contains multiple values. So it doesn’t satisfy 1st normal form. The second table is a reorganization of the first table that uses 1NF to achieve atomicity. In this table, multiple courses chosen by the same people are divided into different rows based on their courses.

Second Normal Form (2NF)

It should be in the First Normal form.

It should not have Partial Dependency.

The second normal form holds the first normal constraints and also it shouldn’t have a partial dependency. The partial dependency here means the proper subset of the candidate key should give a non-prime attribute.

The first table contains composite primary key as cust_id and storeid. The non-key attribute is store_location and which only depends on storeid, i.e., it has a partial dependency on the composite primary key. So it doesn’t satisfy the 2NF. For achieving 2NF, the table break into two parts, and one holds the storeid and store_location, which are related to each other. The next table contains cust_id and storeid.

Third Normal Form (3NF)

A table is said to be in the 3NF when,

It is in the Second Normal form.

It doesn’t have Transitive Dependency.

The third normal form contains 2NF constraints and there should be no transitive dependency for non-prime attributes, which indicates that non-prime attributes should not depend on other non-prime attributes in a table.

Get Anukrishna T’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

i.e., A → C, because of A → B and B → C (where it is not the case that B → A).

The third Normal Form ensures the reduction of data redundancy and is also used to achieve data integrity.

Here in the first table, stu_id is related to subid and subid finds the sub. Therefore stu_id is determined sub through subid and it shows a transitive dependency. To avoid this table is divided into two based on the stu_id and subid.

Boyce-Codd Normal Form (BCNF)

It is in 3rd Normal Form

For each functional dependency ( X → Y ), X should be a super Key.

The first condition for the table to be in Boyce Codd Normal Form is that the table should be in the third normal form. Secondly, every Right-Hand Side (RHS) attribute of the functional dependencies should depend on the super key of that particular table.

The first table satisfies 1NF because every column is unique and atomic. It follows 2NF, as there is no Partial Dependency. There is no transitive dependency and therefore it satisfies 3NF also. But it doesn’t satisfy the BCNF, because stuid, and subject form the primary key, which means the subject is a prime attribute.

However, there exists a dependency between professor and subject. BCNF does not satisfy the dependency between the non-prime attribute and the prime attribute. To resolve this first table is divided into two, one holds stuid and another one has created a new attribute for storing the profid.

Fourth Normal Form (4NF)

A table is said to be in the Fourth Normal Form when,

It is in the Boyce-Codd Normal Form.

It doesn’t have Multi-Valued Dependency.

The above first table satisfies 3 NF, but the COURSE and HOBBY are independent of each other and they have a multi-valued dependency. (i.e., one student (STU_ID 21)has chosen two different courses and hobbies. So the table is divided into two for satisfying the 4NF, one is for COURSE and another for HOBBY.

Fifth Normal Form (5NF)

It is in 4NF and not contains any join dependency.

All the tables are broken into as many tables as possible in order to avoid redundancy.

5NF (Fifth Normal Form) is also known as the project-join normal form. A relation is in Fifth Normal Form (5NF), if it is in 4NF, and won’t have lossless decomposition into smaller tables. It breaks the large tables into subtables based on the attributes specified on the table.

It is the final normal form as far as removing redundancy is concerned.

Conclusion

In this blog, you can understand the different types of normalization techniques in the database. This helps to reduce the data redundancy and anomalies problem in our database system and can achieve data integrity.