Normal Forms
Normalization works through a series of steps called normal forms. The first three are
described as first normal form (1NF), second normal form (2NF), and third normal form (3NF).
From a structural point of view, 2NF is better than 1NF, and 3NF is better than 2NF. For most
purposes in business database design, 3NF is as high as you need to go in the normalization
process.
First Normal Form (1NF)
A table or relation is in unnormalized form (UNF) if it does not comply with any of the
normalization rules. Since these forms are applied in sequence, if a relation does not comply
with the first normal form it will not comply with any of the others. The rules of first normal form
are as follows:
1. Attribute values should be atomic, in other words there should be no multi-valued
attributes (cells with multiple values of the same type).
2. Each record must be unique. No two records should have the exact combination of
attribute values.
In order to demonstrate the application of normalization rules we will make use of the following
table which records data on students and the courses that they take.
Student Student Course Course Lecturer Lecturer
ID Name Number Number Name
0001 James L. 1001 Math 2001 Jane C.
1002 Biology 2002 Henry W.
0002 Janet W. 1002 Biology 2002 Charles B.
0003 John C. 1003 Computer Science 2003 Neumann J.
This table is not in 1NF because the Courses column (attribute) has multiple values in a single
record (tuple). In order to solve this problem we can separate each value into an individual
record. Each record also needs to be unique so we use the STUD_ID along with COURSE
Number as the primary key since this combination does not repeat. We get the following table:
Student Student Course Course Lecturer Lecturer
ID Name Number Number Name
0001 James L. 1001 Math 2001 Jane C.
0001 James L. 1002 Biology 2002 Charles B.
0002 Janet W. 1002 Biology 2002 Charles B.
0003 John C. 1003 Computer Science 2003 Neumann J.
Second Normal Form (2NF)
The rules of second normal form are as follows:
1. Must be in first normal form (1NF)
2. Must not have partial dependencies (non-key attributes which depend only on a subset
of the composite primary key)
Our current table (above) has the Course, Lecturer Number and Lecturer Name
columns dependent on the Course_ID attribute but not the STUD_ID column since many
students may take the same course. In other words the Course_ID column only uniquely
determines the Courses, Lecturer Number and Lecturer Name columns. In order to
solve this we separate the relation into two as follows:
Students Table
Student ID Student Name
0001 James L.
0002 Janet W.
0003 John C.
Courses Table
Course Number Course Lecturer Number Lecturer Name
1001 Math 2001 Jane C.
1002 Biology 2002 Charles B.
1003 Computer Science 2003 Neumann J.
We must also create a third table in order to establish the many-to-many relationship that exists
between Students and Courses (One student can take multiple courses and one course can
be taken by multiple students). This table includes both the Student ID and Course Number
fields. We can call this table Student_Course:
Student_Course
Student ID (FK) Course Number (FK)
0001 1001
0001 1002
0002 1002
0003 1003
In this case both Student ID and Course Number are foreign keys. The Course Number
references a course in the Courses table while the Student ID references a student in the
Students table.
Third Normal Form (3 NF)
The rules of third normal form are as follows:
1. Must be in second normal form (2NF)
2. Must not contain transitive functional dependencies
The Courses table is in the following form after applying the rules of first and second
normal form:
Course Number Course Lecturer Number Lecturer Name
1001 Math 2001 Jane C.
1002 Biology 2002 Charles B.
1003 Computer Science 2003 Neumann J.
A transitive functional dependency exists where one non-key attribute in a relation is
functionally dependent on another non-key attribute, which in turn is dependent on the primary
key. This relationship can be expressed in the form:
A→B →C
where A is the key attribute and B and C are non-key attributes. A determines B and B
determines C. These types of dependencies may be difficult to detect but can be identified by
responding to the question of whether a change in one non-key field will result in the change in
another non-key field.
In the Courses table above, the Course Number primary key determines the Lecturer
Number since only a single lecturer can be assigned to a course. However the Lecturer
Number also determines the Lecturer Name, since only one lecturer can have a specific
Lecturer Number.
This problem can be solved by separating the Lecturer Number and Lecturer Name
columns into a separate table. A foreign key for the Lecturer Number needs to be placed in
the Courses table in order to establish the relationship.
Courses
Course Number Course Lecturer Number (FK)
1001 Math 2001
1002 Biology 2002
1003 Computer Science 2003
Lecturers
Lecturer Number Lecturer Name
2001 Jane C.
2002 Charles B.
2003 Neumann J.