Second Normal Form (2NF) - Administration

Put simply, normalization is an attempt to make sure you do not destroy true data or create false data in your database. Errors are avoided by representing a fact in the database one way, one time, and in one place. Duplicate data is a problem as old as data processing. Efficient and accurate data processing relies on the minimizing redundant data and maximizing data integrity. Normalization and the Normal Forms (NF) are efforts to achieve these two core objectives of data processing. This article will examine the concept of normalization in-depth.

TABLE OF CONTENTS:

By: David Fells

Poor Best

Rating:

/ 125

June 16, 2004

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Second Normal Form (2NF)

Second normal form states that there are no non-trivial functional dependencies on a non-key attribute. Let's look at this table:

CREATE TABLE Sales (

            name,
            extension,
            sales_manager,
            store_location,
            customer_name,
            customer_address,
            product_name,
            product_price
);

The primary key for this table could be defined as (name, store_location, customer_name), giving us a 1NF table. There is a problem though when considering 2NF - what happens if a customer changes their mind at the last minute and changes the product? The product_price column has to change, which means the product_price column has a functional dependency on the product_name column. In order for this table to be 2NF, we would need to have a separate table for products that stored the price - something like this:

CREATE TABLE Sales (

            name,
            extension,
            sales_manager,
            store_location,
            customer_name,
            customer_address,
            product_id
);

CREATE TABLE Products (

            product_id,
            name,
            price
);

In this case there are no functional dependencies, but it is obvious that there are still some issues to be resolved. Let's have a look at 3NF.

Next: Third Normal Form (3NF) and BCNF >>