Three writes that go wrong on one good-looking table

01 The idea

The register that cannot record a new student

A department keeps one table for its DBMS lab: who the student is, which experiment they submitted, and what it scored. Every column has a type, the primary key is declared, and every row in it is true. Three ordinary operations still go wrong on it, and not one of them raises an error or violates a constraint.

Those three are called anomalies: an insert, update or delete that leaves the data wrong or that the table will not let you perform, even though your code has no bug in it. They are not bad luck and they are not three separate diseases. All three come from one cause, which is that this table stores two different kinds of fact at once. “CS07 is Asha, batch B1” is a fact about a student. “CS07 scored 15 on E2” is a fact about a submission. Put them in the same row and the student fact gets copied onto every submission the student makes.

That copying is redundancy, and it is worth being precise about why it matters, because the usual answer is wrong. Redundancy is not a disk problem. Splitting the register in this lesson saves three cells out of thirty, which is not a reason to do anything. Redundancy is a write problem: a fact stored in three places gives every future update three chances to be right, and no way to notice when it was right twice. The anomalies are that bill arriving.

Normalisation pays that bill by moving each fact to a table whose key is the thing the fact is about. This lesson does the first two steps. 1NF makes every cell hold one value, which is what lets you state a rule about the data at all. 2NF then removes exactly the copying that 1NF created. They run in that order for a reason you will see in section 02: flattening the table is what grows the key from one column to two, and a key made of more than one column is the only thing that makes a 2NF violation possible in the first place.

02 Worked example

One lab register, and the three writes it breaks on

Here is the register as the department actually keeps it: one line per student, with the experiment numbers typed into one cell and the marks into another. Three students, three rows, and everything a human needs to read it.

-- LAB_RECORD, as kept in the spreadsheet. Not a relation yet.RollNo  StudentName  Batch  Experiments   MarksCS07    Asha         B1     E1, E2, E3    18, 15, 20CS12    Ravi         B1     E1, E2        12, 19CS19    Meera        B2     E1            17 -- two lists in two cells, and nothing but position pairs E2 with 15.-- delete one entry from one list and every pairing after it shifts.

The Experiments and Marks columns are what stops this from being a relation, and they cost you twice. A cell in them holds three values instead of one, and the pairing between an experiment and its mark is carried by nothing but the order of two strings. 1NF requires that every attribute value is a single value drawn from its domain, with no repeating group. Flattening turns each list entry into its own row, so 3 rows become 3 + 2 + 1 = 6.

-- LAB_RECORD in 1NF. One value per cell. Key: {RollNo, ExptNo}RollNo  StudentName  Batch  ExptNo  MarksCS07    Asha         B1     E1      18CS07    Asha         B1     E2      15CS07    Asha         B1     E3      20CS12    Ravi         B1     E1      12CS12    Ravi         B1     E2      19CS19    Meera        B2     E1      17 -- RollNo -> StudentName, Batch      half the key decides both-- {RollNo, ExptNo} -> Marks         the whole key decides this one

The key had to grow. Before flattening, RollNo identified a row on its own. Now CS07 is on three rows and E1 is on three rows, so neither column is unique by itself and the candidate key is the pair {RollNo, ExptNo}. Drop either half and rows collide, so the pair is minimal, and there is no other candidate key in this table.

Line 10 is the whole lesson. RollNo is half of that key, and it decides both StudentName and Batch on its own. So Asha’s name and batch are written on all three of her rows and Ravi’s on both of his. Count the copies: six cells out of thirty are repeats of a fact already stored one line above.

Read the highlighted node as the pivot, not the mistake. 1NF was correct and necessary: until every cell held one value there was no dependency you could state and no key you could declare. What 1NF did was make the key composite, and a composite key is the only place a column can depend on part of the key. Node 3 is that possibility being realised, and nodes 4 and 5 are its consequence and its fix.

Now run the three operations against the six-row table. The exact statements, and the exact thing the database says back:

-- 1. INSERT anomaly. Karan joins the lab today and has submitted nothing.INSERT INTO lab_record VALUES ('CS24','Karan','B2', NULL, NULL);  -> ERROR: null value in column "exptno" violates not-null constraint -- 2. UPDATE anomaly. Asha moves to B2. Her batch sits on three rows.UPDATE lab_record SET batch='B2' WHERE rollno='CS07' AND exptno IN ('E1','E2');  -> UPDATE 2        the third row still says B1SELECT DISTINCT batch FROM lab_record WHERE rollno='CS07';  -> B1  -> B2            one student, two batches, no error anywhere -- 3. DELETE anomaly. Meera withdraws her only submission.DELETE FROM lab_record WHERE rollno='CS19' AND exptno='E1';  -> DELETE 1        and CS19 Meera B2 is now stored nowhere at all

The insert. Karan exists, he is in batch B2, and the register has nowhere to put him. ExptNo is half the primary key, and no part of a primary key may be null: that is entity integrity, and it is the rule doing the rejecting. PostgreSQL words the message as a not-null violation because it implements a primary key as NOT NULL plus UNIQUE; Oracle says ORA-01400 and MySQL raises error 1048. Same rule, three wordings. The only way to record Karan is to invent an experiment he has not done, and inventing data to satisfy a schema is the definition of an insertion anomaly.

The update. Asha’s batch is not one cell to change. It is three, and nothing in the schema links them, so the update has to be right three times over. This one was written off a report of the two experiments already graded, so its WHERE clause names E1 and E2 and never reaches E3. Two rows change and one does not. Look at what the database now believes: CS07 is in B2 on two rows and B1 on one, and every one of those three rows is individually legal. No type is wrong, no constraint is broken, no error was raised. The table holds two answers to one question and cannot tell you which is current.

The delete. Meera has exactly one row, because she has submitted exactly one experiment. Deleting that submission deletes the only place the register recorded that CS19 is Meera and that she is in batch B2. She has not left the college. The database no longer holds any record that she is in it, and nothing about the operation looked unusual: one row asked for, one row deleted.

03 Mechanics

Two tests, and the split the second one forces

Anomalies are the symptom. The test is mechanical, and it runs on functional dependencies, written X → Y: any two rows that agree on X must agree on Y. That is a rule about every legal row, so you confirm it from what the data means, never by eyeballing six sample rows. Here is every non-trivial dependency this table has, tested one at a time.

Row four is why this decomposition produces two tables and not three. Nothing hangs off ExptNo, so there is no experiment table to build. Row five matters as much and is the one candidates skip: you have to check the columns that are not keys before you claim a table is finished, rather than assume they determine nothing.

The fix is mechanical once the dependency is written down: take the offending determinant and everything it determines into a new table where that determinant is the key, and leave it behind in the original as a foreign key. Both violations here share the determinant RollNo, so one split closes both.

CREATE TABLE student (  rollno  TEXT PRIMARY KEY,            -- the whole key of this table now  name    TEXT NOT NULL,  batch   TEXT NOT NULL);CREATE TABLE result (  rollno  TEXT REFERENCES student(rollno),  -- left behind as a foreign key  exptno  TEXT,  marks   INT NOT NULL CHECK (marks BETWEEN 0 AND 20),  PRIMARY KEY (rollno, exptno));

Line 2 is the split. RollNo was half a key and is now a whole one, which is the only change that matters. A student’s name and batch are written once each, on the single row that is about that student.

Line 10 keeps the composite key where it belongs. Marks genuinely depends on both halves, so result keeps the pair as its key and carries nothing else. Its only dependency is {rollno, exptno} → marks, and the determinant is the key.

Neither table has a rule left over. Check rather than assume: in student, name determines nothing because two students may share a name, and batch determines nothing because B1 covers two roll numbers. Both tables are as far as this lesson’s two rules can take them. Whether a 2NF split always lands there is the question the next lesson opens.

Now check the split did not lose anything, because a decomposition that drops or invents rows is worse than the table you started with. A two-way split is lossless when the columns the two pieces share are a superkey of at least one of them. Here the shared column is rollno, which is the whole key of student, so every result row matches exactly one student row: never two, because the key is unique, and never zero, because the foreign key says so. Join them back and count: CS07 contributes 3 rows, CS12 contributes 2, CS19 contributes 1. Six rows in, six rows out, identical to the register.

Compare that with a split that ignores the condition. Take A(RollNo, StudentName, Batch) and B(Batch, ExptNo, Marks). Every column survives and neither piece has a duplicate row, so it looks fine. The shared column is Batch, which is a key of neither. Joining on it pairs A’s two B1 rows with B’s five B1 rows and A’s single B2 row with B’s single B2 row: 2 × 5 + 1 × 1 = 11 rows where there were 6. Nothing was lost, and five rows appeared that were never real, among them CS12, Ravi, B1, E3, 20 for an experiment Ravi never submitted.

Last, the arithmetic that settles the space argument. The register is 6 rows by 5 columns, so 30 cells. After the split, student is 3 by 3 and result is 6 by 3, so 27. You saved three name cells and three batch cells and paid three back on the duplicated rollno column: a net saving of 3 cells, or 10 per cent, which would not justify a single extra join on its own. The reason to do it is the thing you cannot measure on disk, which is that after the split there is no write that can leave two rows disagreeing.

05 Cheat sheet

1NF and 2NF on one card

Every row here is something an interviewer asks directly. Learn the middle column as a sentence you can say, not as a phrase you recognise.

06 Where & why

Where 1NF is a choice, and where 2NF is never checked

No product refuses a table for a partial dependency, and several will happily store a list in a cell. Both forms are decisions you make and defend, which is exactly why an interviewer can tell the difference between a candidate who has made them and one who has read about them.

07 Interview questions

What they actually ask

Normalisation is asked in almost every DBMS interview, and it is almost always answered as recited definitions. What separates an answer is a concrete failure you can produce on demand and a dependency you can write on the whiteboard.

08 Practice problems

Six to take apart

Work in the same order every time: write the candidate keys down first, mark every attribute prime or non-prime, and only then test a dependency. Reaching for the split first is how people report violations that are not there.