Database Normalization Made Simple: 1NF, 2NF & 3NF Solved Step by Step

The Problem — One Fat Table That Lies to You

Every problem in this assignment is the same fat table in a different costume — employees, classrooms, students. We will fix all of them with the same three-step recipe: 1NF → 2NF → 3NF, stating the primary key after each step, and explaining the reasoning from both an academic and an industry angle.

The Toolkit — Five Words You Must Own

Before touching the tables, lock down the vocabulary. Every decision below is justified using exactly these five ideas.

The Three Normal Forms at a Glance

Employee – Project Assignment

The Given Table (Unnormalized)

The Functional Dependencies

Step 1 → First Normal Form (1NF)

The only violation of 1NF is the multivalued EmpPhones cell. We remove the repeating group by pulling phones into their own relation, one phone per row. Everything else is already atomic.

📋 Resulting tables with sample data after 1NF:

Step 2 → Second Normal Form (2NF)

The key is composite: {EmpID, ProjectID}. Now hunt for partial dependencies — non-prime attributes leaning on only one half of the key.

Split each partial group into its own relation keyed by the part it truly depends on:

📋 Resulting tables with sample data after 2NF:

Step 3 → Third Normal Form (3NF)

Now look inside the 2NF tables for transitive chains — a non-key attribute determining another non-key attribute.

Pull each "middle-man" non-key determinant into its own table:

📋 Resulting tables with sample data after 3NF (each fact now stored once):

The Same Schema in SQL (Industry Deliverable)

CREATE TABLE Department (
    DeptID    VARCHAR(10) PRIMARY KEY,
    DeptName  VARCHAR(50)
);

CREATE TABLE Manager (
    ManagerID    VARCHAR(10) PRIMARY KEY,
    ManagerName  VARCHAR(50)
);

CREATE TABLE Employee (
    EmpID    VARCHAR(10) PRIMARY KEY,
    EmpName  VARCHAR(50),
    DeptID   VARCHAR(10) REFERENCES Department(DeptID)
);

CREATE TABLE Project (
    ProjectID    VARCHAR(10) PRIMARY KEY,
    ProjName     VARCHAR(50),
    ManagerID    VARCHAR(10) REFERENCES Manager(ManagerID)
);

CREATE TABLE EmpPhone (
    EmpID     VARCHAR(10) REFERENCES Employee(EmpID),
    EmpPhone  VARCHAR(20),
    PRIMARY KEY (EmpID, EmpPhone)
);

CREATE TABLE Assignment (
    EmpID        VARCHAR(10) REFERENCES Employee(EmpID),
    ProjectID    VARCHAR(10) REFERENCES Project(ProjectID),
    HoursWorked  INT,
    PRIMARY KEY (EmpID, ProjectID)
);

University Classroom Allocation

The Given Table & Dependencies

Step 1 → First Normal Form (1NF)

Inst_Phones is multivalued (98760-11111, 98760-22222). Pull the phones into a dedicated relation; the rest is atomic.

📋 Resulting tables with sample data after 1NF:

Step 2 → Second Normal Form (2NF)

The key is {ClassID, Day, TimeSlot}. But every descriptive attribute depends on ClassID alone (via FD d) — a textbook partial dependency on part of the key. Day and TimeSlot contribute nothing to determining course, instructor or room.

📋 Resulting tables with sample data after 2NF:

Step 3 → Third Normal Form (3NF)

Inside ClassDetails, ClassID reaches the descriptive fields only by hopping through CourseID, InstructorID and RoomID — three transitive chains.

📋 Resulting tables with sample data after 3NF:

Student – Course Enrollment

The Given Table & Dependencies

Step 1 → First Normal Form (1NF)

StudentPhones is multivalued (98765-11111, 98765-22222). Extract phones to their own relation.

📋 Resulting tables with sample data after 1NF:

Step 2 → Second Normal Form (2NF)

📋 Resulting tables with sample data after 2NF:

Step 3 → Third Normal Form (3NF)

In Course, the instructor's name and department do not really depend on the course — they depend on the instructor. So CourseID → InstructorID → (InstructorName, Inst_Dept) is a transitive chain.

📋 Resulting tables with sample data after 3NF:

Academic vs Industry — Why It Matters Twice

Golden Rules & Common Traps