Achieving Second Normal Form

Question: What are the steps to put a table in second normal form? To put a table in second normal form, follow these steps:

1. Identify the functional dependencies: Determine which attributes in the table depend on which other attributes. This means that if you change the value of one attribute, it will affect the value of another attribute.
2. Create separate tables: If any attributes depend on only part of the primary key, create a new table for them. Move the attribute(s) and the part of the primary key they depend on to the new table, along with a copy of the primary key.
3. Assign foreign keys: In the original table, add a foreign key that references the primary key in the new table. This foreign key is used to link the two tables together.
4. Repeat as necessary: If there are still attributes that depend on only part of the primary key in the new table(s), repeat steps 2 and 3 until all tables are in second normal form.

By following these steps, you can ensure that your table is in second normal form, which means that it is free from certain types of data anomalies that can occur in poorly designed tables.

The preceding lessons described how to put a table in second normal form. To review, that process is:

1. Verify the table is in 1NF.
2. Identify the functional dependencies in the table.
3. Represent those functional dependencies using the determinant -> attributes notation.
4. Use your knowledge of the business objects in the ER diagram to identify any fields that are not either:
   1. inherent characteristics of the object represented by the table, or
   2. foreign keys used to link the table to other tables.
5. Separate any fields that are not inherent characteristics or foreign keys into new tables, linked by a foreign key.

1. Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
2. Create relationships between these new tables and their predecessors through the use of foreign

When it comes to deciding what is driving database design, 1) functional dependencies or 2) entities, this question is really a chicken and egg problem. Normalization seems to share little with the entity relationship model. However, it turns out that a 1) relational design based on normalization and 2) an ER design transformed into relational tables have almost identical results, and the two approaches reinforce each other. In the normalization approach, the designer starts with

1. a real-world situation to be modeled and
2. lists the data items that are candidates to become column names in relational tables,
3. together with a list of rules with respect to how these data items are related.

The objective is to represent all these data items as attributes of tables so that they obey restrictive conditions associated with what we call normal forms^[1]. These normal form definitions limit the acceptable form of a table so that it has certain desirable properties, thus avoiding various kinds of anomalies. There exists a series of normal form definitions, where each definition is more restrictive than the previous. What is most important is that there is consistency between the ER diagram and the functional dependencies you identify in your relations. It makes no difference whether you design by looking for 1) functional dependencies during normalization or 2) entities during the data modeling process. In most cases, database design is an iterative process (as mentioned in the database life cycle) when you create an initial design, check it, modify it, and check it again. You can look at either functional dependencies and/or entities at any stage in the process, checking one against the other for consistency. Are the functional dependencies identified during normalization the same as the entities created during the data modeling process?

A table T in a database schema with Functional Dependency set F is said to be in second normal form (2NF) under the following condition:
For any functional dependency

X --> A implied by F that lies in T, 

where A is a single attribute that is not in X and is nonprime, X is not a proper subset of any key K of T. A database schema is in 2NF when all the tables it contains are in 2NF.

Before you move on to the next lesson, complete the following exercise to reinforce your understanding of second normal form.
Second Normal Form - Exercise
The next lesson describes the limitations of second normal form.

[1] normal form: A defined standard structure for relational databases in which a relation may not be nested within another relation.