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.
What a learner can do afterwards
- 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
1 · Read
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.
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.
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.
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.
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.
These five readings and the accompanying flashcards were written for this dataset. The readings use new wording and examples rather than reproducing textbook or how-to passages.
More to read
Check what each field means
Before splitting a table, describe the fact each field records. A customer's current address describes the customer now. An order's delivery address may record where a parcel was sent. Those values can start out equal and later differ without either record being inconsistent.
Next, identify the rule that determines each fact. A catalog name is determined by ProductID. An ordered quantity needs both an order and a product: ordering the same product on another order can require a different quantity.
Use the requirements to establish these rules. Suppose the rows visible today contain one product per order. That sample does not prove that OrderID will identify every order line in future. The shop may allow several products on the next order.
Repeated identifiers are useful when they preserve relationships. Several lines can carry the same OrderID because they belong to the same order. Normalisation does not mean removing every repeated value. It means checking which facts depend on which identifiers, then separating facts that should be maintained independently.
Normalising session sign-ups
A training centre records SessionID, RoomID, RoomName and a list of members with their names. Each member can sign up for a session once. Each session uses one room. RoomID determines RoomName, and MemberID determines MemberName. Assume the keys described below are the only candidate keys.
For 1NF, replace the member list with one row per session-member pair. Each row keeps SessionID, MemberID, MemberName, RoomID and RoomName. The pair SessionID and MemberID identifies a sign-up.
For 2NF, find facts that need only part of that pair. Put MemberID and MemberName in Members. Put SessionID, RoomID and RoomName in Sessions. Keep SignUps with the combined key SessionID and MemberID. Its two IDs reference Sessions and Members.
Sessions still has a dependency through a non-key field. SessionID determines RoomID, and RoomID determines RoomName. For 3NF, create Rooms(RoomID, RoomName). Sessions keeps SessionID and RoomID. SignUps and Members stay unchanged.
A room name now has one room record to update, even if many sessions use it. Check the references after each split: each sign-up must still identify its member and session, and each session must still identify its room.
Try three changes to a design
A shop stores current catalog names only on sales rows. Every row requires SaleID and ProductID. Try three changes to find problems in this design.
First, correct a product name that occurs on several sales. If one copy is missed, the stored copies disagree about the same current catalog fact. That is an update anomaly. Storing the name in a product record removes the need to update sales-row copies.
Next, add a product that has never been sold. The product has a ProductID, but there is no real SaleID to complete its sales row. Inventing a sale would record a false transaction. This insertion anomaly is avoided by allowing products to have records without sales.
Finally, delete the last sale of one product. If no other record holds its catalog details, deleting the sale also removes those details. This is a deletion anomaly. A separate product record lets the shop decide independently when to remove a catalog entry.
These tests concern current catalog names. The price charged on a past sale is a fact about that sale. It need not change when today's catalog price changes.
Keep keys and references valid
A candidate key identifies a row and has no unnecessary columns. A table may have several candidate keys. The primary key is the one selected to identify its rows and provide a target for references.
Consider sign-ups identified by SessionID together with MemberID. One session can have several members, and one member can attend several sessions. Neither ID identifies a sign-up alone. The pair is needed. Adding MemberName to that pair would introduce an unnecessary column rather than create a minimal key.
An assigned identifier can help when names repeat or change. It still needs constraints that reject duplicate or missing primary-key values. A natural identifier can also be suitable if it meets the data requirements. An assigned number is not automatically the best choice.
Stable primary keys reduce changes to related references. A primary-key value can be changed, but the change must leave those references valid.
When normalising, retain the identifiers needed to reconstruct the original relationships. Keeping an ID column is not enough on its own: the foreign-key values must refer to the intended records.
Displaying linked facts and maintaining copies
A report can display facts from several linked tables. For a chosen order, OrderID locates its lines. Each line's ProductID identifies a product, while the order's CustomerID identifies its customer. Displaying these facts together does not require storing them all in the same table.
A main form with a detail form is a presentation choice. That layout does not prove the stored tables are in 3NF. Check the tables' candidate keys and functional dependencies to assess normal form.
A designer may instead store a copy of a current customer field to reduce the work needed for a frequent report. Test whether the copy gives a useful speed benefit. Then identify which record is authoritative and how changes reach the copies. A report can run quickly while returning stale data if that maintenance is neglected.
A saved delivery address on an old order is different. It describes a past delivery, while the customer's current address describes the present. Keeping both facts is not the same as maintaining competing copies of one current address.
2 · Watch
به انگلیسیTake it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.