Second and third normal form
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A table in 1NF can still repeat facts. Second and third normal form remove the two remaining kinds of repetition: facts that depend on only part of the key, and facts that depend on another non-key column. The short version: every non-key column must depend on the key, the whole key, and nothing but the key.
Partial dependencies and 2NF
A partial dependency is a column that depends on only part of a composite primary key. A table is in second normal form (2NF) when it is in 1NF and has no partial dependencies.
In results_v1 the key is (roll_no, course_code). marks needs both, but student_name needs only roll_no, and course_title and credits need only course_code. The script shows the damage, then splits the table.
Example 1 · 2NF: course details depend on part of the key
CREATE TABLE results_v1 ( roll_no VARCHAR(10) NOT NULL, course_code CHAR(5) NOT NULL, student_name VARCHAR(30) NOT NULL, -- depends on roll_no only course_title VARCHAR(40) NOT NULL, -- depends on course_code only credits TINYINT NOT NULL, -- depends on course_code only marks TINYINT NOT NULL, -- depends on the whole key PRIMARY KEY (roll_no, course_code));INSERT INTO results_v1 VALUES ('21CS01', 'CS201', 'Aditya', 'Data Structures', 4, 88), ('21CS01', 'CS202', 'Aditya', 'Database Systems', 4, 92), ('21CS02', 'CS201', 'Bhavana', 'Data Structures', 4, 79), ('21EC07', 'CS201', 'Chirag', 'Data Structures', 4, 58);
-- The exam cell changes CS201 to 3 credits, but only in one rowUPDATE results_v1 SET credits = 3 WHERE roll_no = '21CS01' AND course_code = 'CS201';SELECT course_code, COUNT(*) AS copies, COUNT(DISTINCT credits) AS credit_valuesFROM results_v1GROUP BY course_codeORDER BY course_code;
-- 2NF: one table per part of the key, marks stay with the whole keyCREATE TABLE r_students (roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL);CREATE TABLE r_courses ( course_code CHAR(5) PRIMARY KEY, title VARCHAR(40) NOT NULL, credits TINYINT NOT NULL);CREATE TABLE r_results ( roll_no VARCHAR(10) NOT NULL, course_code CHAR(5) NOT NULL, marks TINYINT NOT NULL, PRIMARY KEY (roll_no, course_code), FOREIGN KEY (roll_no) REFERENCES r_students (roll_no), FOREIGN KEY (course_code) REFERENCES r_courses (course_code));INSERT INTO r_students SELECT DISTINCT roll_no, student_name FROM results_v1;INSERT INTO r_courses VALUES ('CS201', 'Data Structures', 4), ('CS202', 'Database Systems', 4);INSERT INTO r_results SELECT roll_no, course_code, marks FROM results_v1;
UPDATE r_courses SET credits = 3 WHERE course_code = 'CS201'; -- one row, doneSELECT s.name, c.course_code, c.credits, r.marksFROM r_results rJOIN r_students s ON s.roll_no = r.roll_noJOIN r_courses c ON c.course_code = r.course_codeORDER BY s.name, c.course_code;+-------------+--------+---------------+ | course_code | copies | credit_values | +-------------+--------+---------------+ | CS201 | 3 | 2 | | CS202 | 1 | 1 | +-------------+--------+---------------+ +---------+-------------+---------+-------+ | name | course_code | credits | marks | +---------+-------------+---------+-------+ | Aditya | CS201 | 3 | 88 | | Aditya | CS202 | 4 | 92 | | Bhavana | CS201 | 3 | 79 | | Chirag | CS201 | 3 | 58 | +---------+-------------+---------+-------+
What this code does
- CS201's title and credits are copied into three rows because they depend on
course_codealone. A one-row update left CS201 with two credit values: an update anomaly caused by a partial dependency. - After the split, credits live once in
r_courses. The same change is a one-row update, and every result shows 3 credits for CS201.r_coursesis filled by hand here because the old sheet already disagrees with itself.
The fix is to move each partial dependency into a table keyed by the part it depends on: course_code → course_title, credits becomes a courses table, and the results table keeps only marks.
A table whose primary key is a single column cannot have a partial dependency, so it is automatically in 2NF once it is in 1NF. 2NF problems live in tables with composite keys, which usually means join tables that have collected extra columns.
Find the bug
Spot the partial dependency
Click the column that breaks 2NF.
The key is (roll_no, course_code). Which column depends on only part of it?
Round 1 of 2Transitive dependencies and 3NF
A transitive dependency is a chain through a non-key column: key → A → B. A table is in third normal form (3NF) when it is in 2NF and no non-key column depends on another non-key column.
In students_v2, roll_no decides dept_code, and dept_code decides dept_name and hod. The picture shows the chain; the script shows the repetition and builds the 3NF tables.
Example 2 · 3NF: department details depend on dept_code
CREATE TABLE students_v2 ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, dept_code VARCHAR(5) NOT NULL, dept_name VARCHAR(40) NOT NULL, -- roll_no -> dept_code -> dept_name hod VARCHAR(30) NOT NULL -- roll_no -> dept_code -> hod);INSERT INTO students_v2 VALUES ('21CS01', 'Aditya', 'CSE', 'Computer Science', 'Dr Kulkarni'), ('21CS02', 'Bhavana', 'CSE', 'Computer Science', 'Dr Kulkarni'), ('21EC07', 'Chirag', 'ECE', 'Electronics', 'Dr Banerjee'), ('21CS09', 'Deepika', 'CSE', 'Computer Science', 'Dr Kulkarni');
SELECT dept_code, COUNT(*) AS rows_storing_hodFROM students_v2GROUP BY dept_codeORDER BY dept_code;
-- 3NF: the chain dept_code -> dept_name, hod gets its own tableCREATE TABLE departments_3nf ( dept_code VARCHAR(5) PRIMARY KEY, dept_name VARCHAR(40) NOT NULL, hod VARCHAR(30) NOT NULL);CREATE TABLE students_3nf ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, dept_code VARCHAR(5) NOT NULL, FOREIGN KEY (dept_code) REFERENCES departments_3nf (dept_code));INSERT INTO departments_3nf SELECT DISTINCT dept_code, dept_name, hod FROM students_v2;INSERT INTO students_3nf SELECT roll_no, name, dept_code FROM students_v2;
-- A new HOD: one row. A new department with no students yet: no problem.UPDATE departments_3nf SET hod = 'Dr Fernandes' WHERE dept_code = 'CSE';INSERT INTO departments_3nf VALUES ('AIML', 'AI and Machine Learning', 'Dr Qureshi');
SELECT d.dept_code, d.dept_name, d.hod, COUNT(s.roll_no) AS studentsFROM departments_3nf dLEFT JOIN students_3nf s ON s.dept_code = d.dept_codeGROUP BY d.dept_code, d.dept_name, d.hodORDER BY d.dept_code;+-----------+------------------+ | dept_code | rows_storing_hod | +-----------+------------------+ | CSE | 3 | | ECE | 1 | +-----------+------------------+ +-----------+-------------------------+--------------+----------+ | dept_code | dept_name | hod | students | +-----------+-------------------------+--------------+----------+ | AIML | AI and Machine Learning | Dr Qureshi | 0 | | CSE | Computer Science | Dr Fernandes | 3 | | ECE | Electronics | Dr Banerjee | 1 | +-----------+-------------------------+--------------+----------+
What this code does
- The CSE HOD is stored in three rows, because
hoddepends ondept_code, which is not the key. That chainroll_no → dept_code → hodis the transitive dependency 3NF forbids. - After the split, the new HOD is one update and AIML can exist with 0 students.
students_3nfkeeps onlydept_code, as a foreign key.
The fix is the same move: dept_code → dept_name, hod becomes a departments table, and students keeps dept_code as a foreign key.
Transitive dependencies cause exactly the anomalies of the fest sheet: a new HOD means updating every student row of the department, and a department with no students cannot be stored. Common real examples are pincode → city, ifsc_code → bank_name, branch and train_no → train_name.
Worked example: an exam results sheet to 3NF
An exam sheet can be in 1NF and still repeat student, course and branch facts. The steps below list its dependencies; the diagram and script then rebuild the sheet from 3NF tables.
- Find the key: one row per student per course, so
(roll_no, course_code). - List the dependencies, from the rules of the college.
- 2NF: move the partial dependencies (
roll_no → ...andcourse_code → ...) to their own tables. - 3NF: move the transitive dependencies (
branch_code → branch_name,marks → grade). - Link the tables with foreign keys and check that joins rebuild the sheet.
Example 3 · The exam results sheet and its dependencies
results_sheet (one row per student per course)roll_no | name | branch_code | branch_name | course_code | course_title | credits | marks | grade21CS01 | Aditya | CSE | Computer Science | CS201 | Data Structures | 4 | 88 | A21CS01 | Aditya | CSE | Computer Science | CS202 | Database Systems | 4 | 92 | O21CS02 | Bhavana | CSE | Computer Science | CS201 | Data Structures | 4 | 79 | B21CS02 | Bhavana | CSE | Computer Science | CS202 | Database Systems | 4 | 85 | A21EC07 | Chirag | ECE | Electronics | EC301 | Digital Electronics | 3 | 67 | C21EC07 | Chirag | ECE | Electronics | CS201 | Data Structures | 4 | 58 | DDependencies (from the college's rules) roll_no, course_code -> marks full key: stays roll_no -> name, branch_code partial: breaks 2NF course_code -> course_title, credits partial: breaks 2NF branch_code -> branch_name transitive: breaks 3NF marks -> grade transitive: breaks 3NF2NF tables: exam_students(roll_no, name, branch_code), exam_courses(course_code, title, credits), results(roll_no, course_code, marks, grade) 3NF tables: branches(branch_code, branch_name) taken out of exam_students; grade_bands(grade, min_marks, max_marks) replaces the grade column
What this code does
- Each dependency is checked against the key
(roll_no, course_code): full key, part of the key (2NF problem) or a non-key column (3NF problem). - The grading rule (90 and above is O, 80 to 89 is A, and so on) makes
marks → gradea real dependency, sogradeis not stored per row.
Example 4 · ER diagram of the results design in 3NF
+----------------+ +----------------+
| branches | | exam_students |
|----------------| |----------------|
| PK branch_code | ||------o{ | PK roll_no |
| branch_name | | name |
+----------------+ | FK branch_code |
+----------------+
||
|
o{
+-------------------+ +----------------+
| results | | exam_courses |
|-------------------| |----------------|
| PK,FK roll_no | o{------|| | PK course_code |
| PK,FK course_code | | title |
| marks | | credits |
+-------------------+ +----------------+
+----------------+
| grade_bands |
|----------------|
| PK grade |
| min_marks |
| max_marks |
+----------------+
(no line: results join it with
marks BETWEEN min_marks AND max_marks)One branch has zero or many students; each student is in exactly one branch. One student has zero or many results; one course has zero or many results. Each result is one student in one course, with only the marks stored. The grade is looked up from grade_bands by the marks.
What this code does
resultsis the join table between students and courses, and the only column left in it ismarks, which depends on the whole key.grade_bandshas no foreign key line: a result finds its band with a range condition, not by an id.
Example 5 · The 3NF tables rebuild the marks sheet
CREATE TABLE branches ( branch_code VARCHAR(5) PRIMARY KEY, branch_name VARCHAR(40) NOT NULL);CREATE TABLE exam_students ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, branch_code VARCHAR(5) NOT NULL, FOREIGN KEY (branch_code) REFERENCES branches (branch_code));CREATE TABLE exam_courses ( course_code CHAR(5) PRIMARY KEY, title VARCHAR(40) NOT NULL, credits TINYINT NOT NULL);CREATE TABLE results ( roll_no VARCHAR(10) NOT NULL, course_code CHAR(5) NOT NULL, marks TINYINT NOT NULL CHECK (marks BETWEEN 0 AND 100), PRIMARY KEY (roll_no, course_code), FOREIGN KEY (roll_no) REFERENCES exam_students (roll_no), FOREIGN KEY (course_code) REFERENCES exam_courses (course_code));CREATE TABLE grade_bands ( grade CHAR(1) PRIMARY KEY, min_marks TINYINT NOT NULL, max_marks TINYINT NOT NULL);
INSERT INTO branches VALUES ('CSE', 'Computer Science'), ('ECE', 'Electronics');INSERT INTO exam_students VALUES ('21CS01', 'Aditya', 'CSE'), ('21CS02', 'Bhavana', 'CSE'), ('21EC07', 'Chirag', 'ECE');INSERT INTO exam_courses VALUES ('CS201', 'Data Structures', 4), ('CS202', 'Database Systems', 4), ('EC301', 'Digital Electronics', 3);INSERT INTO results VALUES ('21CS01', 'CS201', 88), ('21CS01', 'CS202', 92), ('21CS02', 'CS201', 79), ('21CS02', 'CS202', 85), ('21EC07', 'EC301', 67), ('21EC07', 'CS201', 58);INSERT INTO grade_bands VALUES ('O', 90, 100), ('A', 80, 89), ('B', 70, 79), ('C', 60, 69), ('D', 50, 59), ('F', 0, 49);
-- The original sheet, rebuilt with joins (grade computed, never stored)SELECT s.roll_no, s.name, b.branch_name, c.course_code, c.credits, r.marks, g.gradeFROM results rJOIN exam_students s ON s.roll_no = r.roll_noJOIN branches b ON b.branch_code = s.branch_codeJOIN exam_courses c ON c.course_code = r.course_codeJOIN grade_bands g ON r.marks BETWEEN g.min_marks AND g.max_marksORDER BY s.roll_no, c.course_code;
-- A credit-weighted average per student: easy once facts live in one placeSELECT s.name, ROUND(SUM(r.marks * c.credits) / SUM(c.credits), 2) AS weighted_avgFROM results rJOIN exam_students s ON s.roll_no = r.roll_noJOIN exam_courses c ON c.course_code = r.course_codeGROUP BY s.roll_no, s.nameORDER BY weighted_avg DESC;+---------+---------+------------------+-------------+---------+-------+-------+ | roll_no | name | branch_name | course_code | credits | marks | grade | +---------+---------+------------------+-------------+---------+-------+-------+ | 21CS01 | Aditya | Computer Science | CS201 | 4 | 88 | A | | 21CS01 | Aditya | Computer Science | CS202 | 4 | 92 | O | | 21CS02 | Bhavana | Computer Science | CS201 | 4 | 79 | B | | 21CS02 | Bhavana | Computer Science | CS202 | 4 | 85 | A | | 21EC07 | Chirag | Electronics | CS201 | 4 | 58 | D | | 21EC07 | Chirag | Electronics | EC301 | 3 | 67 | C | +---------+---------+------------------+-------------+---------+-------+-------+ +---------+--------------+ | name | weighted_avg | +---------+--------------+ | Aditya | 90.00 | | Bhavana | 82.00 | | Chirag | 61.86 | +---------+--------------+
What this code does
- Five small tables, each holding one kind of fact. The join gives back every row of the sheet, with the grade worked out from
grade_bandsthroughBETWEEN. - If the college moves A to 85 and above, one row of
grade_bandschanges and every grade in every report follows. A storedgradecolumn would have needed a mass update. - The weighted average uses credits from
exam_courses. Because credits are stored once, there is no risk of two different credit values for one course.
marks → grade is transitive too: the grade follows from the marks by a fixed rule. Store the rule once (a grade_bands table) and compute the grade in the query, so a change in the grading rule cannot leave old rows with stale grades.BCNF in one paragraph
Boyce-Codd normal form (BCNF) is a slightly stricter 3NF: for every dependency A → B in a table, A must be a key. It matters only in rare tables with overlapping candidate keys.
In this tutoring table each tutor teaches one subject, yet tutor is not a key. The query shows the rule tutor → subject stored again in every row of that tutor.
CREATE TABLE tutoring ( student VARCHAR(20) NOT NULL, subject VARCHAR(20) NOT NULL, tutor VARCHAR(20) NOT NULL, -- rule: each tutor teaches one subject PRIMARY KEY (student, subject));INSERT INTO tutoring VALUES ('Asha', 'Maths', 'Mr Iyer'), ('Ravi', 'Maths', 'Mr Iyer'), ('Asha', 'Physics', 'Ms Rao'), ('Ravi', 'Physics', 'Mr Sen');
-- tutor -> subject is stored again in every row of that tutorSELECT tutor, subject, COUNT(*) AS rows_repeating_itFROM tutoringGROUP BY tutor, subjectORDER BY tutor;+---------+---------+-------------------+ | tutor | subject | rows_repeating_it | +---------+---------+-------------------+ | Mr Iyer | Maths | 2 | | Mr Sen | Physics | 1 | | Ms Rao | Physics | 1 | +---------+---------+-------------------+
3NF allows one exception: B may be part of a candidate key, a column set that could have been chosen as the key. That exception matters only in rare tables with overlapping candidate keys, like the tutoring table above, where each tutor teaches one subject. Most tables in 3NF are already in BCNF, which is why interviews mostly stop at 3NF.
The fix is the usual move: tutor → subject gets its own table, tutors(tutor, subject), and the student table keeps only (student, tutor). The last practice task builds it.
Next step
Check your understanding
12 quick questions