What is 2NF? How is it achieved.
Database Management System
Computer Science Engineering
687
Angelina
The second step in Normalization is 2NF. A table is in 2NF, only if a relation is in 1NF and meet all the rules, and every non-key attribute is fully dependent on primary key. The Second Normal Form eliminates partial dependencies on primary keys.
A relation is said to be in the 2nd Normal Form in DBMS (or 2NF) when it is in the First Normal Form but has no non-prime attribute functionally dependent on any candidate key's proper subset in a relation. A relation's non-prime attribute refers to that attribute that isn't a part of a relation's candidate key.
Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject. In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That's why it violates the rule for 2NF.
Below are a few of the advantages of using the second normal form.
Second Normal Form (2NF) is a level of database normalization that aims to eliminate redundant data in a database table. It is based on the concept of functional dependencies, which refers to the relationship between attributes in a table.
To satisfy 2NF, a table must first satisfy 1NF, which requires that the table be free of repeating groups and have a primary key defined. Additionally, every non-key attribute in the table must be functionally dependent on the entire primary key.
A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).
In other words, a table is in 2NF if it meets the following two requirements:
If a table has attributes that are dependent on only part of the primary key, those attributes are moved to a separate table with a foreign key relationship to the original table.
By eliminating partial dependencies in this way, we can avoid redundancy and ensure that each piece of data is stored in only one place, making the database more efficient and less prone to errors.
By satisfying 2NF, we can ensure that the data in our database is organized in a way that minimizes redundancy, which can lead to data inconsistencies and other issues.
To explain 2NF with an example, consider a table called "Orders" with the following columns:
This table is not in 2NF because the non-key attributes "Customer Name," "Product Name," and "Product Description" are not fully dependent on the primary key. Instead, they are partially dependent on the "Customer ID" and "Product ID" columns.
To normalize this table to 2NF, we need to split it into two tables: "Orders" and "Products."
The "Orders" table will have the following columns:
The "Products" table will have the following columns:
By doing this, we have eliminated data redundancy and ensured that each table contains data that is relevant and fully dependent on the primary key.
Second Normal Form (2NF) is a level of database normalization that aims to eliminate redundant data in a database table. It is based on the concept of functional dependencies, which refers to the relationship between attributes in a table.
Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject.
TEACHER table
Teacher ID | Subject | Age |
---|---|---|
25 | Chemistry | 30 |
15 | Biology | 35 |
16 | Physics | 30 |
25 | Computer | 35 |
12 | Computer | 35 |
In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That's why it violates the rule for 2NF. To convert the given table into 2NF, we decompose it into two tables:
TEACHER_DETAIL table:
Teacher ID | Age |
---|---|
25 | 30 |
15 | 35 |
16 | 30 |
TEACHER_SUBJECT table:
Teacher ID | Subject |
---|---|
25 | Chemistry |
15 | Biology |
16 | Physics |
25 | Computer |
12 | Computer |