162 ) Normalization with eg (1nf, 2nf , 3nf )

 

-------------------------------------------------------------
2 NF  With Example scenario
-------------------------------------------------------------

1. Unnormalized / 1NF Table (The Problem)

Suppose we have a table tracking Order Details for an e-commerce application. It satisfies 1NF because all columns contain atomic (single) values, and each row is unique.

order_id	product_id	product_name	product_price	quantity	customer_id	customer_name
101	P1	Laptop	1000.00	1	C1	Alice
101	P2	Mouse	25.00	2	C1	Alice
102	P1	Laptop	1000.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Why does this violate 2NF?

1. Composite Primary Key: The primary key is a combination of (order_id, product_id) because a single order can contain multiple products.
   
   

2. Partial Dependency:

   • product_name and product_price depend only on the product_id (part of the key), not the order_id. or on the whole composite key ( order_id , product_id ) 

   • customer_name depends only on the customer_id (which isn't even part of the primary key, but implies a functional dependency).

• Redundancy & Anomalies: If "Laptop" changes its price, we have to update it across multiple rows. If order 101 is deleted, we completely lose the record of who product P1 is.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Applying 2NF Normalization (The Solution)

To achieve 2NF, the table must meet 1NF and have no partial dependencies (every non-key attribute must be fully functionally dependent on the entire primary key).

We fix this by breaking the table into three separate tables:

Table 1: orders (Tracks which customer placed which order)

order_id (PK)	customer_id	customer_name
101	C1	Alice
102	C2	Bob

Table 2: products (Removes partial dependency of product details)

product_id (PK)	product_name	product_price
P1	Laptop	1000.00
P2	Mouse	25.00

Table 3: order_items (The junction table containing the composite primary key)

order_id (PK, FK)	product_id (PK, FK)	quantity
101	P1	1
101	P2	2
102	P1	1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~