Normalisation and update anomalies

Design a single source of truth.

UnderstandStudy an examplePractise

Understand the concept

Normalisation reduces unnecessary repetition and insertion, deletion and update anomalies. For 1NF, store queryable individual values rather than comma-separated product lists. For 2NF, avoid non-key attributes depending on only part of a composite key. For 3NF, separate unnecessary dependencies through non-key attributes.

OrderItem.UnitPrice is a historical sale fact, not a duplicate of the current Product.Price. Similar-looking columns are not automatically a violation. Denormalisation can serve reporting performance, but its source of truth, refresh process and inconsistency risks must be explicit.

WHEN TO USE IT

Use it to separate repeated customer and product facts in transactional models.

COMMON PITFALL

Replacing historical sale prices with current product prices.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT CustomerId, FullName, Email FROM lab.Customer;

Customer facts are stored separately from orders.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Compare order 109’s historical amount with an amount based on current prices.

Show the solution and explanation

Worked solution

SELECT SUM(i.Quantity * i.UnitPrice) AS Historical, SUM(i.Quantity * p.Price) AS CurrentPriceBased FROM lab.OrderItem AS i JOIN lab.Product AS p ON p.ProductId = i.ProductId WHERE i.OrderId = 109;

37500 and 39000 differ; actual historical sales total 37500.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.