Database Analysis   «Prev  Next»
Lesson 1

Introduction to First, Second, and Third Normal Forms used in Relational Database Analysis

Relational databases work because they are based on logically sound elements of set theory. Fortunately, you do not have to become an expert in a theoretical branch of mathematics to design efficient databases.
If you learn a few of the principles without delving into the underlying theory, you will be a better designer. This module introduces techniques you can use to improve your database designs without worrying about the math.

Learning objectives

After completing the lessons in this module, you should be able to:
  1. Define the concept of normalization
  2. Explain the requirements for first normal form (1NF)
  3. Identify repeating groups
  4. Explain the requirements for second normal form (2NF)
  5. Identify functional dependencies
  6. Describe the limitations of 2NF
The next lesson introduces normalization.

When we design a database for an enterprise, the main objective is to create an accurate representation of the data, relationships between the data, and constraints on the data that is pertinent to the enterprise. To help achieve this objective, we can use one or more database design techniques. Entity Relationship or (ER) modeling is one of those tecniques. In this module we describe another database design technique called normalization. Normalization is a database design technique, which begins by examining the relationships (called functional dependencies) between attributes.
Attributes describe some property of the data or of the relationships between the data that is important to the enterprise. Normalization uses a series of tests (described as normal forms) to help identify the optimal grouping for these attributes to ultimately identify a set of suitable relations that supports the data requirements of the enterprise.
The main purpose of this module is to introduce
  1. the concept of functional dependencies and
  2. describe normalization up to Third Normal Form (3NF).
Later we take a more formal look at functional dependencies and also consider later normal forms that go beyond 3NF.
Database normalization prevents data redundancy and makes the best possible usage of storage.

Purpose of Normalization

The purpose of normalization is to identify a suitable set of relations that support the data requirements of an enterprise. The characteristics of a suitable set of relations include the following:
  1. The minimal number of attributes necessary to support the data requirements of the enterprise;
  2. attributes with a close logical relationship (described as functional dependency) are found in the same relation;
  3. minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys, which are essential for the joining of related relations.
The benefits of using a database that has a suitable set of relations is that
  1. the database will be easier for the user to access and maintain the data, and
  2. take up minimal storage space on the computer.