Database Normalization – Normal Forms 1NF 2NF 3NF

[qingquan-li]

1NF 2NF 3NF

1NF (First Normal Form):

• Single valued attributes: Each column must contain only one value.
• No duplicate rows.
• Every column value is atomic (indivisible).

2NF (Second Normal Form):

• Must be in 1NF.
• No partial dependencies: All non-key attributes must be fully functionally dependent on the primary key.
• If it's a composite primary key, no column should be dependent on just part of that composite key.

3NF (Third Normal Form):

• Must be in 2NF.
• No transitive dependencies: Attributes must be functionally dependent only on the primary key.
• Every non-key attribute is non-transitively dependent on the primary key. (i.e., no column should depend on another column, which is not a primary key).

Explain 1NF

Table: StudentCourses (Before 1NF)

StudentID	CourseID	Instructor	CourseName
1	101	Mr. A	Math, Physics
1	102	Mr. B	English, History
2	101	Mr. A	Math, Biology

Here, the CourseName column violates 1NF because:

1. Multiple Values in a Single Column: The CourseName column contains multiple values (e.g., "Math, Physics" and "English, History").
2. Repeating Groups of Data: The table has groups of courses that repeat across different rows.

To make this table comply with 1NF, we need to:

• Ensure Single Value in Each Column: Each column should contain atomic (indivisible) values.
• Eliminate Repeating Groups: Data should not be duplicated.

Table: StudentCourses (After 1NF)

StudentID	CourseID	Instructor	CourseName
1	101a	Mr. A	Math
1	101b	Mr. A	Physics
1	102a	Mr. B	English
1	102b	Mr. B	History
2	101a	Mr. A	Math
2	101c	Mr. A	Biology

Notes:

• The CourseID has been adjusted to uniquely identify each course for the student. Another approach could involve having a composite key with StudentID and CourseName or a completely unique identifier for each row.
• Each CourseName now holds a single, atomic value.
• The repeating group issue is resolved as each course is listed on its own row.

This modified table now complies with the First Normal Form (1NF).

Defference between 2NF and 3NF

Difference between 2NF and 3NF:

In essence, the difference between 2NF and 3NF is in the kind of dependencies they address:

• 2NF addresses partial dependencies, where an attribute is functionally dependent on just a part of a composite primary key.
• 3NF addresses transitive dependencies, where an attribute is functionally dependent on another non-key attribute.

Example to illustrate the difference:

Consider a table that records the subjects that students are taking in a specific semester:

Table: StudentCourses

StudentID	CourseID	Instructor	CourseName
1	101	Mr. A	Math
1	102	Mr. B	English
2	101	Mr. A	Math

In this table, let's say the combination of StudentID and CourseID forms a composite primary key.

Violation of 2NF: Notice that CourseName is dependent only on CourseID, not on the combination of StudentID and CourseID. This is a partial dependency, making our table not comply with 2NF.

To normalize to 2NF, we'll split the table:

Table: StudentCourses

StudentID	CourseID	Instructor
1	101	Mr. A
1	102	Mr. B
2	101	Mr. A

Table: Courses

CourseID	CourseName
101	Math
102	English

Now, our tables are in 2NF.

Violation of 3NF: However, notice that Instructor is dependent on CourseID, which means there's a transitive dependency. The instructor of a course is likely to be the same, irrespective of the student taking it. This violates 3NF.

To normalize to 3NF, we'll further split the table:

Table: StudentCourses

StudentID	CourseID
1	101
1	102
2	101

Table: CourseInstructors

CourseID	Instructor
101	Mr. A
102	Mr. B

Table: Courses

CourseID	CourseName
101	Math
102	English

Now, our tables are in 3NF. There are no partial or transitive dependencies.