normalization in dbms | normal forms | 1nf, 2nf, 3nf, bcnf, 4nf, 5nf normal forms with examples

Summary

The video explains normalization, also called schema refinement, as a process for organizing database tables to reduce duplicated data and prevent errors. It covers the normal forms from 1NF through 5NF, using examples involving students, branches, courses, hobbies, and faculty.

Why Normalize?

Repeated data, known as data redundancy, can make databases harder to maintain and lead to anomalies:

• Insertion anomaly: Data cannot be added because the table structure requires other information that is unavailable. The example describes trying to insert a student record when the table has no age column.
• Update anomaly: The same fact appears in multiple rows and must be changed in each one. Missing an update can leave inconsistent data.
• Deletion anomaly: Removing one record unintentionally removes other useful information. The example describes deleting a student record and losing associated information.

Normalization helps reduce redundancy and supports data consistency and integrity. The lecture also notes that advanced normalization can affect performance, and that 1NF does not allow multiple values in a single attribute.

Normal Forms

1. First Normal Form (1NF)

   • Each field should contain a single, atomic value—not a list or set of values.
   • In the example, a student’s multiple known languages are represented as separate rows rather than stored together in one field.

2. Second Normal Form (2NF)

   • A table must first be in 1NF and have no partial dependencies.
   • The example separates student details, such as roll number, name, and branch, from student–language records.
   • More precisely, a partial dependency occurs when a non-key attribute depends on only part of a composite candidate key. Repeated values in a column alone do not define a partial dependency.

3. Third Normal Form (3NF)

   • A table must be in 2NF and have no transitive dependency of non-key attributes on a key.
   • In the example, a student’s branch determines the fee, so the fee depends on the branch rather than directly on the student’s roll number.
   • The lecture separates branch and fee information into another table.

4. Boyce–Codd Normal Form (BCNF)

   • BCNF is described as a stricter extension of 3NF.
   • Every determinant—the attribute or set of attributes that determines another attribute—must be a superkey.
   • In the example, branch ID determines branch name but repeats in the student table. The lecture separates branch details into a branch table, where branch ID is unique.

5. Fourth Normal Form (4NF)

   • A table must be in BCNF and have no nontrivial multivalued dependencies.
   • In the example, each student is independently associated with multiple courses and multiple hobbies. The lecture separates these into a student–course table and a student–hobby table.

6. Fifth Normal Form (5NF), or Project-Join Normal Form

   • A table must be in 4NF and have no problematic join dependencies.
   • The faculty example uses subject, faculty, and year columns. The lecture decomposes it into subject–faculty, faculty–year, and subject–year tables to remove duplicate records within those tables.
   • The stated aim is to decompose a relation further where needed to avoid redundancy.

Speakers and Sources Featured

• NS Lectures instructor: The sole speaker; the instructor is not named in the subtitles.
• No other speakers or external sources are featured in the subtitles.