Skip to main content
Northern IrelandDigital TechnologySyllabus dot point

What makes a database relational, and how is it modelled and normalised?

The features of a relational database (tables, primary and foreign keys, relationships), entity relationship modelling, and normalisation to third normal form.

A CCEA A-Level Digital Technology answer on relational databases: tables, primary and foreign keys and relationships, entity relationship modelling, and normalisation to third normal form to remove redundancy.

Generated by Claude Opus 4.812 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this dot point is asking
  2. Features of a relational database
  3. Entity relationship modelling
  4. Normalisation
  5. Why normalisation matters
  6. Try this

What this dot point is asking

CCEA wants you to describe the features of a relational database (tables, primary and foreign keys, relationships), to model data with entity relationship diagrams, and to normalise data to third normal form. This is the core data-handling theory of A2 1.

Features of a relational database

The relational model avoids storing the same data in many places. Instead of repeating a customer's full details on every order, the orders table holds a foreign key (the customer's ID) that links to a single record in the customers table. This is why relationships and keys are central: they let data be stored once and referenced wherever needed.

Entity relationship modelling

ER modelling comes before the tables are built: it captures what entities exist and how they relate, which then guides the table design and the keys that link them.

Normalisation

The normal forms are applied in order:

  • First normal form (1NF). No repeating groups; every field holds a single (atomic) value, and each record has a primary key.
  • Second normal form (2NF). In 1NF, and every non-key attribute depends on the whole primary key, not just part of it (removes partial dependencies; relevant when the key is composite).
  • Third normal form (3NF). In 2NF, and there are no transitive (non-key) dependencies: no non-key attribute depends on another non-key attribute.

Why normalisation matters

Redundant data causes anomalies: update a customer's address in one row but not another and the data becomes inconsistent; you may be unable to add a product until an order exists (insertion anomaly) or lose data when deleting (deletion anomaly). Normalising to 3NF stores each fact once, so updates are made in one place and the data stays consistent.

Try this

Q1. State what a primary key does. [1 mark]

  • Cue. It uniquely identifies each record in a table.

Q2. Name the type of relationship between Customers and Orders if one customer can place many orders. [1 mark]

  • Cue. A one-to-many relationship.

Q3. Explain one anomaly that normalisation to 3NF helps to avoid. [2 marks]

  • Cue. For example an update anomaly: if a customer's address were stored in many rows, updating one and not the others would make the data inconsistent; storing it once removes the risk.

Exam-style practice questions

Practice questions written in the style of CCEA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

CCEA A2 14 marksExplain the terms primary key and foreign key, and describe how they create a relationship between two tables.
Show worked answer →

Define both keys, then describe the link.

A primary key is an attribute (or combination of attributes) that uniquely identifies each record in a table, for example a StudentID in a Students table. A foreign key is an attribute in one table that refers to the primary key of another table.

A relationship is created when the foreign key in one table holds the same values as the primary key in another. For example, a Bookings table has a foreign key StudentID that matches the primary key StudentID in the Students table, linking each booking to the student who made it. This lets data be stored once and referenced wherever needed.

Markers reward the definitions of both keys (unique identifier; reference to another table's primary key) and the description of how matching values create the link. Saying a foreign key is "just another key" without the reference point loses marks.

CCEA A2 16 marksExplain what is meant by normalisation and describe the conditions for first, second and third normal form.
Show worked answer →

Define normalisation, then give the rule for each normal form in order.

Normalisation is the process of organising data into tables to remove redundancy and avoid update, insertion and deletion anomalies.

First normal form (1NF): there are no repeating groups; each field holds a single (atomic) value and each record is identified by a primary key. Second normal form (2NF): the table is in 1NF and every non-key attribute depends on the whole primary key, not just part of it (this removes partial dependencies, relevant where the key is composite). Third normal form (3NF): the table is in 2NF and there are no non-key (transitive) dependencies, meaning no non-key attribute depends on another non-key attribute.

Markers award marks for the definition and for each normal form's condition stated correctly and in order. The common error is muddling 2NF (partial dependency) with 3NF (transitive dependency).

Related dot points

Sources & how we know this