Simulic
English

Database normalisation – from a flat table to 3NF, with ER diagrams

Computer ScienceFiles, Storage & DatabasesAges 16–17

Loading…

Use with my class ✨ Customize with AI Report a problem

Start from a flat table full of repeated data, trigger update, insert and delete anomalies, then step through 1NF, 2NF and 3NF as the table splits, primary and foreign keys appear and partial and transitive dependencies are removed. A crow's-foot entity–relationship (ER) diagram changes at every step, a chart counts stored values and repeated copies, and an SQL query with JOINs rebuilds the original rows to show that no information was lost.

Lesson: Relational database design: functional dependencies, normalisation to 1NF, 2NF and 3NF, data anomalies, ER diagrams and one-to-one, one-to-many and many-to-many relationships

What it shows

A flat table that stores everything in one place repeats the same facts many times, so one change must be made in several rows (update anomaly), new facts cannot be stored until a record uses them (insert anomaly) and deleting one record can destroy unrelated facts (delete anomaly). Normalisation splits the table step by step: 1NF removes repeating groups, 2NF removes partial dependencies on part of a composite key, and 3NF removes transitive dependencies between non-key columns. Foreign keys link the tables, and JOINs rebuild the original rows without loss.

How to use

Pick the Data set and the number of Records, then press UNF, 1NF, 2NF and 3NF (or Back and Next) to watch the table split. Tick Dependencies to see which functional dependencies break which normal form. Choose an Anomaly and press Try it to change, add or delete data; Undo restores it. Switch the ER diagram between Logical and Conceptual, tap an entity to highlight its table, and use Filter to run the SQL query for one record.

Parameters you can change

  • Data set Shop orders, Student course enrolments
  • Normalisation step Unnormalised form (UNF), First normal form (1NF), Second normal form (2NF), Third normal form (3NF)
  • Number of records (orders or students) 3–8 records
  • Anomaly to test None, Update anomaly, Insert anomaly, Delete anomaly
  • ER diagram level Logical (with link table), Conceptual (many-to-many)
  • Show functional dependencies

Questions to explore

  1. Why does the 1NF table hold more repeated copies of the same facts than the unnormalised table?
  2. In 2NF, why can a new customer who has not ordered anything still not be stored?
  3. Why must the many-to-many relationship between orders and products be replaced by a link table?

Related simulations