Computer ScienceCore22 min read

Data Integrity and Normalization

Removing the duplication that lets a database contradict itself

This topic appears in:

2nd Year Computer Science · ch.4A Level Computer Science (9618) · ch.8
01

Integrity: the rules that keep data believable

Data integrity means the data is accurate, consistent and complete. A database enforces it through rules, so that invalid data is refused at the point of entry rather than discovered in a report six months later.

Three kinds of integrity rule are named in the syllabus.

  • Entity integrity — every primary key must be present and unique. No record may exist without an identity, and no two may share one.
  • Referential integrity — a foreign key must either be empty or match an existing primary key. You cannot enrol a student who does not exist, and you cannot delete a class while students still refer to it.
  • Domain integrity — every value must be valid for its field: the right data type, within an allowed range, matching a required format, or drawn from a permitted list.

What referential integrity prevents

Without it, deleting a class leaves student records pointing at a class that no longer exists — an orphaned record. Reports then show students in "class 7", which cannot be looked up. The DBMS prevents this by refusing the deletion, or by cascading it, or by setting the foreign keys to null; which of the three is a design decision, but doing nothing is not an option.

02

Validation and verification are different things

Validation is an automatic check that data is reasonable. Verification is a check that data was entered correctly. Neither guarantees the data is true, and the distinction is examined every year.

A date of birth of 1899 fails validation because it is unreasonable. A date of birth typed as 1995 instead of 1985 passes every validation check there is — it is a perfectly reasonable date — and only verification, such as double entry or reading it back to the person, would catch it.

CheckRejectsExample
Range checkvalues outside allowed limitsmark must be 0–100
Type checkthe wrong data typeage must be a number
Format checkthe wrong patternCNIC as 5 digits, 7 digits, 1 digit
Presence checkan empty required fieldname must not be blank
Length checktoo few or too many characterspassword 8–16 characters
Lookup checka value not in a permitted listclass must be an existing class
03

The three anomalies

A badly structured table misbehaves in three specific ways, and normalisation exists to remove them. Naming the anomaly is usually worth a mark.

An insertion anomaly means you cannot record one fact without knowing another. If teacher details live in the enrolment table, a newly hired teacher cannot be recorded until a student enrols with them. A deletion anomaly means removing one fact destroys another: deleting the last enrolment for that teacher erases their phone number entirely. An update anomaly means one change must be made in many rows, and missing one leaves the database contradicting itself.

Start at Unnormalised and step forward. Watch TeacherPhone: it is repeated on every row until 3NF moves it to its own table, after which changing a phone number is one edit in one place.

04

The three normal forms

Normalisation is the process of restructuring tables to remove those anomalies. Each form fixes one kind of dependency, and each assumes the previous form has been reached.

1NF: no repeating groups — every cell holds a single value2NF: in 1NF, and no partial dependency on part of a composite key3NF: in 2NF, and no transitive dependency on a non-key fieldthe traditional summary: every non-key field depends on the key, the whole key, and nothing but the key
Worked example

Normalise to 3NF: ORDER(OrderNo, ProductCode, ProductName, Quantity, CustomerID, CustomerName).

  1. Every cell already holds a single value, so the table is in 1NF.Check this first — if a cell held "shirt, tie" the split would have to come before anything else.
  2. The key is OrderNo + ProductCode. ProductName depends only on ProductCode, and CustomerName only on OrderNo — partial dependencies.Each depends on part of the key rather than all of it, which is what 2NF forbids.
  3. Split them out: PRODUCT(ProductCode, ProductName) and ORDER(OrderNo, CustomerID, CustomerName), leaving ORDERLINE(OrderNo, ProductCode, Quantity). Now 2NF.Quantity genuinely needs both keys, so it stays in the linking table.
  4. In ORDER, CustomerName depends on CustomerID, which is not the key — a transitive dependency.OrderNo → CustomerID → CustomerName is a chain through a non-key field.
  5. Split again: CUSTOMER(CustomerID, CustomerName) and ORDER(OrderNo, CustomerID). Now 3NF.Four tables, each fact stored once, and a customer changing their name is a single edit.

PRODUCT, CUSTOMER, ORDER and ORDERLINE — four tables in 3NF.

Partial and transitive are not the same

A partial dependency is on part of a composite key — so it cannot exist at all if the primary key is a single field. A transitive dependency is on a non-key field, and can exist whatever the key looks like. Students frequently claim a partial dependency in a table with a single-field key, which is impossible by definition.

