2023 HKDSE Information and Communication Technology Practice Paper with Answers

In 8-bit signed two's complement representation, the range of representable integers is \(-128\) to \(+127\).
- For (1): \(01010000_2 = +80\) and \(00110000_2 = +48\). Sum = \(+128\), which exceeds \(+127\) and produces \(10000000_2\) (a negative value). An overflow occurs.
- For (2): \(10100000_2 = -96\) and \(11000000_2 = -64\). Sum = \(-160\), which is less than \(-128\). Adding two negative numbers produces a positive sign bit in 8-bit arithmetic, causing an overflow.
- For (3): \(01000000_2 = +64\) and \(11000000_2 = -64\). Sum = \(0\), which is within \([-128, +127]\). No overflow occurs.
Therefore, (1) and (2) only will result in an overflow error.

Award 1 mark for Option A.
Award 0 marks for incorrect options.

- (1) is correct: Levels of memory closer to the CPU core (Registers, L1 Cache) operate at significantly higher speeds than main memory (RAM).
- (2) is incorrect: ROM is small (typically megabytes for firmware/BIOS/UEFI), while secondary storage (HDDs/SSDs) provides gigabytes or terabytes of capacity.
- (3) is correct: Virtual memory utilizes paging/swap space on secondary storage when physical RAM is insufficient.
Therefore, statements (1) and (3) are correct.

Award 1 mark for Option B.
Award 0 marks for incorrect options.

To filter records before grouping, the `WHERE` clause is used (`WHERE Gender = 'F'`). To aggregate per class, `GROUP BY Class` is required. To filter grouped results based on an aggregate condition (count of students in the group > 15), the `HAVING` clause is used (`HAVING COUNT(*) > 15`). Therefore, option A is the correct syntax.

Award 1 mark for Option A.
Award 0 marks for incorrect options.

- Option A: `LEN("Comp Sci")` counts characters including the space: 8 characters. Correct.
- Option B: `MID("DSE_ICT", 5, 3)` extracts 3 characters starting from position 5, which is `"ICT"`. Correct.
- Option C: `FIND("net", "Internet")` is case-sensitive; `"net"` begins at position 6 (1-based: I-1, n-2, t-3, e-4, r-5, n-6, e-7, t-8). The result given as 2 is incorrect.
- Option D: `INT(-3.7)` rounds down to the nearest integer below \(-3.7\), which is \(-4\). Correct.

Award 1 mark for Option C.
Award 0 marks for incorrect options.

- (1) is correct: IP addresses are logical Layer 3 addresses assigned dynamically or statically per subnet.
- (2) is correct: A MAC address is a unique Layer 2 hardware identifier assigned by the manufacturer.
- (3) is incorrect: An IPv4 address is 32 bits (4 bytes), while a MAC address is 48 bits (6 bytes).
Thus, only (1) and (2) are correct.

Award 1 mark for Option A.
Award 0 marks for incorrect options.

Let us trace the algorithm:
- Initial: \(P = 1\)
- \(K = 1\): \(1 \text{ MOD } 2 = 1\) (True) → \(P = 1 + 1 = 2\)
- \(K = 2\): \(2 \text{ MOD } 2 = 0\) (False) → \(P = 2 \times 2 = 4\)
- \(K = 3\): \(3 \text{ MOD } 2 = 1\) (True) → \(P = 4 + 3 = 7\)
- \(K = 4\): \(4 \text{ MOD } 2 = 0\) (False) → \(P = 7 \times 4 = 28\)
Output: 28.

Award 1 mark for Option D.
Award 0 marks for incorrect options.

- (1) is correct: The public key is part of the digital certificate and is shared openly.
- (2) is correct: Public-key cryptography ensures that data encrypted with the public key can only be decrypted by the matching private key.
- (3) is incorrect: The private key must remain strictly confidential on the server and is never transmitted across the network.
Therefore, (1) and (2) only are correct.

Award 1 mark for Option A.
Award 0 marks for incorrect options.

- (1) is correct: Process scheduling and CPU allocation are fundamental operating system tasks.
- (2) is incorrect: Converting high-level source code to machine code is performed by a compiler or translator, not the operating system kernel.
- (3) is correct: Spooling and print queue management are standard peripheral/I/O management functions provided by the operating system.
Therefore, (1) and (3) only are operating system functions.

Award 1 mark for Option B.
Award 0 marks for incorrect options.

To calculate the uncompressed audio file size:

