Revision notes for AQA A Level Computer Science Database design and normalisation techniques. Open the guide for explanations and worked examples. Written against the AQA A Level Computer Science specification, so the content matches what's examinable rather than general Computer Science background.

Database design and normalisation techniques

What you'll learn

  • How to describe database tables using relations, attributes, tuples and keys.
  • How functional dependencies tell you which attributes belong together.
  • How to normalise relations into first, second and third normal form.
  • Why normalisation reduces duplicated data and prevents data anomalies.

The building blocks: relations and keys

A relational database stores data in tables. In database theory, a table is called a relation.

Definition

Relation, tuple and attribute

A relation is a table. A tuple is one row in the table. An attribute is one column in the table.

For example, Customer(CustomerID, CustomerName, CustomerEmail) is a relation schema: it describes the structure of the Customer table.

A primary key is an attribute, or combination of attributes, that uniquely identifies each tuple in a relation. In these notes, primary key attributes are shown in bold.

Examples:

  • Customer(<strong>CustomerID</strong>, CustomerName, CustomerEmail)
  • Product(<strong>ProductID</strong>, ProductName, UnitPrice)
Definition

Composite key and foreign key

A composite key is a primary key made from more than one attribute, such as OrderLine(<strong>OrderID</strong>, <strong>ProductID</strong>, Quantity).

A foreign key is an attribute in one relation that refers to the primary key of another relation, creating a link between the two tables.

For example:

  • Order(<strong>OrderID</strong>, OrderDate, CustomerID FK)
  • Customer(<strong>CustomerID</strong>, CustomerName, CustomerEmail)

Here, CustomerID FK in Order links each order to one customer.

Functional dependencies

Before you can normalise well, you need to know which facts determine other facts.

Definition

Functional dependency

A functional dependency exists when the value of one attribute, or set of attributes, determines the value of another attribute.

It is often written as X→YX \to YX→Y, meaning “if you know XXX, you can determine YYY”.

For example, if each product has exactly one price, then:

ProductID→UnitPrice\text{ProductID} \to \text{UnitPrice}ProductID→UnitPrice

The left-hand side is called the determinant.

Example

Finding keys and dependencies

Suppose you are given this relation:

OrderLine(<strong>OrderID</strong>, <strong>ProductID</strong>, OrderDate, ProductName, UnitPrice, Quantity)

Each order can contain many products, and each product can appear in many orders.

  1. OrderID alone is not enough to identify a row, because one order can contain several products.
  2. ProductID alone is not enough either, because the same product can appear in many orders.
  3. The pair OrderID + ProductID identifies one order line, so the primary key is composite.
  4. Since one order has one date, OrderID→OrderDate\text{OrderID} \to \text{OrderDate}OrderID→OrderDate.
  5. Since one product has one name and one unit price, ProductID→ProductName,UnitPrice\text{ProductID} \to \text{ProductName}, \text{UnitPrice}ProductID→ProductName,UnitPrice.
  6. Since the quantity depends on the product within a particular order, OrderID,ProductID→Quantity\text{OrderID}, \text{ProductID} \to \text{Quantity}OrderID,ProductID→Quantity.

What normalisation means

Normalisation is the process of organising relations to reduce duplicated data and improve data integrity.

Usually, this means splitting a large relation into smaller relations, then linking them with primary keys and foreign keys.

Key Idea

The aim of normalisation

Each fact should be stored in one sensible place. Other tables should refer to that fact using a key, rather than copying the fact again and again.

This diagram shows the general idea: start with a wide relation containing repeated dependencies, then split it into relations that are in third normal form.

Normalisation from one wide order relation into Customer, Order, Product and OrderLine relations

First normal form: 1NF

Definition

First normal form

A relation is in first normal form (1NF) when every attribute contains only atomic values: each field holds a single value, not a list or repeating group.

A repeating group is a repeated set of similar attributes, such as Product1, Product2, Product3, or a single field containing several product names.

Example

Removing repeating groups

A badly designed order relation stores several products in one row:

Order_UNF(<strong>OrderID</strong>, CustomerName, Product1, Product2, Product3)

  1. Product1, Product2 and Product3 are a repeating group because they store the same kind of fact multiple times.
  2. Replace the repeated columns with multiple rows, one row per product in the order.
  3. The new relation could be OrderLine_1NF(<strong>OrderID</strong>, <strong>ProductID</strong>, CustomerName, ProductName, Quantity).
  4. The primary key is now likely to be OrderID + ProductID, because the same order can appear on several rows.
Common Mistake

Confusing 1NF with uniqueness

1NF is mainly about atomic values and no repeating groups. A primary key is still needed for a well-designed relation, but “having a key” is not the whole meaning of 1NF.

Second normal form: 2NF

Second normal form only becomes interesting when the primary key is composite.

Definition

Second normal form

A relation is in second normal form (2NF) when it is already in 1NF and every non-key attribute depends on the whole primary key, not just part of it.

A non-key attribute is an attribute that is not part of the primary key.

