S5 ICT Practice Paper (Set 4): Relational Databases and SQL Queries, IPv4 Subnetting, Python Code Tracing, Sorting and Searching Complexity, Image and Video File Size with Compression, Information Security
- 2026-11-22
S5 ICT practice paper (set 4): 24 parts, 100 marks, 75 min on SQL queries, IPv4 subnetting, Python tracing, sorting, searching, file size and password hashing.
Question 1 — Relational Databases and SQL (17 marks)
The table below shows two relations of a school database. Scores(student_id, name, class, score); Clubs(student_id, club, joined_year). Answer all parts in SQL.
| # | Question | Answer and marking notes |
|---|---|---|
| 1 | Write an SQL statement that lists the name and score of the top three students in class 5A, highest score first. | SELECT name, score FROM Scores WHERE class = ‘5A’ ORDER BY score DESC LIMIT 3; |
| 2 | Write an SQL statement listing every student name with the average score, showing only students whose average score is at least 60. | SELECT name, AVG(score) FROM Scores GROUP BY student_id, name HAVING AVG(score) >= 60; |
| 3 | Scores holds 2,000 records of 320 bytes each with a data page size of 4,096 bytes. Find the total size in bytes and the minimum number of data pages, showing working. | Total = 2,000 x 320 = 640,000 bytes; pages = 640,000 / 4,096 = 156.25, so at least 157 pages are needed. |
| 4 | The Clubs table originally stored student name, class and club in one table, causing duplication and update anomalies. Describe briefly how to normalise it into two related tables and give one benefit. | Store student data (student_id, name, class) in a Students table and keep only student_id, club, joined_year in Clubs, linked by the foreign key student_id; the name or class is then updated in one place only, avoiding duplication and update anomalies. |
Question 2 — IPv4 Subnetting (17 marks)
A school is allocated the network 192.168.10.0/24 and must divide it into four equal subnets, one for each department.
| # | Question | Answer and marking notes |
|---|---|---|
| 5 | State the new prefix length and the subnet mask in dotted decimal notation. | Prefix /26; subnet mask 255.255.255.192. |
| 6 | Find the total number of addresses and the number of usable hosts per subnet, showing working. | Host bits = 32 – 26 = 6, total addresses = 2^6 = 64, usable hosts = 64 – 2 = 62. |
| 7 | Give the network address, the first and last usable host addresses, and the broadcast address of the third subnet. | Network 192.168.10.128; first usable 192.168.10.129; last usable 192.168.10.190; broadcast 192.168.10.191. |
| 8 | If one department later needs only 20 hosts, is it workable to borrow further host bits to split again? State one consideration. | Yes: borrowing one more bit gives /27 with 30 usable hosts per subnet, enough for 20 hosts; however it wastes addresses and adds routing and management complexity. |
Question 3 — Python Code Tracing (16 marks)
Study the program below and answer each part with the exact output.
nums = [2, 5, 8, 11, 13]
acc = {}
for n in nums:
acc[n % 3] = acc.get(n % 3, 0) + n
print(acc)
print(sum(nums))
| # | Question | Answer and marking notes |
|---|---|---|
| 9 | State the output of print(acc). | {2: 26, 1: 13} |
| 10 | State the output of print(sum(nums)). | 39 |
| 11 | Explain the role of the second argument 0 in acc.get(n % 3, 0). | It returns the default value 0 when the key n % 3 is absent, so a new key starts accumulating from 0 and no KeyError is raised. |
| 12 | If nums becomes [1, 4, 7], state the new output of print(acc). | {1: 12} |
Question 4 — Comparison Counts in Sorting and Searching (17 marks)
A sorted array holds 1,024 integers. Show working in every part.
| # | Question | Answer and marking notes |
|---|---|---|
| 13 | How many comparisons does bubble sort need in the worst case for 6 elements? Write the formula. | Formula n(n – 1) / 2 = 6 x 5 / 2 = 15 comparisons. |
| 14 | Using binary search on 1,024 elements, what is the maximum number of comparisons in the worst case? Explain. | At most 10, because the search range halves each time and 2^10 = 1,024. |
| 15 | If linear search is used instead, how many comparisons are needed in the worst case, and by what factor does it exceed binary search? | 1,024 comparisons in the worst case; 1,024 / 10 = 102.4 times as many. |
| 16 | Merge sort needs about 1,024 x log2(1,024) comparisons for 1,024 elements. Compute it and state why its time complexity is better than bubble sort. | 1,024 x 10 = 10,240 comparisons; merge sort is O(n log n) while bubble sort is O(n squared), so merge sort grows far more slowly as n increases. |
Question 5 — Image and Video File Size and Compression (16 marks)
A 24-bit RGB image has a resolution of 1920 x 1080. A video is recorded at 30 frames per second with 1.0 MB per frame for 90 seconds and sent over a 100 Mbps link.
| # | Question | Answer and marking notes |
|---|---|---|
| 17 | Find the uncompressed size of the image in bytes and in MiB (2 decimal places). | 1920 x 1080 x 24 / 8 = 6,220,800 bytes = 5.93 MiB. |
| 18 | If lossy compression keeps only 40% of the original size, find the compressed size in bytes and MiB. | 6,220,800 x 40% = 2,488,320 bytes = 2.37 MiB. |
| 19 | Find the total size of the video in MB. | 30 x 1.0 x 90 = 2,700 MB. |
| 20 | Find the time needed to send the video over 100 Mbps, in seconds and in minutes and seconds. | 2,700 x 8 = 21,600 Mb; 21,600 / 100 = 216 seconds = 3 minutes 36 seconds. |
Question 6 — Password Space and Information Security (17 marks)
A system requires an 8-character password using lowercase letters only. Assume an attacker can try 1,000,000 passwords per second.
| # | Question | Answer and marking notes |
|---|---|---|
| 21 | Find the total number of possible passwords using the 26 lowercase letters. | 26^8 = 208,827,064,576 passwords. |
| 22 | If the 10 digits are also allowed, give the new total (exponential form accepted). | 36^8 = 2,821,109,907,456 passwords. |
| 23 | At 1,000,000 tries per second, how many days are needed to exhaust 26^8 passwords (2 decimal places)? | 208,827,064,576 / 1,000,000 = 208,827.06 seconds; / 86,400 is about 2.42 days. |
| 24 | The system should store passwords as salted hashes rather than symmetric ciphertext. Explain the security purpose of each of the two measures. | A hash is a one-way function, so leaked data cannot easily be turned back into the password; a salt makes identical passwords hash differently, defeating rainbow tables and bulk matching. |
Find a tutor — freeTutor Registration →
A TutorZone practice paper; original questions and passages, not publisher material.