1. Number of samples per second for 2 channels = \(44,100 \times 2 = 88,200\text{ samples/s}\).
2. Bits per second = \(88,200 \times 16 = 1,411,200\text{ bits/s} = 176,400\text{ bytes/s}\).
3. Total seconds in 5 minutes = \(5 \times 60 = 300\text{ s}\).
4. Total bytes = \(176,400 \times 300 = 52,920,000\text{ bytes}\).
5. In megabytes (MB): \(52,920,000 / 1,000,000 \approx 52.9\text{ MB}\) (or \(52,920,000 / (1024 \times 1024) \approx 50.5\text{ MiB}\)).

Thus, \(50.5\text{ MB} / 53\text{ MB}\) is closest to \(50.5\text{ MB}\) (Option C).

Award 1 mark for option C. No partial marks.

Statement (1) is correct: Virtual memory utilizes secondary storage (such as a paging file or swap partition on an SSD/HDD) as an extension of RAM.
Statement (2) is correct: Virtual memory enables multi-programming by loading only currently needed pages into RAM.
Statement (3) is incorrect: Secondary storage has significantly higher latency and lower data transfer rates than RAM, so accessing pages stored on disk is much slower, not faster.

Award 1 mark for option B. No partial marks.

Statement (1) is correct: MAC addresses are 48-bit hardware addresses permanently burned into the NIC.
Statement (2) is correct: IP addresses are logical addresses assigned to devices based on their network segment.
Statement (3) is incorrect: Data link layer frame delivery on a local network relies on MAC addresses (resolved via Address Resolution Protocol, ARP).

Award 1 mark for option A. No partial marks.

In asymmetric encryption, each user has a pair of mathematically linked keys: a public key and a private key. The private key is kept strictly confidential and never shared across a channel. Thus, statement B is incorrect. The other statements correctly describe asymmetric cryptography: public keys are used for encryption, asymmetric operations are slower than symmetric encryption, and private keys can be used to generate digital signatures for authentication and non-repudiation.

Award 1 mark for option B. No partial marks.

When filtering based on the result of an aggregate function (such as `AVG(Score)`), the `HAVING` clause must be used instead of `WHERE`. The standard SQL syntax places the `GROUP BY` clause before the `HAVING` clause:
`SELECT Class, AVG(Score) FROM STUDENT GROUP BY Class HAVING AVG(Score) >= 60;`

Award 1 mark for option B. No partial marks.

Let us trace the loop step-by-step:

- Start: `Total = 0`, `Count = 1`
- Iteration 1 (`Count = 1`): `1 MOD 2 = 1` (True) → `Total = 0 + 1 * 2 = 2`, `Count` becomes 2.
- Iteration 2 (`Count = 2`): `2 MOD 2 = 1` (False) → `Total = 2 - 2 = 0`, `Count` becomes 3.
- Iteration 3 (`Count = 3`): `3 MOD 2 = 1` (True) → `Total = 0 + 3 * 2 = 6`, `Count` becomes 4.
- Iteration 4 (`Count = 4`): `4 MOD 2 = 1` (False) → `Total = 6 - 4 = 2`, `Count` becomes 5.
- Iteration 5 (`Count = 5`): `5 MOD 2 = 1` (True) → `Total = 2 + 5 * 2 = 12`, `Count` becomes 6.
- Loop terminates as `Count <= 5` is now False.
- Output: `12`.

Award 1 mark for option C. No partial marks.

- Option A is incorrect: Entity integrity requires that the primary key of a table must be unique and non-null.
- Option B is incorrect: Referential integrity states that a foreign key value must match an existing primary key value in the referenced table (or be null).
- Option C is correct: Domain integrity specifies the valid data types, format, and range of values allowed for a given attribute/column.
- Option D is incorrect: A foreign key can be assigned a null value unless a `NOT NULL` constraint is explicitly placed on it.

Award 1 mark for option C. No partial marks.

- Option A describes Infrastructure as a Service (IaaS).
- Option B describes Software as a Service (SaaS), where end-user software applications are hosted by a provider and accessed via a web browser.
- Option C describes Platform as a Service (PaaS).
- Option D describes traditional on-premise software licensing, not cloud computing.

Award 1 mark for option B. No partial marks.

In 8-bit two's complement, the valid integer range is from \(-128\) to \(+127\).

(1) \(01001100_2 = +76_{10}\) and \(00111000_2 = +56_{10}\). Adding them gives \(+132_{10}\), which exceeds \(+127\). An overflow occurs (the MSB becomes 1, indicating a negative result from adding two positive numbers).
(2) \(10110100_2 = -76_{10}\) and \(01011100_2 = +92_{10}\). Subtracting gives \(-76 - 92 = -168_{10}\), which is less than \(-128\). An overflow occurs.
(3) \(11001000_2 = -56_{10}\) and \(11100010_2 = -30_{10}\). Adding them gives \(-86_{10}\), which is well within the valid range \([-128, +127]\). No overflow occurs.

Therefore, only (1) and (2) result in an overflow.

A: 1 mark for identifying that (1) and (2) cause an arithmetic overflow while (3) stays within the range of \([-128, +127]\).

(1) is correct: Cache memory (SRAM) is located inside or closer to the CPU and has significantly lower latency/faster access time than RAM (DRAM).
(2) is correct: With more physical RAM available, the operating system needs to swap data pages between RAM and secondary storage (virtual memory paging / thrashing) less frequently.
(3) is incorrect: SSDs use non-volatile flash memory so that data is retained even when power is turned off.

Hence, only (1) and (2) are correct.

B: 1 mark for correctly identifying statements (1) and (2) as true and (3) as false (SSDs use non-volatile memory).

(1) is correct: Memory management is a core operating system function to allocate, track, and protect memory space among processes.
(2) is incorrect: Translating high-level code into executable binaries is the function of a language translator (such as a compiler), not the operating system.
(3) is correct: Device management and communication with hardware through device drivers is an essential OS function.

Thus, (1) and (3) only are correct.

B: 1 mark for identifying (1) and (3) as OS functions while rejecting (2) as a compiler function.

The subnet mask `255.255.255.224` corresponds to a /27 prefix. The block size in the last octet is \(256 - 224 = 32\).
Subnet ranges for the last octet are:
- Subnet 0: 0 to 31
- Subnet 1: 32 to 63
- Subnet 2: 64 to 95 (Network address: `192.168.10.64`, Broadcast address: `192.168.10.95`, Usable host range: `192.168.10.65` to `192.168.10.94`)

Since `192.168.10.68` lies in Subnet 2, another host in the same subnet is `192.168.10.75`.
`192.168.10.62` is in Subnet 1, `192.168.10.96` is the network address of Subnet 3, and `192.168.10.100` is in Subnet 3.

B: 1 mark for calculating the valid subnet range [192.168.10.64 to 192.168.10.95] and selecting the valid host IP.

Step 1: The `WHERE Salary > 20000` clause filters out row S04 (Salary = 19000).
The remaining records are:
- S01: IT, 28000
- S02: HR, 22000
- S03: IT, 36000
- S05: IT, 32000
- S06: HR, 24000

Step 2: `GROUP BY Department` forms two groups:
- IT group: 3 records (28000, 36000, 32000), `COUNT()` = 3, `AVG(Salary)` = (28000 + 36000 + 32000) / 3 = 32000.
- HR group: 2 records (22000, 24000), `COUNT()` = 2, `AVG(Salary)` = (22000 + 24000) / 2 = 23000.

Step 3: `HAVING AVG(Salary) >= 25000` filters out the HR group because 23000 < 25000.

Therefore, only the IT group is returned with a count of 3.

B: 1 mark for correct execution order (WHERE -> GROUP BY -> HAVING -> SELECT) leading to 'IT 3'.

The string `HKDSE-ICT-2024` has characters at positions:
1: 'H', 2: 'K', 3: 'D', 4: 'S', 5: 'E', 6: '-', 7: 'I', 8: 'C', 9: 'T', 10: '-', 11: '2', 12: '0', 13: '2', 14: '4'.

- `MID(A1, 7, 3)` extracts 3 characters starting from position 7, which gives `ICT`.
- `MID(A1, 6, 3)` starts from position 6, yielding `-IC`.
- `LEFT(MID(A1, 7, 7), 4)` extracts `ICT-`.
- `RIGHT(A1, 3)` extracts the last 3 characters, yielding `024`.

Therefore, option B is correct.

B: 1 mark for identifying the correct starting position (7) and length (3) for the MID function.

Tracing the execution step-by-step:
- Initial state: X = 36, Y = 24
- Iteration 1:
- Y ≠ 0 (24 ≠ 0) is TRUE
- R = 36 MOD 24 = 12
- X = 24
- Y = 12
- Iteration 2:
- Y ≠ 0 (12 ≠ 0) is TRUE
- R = 24 MOD 12 = 0
- X = 12
- Y = 0
- Iteration 3:
- Y ≠ 0 (0 ≠ 0) is FALSE, loop terminates.
- Output X: 12.

(This is the Euclidean algorithm to compute the greatest common divisor of 36 and 24).

C: 1 mark for correct trace of the while loop to output 12.

(1) is correct: Asymmetric cryptography uses a key pair where the public key is freely distributed and the private key is held securely by the owner.
(2) is correct: Public-key cryptography ensures confidentiality by allowing anyone to encrypt with the public key, but only the corresponding private key holder can decrypt it.
(3) is incorrect: Asymmetric encryption involves heavy mathematical operations (e.g., large prime factorisation / discrete logarithms) and is much slower than symmetric encryption. Hence, symmetric encryption is used for large data files, while asymmetric encryption is typically used for key exchange or digital signatures.

