A3.2.5 Differences Between Normal Forms Notes

Differences Between Normal Forms

Understanding Normal Forms

1. First Normal Form (1NF): Ensures that all data is atomic and that each table has a unique identifier.
2. Second Normal Form (2NF): Eliminates partial-key dependencies by ensuring that all non-key attributes depend on the entire primary key.
3. Third Normal Form (3NF): Removes transitive dependencies, ensuring that non-key attributes depend only on the primary key.

First Normal Form (1NF)

1. Repeating data is moved into a new entity.
2. Non-repeating data remains in its own entity and is given a primary key.
3. The primary key of the non-repeating entity is added to the new entity as a foreign key.
4. The new entity is assigned a unique identifier, usually a composite key.

Second Normal Form (2NF)

1. Partial key dependencies are moved into their own entity.
2. A new primary key is assigned to that entity.
3. The original entity keeps the removed data’s key as both a primary and foreign key.

Third Normal Form (3NF)

1. Transitive or non-key dependencies are moved into separate entities.
2. Each new entity is given its own primary key.
3. The new primary key becomes a foreign key in the entity it was removed from.

Normalization Example for School Purchases

Unormalized Form

First Normal Form

Step 1: Make it Atomic

Step 2: Remove Repeated Student Info

Student Table (PK: StudentID):

Purchases (PK StudentID and ItemsPurchased), (FK StudentID):

Second Normal Form

1. Student Table is fine
2. Split Purchases Table into: Purchases (the receipt), and PurchasedItems (the line items in the receipt)

Purchases (PK PurchaseID), (FK StudentID)

PurchasedItems (PK PurchaseID and Item)

Creates Transitive Dependancy

Third Normal Form

1. Tables that are good: Purchases and Students
2. Add new table: Items
3. Update PurchasedItems Table

Items Table (PK ItemID)

PurchasedItems Table (PK PurchaseID and ItemID), (FK PurchaseID, FK ItemID)

1. Now item details no longer depend of the purchase.
2. Purchases don’t Repeat student details.
3. All items depend entirely on the primary key.

Normalization Issues and Solutions

1. Data Duplication: Normalization reduces redundancy by eliminating duplicate data.
2. Missing Data: Ensures that all necessary data is stored in appropriate tables.
3. Dependency Concerns:
   1. Data Dependencies: Addressed by ensuring functional dependencies are properly managed.
   2. Composite Key Dependencies: Resolved in 2NF by removing partial-key dependencies.
   3. Transitive Dependencies: Eliminated in 3NF by creating separate tables for transitive relationships.