Normalisation: Concepts & Rules

CAPE IT · UNIT 2 · MODULE 1 · OBJ. 9, 10 & 11
Step 1 of 6

Watch the explainer video

6 min · narrated walkthrough: one tuck-shop table normalised to 1NF, 2NF and 3NF · captions available

Class activity: Scissors Normalisation — printable student sheets for a 40-minute group lesson.

EXPLORE IN 3D

Normalisation in 3D

Step through unnormalised, 1NF, 2NF and 3NF and watch the table split into related tables.

REVISION · PAST-PAPER PRACTICE

Test yourself

Multiple-choice items in Paper 1 style and structured questions in Paper 2 style, adapted from CAPE Information Technology Unit 2 past papers. Your score and answers are saved on this device. When you have finished, submit them to your teacher at the end of this section.

MCQ best –Structured –

Paper 1 practice · 8 multiple-choice items

Choose one answer for each item, then check. Aim for 80% or better.

Adapted from CAPE U2 P1 2020 Item 3; 2022 Item 4
1. A table is in first normal form (1NF) when
Answer: B. 1NF removes repeating groups so every cell holds a single value. Partial dependencies are removed at 2NF; transitive ones at 3NF.
Adapted from CAPE U2 P1 2019 Item 13; 2020 Item 11
2. Normalising a database does NOT directly affect its
Answer: C. Normalisation reduces redundancy, which improves consistency, integrity and update performance. Security is handled by access controls, not normal forms.
Adapted from CAPE U2 P1 2024 Item 15
3. The purpose of normalisation is to produce a set of relations
Answer: D. Normalisation organises data to remove redundancy and so prevent insertion, update and deletion anomalies.
Adapted from CAPE U2 P1 2022 Item 12
4. A functional dependency is a relationship between
Answer: A. A → B means the value of attribute A determines the value of attribute B, e.g. StudentID → StudentName.
EduTech practice item · Module 1 Obj. 10
5. ENROL (StudentID, CourseID, StudentName, Grade). StudentName depends only on StudentID. This table violates
Answer: B. A non-key field depending on only PART of a composite key is a partial dependency, which breaks 2NF.
EduTech practice item · Module 1 Obj. 10
6. STAFF (StaffID, Name, DeptCode, DeptName). DeptName depends on DeptCode, not directly on StaffID. This is a
Answer: C. StaffID → DeptCode → DeptName is a transitive dependency, a 3NF violation. The fix is a separate DEPARTMENT table.
Based on CAPE U2 P2 2024 Q2(a)(ii)–(iii)
7. In an unnormalised ticket table, a customer moves his order from one event to another. The event date and price must be changed in several rows. This is
Answer: D. Having to change the same fact in several places is an update anomaly. Miss one row and the data contradicts itself.
Based on CAPE U2 P2 2021 Q2(b)(i)
8. Which change converts STUDENT (StudID, Name, Subject1, Subject2, Subject3) towards 1NF?
Answer: A. Subject1–3 is a repeating group. Moving it to STUDENT_SUBJECT (StudID, Subject) gives one value per cell.

Paper 2 practice · structured questions (21 marks)

Write a full answer to each part, then show the mark scheme and tick every point you earned. Marks for a part are capped at its maximum. Mark schemes are EduTech’s own; CXC does not publish them.

Based on CAPE U2 P2 May/June 2021, Question 2(b)(i) and 2026, Question 2(a)

Question 1 [13 marks]

A youth club stores members and their activities like this:
MemIDMemNameParishActivity1Activity2Activity3
M01Tia GrantSt AndrewChessDrama
M02Omar AliSt JamesChess
M03Keisha RoySt AndrewDramaSteelpanChess
Each activity has an ActivityCode and a Coach, and every member–activity pairing records a JoinDate.

(a) Identify the normal-form violation in the table and state how it occurred. [2]

MARK SCHEME (a) · max 2

(b) Convert the data to first normal form. Show the table(s) with the primary key underlined. [3]

MARK SCHEME (b) · max 3

(c) Normalise to second normal form (2NF). Show all resulting tables with primary and foreign keys. [6]

MARK SCHEME (c) · max 6

(d) State whether your 2NF tables need further normalisation to reach 3NF. Justify your answer. [2]

MARK SCHEME (d) · max 2
Based on CAPE U2 P2 May/June 2024, Question 2(a) and 2022, Question 1(b)

Question 2 [8 marks]

A ticket agency records orders like this:
OrderIDCustNameEventEventDateTicketTypeQtyTotal
501Ria DasSoca Fest12 FebVIP2$800
502Leon HartJazz Night20 FebGeneral4$400
503Ria DasJazz Night20 FebVIP1$250

(a) Outline the purpose of normalisation. [2]

MARK SCHEME (a) · max 2

(b) Name TWO anomalies other than an insertion anomaly. [2]

MARK SCHEME (b) · max 2

(c) Leon moves his order from Jazz Night to Soca Fest; the quantity stays the same. Apart from Event, identify TWO fields that must also change. [2]

MARK SCHEME (c) · max 2

(d) Identify TWO features of the table that justify normalising it. [2]

MARK SCHEME (d) · max 2

Submit to your teacher

Send your multiple-choice choices and written answers to your teacher for marking. Your teacher will also see whether you opened the mark scheme or checked your answers before submitting. You can submit again if you improve your answers.

Items are adapted from CAPE® Information Technology Unit 2 Papers 01 and 02 (2019–2026) for practice. © Caribbean Examinations Council for the original papers.