Thus, statements (1) and (2) only are correct.

B: 1 mark for correctly recognising properties (1) and (2) of public-key encryption and rejecting (3) due to speed and usage characteristics.

In 8-bit two's complement, valid numbers range from \(-128\) to \(+127\).

(1) \(01001100_2 = +76_{10}\) and \(00111100_2 = +60_{10}\). \(76 + 60 = +136\), which exceeds \(+127\) (two positive operands result in a negative sign bit \(10001000_2\)). Overflow occurs.

(2) \(10110100_2 = -76_{10}\) and \(11001000_2 = -56_{10}\). \((-76) + (-56) = -132\), which is less than \(-128\) (two negative operands result in a positive sign bit \(01111100_2\)). Overflow occurs.

(3) Adding a positive number and a negative number can never produce an arithmetic overflow in two's complement arithmetic.

Therefore, only (1) and (2) result in overflow.

A (1 mark)

(1) is correct: Cache memory is made of SRAM, which provides much faster access speed than dynamic RAM (DRAM).
(2) is correct: Cache memory holds copies of frequently accessed instructions and data from main memory, reducing latency.
(3) is incorrect: Cache memory is expensive and limited in physical size, so its storage capacity is much smaller than main memory (RAM).

B (1 mark)

Aggregate condition `AVG(Score) >= 70` filters groups, so it must be specified in the `HAVING` clause rather than the `WHERE` clause. The query must group by `Class` and order the results descending by `AVG(Score)`.

B (1 mark)

In option A, `MID(A1, 6, 4)` extracts 4 characters starting at position 6, yielding "Kong". `LEN("Kong")` evaluates to 4.
In option B, `FIND("2", A1)` returns 11 (the position of the first '2').
In option C, `LEN(TRIM(A1))` returns 14.
In option D, `VALUE(RIGHT(A1, 2))` returns 24.

A (1 mark)

The subnet mask `255.255.255.192` has the last octet `192` (binary `11000000`). The block size of each subnet is \(256 - 192 = 64\). Subnet ranges for the fourth octet are 0 to 63, 64 to 127, 128 to 191, and 192 to 255. Since 68 falls into the range 64 to 127, the network address is `192.168.10.64`.

B (1 mark)

(1) is correct: HTTPS relies on SSL/TLS digital certificates issued by Certificate Authorities to authenticate server identity.
(2) is correct: HTTPS establishes an encrypted communication channel to maintain data privacy and integrity.
(3) is incorrect: HTTPS only secures the transport channel; it cannot inspect or prevent malicious file content transferred from an authentic or compromised server.

A (1 mark)

Trace of loop execution:
- Initial: P = 1, Q = 0
- K = 1: P = 1 * 2 = 2, Q = 0 + 2 = 2
- K = 2: P = 2 * 2 = 4, Q = 2 + 4 = 6
- K = 3: P = 4 * 2 = 8, Q = 6 + 8 = 14
- K = 4: P = 8 * 2 = 16, Q = 14 + 16 = 30

The algorithm outputs 30.

C (1 mark)

Virtual memory uses secondary storage (such as hard disks or SSDs) as an extension of physical RAM, swapping pages/segments in and out of memory to allow execution of larger programs.

B (1 mark)

In 8-bit two's complement representation, the range of representable integers is \(-128\) to \(+127\).
(1) \(01001100_2 = +76_{10}\), \(01010101_2 = +85_{10}\). \(76 + 85 = +161\), which exceeds \(+127\). Adding two positive numbers yields a negative result (sign bit 1), causing an overflow.
(2) \(10110000_2 = -80_{10}\), \(10100000_2 = -96_{10}\). \(-80 + (-96) = -176\), which is less than \(-128\). Adding two negative numbers yields a positive result (sign bit 0), causing an overflow.
(3) \(11000000_2 = -64_{10}\), \(01000000_2 = +64_{10}\). \(-64 - (+64) = -128_{10}\), which is exactly \(10000000_2\) and within the valid range \([-128, 127]\). Thus, no overflow occurs.

A (1 mark): (1) and (2) only result in overflow.

(1) is correct: Cache memory (SRAM) is much faster than main memory (DRAM).
(2) is incorrect: Cache memory is volatile memory and loses its contents when power is turned off.
(3) is correct: The primary purpose of cache is to keep copies of frequently referenced memory locations to reduce CPU access latency.

B (1 mark): (1) and (3) only.

(1) is correct: Virtual memory uses secondary storage (such as a swap file or paging file) as an extension of RAM, enabling large programs to execute.
(2) is incorrect: Virtual memory is a management mechanism implemented via hardware/OS; it does not change the physical clock speed of the hardware RAM.
(3) is correct: Virtual memory provides an isolated virtual address space for each process, ensuring process memory protection.

