All subjects · Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development
Data redundancy and why we normalise
Edexcel IAL Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development
Storing the same fact in more than one place wastes space and, worse, lets copies disagree. Normalisation is the process that removes this redundancy from relational databases (spec 12.2.1 and 12.2.2).
Data redundancy
Data redundancy means the same data item is stored more than once, usually in a single flat table or in several unlinked files.
Example: a flat ORDERS table stores the customer's name and address on every order line, so a customer with 30 order lines has their address stored 30 times.
Problems caused by redundancy (anomalies)
- Wasted storage and slower processing and backups.
- Update anomaly: when the customer moves, every copy must be changed; if one is missed the data becomes inconsistent (two addresses for one customer), so integrity is lost.
- Insertion anomaly: you cannot record a new product until someone orders it, because product details only exist on order lines.
- Deletion anomaly: deleting a customer's only order also deletes the only record of that customer.
- Inaccurate reports: totals and counts can double-count duplicated rows.
Normalisation and why it is needed
Normalisation is a formal method of organising data into a set of related tables (relations) so that each fact is stored once, in the table where it belongs, linked by primary and foreign keys.
Benefits: removes redundancy and the update, insertion and deletion anomalies; improves consistency and integrity; saves storage; makes the database easier to maintain and extend; supports referential integrity.
Drawbacks: more tables, so queries need joins, which can be slower and harder to write; very large reporting systems (such as data warehouses) sometimes deliberately keep some redundancy for speed.
Key terms
| Term | Meaning |
|---|---|
| Entity / relation | A table about one thing, such as CUSTOMER |
| Attribute | A field (column), such as Surname |
| Tuple | A record (row) |
| Primary key (PK) | Attribute(s) that uniquely identify each record |
| Composite key | A primary key made of two or more attributes |
| Foreign key (FK) | An attribute that is the primary key of another table, used to link them |
| Functional dependency | A determines B if each value of A gives exactly one value of B (CustomerID determines CustomerName) |
Worked example
A flat table of 9 order lines contains one customer on 6 of the lines. The customer's address is 40 bytes. How much space is wasted storing the address redundantly, and what happens if the customer moves?
- The address is needed once but is stored 6 times
- Redundant copies = 6 - 1 = 5
- Wasted space = 5 x 40 = 200 bytes
- If the customer moves, all six copies must be updated; missing one causes an update anomaly and inconsistent data
Answer: 200 bytes wasted, plus a risk of inconsistent addresses
Check yourself
- What is data redundancy?
- Data that is out of date
- Unnecessary repetition of the same data item
- Data that has been encrypted
- Data that has been deleted
Show answer
Unnecessary repetition of the same data item. Redundant data is duplicated.
- A supplier's phone number is stored on 200 product rows. It is changed on 199 rows only. What is this called?
- An insertion anomaly
- A deletion anomaly
- A transposition error
- An update anomaly
Show answer
An update anomaly. Missing one copy when updating leaves inconsistent data.
- A new course cannot be recorded until a student enrols on it, because course details are stored only on enrolment rows. Which problem is this?
- Update anomaly
- Insertion anomaly
- Deletion anomaly
- Concurrency problem
Show answer
Insertion anomaly. You cannot insert one fact without another.
- Deleting the last booking for a hotel guest also removes the guest's contact details. Which anomaly is this?
- Update anomaly
- Insertion anomaly
- Deletion anomaly
- Validation error
Show answer
Deletion anomaly. Deleting one fact removes another.
- A customer's 25-character name is stored on 12 order rows in a flat table. How many characters are redundant?
Show answer
275 characters. It is needed once; the other 11 copies of 25 characters are redundant: 275.
- Which is a drawback of a fully normalised database?
- It stores more duplicate data
- It causes more update anomalies
- Queries often need joins across several tables, which can be slower
- It cannot use primary keys
Show answer
Queries often need joins across several tables, which can be slower. More tables means more joins.
- Explain why normalisation improves data integrity.
Show answer
Each fact is stored only once, so when it changes it is updated in one place and copies cannot become inconsistent.. Stored once, so one update, so no inconsistent copies.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (first teaching 2018), Issue 2, February 2021
- Pearson Edexcel IAL Information Technology: scheme of work for Unit 3
Last updated 8 November 2026. Spotted a mistake? Tell us.