RelationalDBDesign 




Database Analysis   «Prev  Next»
Lesson 11

Second Normal Form Limitations

This module covered database normalization up to 2NF or second normal form. It identified the requirements of, and procedures for reaching, 1NF and 2NF.
Along the way, it introduced the concepts of normalization, repeating groups, and functional dependencies.
Having completed this module, you should now be able to:
  1. Define normalization: normalization is the process of applying increasingly stringent rules to a relational database to correct any problems associated with poor design.
  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) first normal form.
  4. Identify repeating groups: When an attribute has more than one value in a table row it is called repeating group .
  5. Explain the requirements for second normal form (2NF)
    Second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.
  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 normal form.

Problems with 2NF Relations

Although second normal form eliminates problems from many relations, you will occasionally run into relations that are in second normal form yet still exhibit anomalies.
Assume, for example, that each new DVD title that DistributedNetworks carries comes from one distributor and that each distributor has only one warehouse that has only one phone number. The following relation is therefore in second normal form:
Item (item_numb, title, distrib_numb, warehouse_phone_number)
For each item number, there is only one value for the item's title, distributor, and warehouse phone number. The following anomalies exist:
  1. There is one insertion anomaly: You cannot insert data about a distributor until you have an item from that distributor.
  2. There is one deletion anomaly: If you delete the only item from a distributor, you lose data about the distributor.
  3. There is also a modification anomaly: The distributor's warehouse phone number is duplicated for every item the company gets from that distributor.
The relation is in second normal form but not third.


Glossary terms

  1. normalization: The process of applying increasingly stringent rules to a relational database to correct any problems associated with poor design.
  2. First normal form (1NF): A table is in first normal form if it contains no repeating groups.
  3. Repeating groups: An attribute that has more than one value in a table row.
  4. second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.
  5. 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.
  6. multi-field determinant: A determinant consisting of more than one field.
  7. concatenated primary key: A primary key made up of more than one field.
  8. 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.
  9. normal form: A set of theoretical rules for the design of database tables.
  10. denormalization: To move a table to a lower normal form (e.g., from 3NF to 2NF).
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