Skip to main content
Northern IrelandSoftware Systems DevelopmentSyllabus dot point

How are entity relationship diagrams drawn, and how does normalisation to third normal form remove data anomalies?

Entity relationship modelling (entities, attributes, relationships and cardinality) and normalisation to first, second and third normal form.

A CCEA A-Level Software Systems Development answer on entity relationship modelling (entities, attributes, relationships and cardinality) and normalisation to first, second and third normal form to remove redundancy and anomalies.

Generated by Claude Opus 4.814 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. The answer
  3. Examples in context
  4. Try this

What this dot point is asking

CCEA expects you to draw and read entity relationship diagrams (ERDs) - entities, attributes, relationships and their cardinality (1:1, 1:M, M:N) - and to normalise a set of data to first, second and third normal form (3NF), stating the condition for each form and the anomalies normalisation removes. Drawing an ERD, resolving a many-to-many relationship, and normalising an unnormalised table to 3NF are favourite high-mark questions.

The answer

Entity relationship modelling

The three cardinalities:

  • One-to-one (1:1) - each A relates to at most one B and vice versa (a Person and a Passport).
  • One-to-many (1:M) - one A relates to many B, each B to one A (a Customer and Orders); the most common.
  • Many-to-many (M:N) - each A relates to many B and vice versa (Students and Courses); resolved into two one-to-many relationships using a link (junction) table holding two foreign keys.

Normalisation: why and how far

The three normal forms:

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

Worked example: normalising an orders table to 3NF

Examples in context

Example 1. A college enrolment ERD. Entities Student, Course and Tutor are modelled. Student to Course is many-to-many (a student takes many courses, a course has many students), resolved by an Enrolment link table with studentID and courseID foreign keys. Tutor to Course is one-to-many. The ERD agreed with staff drives the table design before any SQL is written.

Example 2. Normalisation fixes a duplicated supplier. A products spreadsheet repeats each supplier's name and phone on every product row. Normalising to 3NF moves supplier details (a transitive dependency on supplierID) into a Supplier table, so a supplier's phone number is stored once. Changing it is now a single update, and a new supplier can be added before they have any products, removing the anomalies.

Try this

Q1. Name the three types of relationship cardinality. [3 marks]

  • Cue. One-to-one (1:1), one-to-many (1:M) and many-to-many (M:N).

Q2. State the condition for a table to be in first normal form. [2 marks]

  • Cue. It has no repeating groups, every field holds a single (atomic) value, and each record has a primary key.

Q3. State what is removed when a table is taken from second normal form to third normal form. [1 mark]

  • Cue. Transitive dependencies (a non-key field depending on another non-key field).

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 20196 marksExplain what an entity relationship diagram shows, and describe the three types of relationship cardinality, giving an example of each.
Show worked answer →

An entity relationship diagram (ERD) shows the entities in a system (the things data is held about, such as Customer or Order), and the relationships between them. Entities are drawn as boxes and relationships as lines between them, labelled with the cardinality.

The three cardinalities:

One-to-one (1:1): each record in entity A relates to at most one in entity B and vice versa, for example each Person has one Passport and each Passport belongs to one Person.

One-to-many (1:M): one record in A relates to many in B, but each B relates to one A, for example one Customer places many Orders, but each Order belongs to one Customer. This is the most common.

Many-to-many (M:N): records in A relate to many in B and vice versa, for example a Student takes many Courses and a Course has many Students. A many-to-many is resolved into two one-to-many relationships using a link (junction) table.

Markers reward a correct description of an ERD (entities and relationships), the three cardinalities (1:1, 1:M, M:N) with a valid example of each, and ideally the point that M:N is resolved with a link table.

CCEA 20216 marksDescribe the purpose of normalisation, and outline the conditions a table must meet to be in first, second and third normal form.
Show worked answer →

Normalisation is the process of organising the fields and tables of a relational database to reduce data redundancy and remove update, insertion and deletion anomalies, producing well-structured tables.

First normal form (1NF): the table has no repeating groups and every field holds a single (atomic) value; each record is identified by a primary key.

Second normal form (2NF): the table is in 1NF and every non-key field depends on the whole primary key, not just part of it. This removes partial dependencies and applies where the primary key is composite (made of more than one field).

Third normal form (3NF): the table is in 2NF and has no non-key field depending on another non-key field (no transitive dependencies); every non-key field depends only on the primary key. Such non-key dependencies are moved to a separate table.

The result is data stored once in properly related tables, which is consistent and free of the anomalies.

Markers reward the purpose (reduce redundancy and remove anomalies) and a correct condition for each normal form: 1NF atomic values and no repeating groups, 2NF no partial dependency on a composite key, 3NF no transitive (non-key to non-key) dependency.

Related dot points

Sources & how we know this