A partial dependency happens when a non-key attribute depends on only part of a composite key.

Example

Removing partial dependencies

Consider:

OrderLine_1NF(<strong>OrderID</strong>, <strong>ProductID</strong>, OrderDate, ProductName, UnitPrice, Quantity)

  1. The primary key is OrderID + ProductID, so each non-key attribute should depend on both parts together.

  2. OrderDate depends only on OrderID, not on ProductID.

  3. ProductName and UnitPrice depend only on ProductID, not on OrderID.

  4. Move those partial dependencies into separate relations:

    • Order(<strong>OrderID</strong>, OrderDate)
    • Product(<strong>ProductID</strong>, ProductName, UnitPrice)
    • OrderLine(<strong>OrderID FK</strong>, <strong>ProductID FK</strong>, Quantity)

  5. Quantity stays in OrderLine because it depends on the combination of order and product.

Tip

Single-attribute keys

If a relation is in 1NF and its primary key is a single attribute, it cannot have a partial dependency on part of a composite key. So it automatically satisfies the “no partial dependency” part of 2NF.

Third normal form: 3NF

Third normal form removes dependencies between non-key attributes.

Definition

Third normal form

A relation is in third normal form (3NF) when it is already in 2NF and no non-key attribute depends on another non-key attribute.

In other words, there must be no transitive dependency.

A transitive dependency is an indirect dependency. For example:

OrderID→CustomerID\text{OrderID} \to \text{CustomerID}OrderID→CustomerID

and

CustomerID→CustomerName\text{CustomerID} \to \text{CustomerName}CustomerID→CustomerName

So CustomerName depends on OrderID indirectly through CustomerID.

Example

Removing a transitive dependency

Consider:

Order_2NF(<strong>OrderID</strong>, OrderDate, CustomerID, CustomerName, CustomerEmail)

  1. OrderID determines CustomerID, because each order belongs to one customer.

  2. CustomerID determines CustomerName and CustomerEmail, because those are facts about the customer.

  3. CustomerName and CustomerEmail are not really facts about the order, so they should not be stored in Order.

  4. Split the relation into:

    • Order(<strong>OrderID</strong>, OrderDate, CustomerID FK)
    • Customer(<strong>CustomerID</strong>, CustomerName, CustomerEmail)

  5. The foreign key CustomerID FK preserves the relationship, so the customer details can still be found when needed.

Properties of a relation in 3NF

A relation in third normal form has these properties:

  • It is in 1NF: no repeating groups, and each attribute value is atomic.
  • It is in 2NF: no non-key attribute depends on only part of a composite key.
  • It has no transitive dependencies: non-key attributes do not depend on other non-key attributes.
  • Non-key attributes describe the key, not another non-key attribute.
  • Links to other relations are represented using foreign keys.
Key Idea

The 3NF memory phrase

For 3NF, every non-key attribute should depend on the key, the whole key, and nothing but the key.

Why databases are normalised

Databases are normalised to reduce data redundancy, meaning unnecessary duplication of the same fact.

This helps prevent data anomalies: problems caused by inserting, updating or deleting data in a badly structured database.

Common anomalies include:

  • Update anomaly: the same fact is stored in several places, so one copy might be updated while another is missed.
  • Insertion anomaly: you cannot store one fact unless you also store an unrelated fact.
  • Deletion anomaly: deleting one row accidentally removes the only copy of another important fact.
Example

Avoiding an update anomaly

Suppose customer email addresses are copied into every order row.

  1. If one customer has six orders, their email address is stored six times.
  2. If the customer changes email address, all six rows must be updated.
  3. If one row is missed, the database now contains inconsistent customer data.
  4. In a normalised design, the email is stored once in Customer(<strong>CustomerID</strong>, CustomerEmail), and orders store only CustomerID FK.

Normalisation also makes maintenance easier. When each relation represents one clear type of thing, such as customers, products or orders, it is easier to enforce rules and spot design errors.

Common Mistake

Thinking normalisation always makes queries faster

Normalisation mainly improves consistency and integrity. It can sometimes require extra joins, so it does not automatically make every query faster.

A practical normalisation checklist

When normalising a relation to 3NF:

  1. Identify the primary key, including whether it is composite.
  2. List the functional dependencies.
  3. Put the relation into 1NF by removing repeating groups.
  4. Put it into 2NF by removing partial dependencies.
  5. Put it into 3NF by removing transitive dependencies.
  6. Add foreign keys so the split relations can still be linked.
Exam technique

In the exam

  1. State the normal form rule you are applying before splitting the table.
  2. When decomposing a relation, show the new relations clearly and mark primary keys and foreign keys.
  3. For “why normalise?” questions, mention reduced redundancy, improved consistency, and avoidance of update, insertion or deletion anomalies.
Self review

Check yourself

  • What is the difference between a partial dependency and a transitive dependency?
  • Why does a composite primary key matter when checking for 2NF?
  • In a 3NF design, where should customer email be stored if many orders belong to one customer?

Database design and normalisation techniques Revision Guide