B (1 mark): (1) and (3) only.

The subnet mask `255.255.255.224` corresponds to a prefix length of /27. The subnet block size is \(256 - 224 = 32\).
Subnet boundaries are \(192.168.10.0\), \(192.168.10.32\), \(192.168.10.64\), etc.
The IP `192.168.10.45` belongs to the subnet `192.168.10.32/27`:
- Network address: `192.168.10.32`
- Usable host range: `192.168.10.33` to `192.168.10.62`
- Broadcast address: `192.168.10.63`
Among the options, `192.168.10.58` falls within the usable host range.

B (1 mark): 192.168.10.58 is the only valid usable host address on the same subnet.

(1) is correct: A digital certificate is validated and digitally signed by a trusted CA.
(2) is correct: The certificate includes the subject's public key, domain name, issuer details, and expiration date.
(3) is incorrect: Digital certificates authenticate server identity and facilitate encryption; they do not perform antivirus or malware scanning functions.

A (1 mark): (1) and (2) only.

We trace the iterations for each pair \((I, J)\):
- \(I = 1\):
- \(J = 1\): \(1 + 1 = 2\) (even) \(\rightarrow\) COUNT = 1
- \(J = 2\): \(1 + 2 = 3\) (odd)
- \(J = 3\): \(1 + 3 = 4\) (even) \(\rightarrow\) COUNT = 2
- \(J = 4\): \(1 + 4 = 5\) (odd)
- \(I = 2\):
- \(J = 2\): \(2 + 2 = 4\) (even) \(\rightarrow\) COUNT = 3
- \(J = 3\): \(2 + 3 = 5\) (odd)
- \(J = 4\): \(2 + 4 = 6\) (even) \(\rightarrow\) COUNT = 4
- \(I = 3\):
- \(J = 3\): \(3 + 3 = 6\) (even) \(\rightarrow\) COUNT = 5
- \(J = 4\): \(3 + 4 = 7\) (odd)
- \(I = 4\):
- \(J = 4\): \(4 + 4 = 8\) (even) \(\rightarrow\) COUNT = 6

Thus, the final value of COUNT is 6.

B (1 mark): Output is 6.

Step 1: Apply `WHERE Qty > 5`:
- B01: Fiction, 80, Qty 10 (Included)
- B02: Fiction, 120, Qty 4 (Excluded because Qty is not > 5)
- B03: Fiction, 100, Qty 8 (Included)
- B04: Science, 150, Qty 12 (Included)
- B05: Science, 250, Qty 6 (Included)
- B06: Travel, 90, Qty 20 (Included)

Step 2: Group by `Category` and count records in each group:
- Fiction: 2 records (B01, B03)
- Science: 2 records (B04, B05)
- Travel: 1 record (B06)

Step 3: Apply `HAVING COUNT(*) >= 2`:
- Fiction satisfies condition (count = 2)
- Science satisfies condition (count = 2)
- Travel is excluded (count = 1 < 2)

Therefore, exactly 2 records (Fiction and Science) are returned.

B (1 mark): 2 records returned.

(1) `=MID(A1, 4, 4)` starts at index 4 (which is '2') and extracts 4 characters: `"2023"`. (Correct)
(2) `=LEFT(A1, 7)` yields `"HK-2023"`. `=RIGHT("HK-2023", 4)` extracts the 4 rightmost characters: `"2023"`. (Correct)
(3) `=FIND("-", A1)` finds the first hyphen at index 3. `FIND("-", A1) + 1` evaluates to 4. `=MID(A1, 4, 4)` yields `"2023"`. (Correct)

D (1 mark): All (1), (2), and (3) are correct.

(a) (i) SSDs utilize flash memory with no moving parts, delivering significantly higher data transfer rates and better shock resistance compared to mechanical HDDs.
(ii) At the same price point, HDDs offer substantially higher storage capacity than SSDs.

(b) (i) Virtual memory allows the OS to simulate additional RAM by allocating space on the secondary storage (e.g., paging file/swap space) so large programs can still execute when RAM is exhausted.
(ii) Accessing secondary storage is orders of magnitude slower than physical RAM. Frequent data swapping between RAM and secondary storage introduces significant I/O latency.

(c) The feature is Plug and Play (PnP). The device driver acts as a software intermediary that converts high-level operating system I/O requests into device-specific hardware instructions.

(a) (i) [2 marks] 1 mark for each valid advantage (e.g., faster read/write speeds, lower latency, lower power consumption, silent operation, better shock resistance).
(ii) [1 mark] 1 mark for higher cost per unit storage / lower capacity at the same price / limited write endurance.

