/en-tutor-prep-s6-ict-database-design-er-normalisation-practice-3/
/en-tutor-prep-s6-ict-database-design-er-normalisation-practice-3/
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

S6 ICT: decomposing to 3NF and anomaly analysis

S6 ICT: decomposing to 3NF and anomaly analysis

  • 2026-11-20

S6 ICT: decomposing to 3NF and anomaly analysis

From one flat table to 4 tables, checking column slots, candidate keys and the three anomalies.

Challenge: decomposition, key counts and anomalies This paper starts from a six column flat table and splits it into Student, Course, Teacher and Enrol, taking column slots from 6 to 9 at a ratio of 1.5 and needing 3 joins to rebuild. Key counting uses R(A, B, C, D) with A determining B and CD determining A, giving one candidate key and 4 superkeys. The scale case compares 12000 courses with 500 teachers, so a phone change drops from 12000 rows to 500, a factor of 24. The paper closes on insertion, deletion and update anomalies and the trade off of denormalisation.

01 Paper details (check answers afterwards)

Item Detail
Questions 10
Suggested time 25
Difficulty mix Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1
Chapter Chapter: S6 ICT: Database Design and the ER Model
Level Challenge (10 items)

02 Questions (10 items)

  1. FoundationHow many tables are needed to bring FLAT(StudentID, StudentName, CourseID, CourseTitle, TeacherID, TeacherName) to 3NF?
  2. FoundationFor the same decomposition, how many column slots do the 4 tables hold?
  3. FoundationR(A, B, C, D) has functional dependencies A determines B and CD determines A. How many candidate keys and superkeys exist?
  4. IntermediateAn institution has 12000 courses and 500 teachers. Changing one teacher phone number touches how many rows before and after normalisation, and by what factor?
  5. IntermediateThe flat table has 22 rows and repeats a 12 character student name, a 20 character course title and a 15 character teacher name, 1034 characters in all. Normalised storage needs only 411 characters. What percentage is saved?
  6. IntermediateBCNF is stricter than 3NF. What extra condition does it impose?
  7. ChallengeIn the unnormalised design, a new teacher with no assigned course cannot be recorded. Which anomaly is this?
  8. ChallengeDeleting a teacher last course also loses what?
  9. ChallengeDenormalisation buys read speed. What is the price?
  10. Error redoA student claims normalisation always makes queries faster. Where is the error?

03 Answers and one-line notes

# Answer One-line note
1 Four tables: Student, Course, Teacher and Enrol. One table each for students, courses and teachers, plus one for the enrolment link.
2 9 slots in total. 2 plus 3 plus 2 plus 2 is 9, with keys repeated in the link table.
3 One candidate key (CD) and 4 superkeys. The closure of CD covers every column, and the other two columns are optional, so the count is 2 to the power of 2.
4 From 12000 rows down to 500 rows, a factor of 24. 12000 divided by 500 is 24.
5 About 60.25 percent. 1034 minus 411 is 623, and 623 over 1034 is 0.6025.
6 Every determinant must be a candidate key. 3NF tolerates a determinant that is not a candidate key as long as the dependent column is prime.
7 An insertion anomaly: the teacher cannot be stored on its own. The primary key includes CourseID, so no course means no key value.
8 The teacher record itself, which is a deletion anomaly. Teacher data lives only inside course rows, so deleting the row deletes the teacher too.
9 The same data is duplicated, so every update must touch several places. Any failed sync leaves the copies inconsistent.
10 Normalisation removes redundancy but adds joins, so reads can cost more. Writes and consistency improve while read speed depends on the query and the indexes.

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.

Want to draw the flowchart and fill the trace table yourself? Book a TutorZone tutor and unpack all ten items in 25 minutes.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.

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