Second Normal Form (2NF) in SQL - Mini Project: Build & Apply

Organizing a Sales Database into Second Normal Form (2NF)

📖 Scenario: You are managing a small store's sales data. Currently, all sales information is stored in one table, but it has repeated data and some inefficiencies.Your task is to organize this data into Second Normal Form (2NF) to reduce redundancy and improve data integrity.

🎯 Goal: Create tables that follow Second Normal Form (2NF) by removing partial dependencies from the sales data.

📋 What You'll Learn

Create an initial table called Sales with columns OrderID, ProductID, ProductName, Quantity, and Price.

Add a primary key constraint on the combination of OrderID and ProductID.

Create a new table called Products with columns ProductID and ProductName to remove partial dependency.

Modify the Sales table to remove ProductName column after creating Products table.

💡 Why This Matters

🌍 Real World

Organizing sales data in a store database to avoid repeating product names in every order line.

💼 Career

Database designers and developers use normalization to create efficient, maintainable databases that reduce errors and improve performance.

1

Create the initial Sales table

Write a SQL statement to create a table called Sales with columns OrderID (integer), ProductID (integer), ProductName (text), Quantity (integer), and Price (decimal). Add a primary key on the combination of OrderID and ProductID.

SQL

-- Your SQL code to create the Sales table with the specified columns and composite primary key

Need a hint?

Use CREATE TABLE with all columns and specify the primary key as (OrderID, ProductID).

2

Create the Products table

Write a SQL statement to create a new table called Products with columns ProductID (integer) and ProductName (text). Add a primary key on ProductID.

SQL

CREATE TABLE Sales (
  OrderID INT,
  ProductID INT,
  ProductName TEXT,
  Quantity INT,
  Price DECIMAL(10, 2),
  PRIMARY KEY (OrderID, ProductID)
);
-- Your SQL code to create the Products table with ProductID and ProductName

Need a hint?

Use CREATE TABLE Products with ProductID as primary key and include ProductName.

3

Remove ProductName from Sales table

Write a SQL statement to remove the ProductName column from the Sales table to eliminate partial dependency.

SQL

CREATE TABLE Sales (
  OrderID INT,
  ProductID INT,
  ProductName TEXT,
  Quantity INT,
  Price DECIMAL(10, 2),
  PRIMARY KEY (OrderID, ProductID)
);

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName TEXT
);
-- Your SQL code to remove ProductName column from Sales

Need a hint?

Use ALTER TABLE Sales DROP COLUMN ProductName; to remove the column.

4

Add foreign key constraint to Sales

Write a SQL statement to add a foreign key constraint on ProductID in the Sales table that references ProductID in the Products table.

SQL

CREATE TABLE Sales (
  OrderID INT,
  ProductID INT,
  ProductName TEXT,
  Quantity INT,
  Price DECIMAL(10, 2),
  PRIMARY KEY (OrderID, ProductID)
);

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName TEXT
);

ALTER TABLE Sales DROP COLUMN ProductName;
-- Your SQL code to add foreign key constraint on ProductID in Sales referencing Products

Need a hint?

Use ALTER TABLE Sales ADD CONSTRAINT fk_ProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID);