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:
Multiple Values in a Single Column: The CourseName column contains multiple values (e.g., "Math, Physics" and "English, History").
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.
1NF 2NF 3NF
1NF (First Normal Form):
2NF (Second Normal Form):
3NF (Third Normal Form):
Explain 1NF
Table: StudentCourses (Before 1NF)
Here, the
CourseName
column violates 1NF because:CourseName
column contains multiple values (e.g., "Math, Physics" and "English, History").To make this table comply with 1NF, we need to:
Table: StudentCourses (After 1NF)
Notes:
CourseID
has been adjusted to uniquely identify each course for the student. Another approach could involve having a composite key withStudentID
andCourseName
or a completely unique identifier for each row.CourseName
now holds a single, atomic value.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:
Example to illustrate the difference:
Consider a table that records the subjects that students are taking in a specific semester:
In this table, let's say the combination of
StudentID
andCourseID
forms a composite primary key.Violation of 2NF: Notice that
CourseName
is dependent only onCourseID
, not on the combination ofStudentID
andCourseID
. This is a partial dependency, making our table not comply with 2NF.To normalize to 2NF, we'll split the table:
Now, our tables are in 2NF.
Violation of 3NF: However, notice that
Instructor
is dependent onCourseID
, 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:
Now, our tables are in 3NF. There are no partial or transitive dependencies.