SQL Normalization — 1NF, 2NF, 3NF, BCNF

A student’s name is repeated on every subject-result row. Correcting the name in only one row leaves contradictory records.

Normalization organizes relations around their keys and dependencies to reduce avoidable redundancy and data-change anomalies.

A dependency states that knowing one value or key determines another. A student key determines the student name; the pair of student and subject determines a mark in this simplified model. Put the name in students and marks in a relation keyed by that pair. The split should preserve meaningful reconstruction through joins.

Related concepts: Foreign Keys and Denormalize Carefully and Primary and Unique Keys.

Normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity. Each “normal form” adds a layer of structure and removes anomalies in insertions, deletions, and updates.

SQL · portable concepts; preserved examples identify their engine

Rule:

• All values must be atomic (no repeating groups or arrays)

Unnormalized Table:

CREATE TABLE student_subjects (
  roll_no INT,
  name VARCHAR(50),
  subjects VARCHAR(100) -- e.g., "Maths, Science"
);

Problem:

The subjects column contains multiple values. This violates 1NF.

1NF Version:

CREATE TABLE student_subjects_1nf (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30)
);

Now each row holds only atomic values.

SQL · portable concepts; preserved examples identify their engine

Rule:

• Be in 1NF
• No partial dependency (non-key attributes must depend on full primary key)

Example:

CREATE TABLE result (
  roll_no INT,
  subject VARCHAR(30),
  student_name VARCHAR(50),
  marks INT,
  PRIMARY KEY (roll_no, subject)
);

Problem:

student_name depends only on roll_no, not the whole key (roll_no, subject). That’s a partial dependency.

2NF Solution – Split the table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE marks (
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  PRIMARY KEY (roll_no, subject),
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

Now each non-key attribute depends on the full key — no partial dependency.

SQL · portable concepts; preserved examples identify their engine

The following preserved schema assumes one postal code per city only to demonstrate a dependency. Real cities can have several postal codes, so this is not a valid general address model. A real design must establish the actual dependencies first.

Rule:

• Be in 2NF
• No transitive dependency (non-key attributes shouldn't depend on other non-key attributes)

Example:

CREATE TABLE student_details (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(30),
  pincode INT
);

Problem:

pincode depends on city, not directly on the primary key. That’s a transitive dependency.

3NF Solution – Separate the city table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(30)
);

CREATE TABLE cities (
  city VARCHAR(30) PRIMARY KEY,
  pincode INT
);

Now all non-key columns depend directly on the primary key only.

SQL · portable concepts; preserved examples identify their engine

Rule:

• Be in 3NF
• Every determinant must be a candidate key

Example:

Let’s say a teacher is assigned uniquely to each subject, but a teacher can teach only one subject.

CREATE TABLE subject_teacher (
  subject VARCHAR(30),
  teacher_name VARCHAR(50),
  room_no INT,
  PRIMARY KEY (subject, room_no)
);

Problem:

subject → teacher_name but subject is not a superkey here. This violates BCNF.

BCNF Solution:

CREATE TABLE subjects (
  subject VARCHAR(30) PRIMARY KEY,
  teacher_name VARCHAR(50)
);

CREATE TABLE subject_rooms (
  subject VARCHAR(30),
  room_no INT,
  PRIMARY KEY (subject, room_no),
  FOREIGN KEY (subject) REFERENCES subjects(subject)
);

• Prevents redundant data
• Improves consistency and accuracy
• Reduces storage cost
• Makes updates easier and safer

• 1NF – Each student-subject row stored individually
• 2NF – Student details separated from subject marks
• 3NF – City info separated to avoid duplication of pin codes
• BCNF – Ensure teachers uniquely assigned to subjects

Normal forms concern candidate keys and functional dependencies, not only the chosen primary key. A city does not generally determine one postal code; the retained city example is a simplified assumed dependency, not a real postal-address design. Atomicity in 1NF depends on the chosen domain rather than a universal ban on every database type.

Read student names through a separate student-to-result relationship rather than copying them into each result.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT s.name, r.subject, r.marks FROM practice_results AS r INNER JOIN practice_students AS s ON s.roll_no = r.roll_no ORDER BY s.roll_no;

The result rows carry the student key and subject mark, while names come from the student relation. One corrected student name serves every related result.

Normalization organizes relations around their keys and dependencies to reduce avoidable redundancy and data-change anomalies.

The result rows carry the student key and subject mark, while names come from the student relation. One corrected student name serves every related result.

Continue your understanding with Foreign Keys and Denormalize Carefully and Primary and Unique Keys.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.