Second normal form (2NF) is the second step in normalizing a database. 2NF builds on the first normal form (1NF).
Normalization is the process of organizing data in a database so that it meets two basic requirements:
- There is no redundancy of data (all data is stored in only one place).
- Data dependencies are logical (all related data items are stored together).
A 1NF table is in 2NF form if and only if all of its non-prime attributes are functionally dependent on the whole of every candidate key.
After meeting the requirements of 1NF, 2NF requires the database designer to do the following:
- Split up all data resulting in many-to-many relationships and store the data as separate tables. For example, in a database used by a school’s application, two of the tables are STUDENT and SUBJECT. In real life, a student takes several subjects simultaneously while a subject is studied by several students. These are many-to-many relationships. 2NF states that this relationship must be split into more than the two tables above (STUDENT and SUBJECT). One way of splitting them is by introducing a third table, which contains the columns Student_ID, Subject_ID, Semester and Year. In this way, there is no direct relationship between STUDENT and SUBJECT because all relationships are created indirectly through the third table.
- Create relationships between tables by use of foreign keys. For example, a bank’s database contains two tables: CUSTOMER_MASTER (for storing customer details) and ACCOUNT_MASTER (for storing details about bank accounts, including which customer holds which account). There must be a way to link the two tables to know who the customer is for each account. The way to do this is via a foreign key, which is a column in the ACCOUNT_MASTER table pointing to a corresponding column in the CUSTOMER_MASTER table.
A table for which there are no partial functional dependencies on the primary key might or might not be in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.
