Database Normalization Exercise: 1NF, 2NF, 3NF Explained

Problem - Database Normalization up to 3NF

You are tasked with designing a database starting from an unformal (de-normalized) table. Your goal is to normalize the table up to the Third Normal Form (3NF).

Requirements

1. Starting Point: Create an unformal database table with sample attributes and data.
2. Normalization Stages: For each stage—1NF, 2NF, and 3NF—provide:
   • The transformed table(s) at that stage.
   • Clear justification explaining why the changes were necessary, including:
     • Elimination of repeating groups (1NF)
     • Elimination of partial dependencies (2NF)
     • Elimination of transitive dependencies (3NF)
3. Deliverables: Diagrams or tabular representation of tables at each stage, explanation for each transformation, and highlighting of attributes that caused normalization.

Suggested Answer Format

Use the following table format to present your solution:

Stage	Transformed Table(s)	Justification
1NF	Table structure after removing repeating groups	Repeating groups eliminated; table now has atomic values in each column, complying with 1NF.
2NF	Table structure after removing partial dependencies	Attributes that were partially dependent on a composite key are separated into new tables to ensure full functional dependency on the primary key.
3NF	Table structure after removing transitive dependencies	Non-key attributes depending on other non-key attributes are moved to separate tables to eliminate transitive dependencies, ensuring 3NF compliance.

Notes

• Provide diagrams if possible to illustrate table relationships at each stage.
• Highlight specific attributes causing normalization issues and how they were resolved.
• This exercise demonstrates the process of database normalization from unformal structure to 3NF.

Database normalization 1NF 2NF 3NF, unformal table normalization, eliminate repeating groups, remove partial dependencies, remove transitive dependencies, normalization tutorial, database design exercise, relational database, PL/SQL database normalization example

Database Normalization Example: 1NF, 2NF, 3NF

In this tutorial, we normalize an unformal table Student_Course step by step up to Third Normal Form (3NF).

Step 0: Unformal (De-normalized) Table

-- Unformal Table: Student_Course
| StudentID | StudentName | Course1  | Course2  | Instructor1 | Instructor2 | Department | DeptHead |
|-----------|-------------|----------|----------|------------|------------|------------|---------|
| 101       | Alice       | Math     | Physics  | Dr. Khan   | Dr. Rahman | Science    | Prof. Ali |
| 102       | Bob         | Math     | Chemistry| Dr. Khan   | Dr. Sultana| Science    | Prof. Ali |
| 103       | Carol       | Physics  | Chemistry| Dr. Rahman | Dr. Sultana| Science    | Prof. Ali |
  

Step 1: First Normal Form (1NF)

**Objective:** Remove repeating groups and ensure atomic values.

-- Transformed Table: Student_Course_1NF
| StudentID | StudentName | Course     | Instructor  | Department | DeptHead |
|-----------|-------------|-----------|------------|------------|---------|
| 101       | Alice       | Math      | Dr. Khan   | Science    | Prof. Ali |
| 101       | Alice       | Physics   | Dr. Rahman | Science    | Prof. Ali |
| 102       | Bob         | Math      | Dr. Khan   | Science    | Prof. Ali |
| 102       | Bob         | Chemistry | Dr. Sultana| Science    | Prof. Ali |
| 103       | Carol       | Physics   | Dr. Rahman | Science    | Prof. Ali |
| 103       | Carol       | Chemistry | Dr. Sultana| Science    | Prof. Ali |
  

Justification: Repeating course and instructor columns were removed. Each row now represents a single atomic fact for a student-course combination.

Step 2: Second Normal Form (2NF)

**Objective:** Remove partial dependencies (attributes dependent only on part of a composite key).

-- Student Table
| StudentID | StudentName | Department |
|-----------|------------|------------|
| 101       | Alice      | Science    |
| 102       | Bob        | Science    |
| 103       | Carol      | Science    |

-- Course Table
| Course     | Instructor  |
|-----------|------------|
| Math      | Dr. Khan   |
| Physics   | Dr. Rahman |
| Chemistry | Dr. Sultana |

-- Student_Course Table
| StudentID | Course     |
|-----------|-----------|
| 101       | Math      |
| 101       | Physics   |
| 102       | Math      |
| 102       | Chemistry |
| 103       | Physics   |
| 103       | Chemistry |
  

Justification: Partial dependencies were removed. StudentName depends only on StudentID, and Instructor depends only on Course. Tables were separated to satisfy 2NF.

Step 3: Third Normal Form (3NF)

**Objective:** Remove transitive dependencies (non-key attributes depending on other non-key attributes).

-- Department Table
| Department | DeptHead |
|-----------|---------|
| Science   | Prof. Ali |

-- Student Table
| StudentID | StudentName | Department |
|-----------|------------|------------|
| 101       | Alice      | Science    |
| 102       | Bob        | Science    |
| 103       | Carol      | Science    |

-- Course Table
| Course     | Instructor  |
|-----------|------------|
| Math      | Dr. Khan   |
| Physics   | Dr. Rahman |
| Chemistry | Dr. Sultana |

-- Student_Course Table
| StudentID | Course     |
|-----------|-----------|
| 101       | Math      |
| 101       | Physics   |
| 102       | Math      |
| 102       | Chemistry |
| 103       | Physics   |
| 103       | Chemistry |
  

Justification: DeptHead depends on Department, not directly on StudentID. By creating a separate Department table, transitive dependencies are removed, achieving 3NF.

Database normalization example, 1NF, 2NF, 3NF tutorial, normalize unformal table, eliminate repeating groups, remove partial dependencies, remove transitive dependencies, relational database design, normalization steps with examples, student course database normalization