DSE ICT | Relational Databases and SQL Lesson-Prep Guide (Tutor Edition)

DSE ICT | Relational Databases and SQL Lesson-Prep Guide (Tutor Edition) | Keys, Normalisation and Query Cost

2026-11-02

Tutor lesson-prep guides · Secondary ICT

Students can copy SELECT * FROM Student, but not say why tables are split or why an index exists. This lesson turns database design into a decision that can be marked.

Objectives — Students can: (1) explain how two tables join through a primary and a foreign key, and check for orphan records with 120 of 1,200 students (10.0%) unenrolled; (2) split one messy enrolment table into three tables up to 3NF and name the update, insert and delete anomalies that disappear; (3) compare a 1,200-comparison table scan with an 11-comparison index lookup, about 109 times faster, and state what the index costs on writes and storage.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Table	A table has columns and records	Each row is one record, each column holds one kind of data
Logarithms	Two to what power is near 1,200	About 10.2, so 11 comparisons when rounded up
Percentages	120 out of 1,200	10%

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0-10 min	One messy table	Student name, course and teacher all in one table	Point out three repeat points
10-22 min	Keys	Unique identifier versus a pointer to another table	Compute 120 unenrolled students
22-34 min	Normalisation	1NF, 2NF and 3NF step by step	Split into three tables
34-44 min	SQL queries	WHERE, GROUP BY, HAVING, JOIN	Write three queries
44-54 min	Indexes and cost	Table scan versus B-tree lookup	Compute 109 times faster
54-60 min	Exit check	Three questions marked on the spot	All three right

03 Teaching script (read it out)

Scene 1: Two tables join on one key

The Students table has 1,200 rows and Enrolments has 4,860 rows, so each student takes 4,860 / 1,200 = 4.05 courses on average.
A primary key identifies one row uniquely, such as student_id. A foreign key is a column in another table that points back to that key, such as Enrolments.student_id.
The foreign key enforces referential integrity: every student_id in Enrolments must exist in Students, otherwise the row is an orphan. The check is mechanical: SELECT COUNT(DISTINCT student_id) FROM Enrolments gives 1,080, and 1,200 – 1,080 = 120 students unenrolled, or 10.0%.
Teach delete rules at the same time: ON DELETE CASCADE removes enrolments with the student, ON DELETE RESTRICT blocks the delete. Answers must name the choice and the reason, not just the words foreign key.
A composite key of student_id plus course_id is also examinable: it stops the same student enrolling twice in one course.

Ask students: why student_id may repeat in Enrolments but not in Students (one is a foreign key, the other a primary key).

Scene 2: Splitting tables prevents three anomalies

The messy table holds student_id, student_name, course_id, course_name and teacher_name together. Three problems follow: an update anomaly (renaming a course means editing 4,860 rows, and one missed row leaves inconsistent data), an insert anomaly (a new course with no students cannot be recorded), and a delete anomaly (deleting the last student wipes the course record).
After splitting into Students (student_id, name), Courses (course_id, course_name, teacher) and Enrolments (student_id, course_id, grade): 1NF gives one value per cell; 2NF makes every non-key column depend on the whole composite key, so grade stays in Enrolments while course_name moves to Courses; 3NF removes the transitive dependency, so teacher, which depends on course_id, stays in Courses.
The course data drops from 4,860 repeats to about 40 rows (4,860 / 121.5), and one edit keeps everything consistent.
The cost is that queries need JOINs: this is consistency traded for query complexity.

Ask students: why splitting tables is still worth it even though queries become slower (consistency and no anomalies for a little JOIN work).

Scene 3: An index trades writes for reads

Without an index, finding one student_id means comparing row by row, at worst 1,200 comparisons. A B-tree index halves the search each step, so comparisons are about log2 of 1,200 = 10.2, rounded up to 11, which is 1,200 / 11 = 109 times faster.
An index is not free: it takes space, and every INSERT, UPDATE or DELETE must maintain it, so write-heavy tables should not carry many.
Worth adding: columns that appear often in WHERE, JOIN or ORDER BY. Not worth adding: tables with a few dozen rows, or columns with very low selectivity such as gender with two values.
Write SQL in order: SELECT the columns, FROM the tables, WHERE filters rows, GROUP BY groups, HAVING filters groups, ORDER BY sorts. The WHERE versus HAVING difference is examined almost every year: WHERE filters rows before grouping, HAVING filters groups after.

Ask students: why an index on a gender column helps very little (low selectivity still reads many rows).

04 Question types and worked steps

Type	Example	Steps	Answer
Design	Draw three tables and keys from a requirement	Find entities, set primary keys, add foreign keys, check anomalies	Three tables and two foreign keys
SQL	Write a query	SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING	Complete syntax
Calculation	Index comparisons	ceil(log2(rows))	11 comparisons
Explanation	Which anomaly normalisation removes	Walk 1NF, 2NF and 3NF	One example per level

Sanity check (teach this): A primary key is unique and never null, while a foreign key may repeat.
Index comparisons follow log2, not half the row count added up.
1,200 / 11 = 109.1, which is a multiple, not a percentage.
HAVING cannot come before GROUP BY.
A JOIN without a condition becomes a Cartesian product of 1,200 x 4,860 rows.

05 Independent practice (3 questions)

Question	Answer	What to watch
Average courses for 1,200 students and 4,860 enrolments	4.05 courses	4,860 / 1,200
Students unenrolled when only 1,080 appear	120 (10.0%)	1,200 – 1,080
Worst-case index comparisons on 1,200 rows	11	ceil(log2 1,200)

06 Common mistakes and what to say

Mistake	Student writes	Say this
Treating a foreign key as a primary key	Answer: student_id is the primary key of Enrolments	The key of Enrolments is student_id plus course_id; a single student_id is a foreign key.
Defining 3NF as no repetition	Answer: 3NF just means no duplicate data	Name the anomaly removed: update, insert or delete, with one example each.
Assuming every column needs an index	Answer: index every column and it is fastest	Writes must maintain indexes, and low-selectivity columns gain little.
A JOIN with no condition	Answer: SELECT * FROM A, B	A missing ON clause produces 1,200 times 4,860 rows and loses marks.

07 Homework (paper, section and question numbers)

Secondary ICT chapter index — Database unit, questions 4 to 9
Secondary lesson-prep overview — Compare other subjects
ICT practice papers — Paper 2 database section

08 Exit check (last 5 minutes)

Question	Answer	Decision
Unenrolled students and share	120 / 10.0%	All three right means move to normalisation
Index comparisons	11	One wrong means reteach logarithms
WHERE versus HAVING	Before versus after grouping	Two wrong means return to act three

09 Tutor FAQ

Must students memorise all of SQL?

No. SELECT, WHERE, GROUP BY, HAVING, JOIN and ORDER BY cover most questions.

Must every design reach 3NF?

In exams yes. Real systems sometimes denormalise for speed, and the cost must be stated.

How does an index relate to a primary key?

A primary key usually comes with an index, but extra indexes are separate decisions driven by query patterns.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.