(b) (i) [1 mark] 1 mark for explaining that it uses secondary storage as an extension of main memory/RAM.
(ii) [1 mark] 1 mark for pointing out that secondary storage access speed is much slower than RAM, leading to transfer bottlenecks/swapping delays.

(c) [3 marks]
- 1 mark for identifying "Plug and Play" / "PnP".
- 2 marks for explaining driver functions: acts as a bridge/translator (1 mark) converting OS generic commands into device-specific machine commands (1 mark).

(a) The condition tests if the time result in D2 is faster (less than or equal to) the standard in E2: `=IF(D2<=E2, "Yes", "No")`.
(b) To count entries matching multiple criteria across different ranges, `COUNTIFS` is used: `=COUNTIFS(B2:B5, "5A", F2:F5, "Yes")` or an equivalent sum-product formula.
(c) (i) A pie chart is best suited for showing parts of a whole (proportions of categories).
(ii) Useful practices include displaying data percentage labels, providing a distinct legend/title, and limiting the number of slices.

(a) [2 marks]
- 1 mark for correct `IF` syntax with comparison `D2<=E2`.
- 1 mark for correct output values `"Yes"` and `"No"`.

(b) [3 marks]
- 1 mark for using `COUNTIFS` (or `SUMPRODUCT`).
- 1 mark for range and criterion `B2:B5, "5A"`.
- 1 mark for range and criterion `F2:F5, "Yes"`.

(c) [3 marks]
- (i) 1 mark for Pie chart / Doughnut chart.
- (ii) 2 marks for any two valid formatting practices (1 mark each):
* Show data labels / percentage values on slices.
* Use clear and contrasting colors for slices.
* Include a meaningful chart title and legend.

(a)
- For i=2, A[2]=7: (7 > 10) is False, (7 MOD 2 = 0) is False -> Condition is False, count remains 1.
- For i=3, A[3]=18: (18 > 10) is True, (18 MOD 2 = 0) is True -> Condition is True, count becomes 2.
- For i=4, A[4]=10: (10 > 10) is False -> Condition is False, count remains 2.
- For i=5, A[5]=22: (22 > 10) is True, (22 MOD 2 = 0) is True -> Condition is True, count becomes 3.

(b) The final value of `count` is 3.

(c) Using `OR` means any even number (even if $\le T$, e.g., 10) or any odd number $> T$ would be counted. For example, 10 is not greater than 10, but because it is even, the condition evaluates to True, causing an erroneous increment.

(a) [4 marks]
- 1 mark for Row 2: False, 1
- 1 mark for Row 3: True, 2
- 1 mark for Row 4: False, 2
- 1 mark for Row 5: True, 3

(b) [1 mark]
- 1 mark for output = 3

(c) [3 marks]
- 1 mark for stating that `OR` only requires one condition to be met instead of both.
- 2 marks for demonstrating with a clear concrete example (e.g. 10 is not $>10$ but is even, so it gets counted; or an odd number $>10$ would be counted).

(a) (i) A switch maintains a MAC address table to forward traffic only to the designated recipient port, creating dedicated collision domains and maximizing available bandwidth. Hubs broadcast to all ports, causing high collisions.
(ii) Since traffic is only sent to the destination port, other devices on the LAN cannot simply sniff/eavesdrop on patient data packets.

(b) (i) SSL/TLS provides: 1. Encryption of transmitted data to maintain confidentiality. 2. Authentication of the server identity via digital certificates to prevent spoofing/man-in-the-middle attacks.
(ii) The browser will present a security warning indicating the SSL/TLS certificate has expired or is untrusted, advising the user not to proceed.

(c) 1. Maintain offline/disconnected backups (or immutable cloud backups) so ransomware cannot reach and encrypt backup files. 2. Perform frequent, regular backups and test data restoration procedures.

(a) [3 marks]
- (i) [2 marks] 1 mark for mentioning unicast/dedicated forwarding based on MAC address; 1 mark for mentioning reduction of collisions / higher throughput compared to broadcasting in hubs.
- (ii) [1 mark] 1 mark for preventing packet sniffing/eavesdropping by other computers on the LAN.

(b) [3 marks]
- (i) [2 marks] 1 mark for data encryption (confidentiality/integrity); 1 mark for server identity authentication / certificate validation.
- (ii) [1 mark] 1 mark for mentioning security alert/warning that certificate is invalid/expired or connection is not secure.

(c) [2 marks] 1 mark for each valid backup measure (e.g., maintaining an off-site/offline air-gapped backup, keeping multiple versioned snapshots, regular scheduling of backups).

(a) One book can be borrowed multiple times across different loan records, so the relationship from `BOOK` to `LOAN` is One-to-Many (1:N / 1:M).

(b) (i)
```sql
SELECT Title, Price
FROM BOOK
WHERE Category = 'Technology' AND Price > 150
ORDER BY Price DESC;
```

(b) (ii)
```sql
SELECT MemberID, COUNT()
FROM LOAN
GROUP BY MemberID
HAVING COUNT() > 3;
```

(a) [1 mark] 1 mark for "One-to-Many" / "1:M" / "1:N".

(b) (i) [3 marks]
- 1 mark for `SELECT Title, Price FROM BOOK`
- 1 mark for `WHERE Category = 'Technology' AND Price > 150`
- 1 mark for `ORDER BY Price DESC`

(b) (ii) [4 marks]
- 1 mark for `SELECT MemberID, COUNT(...) FROM LOAN`
- 1 mark for `GROUP BY MemberID`
- 2 marks for `HAVING COUNT(...) > 3` (1 mark for `HAVING`, 1 mark for condition `> 3`).

(a) (i) BookID is the unique identifier for each booking record.
(ii) Foreign keys:
- MemID referencing MEMBER(MemID)
- CourtID referencing COURT(CourtID)

(b) (i)
SELECT MName, Tel
FROM MEMBER
WHERE MemType = 'G' AND JoinDate >= '01/01/2023'
ORDER BY MName;

(ii)
SELECT CourtID, SUM(Duration)
FROM BOOKING
WHERE BookDate = '15/05/2024'
GROUP BY CourtID
HAVING SUM(Duration) > 4;

(c) (i) In 2NF, all non-key attributes must be fully functionally dependent on the entire primary key. If EquipID, EquipName, and Quantity are placed in BOOKING, EquipName depends solely on EquipID rather than the whole key (BookID, EquipID), introducing partial functional dependency.

(ii) Proposed 3NF schema:
EQUIPMENT(EquipID, EquipName, UnitFee)
Primary Key: EquipID

BOOKING_EQUIPMENT(BookID, EquipID, Quantity)
Primary Key: (BookID, EquipID)
Foreign Key: BookID REFERENCES BOOKING(BookID)
Foreign Key: EquipID REFERENCES EQUIPMENT(EquipID)

(d) Any two valid integrity constraints:
1. Domain / Check constraint: e.g. Duration > 0 and StartHour BETWEEN 8 AND 22.
2. Referential integrity constraint: MemID and CourtID must exist in their respective parent tables.
3. Uniqueness / non-overlapping constraint: Unique key on (CourtID, BookDate, StartHour) to prevent double bookings on the same court.

(a) (i) 1 mark for BookID.
(a) (ii) 1 mark for identifying MemID -> MEMBER; 1 mark for identifying CourtID -> COURT.
(b) (i) 1 mark for correct SELECT and FROM clauses; 1 mark for correct WHERE condition and ORDER BY clause.
(b) (ii) 1 mark for SELECT CourtID, SUM(Duration) FROM BOOKING WHERE BookDate = '15/05/2024'; 1 mark for GROUP BY CourtID; 1 mark for HAVING SUM(Duration) > 4.
(c) (i) 1 mark for mentioning partial functional dependency; 1 mark for explaining that EquipName depends only on EquipID rather than the full composite key.
(c) (ii) 1 mark for creating an EQUIPMENT table with PK EquipID; 1 mark for creating an associative table (e.g. RENTAL/BOOKING_EQUIPMENT) with composite PK (BookID, EquipID); 1 mark for correctly specifying foreign keys referencing BOOKING and EQUIPMENT.
(d) 1 mark each for any two valid constraints (e.g. range check on StartHour/Duration, foreign key referential integrity, unique constraint on CourtID + BookDate + StartHour) (max 2 marks).

(a) (i) For 100 hosts, we need at least 7 host bits (\(2^7 - 2 = 126\) usable addresses). Subnet mask: /25 = 255.255.255.128.
(ii) If Subnet B uses the first half of /24:
- Network Address: 192.168.10.0
- Usable Host IP Range: 192.168.10.1 to 192.168.10.126
- Broadcast Address: 192.168.10.127
(Accept alternative valid allocation using the second half: 192.168.10.128, 192.168.10.129–192.168.10.254, 192.168.10.255).

(b) (i) NAT maps private (non-routable) IP addresses from the internal network to a valid public IP address on the WAN interface, allowing multiple internal hosts to share one or few public IPs and hiding internal network topology.
(ii) Protocol: CSMA/CA (Carrier Sense Multiple Access with Collision Avoidance).
Reason: In wireless transmission, wireless transceivers cannot transmit and listen simultaneously at the same frequency with sufficient sensitivity to detect collisions (half-duplex transceivers) and due to the hidden node problem.

(c) (i) Non-overlapping channels (e.g. 1, 6, 11) operate on frequencies with no spectrum overlap, eliminating co-channel radio interference, packet collisions, and throughput degradation between neighboring access points.
(ii) 5 GHz provides wider channel bandwidth resulting in higher data transfer rates, but has shorter wavelength which suffers greater signal attenuation when penetrating solid walls compared to 2.4 GHz.

(d) (i) A packet-filtering firewall inspects individual packets in isolation based on static header rules (IP, port) without context; a stateful inspection firewall monitors active TCP/UDP connection states and dynamic session contexts, filtering packets based on whether they belong to an established, valid connection.
(ii) WPA3-Enterprise authenticates each user individually via an external authentication server (e.g. 802.1X/RADIUS), enabling unique credentials, central access revocation, and preventing shared password compromise.

(a) (i) 1 mark for 255.255.255.128 (or /25).
(a) (ii) 1 mark for correct network address; 1 mark for correct usable host IP range; 1 mark for correct broadcast address.
(b) (i) 1 mark for mentioning private-to-public IP address translation; 1 mark for mentioning enabling Internet communication / conserving public IP addresses / hiding internal structure.
(b) (ii) 1 mark for CSMA/CA; 1 mark for explaining inability to detect collisions during wireless transmission / hidden node issue.
(c) (i) 1 mark for mentioning eliminating frequency overlap / radio interference; 1 mark for mentioning avoiding packet collisions / improving throughput.
(c) (ii) 1 mark for comparing data rate (5 GHz higher); 1 mark for comparing penetration capability (2.4 GHz penetrates walls better / 5 GHz suffers higher attenuation).
(d) (i) 1 mark for packet-filtering inspecting stateless headers; 1 mark for stateful inspection tracking connection states/sessions.
(d) (ii) 1 mark for individual user authentication via RADIUS / centralized management / avoiding shared passphrase vulnerability.

(a) (i) Step-by-step tracing:
- Enqueue(P101): Queue[0] = P101, rear = (0+1)%5 = 1, count = 1
- Enqueue(P102): Queue[1] = P102, rear = 2, count = 2
- Enqueue(P103): Queue[2] = P103, rear = 3, count = 3
- Dequeue(): item P101 removed, front = (0+1)%5 = 1, count = 2
- Enqueue(P104): Queue[3] = P104, rear = 4, count = 3
Final array content: Queue[1] = P102, Queue[2] = P103, Queue[3] = P104 (Queue[0] and Queue[4] are unoccupied/empty).
front = 1, rear = 4, count = 3.

(ii) Pseudocode:
SUBPROGRAM Enqueue(item)
IF count = 5 THEN
OUTPUT "Queue is Full"
ELSE
Queue[rear] ← item
rear ← (rear + 1) MOD 5
count ← count + 1
END IF
END SUBPROGRAM

(b) (i)
Line 1: low <= high
Line 2: A[mid] > target (or target < A[mid])

(ii) For binary search on \(N = 1024\), maximum iterations/comparisons = \(\lfloor \log_2(1024) \rfloor + 1 = 10 + 1 = 11\) comparisons.

(c) (i)
- Black-box testing examines the system's external behaviour and functionality according to specifications without examining internal source code or architecture (e.g. testing valid parcel IDs).
- White-box testing examines internal code structure, execution paths, boundary conditions, and logic branches (e.g. code coverage of circular modulo queue logic).

(ii) Two reasons:
1. Flexibility to accommodate changing requirements from stakeholders during development iterations.
2. Continuous user feedback and early delivery of functional sub-modules, reducing project risk compared to Waterfall.

(a) (i) 1 mark for correct array content (Queue[1]=P102, Queue[2]=P103, Queue[3]=P104); 1 mark for front = 1; 1 mark for rear = 4 (or rear = 3 if defined as last element); 1 mark for count = 3. (Total: 4 marks)
(a) (ii) 1 mark for full queue check (count = 5 / count = MAX); 1 mark for storing item at Queue[rear] and incrementing count; 1 mark for circular index update rear ← (rear + 1) MOD 5. (Total: 3 marks)
(b) (i) 1 mark for Line 1: low <= high; 1 mark for Line 2: A[mid] > target (or equivalent). (Total: 2 marks)
(b) (ii) 1 mark for 11 (accept 10 if 3-way branch comparison definition stated). (Total: 1 mark)
(c) (i) 1 mark for explaining black-box testing (testing inputs/outputs without seeing code); 1 mark for explaining white-box testing (testing code structure/paths/branches). (Total: 2 marks)
(c) (ii) 1 mark each for any two valid advantages of Agile over Waterfall (e.g. quicker adaptation to changing requirements, frequent working releases, better risk management via feedback loops) (max 2 marks).