What is a partial dependency and how does 2NF remove it?
A partial dependency is when a non-key column depends on only part of a composite primary key. 2NF requires 1NF plus no partial dependencies, which you achieve by moving that column to a table keyed by that part.
Explain it simply
In an exam hall register, the key is (student, subject) because each row records a student's marks in a subject. If the same row also stores the subject's full name, that name depends only on the subject, not on the student. It repeats for every student, which is wasteful and risky.
Example
Table marks(roll_no, subject_id, subject_name, score) with key (roll_no, subject_id).
(roll_no, subject_id) -> scoreuses the whole key. Fine.subject_id -> subject_nameuses only part of the key. This is the partial dependency.
Decompose:
CREATE TABLE subjects (
subject_id INT PRIMARY KEY,
subject_name VARCHAR(50)
);
CREATE TABLE marks (
roll_no INT,
subject_id INT REFERENCES subjects(subject_id),
score INT,
PRIMARY KEY (roll_no, subject_id)
);Now a subject name is stored once, and renaming it touches one row.
A table with a single-column primary key cannot have a partial dependency, so if it is in 1NF it is already in 2NF.
What to say in the interview
"2NF removes partial dependencies, where a non-key attribute depends on part of a composite key. I move such attributes to a separate table keyed by that part. It only matters when the key is composite."
Common mistakes
- Looking for partial dependencies in tables with a single-column key.