/en-s6-ict-database-design-er-normalisation/
/en-s6-ict-database-design-er-normalisation/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

DSE ICT | Database Design Prep Guide for Tutors | ER Diagrams, Keys and 1NF to 3NF

DSE ICT | Database Design Prep Guide for Tutors | ER Diagrams, Keys and 1NF to 3NF

  • 2026-11-17

Students can draw an ER diagram yet guess the normal form, and they leave the foreign key in the wrong table. This lesson turns design into three checkable moves: fix the entities and cardinality, place the keys, then split repeated data away with four steps.

Objectives — Students can: (1) find entities, attributes and relationships from a short description and label each as one-to-one, one-to-many or many-to-many; (2) choose a primary key for every table and state that the foreign key sits on the many side; (3) push a table to 3NF with four steps and name the repetition each split removes; (4) compute the cost of repeated data (4,800 enrolment rows hold 460,800 bytes, of which 57,600 bytes are repeated names, about 12.5%).

01 Prerequisites (3-minute check)

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

Skill Quick check Pass mark
Tables and fields What makes a table a table Field names, data types, one record per row
Primary key idea Why a student ID works and a name does not The ID is unique and never changes
Basic arithmetic What is 4,800 times 96 460,800
Reading questions Students may take many subjects, a subject has many students: which relationship Many-to-many, so a junction table is required

02 Lesson flow (60 minutes)

Time Stage Tutor does Student does
0-6 min Opening probe Give an 80-word description and ask how many entities exist Write a number, revisit at the end
6-18 min Cardinality Draw the same description three ways, one per relationship type Draw each and mark both ends
18-30 min Key placement Show that the foreign key lives on the many side, then fix a wrong diagram Label PK and FK on their own diagram
30-44 min Four steps to 3NF Split one wide table three times, once per normal form After each split, name the repetition removed
44-54 min Cost arithmetic Use 4,800 records to compute storage and duplication share Compute once and compare
54-60 min Exit check Collect and mark three items; reteach the failed act next lesson Work independently

03 Teaching script (read it out)

Scene 1: Find entities first, then ask how many of one belong to the other

Act one does two things only: circle the entities and write the cardinality. An entity is something that will have many records and carries its own attributes, such as student, subject and enrolment. An enrolment is a relationship rather than an entity, and a mark that belongs to one enrolment is an attribute of that relationship.Decide cardinality with one question: how many subjects can one student take? Many. How many students can one subject have? Many. Both sides many means many-to-many, so a junction table is required. If only one side is many, write one-to-many and put the foreign key on the many side.Give one checking move: after drawing, read every relationship aloud as one A can have how many Bs. If the answer is more than one, star that end. Students draw fast and read rarely, and the reading is what turns a guess into a check.Handle the usual misconception on the spot: many-to-many squeezed into one-to-many, so one table ends up with subject one, subject two and subject three columns. One question breaks it: if the school adds a fifth subject, how many columns do you add? If they can answer, the design is wrong.
Students do: circle three entities and write the cardinality.
Students say: why many-to-many needs a junction table.

Students repeat: find entities first, then ask how many of one belong to the other.

Scene 2: Keys: primary key means unique, foreign key means which side

Act two covers keys. A primary key must be unique, never empty and never changed. A name fails all three because names repeat, can be blank and can change; a student ID passes all three.There can be several candidate keys, so pick one as the PK and mark the rest UNIQUE. Use a composite primary key only when two columns together are required for uniqueness, as in enrolment (student ID, subject code).Foreign key placement causes the most errors, and one rule is enough: the foreign key sits on the many side and points at the primary key of the one side. The enrolment table holds 4,800 rows, each with one student ID and one subject code, so both foreign keys live there; the student table holds only the student ID and the subject table only the subject code.Run the reverse check: if the student table carried a subject code, a student taking four subjects would need four rows and the name would repeat four times. That repetition is what act three removes.
Students do: label PK and FK on all three tables.
Students say: why the subject code does not belong in the student table.

Students say: the foreign key sits on the many side and points at the one side.

Scene 3: Four steps: 1NF removes multi-values, 2NF removes partial dependency, 3NF removes transitive dependency

