RelationalDBDesign 




Database Analysis   «Prev  Next»
Lesson 10 Achieving 2NF
Objective Describe the process for achieving 2NF.

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.

General requirements for Second Normal Form:

  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
The next lesson describes the limitations of second normal form.


Functional dependencies versus Entities

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.
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 functional dependencies or for 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

Second Normal Form - Exercise

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