Achieving Second Normal Form

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 a real-world situation to be modeled and lists the data items that are candidates to become column names in relational tables, 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 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 or 2) entities. In most cases database design is an iterative process (as mentioned in the database life cycle) in which 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

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.