1st, 2nd, 3rd Normal Forms used in Relational Databases

Database Normalisation is a technique of organizing the data in the database. Tables are normalized to eliminate redundant information, to make updates easier, and to save storage space. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like

1. insertion,
2. update and
3. deletion

anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purpose,

1. Eliminating reduntant (useless) data.
2. Ensuring data dependencies make sense i.e data is logically stored.

There are three different normalization levels, or forms. The following series of images describes the three normal forms.

1. first,
2. second, and
3. third normal forms

Here is a detailed breakdown of the three main normal forms used in relational normalization, along with clear characteristics and examples of each:

✅ Characteristics

• All attributes (columns) must contain atomic values—no repeating groups or arrays.
• Each column must contain values of the same type.
• Each row must be uniquely identifiable (typically via a primary key).

📌 Example: Before 1NF

StudentID	Name	Courses
1001	Alice	Math, Physics
1002	Bob	Chemistry, Biology

➡ Problem: `Courses` is a multi-valued attribute (not atomic).
✅ After 1NF

StudentID	Name	Course
1001	Alice	Math
1001	Alice	Physics
1002	Bob	Chemistry
1002	Bob	Biology

✅ Characteristics

• Must be in 1NF.
• All non-key attributes must depend on the entire primary key (not just part of it).
• Applies only to tables with composite primary keys.

📌 Example: Before 2NF
(Composite key: `StudentID + CourseID`)

StudentID	CourseID	CourseName	StudentName
1001	C101	Math	Alice
1001	C102	Physics	Alice

➡ Problem: `StudentName` depends only on `StudentID` (a partial dependency).
✅ After 2NF
Student Table:

StudentID	StudentName
1001	Alice

Enrollment Table:

StudentID	CourseID
1001	C101
1001	C102

Course Table:

CourseID	CourseName
C101	Math
C102	Physics

✅ Characteristics

• Must be in 2NF.
• No transitive dependencies: non-key attributes must depend only on the primary key, and not on other non-key attributes.

📌 Example: Before 3NF

EmployeeID	Name	Department	DeptLocation
2001	John	HR	1st Floor
2002	Alice	IT	3rd Floor

➡ Problem: `DeptLocation` depends on `Department`, not on `EmployeeID` directly (a transitive dependency).
✅ After 3NF
Employee Table:

EmployeeID	Name	Department
2001	John	HR
2002	Alice	IT

Department Table:

Department	DeptLocation
HR	1st Floor
IT	3rd Floor

🧠 Summary Table

Normal Form	Requirement	Fixes
1NF	Atomic values only, no repeating groups	Remove multi-valued fields
2NF	Full dependency on entire primary key	Eliminate partial dependencies
3NF	No transitive dependencies	Break non-key dependencies