Before you leave this chapter

  1. Entity integrity: primary keys present and unique. Referential: foreign keys must match. Domain: values valid for the field.
  2. Validation checks that data is reasonable; verification checks it was entered correctly. Neither proves it is true.
  3. Insertion, deletion and update anomalies are what normalisation removes.
  4. 1NF no repeating groups; 2NF no partial dependency; 3NF no transitive dependency.
  5. A partial dependency requires a composite key; a transitive one does not.

Practice questions

6 questions · 20 marks · full working on every one

Try each one on paper first, then open the working. The marks are shown where they are actually awarded, because that is where they are actually lost.

Short questions

3 · 6 marks

Two marks each, in the style of the short-question section of the paper. Answer in two or three lines.

SQ1[2 marks]
Differentiate between validation and verification.
Model answer

Validation is an automatic check that entered data is reasonable — within range, of the right type, in the right format. Verification checks that the data was entered correctly, for example by double entry or by reading it back. A date typed as 1995 instead of 1985 passes validation but fails verification.

Examiner tip. The example of a plausible but wrong value is what demonstrates the distinction. Definitions alone often score one.

SQ2[2 marks]
What is referential integrity?
Model answer

The rule that a foreign key must either be empty or match an existing primary key in the related table. It prevents orphaned records — a student enrolled in a class that does not exist, or left pointing at a class after it is deleted.

Examiner tip. Naming orphaned records as what it prevents is usually the second mark.

SQ3[2 marks]
A table is in 1NF. What must be true of it?
Model answer

It contains no repeating groups — every cell holds a single, indivisible value, and there are no columns such as Subject1, Subject2, Subject3. Every record is also uniquely identifiable by a primary key.

Examiner tip. Both forms of the fault count: several values in one cell, and several numbered columns for the same thing.

Solved numericals

2 · 8 marks

Full working, one step per line, with the marks shown where they are awarded.

N1[4 marks]
Explain the three anomalies that normalisation removes, with an example of each.
Full working
  1. Insertion anomaly — a fact cannot be recorded without another. A new teacher cannot be entered until a student enrols with them[1]
  2. Deletion anomaly — removing one fact destroys another. Deleting the last enrolment erases the teacher's phone number[1]
  3. Update anomaly — one change must be made in many rows[1]
  4. Missing one row leaves the database holding two different values for the same fact, so no report can be trustedthe consequence is the fourth mark[1]

Insertion, deletion and update anomalies, all caused by storing a fact in more than one row.

Examiner tip. All three come from the same root cause: one fact stored in many places. Saying so ties the answer together.

N2[4 marks]
The table STUDENT(RollNo, Name, ClassID, ClassTeacher) is in 2NF but not 3NF. Explain why and correct it.
Full working
  1. ClassTeacher depends on ClassID, not on the primary key RollNo[1]
  2. RollNo → ClassID → ClassTeacher is a transitive dependency through a non-key field, which 3NF forbidsnaming it is required[1]
  3. Split into STUDENT(RollNo, Name, ClassID)ClassID remains as a foreign key[1]
  4. and CLASS(ClassID, ClassTeacher) — the teacher is now stored once per class[1]

A transitive dependency; split into STUDENT and CLASS tables.

Examiner tip. A transitive dependency is a chain: key → non-key field → another non-key field. Spotting the middle term is how you find it.

Long questions

1 · 6 marks

Theory and numerical together, as they appear in the long-question section.

LQ1[6 marks]
A table records library loans: LOAN(LoanID, MemberID, MemberName, BookISBN, BookTitle, Author, DateOut).
  1. Identify two problems with this structure.
  2. Normalise it to 3NF, showing all tables and keys.
  3. Explain one benefit and one cost of the normalised design.
Mark scheme
  1. MemberName is repeated on every loan by that member, and BookTitle and Author on every loan of that book — redundancy[1]
  2. An update anomaly follows: correcting a member's name means editing every one of their loan records, and missing one leaves contradictory dataaccept insertion: a new book cannot be recorded until it is borrowed[1]
  3. MEMBER(MemberID, MemberName)[1]
  4. BOOK(BookISBN, BookTitle, Author)[1]
  5. LOAN(LoanID, MemberID, BookISBN, DateOut) — both foreign keys, and DateOut belongs to the loan itself[1]
  6. Benefit: each fact is stored once, so updates are single edits and no contradiction is possible. Cost: retrieving a full loan report now requires joining three tables, which is more complex and slower to queryboth halves needed for the mark[1]

(a) redundancy and update anomalies (b) MEMBER, BOOK and LOAN (c) single-point updates, at the cost of joins

Examiner tip. Part (c) matters: normalisation is a trade-off, not a free improvement. Large systems sometimes deliberately denormalise for read speed, and knowing that shows real understanding.