How do I tell 2NF from 3NF quickly?

2NF is about partial dependency on part of a key; 3NF is about a transitive dependency between non-key columns.

Why does an index slow writes down?

Every INSERT, UPDATE and DELETE must maintain each affected index, so more indexes mean higher maintenance cost.

DSE ICT | Relational Databases and SQL Practice Paper 3 (Integrity and Efficiency)

2026-11-04

Compute the rows affected by UPDATE and DELETE on a 1 000-row sales table, work up from 1NF to 3NF, then compare index and join costs.

Attempt first, mark afterwards　Allow about forty minutes; write the dependency for each normalisation item and count rows before judging which plan is faster.

01　Paper details (check answers afterwards)

Item	Detail
Questions	10 items
Suggested time	About 40 minutes
Difficulty mix	Foundation 4 · Intermediate 3 · Challenge 2 · Error redo 1
Chapter	Chapter: DSE ICT \| Relational Databases and SQL
Level	S5 (Secondary 5) ICT

02　Questions (10 items)

FoundationSales has 1 000 rows, 250 of them with cat = ‘A’. How many rows does `UPDATE Sales SET price = price * 1.1 WHERE cat = ‘A’;` affect, and do the others change?
FoundationOn the same 1 000-row Sales table, `DELETE FROM Sales WHERE year < 2020;` matches 180 rows. How many rows remain?
FoundationWhat does first normal form (1NF) require of each cell, and which rule does storing ‘9123 4567, 9876 5432’ in a phone column break?
FoundationHow do primary and foreign keys differ on repetition, and what usually happens when a referenced parent row is deleted?
IntermediateAn enrolment table is keyed on (student_id, course_id) while course_title depends on course_id alone. Which normal form does this break, and how should it be split?
IntermediateIn a teacher table keyed on teacher_id, dept_name depends on dept_code while dept_code depends on teacher_id. What kind of dependency is that, and which normal form is broken?
IntermediateHow is index selectivity computed? A column has 250 distinct values across 1 000 rows, so what is it, and is high or low selectivity better for indexing?
ChallengeA B-tree index node holds 100 keys with fan-out 100. What is the minimum height for 1 000 rows, and how many rows do one and two levels cover?
ChallengeTwo tables hold 1 000 and 500 rows. How many comparisons does a nested loop join need at most, and roughly how many with a hash join?
Error redoError drill: a student claims that more indexes always make queries faster, so every column should be indexed. What is correct?

03　Answers and one-line notes

#	Answer	One-line note
1	250 rows change; the other 750 keep their price	UPDATE touches only rows matched by WHERE, so unaffected rows stay identical and the count can be checked in advance with SELECT COUNT(*).
2	820 rows remain	Deleting rows never removes columns, so the remainder is 1 000 minus 180.
3	Each cell must hold a single atomic value; that column breaks 1NF because it is multi-valued	Two numbers in one cell cannot be filtered or indexed as a single value, so queries degrade into string matching.
4	A primary key cannot repeat while a foreign key can; the delete is refused (RESTRICT) or cascades (CASCADE / SET NULL) depending on the setting	That is referential integrity: no orphan row may point at a parent that does not exist.
5	It breaks 2NF through a partial dependency; move course_id and course_title into a separate Course table	2NF requires every non-key column to depend on the whole key, so anything decided by part of the key belongs in its own table.
6	A transitive dependency (teacher_id to dept_code to dept_name) breaking 3NF; the department belongs in its own table	3NF forbids a non-key column from being decided indirectly through another non-key column, otherwise one rename means updating many rows.
7	Selectivity is distinct values divided by rows, 250 over 1 000, which is 25 percent; high selectivity close to one suits an index best	Low selectivity means one value matches a large share of rows, so an index scan loses to a full table scan.
8	Two levels; one level covers 100 rows and two levels cover 10 000	One hundred to the first power is below 1 000, while the second power reaches 10 000, so the height is two.
9	At most 1 000 times 500, which is 500 000, versus roughly 1 000 plus 500, which is 1 500 for a hash join	A nested loop compares every row with every row, while a hash join builds and probes once, giving a linear sum.
10	No; indexes speed up reads but slow INSERT, UPDATE and DELETE, consume storage and may not be chosen by the optimiser, so index only selective columns used in filters or joins	Every write must maintain each affected index, so piling on indexes raises write cost and storage together.

04　What to do after marking

After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
Repeat the same item two days later; move to the next paper only when both attempts are correct.
Parents only need the answer and note columns; there is no need to explain every item.

Need a tutor for this chapter? Register here Tutor Registration →

Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.