Normalisation to 3NF

1.3.2 Databases

Databases — Note 3 of 6

Candidates should be able to use Normalisation to produce a suitable database schema and explain the benefits of doing so. They should be able to normalise up to 3NF. Candidates should know and be able to apply the criteria for 1NF, 2NF and 3NF.

What you need to know

Normalisation is the process of organising data into tables so that redundancy is removed and each fact is stored only once. You need to know the rules for first, second and third normal form and be able to normalise a set of data to 3NF.

What to know

Summary: every non-key field must depend on 'the key, the whole key, and nothing but the key'.
Benefits: less duplicated data, so less storage; data is updated in one place, avoiding inconsistencies; easier to maintain and query.
Drawback: more tables mean more joins in queries, which can be slower.
METHOD: For 2NF, ask of each non-key field 'does it depend on the WHOLE key?' If it needs only part of the key, move it to a new table keyed by that part. For 3NF, ask 'does it depend on another non-key field?' If so, move it to a new table keyed by that field, and leave that field behind as a foreign key.

Key terms

Exam tipCommon mistake

Normalisation: Structuring a relational database so that redundant data is removed and data integrity is improved.
First normal form1NF: Each field contains a single (atomic) value, there are no repeating groups of fields, and each record has a primary key.
Second normal form2NF: The table is in 1NF and every non-key field depends on the WHOLE primary key (no partial dependencies). This only matters when the primary key is composite.
Common mistake: Calling CustomerName only a transitive dependency and leaving it in the table at 2NF. While the key is composite, a field that depends on part of the key (directly or through another field) is a partial dependency and must move out at 2NF.
Third normal form3NF: The table is in 2NF and no non-key field depends on another non-key field (no transitive dependencies).

Exam technique

Tip:Show the final tables with primary keys underlined (or labelled) and foreign keys identified.
Mistake:Splitting tables without keeping a foreign key to link them back together.

Example:In ORDER(OrderID, ProductID, ProductName, Quantity, CustomerID, CustomerName) the primary key is OrderID + ProductID. ProductName depends only on ProductID, and CustomerID and CustomerName depend only on OrderID (partial dependencies), so for 2NF they move to PRODUCT(ProductID, ProductName) and ORDER(OrderID, CustomerID, CustomerName), leaving ORDER_LINE(OrderID, ProductID, Quantity). In the new ORDER table, CustomerName depends on CustomerID, a non-key field (a transitive dependency), so for 3NF it moves to CUSTOMER(CustomerID, CustomerName) and ORDER keeps CustomerID as a foreign key.

← Previous noteMethods of capturing, selecting, managing and exchanging data1.3.2 Databases Next note →SQL: interpret and modify1.3.2 Databases

Back to Databases