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.
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
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
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
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 |
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)
- Secondary ICT chapter index — S6 database series for this chapter
- Prep guide master index — Matching chapters in other subjects
- Redo the chapter practice — Independent practice items 1 to 3: cardinality, normal form, split and compute
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.



