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)
- FoundationHow many tables are needed to bring FLAT(StudentID, StudentName, CourseID, CourseTitle, TeacherID, TeacherName) to 3NF?
- FoundationFor the same decomposition, how many column slots do the 4 tables hold?
- FoundationR(A, B, C, D) has functional dependencies A determines B and CD determines A. How many candidate keys and superkeys exist?
- 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?
- 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?
- IntermediateBCNF is stricter than 3NF. What extra condition does it impose?
- ChallengeIn the unnormalised design, a new teacher with no assigned course cannot be recorded. Which anomaly is this?
- ChallengeDeleting a teacher last course also loses what?
- ChallengeDenormalisation buys read speed. What is the price?
- 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.



