Database Normalisation Cheat Sheet
A cheat sheet to the database normalisation steps.
Starting Point
Our table before normalisation
subject_enrolment
lecturer_ enrolment_
student_name student_email subject_name lecturer_name semester classroom
office date
Introduction to
Mr Dwayne
John Smith jsmith@abcuni Algorithms, 2024-09-01,
Gleeson, 105, 110 2025-01 B10, B15
versity.com Introduction to 2024-09-01
Mrs Joan Field
HTML
sjones@abcuni Introduction to
Sarah Jones Mrs Joan Field 110 2025-01 B15 2024-09-23
versity.com HTML
mbrown@abcu Mrs Joan Field, 2024-10-04,
Michael Brown Advanced HTML, 2025-01,
niversity.com Mr Chris Smith B8, B12 2024-11-05
Introduction to CSS 110, 108 2025-02
jsmith@abcuni
John Smith Advanced HTML Mrs Joan Field 110 2025-02 B10 2025-03-14
versity.com
First Normal Form
No mixed data or repeating data, tables have primary keys
subject_enrolment
lecturer_ enrolment_
id student_name student_email subject_name lecturer_name semester classroom
office date
jsmith@abcunive Introduction to Mr Dwayne
1 John Smith 105 2025-01 B10 2024-09-01
rsity.com Algorithms Gleeson
jsmith@abcunive Introduction to
2 John Smith Mrs Joan Field 110 2025-01 B15 2024-09-01
rsity.com HTML
sjones@abcunive Introduction to
3 Sarah Jones Mrs Joan Field 110 2025-01 B15 2024-09-23
rsity.com HTML
mbrown@abcuni
4 Michael Brown Advanced HTML Mrs Joan Field 110 2025-01 B8 2024-10-04
versity.com
mbrown@abcuni Introduction to
5 Michael Brown Mr Chris Smith 108 2025-02 B12 2024-11-05
versity.com CSS
jsmith@abcunive
6 John Smith Advanced HTML Mrs Joan Field 110 2025-02 B10 2025-03-14
rsity.com
Second Normal Form
Every non-key value must depend on the primary key
subject_enrolment
enrolment_
id student_id subject_id semester classroom
date
1 1 1 2025-01 B10 2024-09-01
2 1 2 2025-01 B15 2024-09-01
3 2 2 2025-01 B15 2024-09-23
4 3 3 2025-01 B8 2024-10-04
5 3 4 2025-02 B12 2024-11-05
6 1 3 2025-02 B10 2025-03-14
subject student
id subject_name lecturer_name lecturer_office id student_name student_email
Introduction to jsmith@abcuniver
1 Mr Dwayne Gleeson 105 1 John Smith
Algorithms sity.com
Introduction to sjones@abcunive
2 Mrs Joan Field 110 2 Sarah Jones
HTML rsity.com
3 Advanced HTML Mrs Joan Field 110 mbrown@abcuniv
3 Michael Brown
ersity.com
4 Introduction to CSS Mr Chris Smith 108
Third Normal Form
No transitive dependencies
subject_enrolment lecturer
student subject classroom enrolment_ lecturer_
id semester id lecturer_name
_id _id _id date office
1 1 1 2025-01 2 2024-09-01 1 Mr Dwayne Gleeson 105
2 1 2 2025-01 4 2024-09-01 2 Mrs Joan Field 110
3 2 2 2025-01 4 2024-09-23 3 Mr Chris Smith 108
4 3 3 2025-01 1 2024-10-04
5 3 4 2025-02 3 2024-11-05
6 1 3 2025-02 2 2025-03-14
subject student classroom
lecturer student student id room_number
id subject_name id
_id _name _email
1 B8
Introduction to John jsmith@abcuniv
1 1 1
Algorithms Smith ersity.com 2 B10
Introduction to Sarah sjones@abcuniv 3 B12
2 2 2
HTML Jones ersity.com
4 B15
3 Advanced HTML 2 Michael mbrown@abcuni
3
Brown versity.com
4 Introduction to CSS 3
Fourth Normal Form
Avoid multivalued dependencies
Before:
subject_material
subject_id textbook_title guest_lecturer
1 System Algorithms Dr. White
1 System Algorithms Prof. Green
1 Computer Science Basics Dr. White
1 Computer Science Basics Prof. Green
After:
subject_textbooks subject_guest_lecturer
subject_id textbook_title subject_id guest_lecturer
1 System Algorithms 1 Dr. White
1 Computer Science Basics 1 Prof. Green
Fifth Normal Form
Avoid unnecessary decompositions
Before:
lecturer_department
subject_id textbook_title guest_lecturer
1 Computer Science Dr. White
1 Computer Science Prof. Green
2 Maths Dr. Lee
After:
department lecturer_department
id department department_id lecturer
1 Computer Science 1 Dr. White
2 Maths 1 Prof. Green
2 Dr. Lee
subject_lecturer
subject_id lecturer
1 Dr. White
1 Prof. Green
1 Dr. Lee
