Normalization
The process of arranging data in a database is called normalization. It involves creating tables and interactions between them in accordance with guidelines designed to protect the data and increase database flexibility by getting rid of redundant and inconsistent dependencies.
Redundant data causes maintenance issues and wastes disk space. If it is necessary to modify data that is present in several areas, it must be done in exactly the same manner in each site. If a customer's address is only kept in the Customers table and not somewhere else in the database, it will be simpler to implement.
What does the term "inconsistent dependency" mean? Even while it's easy for a user to get a specific client's address in the Customers table, it might not be logical to search there for the employee's pay when calling on that customer. The employee's wage should be transferred to the Employees table since it is connected to or dependent upon the employee. Data access may be restricted by inconsistent dependencies because the path to the data may be incomplete or missing.
Real-world situations don't always provide perfect compliance, as is the case with many formal regulations and standards. Additional tables are typically needed for normalization, which some clients find inconvenient. If you choose to break one of the first three normalization criteria, make sure your application is prepared for potential issues like inconsistent dependencies and redundant data.
Types of Normal Form in DBMS
1. First Normal Form (1NF)
If a table has an atomicity of 1, it is said to be in its First Normal Form.
According to atomicity, a single cell can only have one characteristic and cannot include multiple values.
Multi-valued attributes, composite attributes, and their combinations are not allowed in the First Normal Form.
Now, you will understand the First Normal Form with the help of an example:
The student's name, course, age, and roll number are all listed in the student record table below.
| Roll no. | Name | Course | Age |
| 001 | Varun | C/C++ | 22 |
| 002 | Tannu | Java | 20 |
| 003 | Annu | Java | 19 |
| 004 | Harsh | C/C++ | 23 |
There are two values in the course column of the student record table. It does not, however, belong to the First Normal Form.
Using the First Normal Form in the above table provides the table below.
| Roll no. | Name | Course | Age |
| 001 | Varun | C | 22 |
| 001 | Varun | C++ | 22 |
| 002 | Tannu | Java | 20 |
| 003 | Annu | Java | 19 |
| 004 | Harsh | C | 23 |
| 004 | Harsh | C++ | 23 |
By applying the First Normal Form, we achieve atomicity; every column has unique values.
2. Second Normal Form (2NF)
No partial dependency should exist in 2NF, and the relation should be 1NF. Partial reliance occurs when, even in cases where the primary key is composite, the non-prime attributes are totally dependent on the candidate or primary key.
Example :
Consider the following table. Its primary key is {Student ID, Student Name}.
The functional dependencies given are
Student ID - Student Name
Project ID - Project Name
| Student ID | project ID | Student Name | Project Name |
| 001 | p2 | varun | IOT |
| 002 | P1 | Tannu | Cloud |
| 003 | P7 | Reena | IOT |
| 004 | P3 | Annu | Cloud |
As it represents Partial dependency, we decompose the table as follows :
| Student ID | project ID | Student Name |
| 001 | p2 | varun |
| 002 | P1 | Tannu |
| 003 | P7 | Reena |
| 004 | P3 | Annu |
| project ID | Project Name |
| p2 | IOT |
| P1 | Cloud |
| P7 | IOT |
| P3 | Cloud |
Here Project ID is mentioned in both tables to set up a relationship between them.
3. Third Normal Form
No transitivity dependency should exist in 3NF, meaning that non-prime attributes shouldn't determine non-prime attributes; instead, the given relation should be 2NF.
Example:
Examine the situation below, in which the functional relationships are :
A→B and B→C, where A is Primary Key.
In this case, a prime characteristic can determine a non-prime attribute, suggesting the existence of transitivity dependency. We breakdown and transform this into 3NF in order to eliminate it. We hence establish two relations:
A is the primary key in R1(A, B), while B is the primary key in R2(B, C).
4. Boyce- Code Normal Form
The relationship should be in 3NF in BCNF. In the case of a relation, such as A → B, A ought to be a super key. This suggests that neither prime nor non-prime attributes should be used to determine or derive any prime attribute.
Considering the table that follows. {Student, Teacher} and {Student, Subject} are its candidate keys.
The following are the functional dependencies:
{Student, Teacher} → Subject
{Student, Subject} → Teacher
Teacher → Subject
| Student Name | Subject | Teacher |
| Varun | Physics | Sumit |
| Tannu | English | Kirti |
| Reena | Physics | Sumit |
| Annu | English | Kiran |
As the table is not in BCNF Form , we decompose it into the following table:
| Student Name | Teacher |
| Varun | Sumit |
| Tannu | Kirti |
| Reena | Sumit |
| Annu | Kiran |
| Subject | Teacher |
| Physics | Sumit |
| English | Kirti |
| Physics | Sumit |
| English | Kiran |
Here Teacher is mentioned in both the tables to set up the relationship between them.
5. Fourth Normal Form (4NF)
Any relation must be in Boyce-Codd Normal Form and free of multi-valued dependencies in order to be in 4NF. By removing non-trivial multi-valued dependencies other than those involving the candidate key, it improves the database.
Example: Consider the following table:
| Student Name | Subject | Student Phone Number |
| Varun | Physics | 9863568976 |
| Tannu | English | 9876487653 |
| Reena | Physics | 5678654678 |
| Annu | English | 8765498765 |
| Student Name | Student Phone Number |
| Varun | 9863568976 |
| Tannu | 9876487653 |
| Reena | 5678654678 |
| Annu | 8765498765 |
| Student Name | Subject |
| Varun | Physics |
| Tannu | English |
| Reena | Physics |
| Annu | English |
The subject and student phone number in the above table are two separate things that do not relate to one another. Therefore, we break down the table as follows to convert it to 4NF:
Here Student is mentioned in both tables to set up a relationship between them.
