Second Normal Form(2NF) in DBMS
A table is said to be in Second Normal Form (2NF) when:
• It is in 1NF. and
• It includes no partial dependencies;
that is, no attribute is dependent on only a portion of the primary key.
(Note that it is still possible for a table in 2NF to exhibit transitive dependency; that is, the primary key may rely on one or more nonprime attributes to functionally determine other nonprime attributes, as is indicated by a functional dependence among the nonprime attributes.)
For example consider the following relation which is in 1NF but not in 2NF.
You May Also Like:
Normalization and its advantages.
All relational tables satisfy the 1NF requirements. The problem with the 1NF table structure shown in the above Figure it contains partial dependencies—that is, dependencies based on only a part of the primary key.
Table that contains partial dependencies is still subject to data redundancies, and therefore, to various anomalies.
For example, if Alice K. Johnson submits her work log, then the user would have to make multiple entries during the course of a day.
For each entry, the EMP_NAME, JOB_CLASS, and CHG_HOUR must be entered each time, even though the attribute values are identical for each row entered. Such duplication of effort is very inefficient.
Steps to convert the Relation into Second Normal Form:
Converting to 2NF is done only when the 1NF has a composite primary key. If the 1NF has a single-attribute primary key, then the table is automatically in 2NF.
Step 1: Make New Tables to Eliminate Partial Dependencies:
For each component of the primary key that acts as a determinant in a partial dependency, create a new table with a copy of that component as the primary key. While these components are placed in the new tables, it is important that they also remain in the original table as well. It is important that the determinants remain in the original table because they will be the foreign keys for the relationships that are needed to relate these new tables to the original table. For the construction of our revised dependency diagram, write each key component on a separate line; then write the original (composite) key on the last line. For example:
PROJ_NUM
EMP_NUM
PROJ_NUM EMP_NUM
Each component will become the key in a new table. In other words, the original table is now divided into three tables
(PROJECT, EMPLOYEE, and ASSIGNMENT).
Step 2: Reassign Corresponding Dependent Attributes:
The attributes that are dependent in a partial dependency are removed from the original table and placed in the new table with its determinant. Any attributes that are not dependent in a partial dependency will remain in the original table. In other words, the three tables that result from the conversion to 2NF are given appropriate names (PROJECT, EMPLOYEE, and ASSIGNMENT) and are described by the following relational schemas:
PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR)
ASSIGNMENT (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
The results of Steps 1 and 2 are displayed in the following Figure. At this point, most of the anomalies discussed earlier have been eliminated. For example, if you now want to add, change, or delete a PROJECT record, you need to go only to the PROJECT table and make the change to only one row.
You May Also Like:
