One bad table design, and the two ways to prove it is bad
A single wide table can quietly hide the same fact in multiple rows, causing three specific anomalies. This lesson names them precisely, then introduces functional dependencies — the formal tool that proves whether splitting a table actually fixes the problem.
After this lesson
You should be able to
- Name the three anomalies (update, insertion, deletion) a redundant design causes, with an example each.
- Write a functional dependency and explain what it claims.
One wide table, three ways it goes wrong
Consider one table Enrollment(roll_number, name, branch, course_id, course_title) storing every student-course pairing, with the student's name and branch repeated on every row for every course they take. An update anomaly happens when a student changes branch: every one of their many enrollment rows must be updated, and missing even one leaves the data self-contradictory.
An insertion anomaly happens when you cannot record a fact without an unrelated one — you cannot add a new student to the system until they enroll in at least one course, because the student's name and branch only exist attached to a course_id row. A deletion anomaly happens when removing one fact accidentally destroys another — if a student drops their only course, deleting that row deletes their name and branch from the database entirely, even though they are still a student.
Decomposition splits a table; functional dependencies prove it was correct
Decomposition is splitting one relation into two or more smaller ones — Enrollment becomes Student(roll_number, name, branch) and Enrolls(roll_number, course_id). Now a student's name and branch exist exactly once, regardless of how many courses they take, and all three anomalies disappear. But decomposition is not automatically safe: split a table the wrong way, and joining the pieces back together can produce rows that never existed in the original data, or lose information entirely.
A functional dependency (FD), written X to Y, is a formal claim that the value of attribute set X always determines the value of attribute set Y — roll_number to name, branch claims that knowing roll_number always fixes exactly one name and branch, no matter how many rows share that roll_number. FDs are the precise tool the next lesson uses to prove mathematically whether a decomposition is correct, rather than relying on intuition alone.
Try it yourself
For the Enrollment table in this lesson, write one functional dependency involving course_id and course_title, and explain in one sentence what it claims.
Need a hint?
A course's title is fully determined by its course_id — every row with the same course_id must have the same course_title.
Check the worked solution
course_id to course_title. This claims that knowing a course_id always fixes exactly one course_title — no two rows can have the same course_id with different course_titles, since a course cannot have two different official titles at once.
Quick check
In the single-table Enrollment design, why does deleting a student's only enrollment row also delete facts that have nothing to do with enrollment, like their name and branch?
Why this lesson exists
Syllabus mapping
Problems Caused by redundancy, Decompositions, problem related to decomposition, reasoning about FDS
Maps to course outcome CO3.