This post teaches one method for the normalisation question of Unit 8 in Paper II. The method has four steps: find the key, draw the dependencies, name the normal form, and split the table. Every new-syllabus Paper II from 2019 to 2026 asks this question, and the same four steps answer all of them (8.7).
The post has three parts:
- What the papers ask: the eight questions, their marks, and what the books give (section 1).
- The method: find the key, draw the dependencies, name the normal form, and split to the next form (sections 2 to 5).
- Proof and practice: all eight Paper II questions worked with the method, and more questions to try (section 6).
1. What the normalisation questions ask
Every question gives one table and asks two things: the normal form of the table, and the tables of the next normal form. Only 2026 asks for the dependencies by name first.
The eight questions
- 2019 (New Syllabus) Paper II, B, Q5(a) and (b): three relations of a vehicle rental company, with City and Postal_Code in Customer. 7 marks.
- 2020 Paper II, B, Q9(b)(ii) and (iii): the Winner table, with medals and prizes. 4 marks.
- 2021 (held 2022) Paper II, B, Q9(b)(ii) and (iii): the Contracts table, with an agent in each row. 3 marks.
- 2022 (held 2023) Paper II, B, Q9(b)(iii) and (iv): the Assign_Project table. 2 marks, and 2 more for an update anomaly in (v).
- 2023 (held 2024) Paper II, B, Q9(c): the Show table, with theatres and movies. 2 marks.
- 2024 Paper II, B, Q9(b): the Result table, with students, subjects and teachers. 4 marks.
- 2025 Paper II, B, Q9(b): a table of students in school events, with a Services column. 6 marks.
- 2026 Paper II, B, Q8(b): the CLUB_MEMBERSHIP table. 7 marks.
Four of the eight tables are in 1NF and four are in 2NF. No paper started from a 0NF table.
What the books give
The syllabus lists full, partial and transitive dependency, and the zero to third normal forms (Syllabus 8.7, pages 35 and 36) (විෂය නිර්දේශය 8.7, පිටුව 33). Both Teachers' Guides give the same list and one line on dependency: "FD is a constraint that describes the relationship among attributes in a table" (Teachers' Guide 8.7, page 64) (ගුරු අත්පොත 8.7, පිටුව 65). Neither Guide defines the three kinds of dependency or works one table. Section 3 gives the definitions.
The Guides ask teachers to give "zero normalized tables" and to normalise them "up to third normalized form step by step" (Teachers' Guide 8.7, page 65) (ගුරු අත්පොත 8.7, පිටුව 66). The papers do something else. They start from a table already in 1NF or 2NF, and most ask only for the next normal form. Practise both, but expect the papers' form.
2. Step 1 Find the key
The key is the smallest set of columns whose values never repeat together in two rows. Every later step measures the other columns against it.
When the paper gives the key
Use the key the paper gives. 2020 Paper II, B, Q9(b) says the key is NIC and MatchID. 2021 (held 2022) Paper II, B, Q9(b) says it is CNo. 2022 (held 2023) Paper II, B, Q9(b) underlines ProjectId and EmployeeId. 2026 Paper II, B, Q8(b) gives the business rule that (StudentID, ClubID) names each membership.
When the paper does not give the key
Ask what one row records, then name the columns of that event. One row of Result in 2024 Paper II, B, Q9(b) records one student in one subject, so the key is (Student_ID, Subject_ID). One row of the 2025 table records one student in one event, so the key is (Stu_ID, Event_ID). The official 2025 scheme gives one mark for underlining all four primary keys, so the key counts.
Then test the key on the rows. In 2023 (held 2024) Paper II, B, Q9(c), one row records one show: a theatre, a day, a time and a screen. The note says a theatre can show different movies on several screens at the same time. Theatre, Day and Time alone repeat: Palazzo, Friday, 10:00 is in two rows. Screen makes those two rows different. The marking scheme copy uses (Theatre, Day, Time, Screen) as the key.
A one-column key skips a step
A partial dependency needs a key of two or more columns. So a 1NF table with a one-column key is already in 2NF. Contracts in 2021 has the key CNo, and Customer in 2019 has the key Customer_NIC. The official answer for both is 2NF.
3. Step 2 Draw who decides each column
For each column outside the key, find the smallest set of columns that fixes its value. Draw an arrow from that set to the column. A functional dependency A → B means that one value of A gives exactly one value of B.
Each arrow is one of three kinds:
- Full dependency: the column needs the whole key. JoiningDate needs both a student and a club.
- Partial dependency: the column needs only part of a composite key. StudentName needs only StudentID.
- Transitive dependency: a column outside the key decides another column outside the key. AdvisorID decides AdvisorName, and ClubID decides AdvisorID.
The Sinhala Teachers' Guide calls them "පූර්ණ පරායත්තතා", "ආංශික පරායත්තතා" and "සංක්රාන්ති පරායත්තතා" (ගුරු අත්පොත 8.7, පිටුව 65). The 2026 Sinhala paper calls a partial dependency "අර්ධ පරායත්තතා" in Paper II and "ආංශික පරායත්තතා" in Paper I. Both words have the same meaning.
Use the story first and the rows second
The business rules in the question decide the arrows. The rows can only disprove an arrow: when one value of A appears with two values of B, A does not decide B.
In 2023 (held 2024) Paper II, B, Q9(c), the note says Year is the year the movie was released, so Movie → Year. The rows agree: MI-4 is always 2022. The rows also disprove Theatre → Movie, because Palazzo shows Spider man and Avengers.
In 2024 the rows show one Exam_Date for each subject: ICT is always 2024-09-20. So Subject_ID → Exam_Date is the reading the data supports. Our own worked answer in section 5 uses it.
4. Step 3 Name the normal form
Check the arrows from the lowest normal form up. The first problem found gives the normal form, and that problem is the justification.
- A cell holds a list, or a group of columns repeats: 0NF.
- Else, a partial arrow exists: 1NF.
- Else, a transitive arrow exists: 2NF.
- Else: 3NF.
Write the arrow as the justification, because the schemes give it its own mark. The official 2025 scheme gives one mark for "First Normal Form" and one for the justification. The official 2020 scheme gives one mark for 2NF and one for "No partial dependencies".
The Services column in 2025
The 2025 table is in 1NF, not 0NF. Every Services cell in the printed rows holds one value, and S001 has a separate row for each event. The official answer is 1NF. A cell such as "Refreshments, Printing" would make the table 0NF.
Tables already in 3NF
In 2019 (New Syllabus) Paper II, B, Q5(a), only Customer has a transitive arrow: Customer_NIC → City → Postal_Code. So Customer is in 2NF. Vehicle_Owner and Vehicle have no partial and no transitive arrow, so they are in 3NF. For them, part (b) accepts "It cannot be normalized further from 3 NF" in the official scheme.
5. Step 4 Split to the next normal form
Each arrow that breaks the next normal form becomes its own table. The left side of the arrow is the key of the new table, and the right side holds its other columns. The left side also stays in the old table as the link.
Follow these rules:
- Remove only the kind the next form forbids. From 1NF, move the partial arrows out. From 2NF, move the transitive arrows out.
- Keep the full dependencies with the original key. Mark in 2024 and JoiningDate in 2026 stay with the composite key.
- Name each new table and underline its key. The 2025 official scheme gives a mark for underlining all the primary keys.
- Check the split. Every original column is in exactly one table, except the link columns, which are keys.
Our own worked answer to 2024 Paper II, B, Q9(b), not official. (i) Result is in 1NF, because Student_Name depends on Student_ID alone, which is only part of the key (Student_ID, Subject_ID). (ii) The 2NF tables are STUDENT(Student_ID, Student_Name), SUBJECT(Subject_ID, Subject_Name, Teacher_ID, Teacher_Name, Exam_Date) and RESULT(Student_ID, Subject_ID, Mark).
SUBJECT still has Subject_ID → Teacher_ID → Teacher_Name, a transitive arrow. That is correct for 2NF. A 3NF answer would also split TEACHER(Teacher_ID, Teacher_Name).
6. All eight Paper II questions with the method
The four steps give the answer in every marking scheme from 2019 to 2025, and a full answer for 2026. Each item gives the key, the arrows, the normal form and the next tables, then the source of the answer.
- 2019 (New Syllabus) Paper II, B, Q5: the key of Customer is Customer_NIC. City → Postal_Code is transitive, so Customer is in 2NF. The 3NF tables are Customer(Customer_NIC, Customer_Name, City) and Customer_City(City, Postal_Code). Official scheme.
- 2020 Paper II, B, Q9(b): the key is (NIC, MatchID). A player gets one medal in one match, so MedalType is a full dependency. MedalType → Prize is transitive, so the table is in 2NF. The 3NF tables are Winner(NIC, MatchID, MedalType) and Medal(MedalType, Prize). Official scheme.
- 2021 (held 2022) Paper II, B, Q9(b): the key is CNo. ACode → AName, AMobile is transitive, so Contracts is in 2NF. The 3NF tables are Contracts(CNo, ACode, Client) and Agent(ACode, AName, AMobile). Official scheme.
- 2022 (held 2023) Paper II, B, Q9(b): the key is (ProjectId, EmployeeId). ProjectId → PName, Description is partial, so Assign_Project is in 1NF. The scheme says PName and Description must move out with ProjectId, because they depend partially on the key. So the 2NF tables are Project(ProjectId, PName, Description) and Assign_Project(ProjectId, EmployeeId). Third-party copy of the scheme; the table names are ours.
- 2023 (held 2024) Paper II, B, Q9(c): the key is (Theatre, Day, Time, Screen). Movie → Year is transitive, so Show is in 2NF. The 3NF tables are Show(Theatre, Day, Time, Screen, Movie) and Movie_Year(Movie, Year). Third-party copy of the scheme.
- 2024 Paper II, B, Q9(b): 1NF, split into three 2NF tables, as in section 5. Our own worked answer.
- 2025 Paper II, B, Q9(b): the key is (Stu_ID, Event_ID). Stu_ID → Stu_Name, Phone and Event_ID → Event_Type, Event_Fee, Start_Date, End_Date are partial, so the table is in 1NF. The 2NF tables are STUDENT(Stu_ID, Stu_Name, Phone), EVENT(Event_ID, Event_Type, Event_Fee, Start_Date, End_Date) and STUDENT_EVENT(Stu_ID, Event_ID, Services). Official scheme.
- 2026 Paper II, B, Q8(b): (i) StudentID → StudentName and ClubID → ClubName are partial, and ClubID → AdvisorID is a third. (ii) ClubID → AdvisorID → AdvisorName is transitive. (iii) The 3NF tables are STUDENT(StudentID, StudentName), CLUB(ClubID, ClubName, AdvisorID), ADVISOR(AdvisorID, AdvisorName) and MEMBERSHIP(StudentID, ClubID, JoiningDate). Our own worked answer, because 2026 has no official scheme yet.
In 2019, 2020, 2021 and 2023 the answer is 2NF, and the next table comes from one transitive arrow. In 2022, 2024, 2025 and 2026 the answer is 1NF, and the next tables come from the partial arrows. So learn both splits equally.
More questions to try
Try the four steps on these questions. They have no answers in this post.
- Name the normal form (Paper I): 2019 (New Syllabus) Paper I, Q15, 2021 (held 2022) Paper I, Q34, 2022 (held 2023) Paper I, Q32, 2023 (held 2024) Paper I, Q38.
- Match a dependency to a normal form (Paper I): 2020 Paper I, Q27, 2021 (held 2022) Paper I, Q35, 2022 (held 2023) Paper I, Q33, 2024 Paper I, Q34, 2026 Paper I, Q32 and Q33.
- Old-syllabus Paper II: 2018 Paper II, B, Q5(c), 2019 (Old Syllabus) Paper II, B, Q5(a) and (b).
