Advanced level 2025 Holy Infant High School mock ICT 3

Advanced level 2025 Holy Infant High School mock ICT 3

Advanced level 2025 Holy Infant High School mock ICT 3

A council in 2018 set up a scheme to award its registered members “certificates” for any recognized efforts they made
to improve the lives of people in the community. Previously, the council registered these rewards manually. This year,
they have decided to design and implement an electronic database named “COUNCIL”.Below is a flat file of current
awards for registered members.

i. State from the table where 1NF test fails. (2 marks)
………………………………………………………………………………………………………………
………………………………………………………………………………………………………… ……
To transform the above table to 1NF, create a separate table(s) for each set of repeating columns. Then
create a foreign key to link this new table(s) with the original table.
Member (MemberID, FirstName, LastName, Phone)
Certificate (MemberID,ActivityCode,AssessmentDate,Description,AssessorName, AssessorEmail)
ii. Certificate table is not in 2NF because there is partial dependency. State from the table where 2NF test
of partial dependency fails. (2 marks)
……………………………………… ………………………………………………………………………
………………………………………………………………………………………………………………
Certificate (MemberID, ActivityCode, AssessmentDate, AssessorName, AssessorEmail)
Activity (ActivityCode, Description)
Member (MemberID, FirstName, LastName, Phone)
iii. Certificate table is not in 3NF because there is transitive dependence. State from the table where 3NF
test of transitive dependence fails. (2 marks)
……………………………………………………………………… ………………………………………
………………………………………………………………………………………………………………
Certificate (MemberID, ActivityCode, AssessmentDate, AssessorEmail)
Assessor (AssessorEmail, AssessorName)
Activity (ActivityCode, Description)
Member (MemberID, FirstName, LastName, Phone)
iv. you have to complete the normalization process by obtaining data from the COUNCIL table to populate
each table below with correct data values. Also, underline the primary key of each table values.
.. ( 8 marks)

• Download the candidate folder here