Skip to content Skip to main navigation Skip to footer

Third Normal Form

Objectives

This tutorial covers the following objectives:

  • Identify transitive dependencies in a data model
  • Define the rule of Third Normal Form in the normalization in database process
  • Examine a non-normalized entity and determine which rule, or rules of normalization are being violated
  • Apply the rule of Third 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.”
  • Following the rules of normalization helps you achieve this goal.
  • You may want to enter different kinds of information for a friend in your personal address book: phone number, address, name of school or place of work.
  • If you have several friends who go to the same school, and you enter the school’s street address along with each of them, you would not only be duplicating data but causing potential problems – for instance, if the school moved and changed its address, you would have to go back and change it everywhere!
  • Normalization is a process to eliminate these kinds of problems.

Third Normal Form Rule

  • The rule of Third Normal Form (3NF) states that no non-UID attribute can be dependent on another non-UID attribute.
  • Third Normal Form prohibits transitive dependencies.
  • A transitive dependency exists when any attribute in an entity is dependent on any other non-UID attribute in that entity.
Third Normal Form
  • Think of the kind of information you’d like to store about your CD collection.
  • Does information about the store where you bought the CD belong in the same entity?
  • If the store address changed, you would have to change the information on all the CDs that were bought at that store.

Third Normal Form Transitive Dependency

  • The store address is dependent on the CD number, which is the UID of the CD entity. So this entity is in 1NF and 2NF.
  • But store address is also dependent on store name, which is a non-UID attribute.
  • This is an example of a transitive dependency and a violation of Third Normal Form.
  • The correctly normalized model is shown here: create a second entity STORE, with a relationship to CD.
Third Normal Form Transitive Dependency

Third Normal Form Example

  • Consider a system that tracks information about cities – size, population, mayor, and so on.
  • The first model shows an entity that includes state information.
Third Normal Violation
  • Although state is an attribute of city, state flower is really an attribute of state.
  • The second model, with a new entity STATE, is in Third Normal Form.
Third Normal Form

Third Normal Form Second Example

  • In this example, assume the following business rule: each employee can have one partner.
  • This model violates Third Normal Form because partner birth date is an attribute of partner, not of EMPLOYEE.
Third Normal Form Violation
  • Another way of stating Third Normal Form: non-UID attributes can’t have attributes of their own.

3NF Second Example Solution

  • This model supports Third Normal Form because partner birth date is an attribute of partner, not of EMPLOYEE.
3NF Second Example Solution
  • The 1:1 relationship is optional on the EMPLOYEE end because some employees will not have partners.
  • It is mandatory on the PARTNER end because information about a partner is tracked only if that person is a partner of one and only one EMPLOYEE.

Terminology

Key terms used in this tutorial included:

  • Third Normal Form (3NF)
  • Transitive dependency

Summary

In this tutorial, you should have learned how to:

  • Identify transitive dependencies in a data model
  • Define the rule of Third 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 Third 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 *