📐

Normalization Simulator

Learn how database normalization eliminates redundancy through interactive examples. Step through 1NF, 2NF, 3NF, and BCNF transformations on real data.

Normal Forms Theory

1NFFirst Normal Form

Eliminate repeating groups. Each column must contain atomic (indivisible) values, and each row must be unique. No arrays or nested tables.

2NFSecond Normal Form

Remove partial dependencies. Every non-key column must depend on the entire primary key, not just part of it. Must already be in 1NF.

3NFThird Normal Form

Remove transitive dependencies. Every non-key column must depend only on the primary key, not on other non-key columns. Must already be in 2NF.

BCNFBoyce-Codd Normal Form

Every determinant must be a candidate key. A stricter version of 3NF where every functional dependency X → Y requires X to be a superkey.

Interactive Simulator

Step: Denormalized

A single table with redundant data. StudentName repeats for each course, InstructorDept repeats for each instructor. This leads to update anomalies and data inconsistency.

Student_Course

StudentID	StudentName	CourseID	CourseName	Instructor	InstructorDept	Grade
S1	Alice	C1	Math	Dr. Smith	Math Dept	A
S1	Alice	C2	Physics	Dr. Jones	Physics Dept	B
S2	Bob	C1	Math	Dr. Smith	Math Dept	C
S2	Bob	C3	Chemistry	Dr. Brown	Chem Dept	A
S3	Charlie	C2	Physics	Dr. Jones	Physics Dept	B

💾

SQL Schema

CREATE TABLE statements for the fully normalized schema:

-- Fully Normalized Schema (3NF / BCNF)

CREATE TABLE Student (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

CREATE TABLE Instructor (
    InstructorID VARCHAR(10) PRIMARY KEY,
    InstructorName VARCHAR(100) NOT NULL,
    InstructorDept VARCHAR(100) NOT NULL
);

CREATE TABLE Course (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    InstructorID VARCHAR(10),
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    Grade CHAR(2) NOT NULL,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

❓

Interview Questions

Q1What is the difference between 2NF and 3NF?

2NF removes partial dependencies (where a non-key column depends on only part of a composite key). 3NF removes transitive dependencies (where a non-key column depends on another non-key column). A table in 3NF is always in 2NF, but not vice versa.

Q2When would you denormalize a database on purpose?

Denormalization is used for read-heavy workloads to avoid expensive JOIN operations. It improves query performance at the cost of data redundancy. Common in data warehouses, reporting systems, and NoSQL databases where read speed is critical.

Q3What is a functional dependency and how does it relate to normalization?

A functional dependency X → Y means that knowing X determines Y uniquely. Normalization uses functional dependencies to identify and remove redundancy. Each normal form targets specific types of problematic dependencies (partial, transitive, etc.).

Q4Can a table be in BCNF but not in 3NF?

No. BCNF is stricter than 3NF, so any table in BCNF is automatically in 3NF (and 2NF and 1NF). However, a table can be in 3NF but not in BCNF if there exists a functional dependency where the determinant is not a candidate key.

Q5What are update anomalies and how does normalization fix them?

Update anomalies include: Insert anomaly (cannot insert data due to missing fields), Update anomaly (must update same data in multiple rows), Delete anomaly (deleting a row removes unintended data). Normalization eliminates redundancy so each fact is stored exactly once, preventing these anomalies.

Normal Form Requirements

NF	Requirement	Eliminates
1NF	Atomic values in each column	Repeating groups / multi-valued attributes
2NF	1NF + full key dependency	Partial dependencies
3NF	2NF + no transitive dependencies	Transitive dependencies
BCNF	Every determinant is a candidate key	All anomalies from non-key determinants

Steps Summary

0 → 1NF
Atomic columns, split Student & Enrollment
1NF → 2NF
Create Course table, remove partial deps
2NF → 3NF
Create Instructor table, remove transitive deps
3NF → BCNF
All determinants are candidate keys