SECOND NORMAL FORM (2NF)
A relation is in second normal form if it is in 1NF and every non key attribute is fully
functionally dependent on the primary key.
A university uses the following relation:
Student(IDSt, StudentName, IDProf, ProfessorName, Grade)
The attributes IDSt and IDProf are the identification keys.
All attributes a single valued (1NF).
The following functional dependencies exist:
1. The attribute ProfessorName is functionally dependent on attribute IDProf (IDProf -->
ProfessorName)
2. The attribute StudentName is functionally dependent on IDSt (IDSt --> StudentName)
3. The attribute Grade is fully functional dependent on IDSt and IDProf (IDSt, IDProf -->
Grade)
Example Second normal form
The table in this example is in first normal form (1NF) since all attributes are single
valued. But it is not yet in 2NF. If student 1 leaves university and the tuple is deleted,
then we loose all information about professor Schmid, since this attribute is fully
functional dependent on the primary key IDSt. To solve this problem, we must create a
new table Professor with the attribute Professor (the name) and the key IDProf. The
third table Grade is necessary for combining the two relations Student and Professor
and to manage the grades. Besides the grade it contains only the two IDs of the student
and the professor. If now a student is deleted, we do not loose the information about the
professor.
Imagine we're building a restaurant management application. That application needs to
store data about the company's employees and it starts out by creating the following
table of employees:
All the entries are atomic and there is a composite primary key (employee_id, job_code)
so the table is in the first normal form (1NF).
But even if you only know someone's employee_id, then you can determine
their name, home_state, and state_code (because they should be the same person).
This means name, home_state, and state_code are dependent on employee_id (a part
of primary composite key). So, the table is not in 2NF. We should separate them to a
different table to make it 2NF.
Example of Second Normal Form (2NF)
employee_roles Table
EMPLOYEE_ID JOB_CODE
E001 J01
EMPLOYEE_ID JOB_CODE
E001 J02
E002 J02
E002 J03
E003 J01
employees Table
EMPLOYEE_ID NAME STATE_CODE HOME_STATE
E001 Alice 26 Michigan
E002 Bob 56 Wyoming
E003 Alice 56 Wyoming
jobs table
JOB_CODE JOB
J01 Chef
J02 Waiter
J03 Bartender
