Normalisation: functional dependencies, 1NF and 2NF
50 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)"What is normalisation and why do we do it?" is the most asked DBMS theory question after keys. The interviewer checks that you can find the repeated facts, name the anomaly and fix the table step by step.
Insert, update and delete anomalies
An anomaly is a problem caused by storing the same fact in many rows: an update, insert or delete that leaves the data wrong, or cannot be done at all.
Take enrolment(roll_no, student_name, course_id, course_name, faculty): one row per student per course, with the faculty repeated in every row of a course. The program updates only some of those rows.
Example 1 · An update anomaly in a repeated-data table
# enrolment(roll_no, course_id, faculty): faculty repeats for every student of a courserows = [ ["21CS045", "DBMS", "Dr. Rao"], ["21CS046", "DBMS", "Dr. Rao"], ["21CS047", "DBMS", "Dr. Rao"], ["21CS045", "OS", "Dr. Iyer"],]
# The DBMS course gets a new faculty, but the update misses one row.rows[0][2] = "Dr. Menon"rows[1][2] = "Dr. Menon"
for row in rows: print(" | ".join(row))names = {faculty for _, course, faculty in rows if course == "DBMS"}print("Faculty names recorded for DBMS:", len(names))21CS045 | DBMS | Dr. Menon 21CS046 | DBMS | Dr. Menon 21CS047 | DBMS | Dr. Rao 21CS045 | OS | Dr. Iyer Faculty names recorded for DBMS: 2
What this code does
- One fact (who teaches DBMS) is stored three times, so one missed row leaves two different answers in the same table.
- After normalisation, course(course_id, faculty) holds the fact once and a single-row update is enough.
Each repeated fact causes a different failure:
- Update anomaly: changing a course's faculty means updating many rows; miss one and the data disagrees.
- Insert anomaly: a new course with no students yet cannot be added without a fake student.
- Delete anomaly: deleting the last student of a course also deletes the only record of that course.
- Redundancy: course_name and faculty repeat in every row of that course, wasting space. Revise: Anomalies and normalisation (opens in a new tab).
Predict the output
Which anomaly is it?
Read the situation, then pick the anomaly you would name aloud.
Round 1 of 3
Course CS301 has 40 enrolment rows. The faculty changes, but only 39 rows are updated.
Functional dependencies and attribute closure
A functional dependency X → Y means that two rows with the same X must have the same Y. It comes from the meaning of the data: roll_no → student_name holds because one roll number belongs to one student.
A table is only as good as the dependencies you find in it. The program computes attribute closures from a list of FDs, one step at a time.
Example 2 · Computing attribute closures
def closure(attrs, fds): result = set(attrs) changed = True while changed: changed = False for lhs, rhs in fds: # If everything on the left is already known, the right side is known too. if set(lhs) <= result and not set(rhs) <= result: result |= set(rhs) changed = True return result
fds = [("A", "B"), ("B", "C"), ("CD", "E")]for x in ("A", "D", "AD", "BD"): c = closure(x, fds) note = " <- determines all of ABCDE" if len(c) == 5 else "" print(f"{{{x}}}+ = {{{', '.join(sorted(c))}}}{note}"){A}+ = {A, B, C}
{D}+ = {D}
{AD}+ = {A, B, C, D, E} <- determines all of ABCDE
{BD}+ = {B, C, D, E}What this code does
- {A}⁺: A gives B (A → B), B gives C (B → C); CD → E cannot fire without D. So {A}⁺ = {A, B, C}.
- Only sets containing both A and D reach all five attributes. D appears on no right side, so it must be in every key; {A, D} is the candidate key.
The closure of X, written X⁺, is every attribute that X determines, directly or through a chain. To compute it, start with X and keep adding the right side of any FD whose left side is already inside, until nothing changes. If X⁺ contains every attribute, X is a super key; if no smaller part of X does, X is a candidate key.
Predict the output
Compute the closure
Follow the FDs from the starting set and pick the answer.
Round 1 of 3
What is {P}⁺?
1NF: atomic values
A table is in first normal form (1NF) when every cell holds a single atomic value and there are no repeating groups of columns. A phones column holding two numbers breaks 1NF, and so do columns phone1, phone2, phone3.
A spreadsheet import puts two phone numbers in one cell. The example shows the table before and after it is brought into 1NF.
Example 3 · Bringing a table into 1NF
student (not in 1NF)roll_no | name | phones21CS045 | Priya | 98450 11111, 080 2345 678921CS046 | Rahul | 99000 22222student (1NF) student_phone (1NF)
roll_no | name roll_no | phone
21CS045 | Priya 21CS045 | 98450 11111
21CS046 | Rahul 21CS045 | 080 2345 6789
21CS046 | 99000 22222
Key of student_phone: (roll_no, phone)What this code does
- Each cell now holds one value, so "find the student with phone 99000 22222" is a simple equality search, not text matching inside a list.
- Putting phones in a separate table, not phone1/phone2/phone3 columns, allows any number of phones without NULL-filled columns.
The fix is one row per value, usually in a separate table: student_phone(roll_no, phone) with both columns as the key. A relational table is in 1NF by definition in theory, but interviewers still ask for the fix, because real spreadsheets and CSV imports often break it. Revise: First normal form (opens in a new tab).
Find the bug
Find the 1NF mistake
Each snippet has one line that breaks 1NF or its fix. Tap it.
Which row of the imported student table breaks 1NF?
Round 1 of 22NF: removing partial dependencies
A table is in second normal form (2NF) when it is in 1NF and no non-prime attribute depends on part of a candidate key. A prime attribute belongs to some candidate key; a partial dependency is a non-prime attribute determined by only part of a key.
In marks(roll_no, subject_code, student_name, subject_name, score) the key is (roll_no, subject_code). student_name needs only roll_no, and subject_name only subject_code. The program spots these partial dependencies.
Example 4 · Spotting partial dependencies
def closure(attrs, fds): result = set(attrs) changed = True while changed: changed = False for lhs, rhs in fds: if set(lhs) <= result and not set(rhs) <= result: result |= set(rhs) changed = True return result
def show(c): return "{" + ", ".join(sorted(c)) + "}"
# marks(R = roll_no, S = subject_code, N = student_name, M = subject_name, C = score)fds = [("R", "N"), ("S", "M"), ("RS", "C")]print("{RS}+ =", show(closure("RS", fds)), " -> RS is the key")print("{R}+ =", show(closure("R", fds)), " -> N depends on part of the key")print("{S}+ =", show(closure("S", fds)), " -> M depends on part of the key")print("Not in 2NF: move R->N to student(R, N) and S->M to subject(S, M)"){RS}+ = {C, M, N, R, S} -> RS is the key
{R}+ = {N, R} -> N depends on part of the key
{S}+ = {M, S} -> M depends on part of the key
Not in 2NF: move R->N to student(R, N) and S->M to subject(S, M)What this code does
- The closure of each half of the key shows what that half decides on its own: R decides N, S decides M. Those are the partial dependencies.
- Score C needs both R and S, so it stays with the full key in marks(R, S, C).
2NF matters only when a candidate key is composite. Moving each partial dependency into its own table, keyed by the part of the key it depends on, fixes it. Revise: Second and third normal form (opens in a new tab).
Predict the output
Does it break 2NF?
Read the FDs and pick the answer you would give.
Round 1 of 2
Which attribute breaks 2NF?
Worked decomposition example
The marks table splits into student, subject and marks. The SQL creates the three tables and joins them back to show that no information is lost.
- Write all FDs from the meaning of the data.
- Find the candidate key with closures.
- For each non-prime attribute, check whether part of the key determines it.
- Move each partial dependency into a table keyed by that part.
- Check that a join on the keys rebuilds the original table.
Example 5 · Decomposing to 2NF and joining back
Runs in: MySQL 8
CREATE TABLE student (roll_no VARCHAR(10) PRIMARY KEY, student_name VARCHAR(40));CREATE TABLE subject (subject_code VARCHAR(8) PRIMARY KEY, subject_name VARCHAR(40));CREATE TABLE marks ( roll_no VARCHAR(10), subject_code VARCHAR(8), score INT, PRIMARY KEY (roll_no, subject_code), FOREIGN KEY (roll_no) REFERENCES student(roll_no), FOREIGN KEY (subject_code) REFERENCES subject(subject_code));INSERT INTO student VALUES ('21CS045', 'Priya'), ('21CS046', 'Rahul');INSERT INTO subject VALUES ('CS301', 'DBMS'), ('CS302', 'OS');INSERT INTO marks VALUES ('21CS045', 'CS301', 88), ('21CS045', 'CS302', 79), ('21CS046', 'CS301', 91);SELECT m.roll_no, s.student_name, m.subject_code, sub.subject_name, m.scoreFROM marks mJOIN student s ON s.roll_no = m.roll_noJOIN subject sub ON sub.subject_code = m.subject_codeORDER BY m.roll_no, m.subject_code+---------+--------------+--------------+--------------+-------+ | roll_no | student_name | subject_code | subject_name | score | +---------+--------------+--------------+--------------+-------+ | 21CS045 | Priya | CS301 | DBMS | 88 | | 21CS045 | Priya | CS302 | OS | 79 | | 21CS046 | Rahul | CS301 | DBMS | 91 | +---------+--------------+--------------+--------------+-------+
What this code does
- Each name is now stored once: renaming a subject is a one-row update, and a new subject can be added before anyone has marks in it.
- The join rebuilds the original wide table exactly, which shows the decomposition is lossless.
For marks: FDs roll_no → student_name, subject_code → subject_name, (roll_no, subject_code) → score. The result is student(roll_no, student_name), subject(subject_code, subject_name) and marks(roll_no, subject_code, score). Joining them on the keys gives back exactly the original rows, so no information is lost.
Order of execution
Decompose to 2NF
Tap the steps in the order you would do them.
Next step
Check your understanding
12 quick questions