Skip to content Skip to main navigation Skip to footer

Second Normal Form

Objectives

Second Normal Form
  • Define the rule of Second Normal Form in the normalization process
  • Examine a non-normalized entity and determine which rule, or rules of normalization are being violated
  • Apply the rule of Second Normal Form to resolve a violation in the model

Purpose

  • Your goal as a database designer is to “store information in one place only and in the best possible place.”
  • Consistently applying the rules of normalization helps you achieve this goal.
  • When organizing information such as your friends’ phone numbers and addresses, you want to make sure that you store that information in the appropriate place — such as a personal address book.
  • If you store a friend’s address in your recipe box, for instance, you may not find it until the next time you look up that recipe.
  • Normalization in database is a process that helps eliminate these kinds of problems.

Second Normal Form Example

  • Examine the entity PRODUCT SUPPLIER.
  • The UID is a composite UID consisting of the supplier number and the product number.
  • If one supplier supplies 5 different products, then 5 different instances are created.
  • What happens if the supplier name changes?
Second Normal Form Example
  • The supplier name would then need to be changed in 5 different instances.
  • What if some of them were changed, but not others?
  • How would users know which name is the correct name?

Second Normal Form Description

  • Second Normal Form (2NF) requires that any non-UID attribute be dependent on (be a property of, or a characteristic of) the entire UID.
  • Is purchase price a property of supplier number, product number, or both?
  • Is supplier name a property of supplier number, product number, or both?
  • 2NF requires a “both” answer to each question.
Second Normal Form Description

Second Normal Form Bar Relationship

  • The UID for ACCOUNT is a composite UID from a barred relationship consisting of ACCOUNT number and BANK number.
  • Is balance a property of ACCOUNT number, BANK number, or both?
  • Is date opened a property of ACCOUNT number, BANK number, or both?
Second Normal Form Bar Relationship

Second Normal Form Violation

  • In this ERD, the attribute bank location has been added. Is bank location a property of ACCOUNT number, BANK number, or both?
  • It is a property of BANK number only and is thus misplaced. This is a violation of Second Normal Form.
  • What would happen if a bank’s location changed?
  • Every account at that bank would need to be updated.
Second Normal Form Violation

Order ERD

  • What is wrong with this diagram?
Order ERD

Order ERD

  • The ERD is now in 2NF
  • Answer: The price attribute is misplaced.
  • Item Price depends solely on PRODUCT.
  • This is a violation of Second Normal Form.

Terminology

Key terms used in this tutorial included:

  • Second Normal Form (2NF)

Summary

In this article, you should have learned how to:

  • Define the rule of Second Normal Form in the normalization process
  • Examine a non-normalized entity and determine which rule, or rules of normalization are being violated
  • Apply the rule of Second Normal Form to resolve a violation in the model

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *