--- title: "Normalisation to Third Normal Form" description: "Taking tables through first, second and third normal form by checking values and functional dependencies. Table splits reduce repeated facts and related update, insert and delete anomalies." canonical: https://lightmysky.com/learn/computing/normalisation-to-third-normal-form-mt_cbRXVW6KxX source: https://lightmysky.com/learn/computing/normalisation-to-third-normal-form-mt_cbRXVW6KxX.md retrieved: 2026-11-12 --- > **Agent view.** This is the Markdown twin of the page, for tools and assistants. > When to use this site, and the call that answers each job: https://lightmysky.com/agent-instructions.md > API description (OpenAPI 3.1): https://lightmysky.com/openapi.json · Authentication: https://lightmysky.com/auth.md > Pricing: https://lightmysky.com/pricing.md · Catalog: https://lightmysky.com/llms.txt · Full catalog: https://lightmysky.com/llms-full.txt > Every machine-readable file on this domain: https://lightmysky.com/.well-known/ai-catalog.json > Ask for Markdown with `Accept: text/markdown`, a `.md` address, or `?mode=agent`. # Normalisation to Third Normal Form Taking tables through first, second and third normal form by checking values and functional dependencies. Table splits reduce repeated facts and related update, insert and delete anomalies. Subject: Computing · Area: Computer Systems · Ages 17 to 18 Page: https://lightmysky.com/learn/computing/normalisation-to-third-normal-form-mt_cbRXVW6KxX ## Ready when they can - Take an unnormalised table through first, second and third normal form - Identify update, insert and delete anomalies and explain which table split addresses each one - Say when a designer might accept a repeated field on purpose ## Lesson: Normalising an orders table Normalisation reduces repeated facts and related data problems, called anomalies. You already know tables and keys. A candidate key identifies a row with no unnecessary columns. Here, each table has one candidate key; other fields are non-key. First normal form (1NF) has one value of the intended kind per cell and no repeating groups. Turn lists into rows and keep the owner's ID with each value. *(drawing: One product list becomes two order-product rows. Both rows keep OrderID 72.)* **Example.** A functional dependency is a rule for all valid rows: matching values in one field or group must give matching values in another. Start with one row per order-product pair. The only candidate key is (OrderID, ProductID). Each product occurs once per order. ProductID determines ProductName. OrderID determines CustomerID, which determines CurrentAddress. Quantity depends on the whole pair. Each order has one customer. For second normal form (2NF), start in 1NF and remove partial dependencies: a non-key field needs only part of a candidate key. Put ProductName in Products(ProductID, ProductName). Put CustomerID and CurrentAddress in Orders(OrderID, CustomerID, CurrentAddress), since OrderID determines both. Keep Lines(OrderID, ProductID, Quantity). Its combined key links to Orders and Products; Quantity needs the whole pair. *(drawing: ProductID determines ProductName. Only part of the original combined key is needed, so this is a partial dependency.)* Third normal form (3NF) requires 2NF. In these one-candidate-key examples, remove dependencies from the key through one non-key field to another. OrderID determines CustomerID, which determines CurrentAddress: a transitive dependency. Split into Customers(CustomerID, CurrentAddress), keyed by CustomerID, and Orders(OrderID, CustomerID), keyed by OrderID. Orders keeps CustomerID as a foreign key to Customers. Products and Lines stay unchanged. A one-column key alone does not guarantee 3NF. *(drawing: Each field determines the next. Store CurrentAddress with CustomerID and keep the customer link in Orders.)* An update anomaly occurs when changing some copies of a fact leaves others inconsistent. If orders repeat a customer's current address, an update can miss a copy. Store that address in Customers and reference it. An old order's delivery address records a different fact and need not change when the customer moves. Suppose product facts exist only on order rows, whose key requires an OrderID. A new product with no orders has no valid row to hold it. That is an insertion anomaly. A separate Products table lets you add the product without inventing an order. Deleting a product's last order row can also erase its product facts if they exist nowhere else. That is a deletion anomaly. A separate Products record preserves those facts when an order is deleted, so deleting a sale need not delete a catalog entry. A designer may keep copies of a current field for faster reads. This is denormalisation. Test the speed benefit, choose an authoritative source and plan how updates will reach its copies. Ignored copies can disagree. A historical delivery address records a different fact, so it is not a copy of the customer's current address. **Recap.** Check single values, then partial and transitive dependencies. Preserve keys and links when splitting tables, and explain the particular anomaly each split addresses. *This lesson, its practice questions and flashcards were written for this dataset.* ## Practice 8 questions on this page, each with its working shown. ## Needs first - [Relational Databases: Tables, Keys and Relationships](https://lightmysky.com/learn/computing/relational-databases-tables-keys-and-relationships-mt_i7pZV6H902) ## Opens up - [SQL: Selecting, Filtering and Ordering Rows](https://lightmysky.com/learn/computing/sql-selecting-filtering-and-ordering-rows-mt_lDVVG2AKzs) - [Functional Dependencies and Boyce-Codd Normal Form](https://lightmysky.com/learn/computing/functional-dependencies-and-boyce-codd-normal-form-mt_PfWLq1aEQz)