Normal Forms

Normal Forms in Database Design

Normal Forms (NF) are a set of guidelines or rules used to design relational database schemas that minimize redundancy and dependency. The goal of using normal forms is to organize the data efficiently to avoid anomalies like update, insert, and delete anomalies, which can lead to data inconsistencies.

Each normal form addresses a different type of redundancy or anomaly. The process of achieving higher normal forms involves ensuring the relation (or table) meets certain conditions. There are several normal forms, and each normal form builds on the previous one, aiming to refine the structure of the database schema.

Let's go through each normal form and the steps necessary to achieve them.

---

1. First Normal Form (1NF)

A relation (table) is in First Normal Form (1NF) if:

• Each attribute contains only atomic (indivisible) values.
• Each column contains a single value (no sets, lists, or arrays).
• Each row (tuple) in the table must be unique, meaning no duplicate rows.

Key Points:

• Eliminate repeating groups or multi-valued attributes. A repeating group occurs when a column contains multiple values (such as multiple phone numbers or addresses).
• Each record (row) in the table should be unique.

Example (Before 1NF):

EmployeeID	Name	Phone Numbers
1	Alice	12345, 67890
2	Bob	23456

Here, Phone Numbers is not atomic because it contains multiple values for each employee.

After 1NF:

EmployeeID	Name	Phone Number
1	Alice	12345
1	Alice	67890
2	Bob	23456

Now, each column contains only atomic values, and the table is in 1NF.

---

2. Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) if:

• It is in First Normal Form (1NF).
• There are no partial dependencies. That is, every non-key attribute is fully functionally dependent on the entire primary key (in the case of composite keys).

Key Points:

• Partial dependency occurs when a non-key attribute is dependent on only part of a composite primary key. 2NF removes these dependencies.

Example (Before 2NF):

Consider a table storing information about students, courses, and instructors:

StudentID	CourseID	Instructor	InstructorPhone
1	C101	Dr. Smith	12345
1	C102	Dr. Johnson	67890
2	C101	Dr. Smith	12345

Here, StudentID, CourseID is the primary key, but Instructor and InstructorPhone are only dependent on CourseID, not the entire composite key. This is a partial dependency.

After 2NF:

We need to separate the data into two tables to eliminate the partial dependency:

Student-Course Table:

StudentID	CourseID
1	C101
1	C102
2	C101

Course-Details Table:

CourseID	Instructor	InstructorPhone
C101	Dr. Smith	12345
C102	Dr. Johnson	67890

Now, there are no partial dependencies, and the table is in 2NF.

---

3. Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) if:

• It is in Second Normal Form (2NF).
• There are no transitive dependencies. A transitive dependency occurs when one non-key attribute depends on another non-key attribute.

Key Points:

• A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key. In 3NF, every non-key attribute must be directly dependent on the primary key.

Example (Before 3NF):

StudentID	CourseID	Instructor	InstructorPhone	Department
1	C101	Dr. Smith	12345	HR
1	C102	Dr. Johnson	67890	IT

Here, InstructorPhone is dependent on Instructor, and Instructor is dependent on CourseID. Thus, InstructorPhone is transitively dependent on CourseID via Instructor.

After 3NF:

To remove the transitive dependency, we decompose the table into two:

Student-Course Table:

StudentID	CourseID
1	C101
1	C102

Course-Details Table:

CourseID	Instructor	Department
C101	Dr. Smith	HR
C102	Dr. Johnson	IT

Instructor-Phone Table:

Instructor	InstructorPhone
Dr. Smith	12345
Dr. Johnson	67890

Now, the table is in 3NF because there are no transitive dependencies.

---

4. Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd Normal Form (BCNF) if:

• It is in Third Normal Form (3NF).
• For every non-trivial functional dependency, the left-hand side is a superkey.

Key Points:

• BCNF is a stricter version of 3NF. It ensures that there are no exceptions to the rule that every determinant is a superkey.

Example (Before BCNF):

Consider a table storing information about courses and instructors:

CourseID	Instructor	Room
C101	Dr. Smith	R1
C102	Dr. Johnson	R2

If Instructor → Room, then Instructor determines Room, but Instructor is not a superkey (since CourseID is also required to uniquely identify a record). This violates BCNF.

After BCNF:

We decompose the table to ensure all determinants are superkeys:

Instructor-Details Table:

Instructor	Room
Dr. Smith	R1
Dr. Johnson	R2

Course Table:

CourseID	Instructor
C101	Dr. Smith
C102	Dr. Johnson

Now, the relation is in BCNF because Instructor is a superkey in the first table, and no non-key attribute determines anything other than the key.

---

5. Fourth Normal Form (4NF)

A relation is in Fourth Normal Form (4NF) if:

• It is in Boyce-Codd Normal Form (BCNF).
• It has no multi-valued dependencies. A multi-valued dependency occurs when one attribute determines multiple values of another attribute, but there is no logical relationship between the two.

Key Points:

• Multi-valued dependencies can lead to redundant data. In 4NF, these dependencies are removed.

Example (Before 4NF):

Consider a table storing information about employees and their skills and languages:

EmployeeID	Skill	Language
1	Programming	English
1	Management	Spanish

Here, EmployeeID determines both Skill and Language, but there is no direct relationship between Skill and Language. This is a multi-valued dependency.

After 4NF:

We split the table into two:

Employee-Skill Table:

EmployeeID	Skill
1	Programming
1	Management

Employee-Language Table:

EmployeeID	Language
1	English
1	Spanish

Now, the table is in 4NF because there are no multi-valued dependencies.

---

Summary of Normal Forms

1. 1NF (First Normal Form): Ensure that the table contains atomic values, with no repeating groups or arrays.
2. 2NF (Second Normal Form): Eliminate partial dependencies by ensuring all non-key attributes are fully dependent on the primary key.
3. 3NF (Third Normal Form): Eliminate transitive dependencies by ensuring non-key attributes depend directly on the primary key.
4. BCNF (Boyce-Codd Normal Form): A stricter version of 3NF, ensuring that every determinant is a superkey.
5. 4NF (Fourth Normal Form): Eliminate multi-valued dependencies to ensure the relation is free from redundancy caused by independent sets of data.

By following these normal forms, we create database schemas that are efficient, minimize redundancy, and reduce the likelihood of data anomalies.