Normalization in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn normalization, redundancy, anomalies, functional dependency, 1NF, 2NF, 3NF, BCNF, and how normalization improves table design.
Normalization is the process of organizing database tables to reduce duplicate data and avoid data modification problems. It helps create clean table designs where each fact is stored in the right place. A beginner can understand normalization with a student-course example. If student details are repeated in every course record, changing a student's phone number requires updating many rows. If one row is missed, the database becomes inconsistent.
Normalization is important because poor table design can create confusion, extra storage usage, and wrong reports. A well-normalized database is easier to update, maintain, and understand.
Why Normalization Is Needed
Normalization is needed to reduce unnecessary duplicate data. Duplicate data is called redundancy. Some redundancy may be acceptable in special cases, but unnecessary redundancy usually creates problems. For example, if a student's department name is stored in many rows, and the department name changes, every row must be updated. If even one row is not updated, the database will contain conflicting information.
Normalization also helps remove anomalies. An anomaly is an unwanted problem that happens during insert, update, or delete operations because of poor table design.
Unnormalized Table Example
The following table stores student and course data together. It looks simple, but it repeats student information for every course.
```text
StudentCourse
+---------+--------+--------+-----------+-------------+
| roll_no | name | phone | course_id | course_name |
+---------+--------+--------+-----------+-------------+
| 101 | Rahul | 9999 | C01 | DBMS |
| 101 | Rahul | 9999 | C02 | Java |
| 102 | Priya | 8888 | C01 | DBMS |
+---------+--------+--------+-----------+-------------+
```
Here, Rahul's name and phone number are repeated because Rahul has two courses. If Rahul changes phone number, multiple rows must be updated.
Normalized Design Example
A better design separates student data, course data, and enrollment data into different tables.
```text
Students Courses
+---------+--------+-------+ +-----------+-------------+
| roll_no | name | phone | | course_id | course_name |
+---------+--------+-------+ +-----------+-------------+
| 101 | Rahul | 9999 | | C01 | DBMS |
| 102 | Priya | 8888 | | C02 | Java |
+---------+--------+-------+ +-----------+-------------+
Enrollment
+---------+-----------+
| roll_no | course_id |
+---------+-----------+
| 101 | C01 |
| 101 | C02 |
| 102 | C01 |
+---------+-----------+
```
Now student details are stored once, course details are stored once, and the Enrollment table connects students with courses. This is cleaner and easier to update.
Data Redundancy
Data redundancy means storing the same data repeatedly in multiple places. It wastes storage and increases the chance of inconsistency. For example, if a student's phone number is repeated in ten rows, every row must be changed when the phone number changes. If only nine rows are updated, one row still contains old data.
Normalization reduces redundancy by splitting data into proper tables and connecting those tables using keys.
Anomalies
An anomaly is a problem that occurs because of poor database design. The three main types are insertion anomaly, update anomaly, and deletion anomaly.
1. Insertion anomaly: We cannot insert some data unless unrelated data is also available.
2. Update anomaly: The same data must be updated in many places.
3. Deletion anomaly: Deleting one row accidentally removes another useful fact.
These anomalies are the main reason normalization is taught. Good table design prevents these problems before they appear in real applications.
Insertion Anomaly
Insertion anomaly happens when we cannot insert a record because some unrelated information is missing. For example, in the combined StudentCourse table, we may not be able to insert a new course unless at least one student has enrolled in it. This is a design problem because course information should be stored independently. After normalization, course details can be inserted into the Courses table even if no student has enrolled yet.
Update Anomaly
Update anomaly happens when the same data appears in multiple rows and all copies must be updated. For example, if Rahul's phone number appears in many course rows, every row must be changed when his number changes. If one row is missed, the database becomes inconsistent. After normalization, Rahul's phone number appears only once in the Students table, so it needs to be updated only once.
Deletion Anomaly
Deletion anomaly happens when deleting one record accidentally removes another important fact. For example, if the only student enrolled in a course is deleted from the combined table, the course information may also disappear. This is wrong because course information should not depend on one enrollment row. After normalization, deleting an enrollment record does not delete the course itself because course details are stored separately.
Functional Dependency
Functional dependency means the value of one attribute determines the value of another attribute. It is written as A -> B, meaning A determines B. For example, roll_no -> student_name means if we know the roll number, we can determine the student name. course_id -> course_name means if we know the course ID, we can determine the course name.
Functional dependency is important because normal forms are based on how attributes depend on keys.
Normal Forms
Normal forms are step-by-step rules used to improve table design. Each normal form removes a specific kind of problem. The beginner-required normal forms are listed below.
1. 1NF: Removes repeating groups and ensures atomic values.
2. 2NF: Removes partial dependency.
3. 3NF: Removes transitive dependency.
4. BCNF: A stronger version of 3NF for certain dependency cases.
Students should understand 1NF, 2NF, and 3NF clearly first. BCNF can be studied after these are comfortable.
First Normal Form
First Normal Form, or 1NF, says that every attribute should contain atomic values. Atomic means a value should not be a list or group of multiple values in one field. For example, storing two phone numbers in one column like `9999, 8888` violates 1NF. A better design stores phone numbers separately so each value is clear.
1NF also removes repeating groups. A table should not have columns like course1, course2, and course3 for the same type of information. Such repeated columns make the design inflexible.
Second Normal Form
Second Normal Form, or 2NF, applies when a table has a composite key. A table is in 2NF if it is already in 1NF and every non-key attribute depends on the whole composite key, not only part of it. For example, suppose Enrollment has student_id and course_id as a composite key. If course_name depends only on course_id, then course_name should not be stored in Enrollment. It should be stored in the Courses table.
This problem is called partial dependency. 2NF removes partial dependency.
Third Normal Form
Third Normal Form, or 3NF, removes transitive dependency. A transitive dependency happens when a non-key attribute depends on another non-key attribute. For example, suppose a Student table stores roll_no, student_name, department_id, and department_name. If department_name depends on department_id, not directly on roll_no, then department details should be placed in a separate Department table.
3NF helps keep each table focused on one main topic and prevents indirect dependency problems.
BCNF
BCNF stands for Boyce-Codd Normal Form. It is a stronger version of 3NF. A table is in BCNF when every determinant is a candidate key. BCNF handles special cases where 3NF may still allow some dependency problems. It is usually studied after students understand functional dependency and 3NF properly.
For beginner preparation, it is enough to understand that BCNF is stricter than 3NF and is used to improve table design further.
Normalization Summary Table
This table gives a clean summary of the main normal forms. It is useful for quick revision.
| Normal Form | Main Rule | Problem Removed |
| --- | --- | --- |
| 1NF | Values should be atomic | Repeating groups |
| 2NF | Non-key attributes depend on full key | Partial dependency |
| 3NF | Non-key attributes should not depend on other non-key attributes | Transitive dependency |
| BCNF | Every determinant should be a candidate key | Advanced dependency issues |
Advantages of Normalization
Normalization reduces duplicate data and keeps records more consistent. It makes updates safer because each fact is usually stored in one proper place. It also makes the database easier to maintain. When tables have clear responsibilities, developers can understand the design more quickly.
Normalization supports better data integrity because relationships are handled through keys rather than repeated text values.
Disadvantages of Over-Normalization
Too much normalization can sometimes make queries more complex because data may be split into many tables. To display a report, the database may need several joins. In some performance-focused systems, designers may intentionally use limited denormalization. Denormalization means adding some controlled redundancy to improve read performance.
For beginners, the first goal should be learning proper normalization. Denormalization should be understood later as a performance decision, not as a replacement for good design.
Important Points to Remember
Normalization organizes tables to reduce redundancy and avoid anomalies. It uses normal forms such as 1NF, 2NF, 3NF, and BCNF. 1NF focuses on atomic values. 2NF removes partial dependency. 3NF removes transitive dependency. BCNF is stricter than 3NF.
For students, the best way to understand normalization is through examples. Always ask: is data repeated unnecessarily, and does every attribute depend on the correct key?