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.