Normal Forms in DBMS

In database management, Normalization is the process of organizing data to minimize redundancy and dependency. It involves dividing a database into smaller tables and defining relationships between them to eliminate data anomalies. The stages of normalization are called normal forms (NF), each building upon the previous one with stricter requirements.

1. First Normal Form (1NF)

A relation is in 1NF if:

• The table has a primary key.
• All attributes (columns) contain atomic (indivisible) values.
• There are no repeating groups or arrays (each field contains a single value).

Example:

This table is not in 1NF because the “Subjects” column contains multiple values. To convert it to 1NF:

2. Second Normal Form (2NF)

A table is in 2NF if:

1. It is in 1NF.
2. All non-key attributes are fully dependent on the primary key (no partial dependency).

Example:

Here, “ProductName” depends only on “ProductID,” not on the composite key (“OrderID, ProductID”). To convert it to 2NF:

Orders Table:

Products Table:

3. Third Normal Form (3NF)

A table is in 3NF if:

1. It is in 2NF.
2. There is no transitive dependency (non-key attributes depend only on the primary key).

Example:

Here, “DeptName” is dependent on “DeptID,” which is not the primary key. To convert it to 3NF:

Students Table:

Departments Table:

4. Boyce-Codd Normal Form (BCNF)

A table is in BCNF if:

1. It is in 3NF.
2. Every determinant is a candidate key (a determinant is an attribute that uniquely identifies another attribute).

Example:

Here, “Subject” determines “DeptID,” but “Subject” is not a candidate key. To convert it to BCNF:

Teachers Table:

Subjects Table:

5. Fourth Normal Form (4NF)

A table is in 4NF if:

1. It is in BCNF.
2. It has no multi-valued dependencies (an attribute must not have two or more independent multi-valued attributes for the same primary key).

Example:

Here, “Course” and “Hobby” are independent of each other but linked by the same primary key. To convert it to 4NF:

Courses Table:

Hobbies Table:

6. Fifth Normal Form (5NF)

A table is in 5NF if:

1. It is in 4NF.
2. It cannot have any join dependency that is not implied by candidate keys.

This occurs in cases where data can be split into smaller tables without losing any information.

6. Sixth Normal Form (6NF)

Rarely used in practice, 6NF ensures that a table is free of non-trivial join dependencies, often used in specialized temporal databases.

Summary of Normal Forms

Each normal form reduces redundancy and improves data integrity, making the database more efficient and easier to maintain. Let me know if you need more examples or clarification! 😊