Functional Dependencies & Anomalies

You build the college database as one big table. Everything in it — student, course, instructor, grade. One table, no joins, nice and simple.

Three weeks later Prof. Rao gets married and changes his surname.

You now have to edit 400 rows. You miss two.

Why & what

That's not bad luck. It's the table design biting you.

The problem is that one table is holding facts about three different subjects at once students, courses, and enrollments. And when unrelated facts share a row, editing one thing means touching many rows.

Before we fix it, we need a way to say precisely which fact decides which. That's what a functional dependency is.

A functional dependency means: if you know the value of column A, then the value of column B is fixed. We write it A → B, and read it "A decides B".

Normalization is the process of splitting tables so that every fact is stored exactly once.

How it works

Look at one wide table holding roll_no, name, course, title, instructor, grade.

1. Find who decides what. Give me a roll_no and I can tell you the name — so roll_no → name. Give me a course and I can tell you the title and the instructor — so course → title, instructor.
2. Find what the grade needs. Does roll_no alone decide the grade? No — Aisha has different grades in different courses. Does course alone? No. You need both: (roll_no, course) → grade.
3. Now spot the mismatch. The key of this table is (roll_no, course). But title only needed course. So title is sitting in a table whose key is bigger than what it actually depends on. That mismatch is what causes the repetition.
4. Watch it repeat. Aisha and Ben are both in DBMS 101, so "Databases" and "Rao" get stored twice. Add 400 students and it's stored 400 times.
5. Name the three ways it hurts you:oUpdate anomaly — Rao changes his name, and you must edit every row he appears in. Miss one and the database now claims two different names. oInsert anomaly — a new course opens with no students yet. There's no row to put it in, so the course simply can't exist. oDelete anomaly — Cara drops OS101, and hers was the only OS101 row. Delete it and the whole course disappears with her.

Pause here. Which anomaly hits you if the college wants to record a new instructor who hasn't been assigned a course yet?

Insert anomaly. There's no row to hold him, because a row can only exist if a student-course pair exists.

Notice: all three problems come from one cause — mixed-up facts in one table.

Common confusion

People read A → B as "A causes B" or "A comes before B." It means neither. It only means: for one value of A, there is exactly one value of B.

Test it with real rows. If you ever find two rows with the same A but different B, then A → B is false. That's the whole check.

Second thing: a functional dependency is a rule about the real world, not about the rows you happen to have today. name → roll_no might look true if all your current students have different names. It's still wrong, because two students could share a name. Ask "could this ever collide," not "has it collided yet."

Interview angle

1. "What is a functional dependency?" — If you know A, B is fixed: A → B. Give a quick example like roll_no → name and say why the reverse doesn't hold.
2. "Name the three anomalies and give an example of each." — Update (edit many rows for one fact), insert (can't add a course with no students), delete (removing a student deletes the course). Interviewers want the examples, not the labels.
3. "Why do these anomalies happen in the first place?" — Because one table is storing facts about more than one subject, so a single fact ends up repeated across rows.

Quiz: skipped — reinforced above.

Recap

A functional dependency A → B means A decides B, and when a table mixes facts from different subjects it repeats data — causing update, insert and delete anomalies.