Normal Forms   «Prev  Next»

Lesson 12

First, second, third Normal Forms Conclusion

This module covered database normalization up to 2NF. It identified the requirements of and procedures for reaching first and second normal form.
Along the way, it introduced the concepts of normalization, repeating groups, and functional dependencies.

Role of Second Normal Form

Second normal form (2NF) is an important step in the normalization process of a database. The primary goal of 2NF is to ensure that the table is free from certain types of data anomalies that can occur in poorly designed tables. In order to achieve 2NF, the table must first satisfy the rules of first normal form (1NF). Additionally, the table must ensure that each non-key attribute is fully dependent on the entire primary key. This means that each attribute must depend on the entire primary key, rather than on only a part of the primary key. If an attribute depends only on part of the primary key, it must be moved to a separate table.
By eliminating partial dependencies in a table, 2NF helps to minimize data redundancy and inconsistencies, which can lead to data integrity issues. This, in turn, helps to improve the efficiency and performance of the database. Overall, 2NF is an important step in the normalization process, and it sets the foundation for achieving higher levels of normalization in a database.

Topics covered in this Module

  1. Define normalization:[1]
  2. Explain requirements for first normal form (1NF)
    first normal form (1NF): A table is in first normal form if it contains no repeating groups.
  3. Describe the limitations of 1NF:
  4. Identify repeating groups: When an attribute has more than one value in a table row it is called repeating groups .
  5. Explain the requirements for second normal form (2NF):
  6. Identify functional dependency: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.
  7. Describe the limitations of 2NF:

Second Norm Form Summary

  1. Uses the concepts of functional dependencies and primary key
  2. Prime attribute, where the attribute that is member of the primary key K
  3. Full functional dependency
    a functional dependency Y -> Z where removal of any attribute from Y means the functional dependency does not hold any more
  4. A relation schema R is in second normal form (2NF) if every nonprime attribute A in R is fully functionally dependent on every key of R

Glossary terms

  1. concatenated primary key: A primary key made up of more than one field.
  2. denormalization: To move a table to a lower normal form (e.g., from 3NF to 2NF).
  3. First normal form (1NF): A table is in first normal form if it contains no repeating groups.
  4. functional dependency: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.
  5. multi-field determinant: A determinant consisting of more than one field.
  6. normal form: A set of theoretical rules for the design of database tables.
  7. normalization: The process of applying increasingly stringent rules to a relational database to correct any problems associated with poor design.
  8. Repeating groups: An attribute that has more than one value in a table row.
  9. requirements analysis: The stage in the database design cycle when designers find out everything they can about the data the client needs to store in the database and the conditions under which that data needs to be accessed.
  10. second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.
The next module describes third normal form.

Normalization - Quiz

Before you move on to the next module, take the following quiz to check your knowledge of normalization to 2NF.
Normalization - Quiz
[1]normalization: The process of applying increasingly stringent rules to a relational database to correct any problems associated with poor design.