Act three is the core, and every step must be named by the repetition it removes, using one starting table throughout.Step one, 1NF: one value per cell. A phone column holding 9123 4567, 9876 5432 breaks the rule, so split it into rows or move phones to their own table. Test: a comma inside a cell means it is not in 1NF.Step two, 2NF: with a composite key (student ID plus subject code), no column may depend on only half of it. The student name depends on the ID alone, so it moves to the student table; the subject name depends on the code alone, so it moves to the subject table. Test: which single column changes and drags this one with it? If one column is enough, move it out.Step three, 3NF: no column may depend on another non-key column. The department name depends on the department code, which is a transitive dependency, so it moves to a department table. Test: A determines B and B determines C while C is not a key, so split.Step four, verify with arithmetic. The starting table has 4,800 rows at 96 bytes each, 460,800 bytes or 450 KiB; repeated names alone account for 57,600 bytes, about 12.5%. After splitting into 1,200 student rows, 4 subject rows and 4,800 enrolment rows you hold 6,004 rows: more rows, shorter ones, and one update instead of 4,800.
Students do: split the table once and name each removed repetition.
Students say: how the 12.5% is calculated.

Students repeat: first multi-values, second partial dependency, third transitive dependency.

04 Question types and worked steps

Type Example Steps Answer
ER diagram A library: readers borrow books, a book can be borrowed many times Circle entities, write cardinality, add a junction table for many-to-many Reader and book are many-to-many; the loan table holds borrowed and returned dates
Key question Enrolment (student ID, subject code, mark) Name the composite key, then the two foreign keys and their targets PK is student ID plus subject code; student ID points at student, subject code at subject
Normal form A table with department code and department head Run the three test sentences in order In 2NF but not 3NF, because the head depends on the department code transitively
Splitting A phone cell holds two numbers Reach 1NF first, then check each level Create phone (student ID, phone) and drop the phone column
Sanity check (teach this): Sanity checks: every table must have a primary key, and a table without one is unfinished; a foreign key data type must match the key it points at, so a text student ID cannot become numeric in the foreign key; a many-to-many relationship without a junction table always produces multi-valued columns; a comma inside a cell breaks 1NF; 2NF only matters under a composite key, so a single-column key table cannot violate 2NF and such an answer must be corrected at once; 3NF checks always start from dependencies between non-key columns; storage units must match, since 460,800 bytes is 450 KiB rather than 450 KB when dividing by 1,024; the duplication share must use the same denominator of 460,800 rather than the post-split total.

05 Independent practice (3 questions)

Question Answer What to watch
Cardinality One tutor teaches many students, each student has one tutor: draw the ER diagram Check that they write one-to-many and put the foreign key in the student table
Normal form Table (student ID, subject code, subject name, mark): which normal form Check that they spot the subject name depending on the subject code alone
Split and compute Repeated names take 57,600 bytes of 460,800: what percentage Check that they divide by 460,800 and answer 12.5%

06 Common mistakes and what to say

Mistake Student writes Say this
Many-to-many drawn as one-to-many Subject one, subject two and subject three become three columns If the school adds a fifth subject, how many columns do you add? If you can add them, the structure is wrong.
Foreign key on the wrong side The subject code sits in the student table A student taking four subjects repeats the name how many times? That repetition is where duplicate data comes from.
Reciting normal forms Answering that it is not 3NF because data repeats I need to hear which column determines which; without a dependency you are guessing.
Unit error Calling 460,800 bytes 450 KB Are you dividing by 1,024 or by 1,000? State the base, because the answers differ by 2.4 percent.

07 Homework (paper, section and question numbers)

08 Exit check (last 5 minutes)

Question Answer Decision
Many-to-many Add a junction table holding both keys An answer of one-to-many means act one is retaught
Foreign key On the many side, pointing at the one side Reversed answers mean act two is retaught
3NF test No non-key column determines another Saying only that data repeats means act three is retaught

09 Tutor FAQ

Must students memorise the 1NF, 2NF and 3NF definitions

They should, but start from three test sentences: one value per cell, no dependency on half a key, no dependency on another non-key column. The definitions then follow.

Does the syllabus include BCNF

The DSE syllabus stops at 3NF; BCNF only needs its name and the fact that it handles tables with several candidate keys, so do not spend lesson time on derivations.

What if students are weak at arithmetic

Fix three steps: rows times bytes per row, divide by 1,024 for KiB, then repeated bytes over total bytes times 100, and drill the same numbers three times.

Find a tutor — freeTutor 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.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →