HKDSE ICT Practice Paper: Databases, SQL And System Development (12 Questions, 100 Marks, 75 Minutes)

HKDSE ICT Practice Paper: Databases, SQL and System Development (12 questions, 100 marks, 75 minutes)

2026-11-04

HKDSE ICT: Databases, SQL and System Development

HKDSE ICT: 12 questions, 100 marks, 75 minutes on the relational model, SQL queries, normalisation, indexing, query efficiency, system development, testing and backup for S4-S6 candidates.

Download　Question paper PDF　|　Answer key PDF

Paper details　Level: S4-S6 | Subject: ICT | Time: 75 minutes | Marks: 100 | Questions: 12

Section A: The Relational Model, Table Design and SQL (Questions 1-4, 32 marks)

Each question carries 8 marks; show the working and units in calculation questions and write complete, executable SQL statements.

#	Question	Answer and marking notes
1	A school database table STUDENT holds 4,800 records and each record takes 180 bytes on average. (a) Work out the total size of the table in bytes and in KB (2 decimal places). (b) If the number of records grows by 25% each year, find the number of records after two years. (c) Explain the difference between storing a name as CHAR(60) and as VARCHAR(60), and how it affects storage.	(a) Total size = 4,800 x 180 = 864,000 bytes = 843.75 KB. (b) Records after two years = 4,800 x 1.25 x 1.25 = 7,500. (c) CHAR(60) always occupies 60 bytes and pads with spaces, while VARCHAR(60) stores only the actual length plus a length marker, so VARCHAR saves space for short names but makes record positions variable.
2	The class distribution of the STUDENT table is: 5A 120 records, 5B 118, 5C 122 and 5D 119. (a) Find the total number of records. (b) Find the percentage of records in class 5A, correct to 2 decimal places. (c) Write an SQL statement that returns every record of class 5A.	(a) Total = 120 + 118 + 122 + 119 = 479 records. (b) 5A takes 120 / 479 x 100% = 25.05%. (c) SELECT * FROM STUDENT WHERE class = ‘5A’;
3	To find one record in a table of 4,800 records, a linear search needs (n+1)/2 comparisons on average while a binary search needs at most ceil(log2(n+1)) comparisons. (a) Compute both comparison counts for n = 4,800 (1 decimal place for linear search). (b) Compute the ratio between them (1 decimal place). (c) Explain why a binary search requires an index to be built first.	(a) Linear search = (4,800 + 1) / 2 = 2,400.5 comparisons; binary search = ceil(log2(4,801)) = 13 comparisons. (b) Ratio = 2,400.5 / 13 = 184.7 times. (c) A binary search needs random access to sorted data; an index maps sorted key values to record addresses, without which the table can only be scanned record by record.
4	An un-normalised relation has 6 attributes and its primary key is made up of 2 attributes; a partial dependency exists (a non-key attribute depends on only part of the key). (a) State which normal form this relation violates. (b) What is the minimum number of tables needed to remove the partial dependency, and how many foreign keys are required? (c) State two data anomalies caused by leaving the partial dependency in place.	(a) It violates 2NF (and therefore does not reach 3NF) because the primary key is composite and a partial dependency exists. (b) At least 3 tables are needed, with 2 foreign keys linking the child tables back to the parent. (c) Insert anomaly: a new row cannot be added without supplying unrelated attributes. Update anomaly: the same fact is stored more than once, so changing one copy and missing another creates inconsistency.

Section B: Normalisation, Indexing and Query Efficiency (Questions 5-8, 36 marks)

Each question carries 9 marks; quote the given figures or table structure and show each step of your reasoning.

#	Question	Answer and marking notes
5	The STUDENT table holds 4,800 records. Girls make up 60% of the table and girls in class 5A make up 15% of the whole table. (a) Find the number of girls. (b) Find the number of girls in class 5A. (c) If a composite index on (gender, class) is built, to what percentage of the table does the number of scanned records fall for the query “girls in 5A”? Also give the reduction.	(a) Girls = 4,800 x 60% = 2,880. (b) Girls in 5A = 4,800 x 15% = 720. (c) Scanned records fall from 4,800 to 720, that is 15.0% of the table, a reduction of 85.0%.
6	A database currently holds 1,200 records and management expects the volume to double every 18 months. (a) Find the number of records after 54 months. (b) Find the growth percentage over that period. (c) Explain two effects of this growth on query performance.	(a) 54 / 18 = 3 doubling periods, so 1,200 x 2^3 = 9,600 records. (b) Growth = (9,600 – 1,200) / 1,200 x 100% = 700%. (c) The I/O cost of a full scan rises with the record count, and the index gains more levels, so disk reads and comparisons per query both increase.
7	The table MARKS holds 5 records: 62, 78, 85, 91 and 54. (a) Write the SQL aggregate query that returns the average and the highest score. (b) Compute the total and the average. (c) Which clause groups the average by class, and which clause filters the grouped result?	(a) SELECT AVG(score), MAX(score) FROM MARKS; (b) Total = 62 + 78 + 85 + 91 + 54 = 370, average = 370 / 5 = 74.0, highest = 91. (c) GROUP BY class groups the averages and HAVING filters the grouped result.
8	Sorting 1,024 records takes about n*log2(n) comparisons with merge sort and about n^2/2 comparisons with bubble sort on average. (a) Compute both counts. (b) Compute the ratio (1 decimal place). (c) Explain why merge sort is much better for large data sets.	(a) Merge sort = 1,024 x 10 = 10,240 comparisons; bubble sort = 1,024^2 / 2 = 524,288 comparisons. (b) Ratio = 524,288 / 10,240 = 51.2 times. (c) Merge sort runs in O(n log n), so comparisons grow logarithmically, while bubble sort runs in O(n^2) and quadruples its work when the data doubles, so the gap widens with size.

Section C: System Development, Testing and Backup (Questions 9-12, 32 marks)

Each question carries 8 marks; list causes and risks point by point and support every conclusion with the data given.

#	Question	Answer and marking notes
9	A development project is scheduled as: requirements analysis 15 days, system design 20 days, coding 40 days and system testing 25 days. (a) Find the total project duration. (b) Find the percentage of the total taken by coding. (c) Explain why the testing phase should not be compressed below 5 days.	(a) Total duration = 15 + 20 + 40 + 25 = 100 days. (b) Coding takes 40 / 100 x 100% = 40.0% of the total. (c) Testing must run the test cases, log defects and re-run after fixes; compressing it below 5 days lets defects reach production, where repair costs far more than fixing them early.
10	System testing defines 240 test cases and 222 of them pass. (a) Find the pass rate (1 decimal place). (b) Find the number of failing cases. (c) State the two tasks that must be completed for the failing cases before release.	(a) Pass rate = 222 / 240 x 100% = 92.5%. (b) Failing cases = 240 – 222 = 18. (c) Fix the related defects and re-run those cases as regression tests, then have the tester or user sign off the fix.
11	User acceptance testing (UAT) finds 36 defects in a system of 12.5 thousand lines of code, 9 of which are critical. (a) Find the percentage of critical defects. (b) Find the defect density per thousand lines of code (2 decimal places). (c) State one management decision that defect density supports.	(a) Critical defects = 9 / 36 x 100% = 25.0%. (b) Defect density = 36 / 12.5 = 2.88 per thousand lines. (c) It supports a release decision by comparing the quality of modules: a module with a high density needs more testing or refactoring before it ships.
12	The backup plan takes one full backup of 12.0 GB per week plus one incremental backup of 1.6 GB on each of the other 6 days. (a) Find the weekly backup volume. (b) If compression reduces the volume to 60% of the original, find the compressed weekly volume (2 decimal places). (c) Explain two risks of keeping only full backups and dropping the incremental ones.	(a) Weekly volume = 12.0 + 6 x 1.6 = 21.6 GB. (b) Compressed volume = 21.6 x 60% = 12.96 GB. (c) Full-only backups take a long backup window and a large amount of storage; after data corruption the system can only be restored to the previous week, so the recovery point objective slips from one day to seven days.

Find a tutor — free Tutor Registration →

A TutorZone practice paper; original questions and passages, not publisher material.