1NF, 2NF and 3NF

Part of the free LogicWiz SQL & Databases course, Module 10: Normalization. The queries and practice run in the app on real Postgres.

The examiner asks "What is the highest normal form of R?" Half the class writes 2NF because "there is a transitive dependency". Here is how to get it right every time, and then fix the table with SQL.

You'll learn to

• Spot 1NF violations and flatten them with SQL
• Find partial dependencies (2NF) and transitive dependencies (3NF)
• Name the highest normal form of a relation from its FDs
• Decompose a table into 3NF with INSERT … SELECT DISTINCT

Learn

1NF: one value per cell

A table is in First Normal Form (1NF) when every cell holds a single, atomic value: no lists, no repeating groups like item1, item2, item3 columns.

The order table below breaks 1NF: items holds a list. You can't ask "how many Masala Chai were ordered?" without string tricks, and you can't store a quantity per item.

The fix: one row per item. In SQL, string_to_array splits the text and unnest turns the array into rows.

Not in 1NF: orders_raw

Flatten to 1NF

SELECT order_id, customer,
       TRIM(unnest(string_to_array(items, ','))) AS item
FROM orders_raw
ORDER BY order_id, item;

Three rows become six, one per (order, item). TRIM removes the space after each comma. The key of the 1NF table is (order_id, item).

2NF: no partial dependency

A table is in 2NF if it is in 1NF and no non-prime attribute depends on just part of a candidate key. Such an FD is a partial dependency.

In marks_sheet the key is (student_id, course_id). But student_id → student_name, dept and course_id → course_title, credits: they depend on half the key. That is exactly why Aarav's name repeats for each of his courses.

Fix: move each partial dependency into its own table: students(student_id, student_name, dept, hod), courses(course_id, course_title, credits), and keep results(student_id, course_id, marks).

A table whose keys are all single columns has no "part" of a key, so it is automatically in 2NF (if it is in 1NF).

3NF: no transitive dependency

After 2NF, students(student_id, student_name, dept, hod) still repeats "CSE → Dr. Rao" for every CSE student. Why? student_id → dept and dept → hod, so hod depends on the key only through dept. That is a transitive dependency.

3NF, the exam definition: for every non-trivial FD X → A, either X is a superkey, or A is a prime attribute.

dept → hod fails both tests (dept is not a superkey of students, hod is not prime). Fix: departments(dept, hod), and students keeps dept as a foreign key.

marks_sheet, step by step

Keys are in bold. Each table now stores one kind of fact, once.

Decompose with INSERT … SELECT DISTINCT

CREATE TABLE departments (
  dept  VARCHAR(10) PRIMARY KEY,
  hod   VARCHAR(30) NOT NULL
);
CREATE TABLE students (
  student_id    INT PRIMARY KEY,
  student_name  VARCHAR(30) NOT NULL,
  dept          VARCHAR(10) NOT NULL REFERENCES departments(dept)
);
CREATE TABLE courses (
  course_id     VARCHAR(8) PRIMARY KEY,
  course_title  VARCHAR(40) NOT NULL,
  credits       INT NOT NULL
);
CREATE TABLE results (
  student_id  INT REFERENCES students(student_id),
  course_id   VARCHAR(8) REFERENCES courses(course_id),
  marks       INT,
  PRIMARY KEY (student_id, course_id)
);
INSERT INTO departments SELECT DISTINCT dept, hod FROM marks_sheet;
INSERT INTO students SELECT DISTINCT student_id, student_name, dept FROM marks_sheet;
INSERT INTO courses SELECT DISTINCT course_id, course_title, credits FROM marks_sheet;
INSERT INTO results SELECT student_id, course_id, marks FROM marks_sheet;

SELECT 'departments' AS tbl, COUNT(*) AS row_count FROM departments
UNION ALL SELECT 'students', COUNT(*) FROM students
UNION ALL SELECT 'courses', COUNT(*) FROM courses
UNION ALL SELECT 'results', COUNT(*) FROM results;

SELECT DISTINCT collapses the copies: 3 departments, 4 students, 4 courses, and 6 results (one per original row). Changing CSE's HOD is now a one-row UPDATE.

Mixing up 2NF and 3NF is the classic mistake. 2NF is about depending on part of a key (it can only happen with a composite key). 3NF is about a non-key attribute depending on another non-key attribute. R(roll_no, name, dept, hod) with key roll_no and dept → hod is in 2NF (the key has no parts) but not in 3NF.

Recipe for "highest normal form": (1) find all candidate keys; (2) mark the prime attributes; (3) for each FD X → A: if X is a proper part of a key and A is non-prime, it is not 2NF; else if X is not a superkey and A is non-prime, it is not 3NF; else if X is not a superkey, it is not BCNF. The highest form is the last one no FD breaks.

In one line

1NF: atomic cells. 2NF: no non-prime attribute depends on part of a key. 3NF: for every X → A, X is a superkey or A is prime.

Interview corner

Explain 1NF, 2NF and 3NF with an example.

1NF: one atomic value per cell (no phone lists). 2NF: no column depends on part of a composite key; in (student_id, course_id, student_name, marks), student_name depends only on student_id, so it moves to students. 3NF: no non-key column depends on another non-key column; dept → hod moves to departments.

What is the difference between 2NF and 3NF?

2NF removes partial dependencies (a non-prime attribute depending on part of a candidate key), which only exist with composite keys. 3NF also removes transitive dependencies (a non-prime attribute depending on another non-key attribute).

Is a table with a single-column primary key always in 2NF?

Yes, if it is in 1NF and that is its only candidate key: a partial dependency needs a proper part of a key, and one column has no proper part. It can still fail 3NF.