HKDSE · Answers & Marking Scheme

2023 HKDSE Information and Communication Technology Answers & Marking Scheme

Thinka 2023 DSE-Style Mock — Information and Communication Technology

125 marks210 mins2023
AnalysisSample paperSolution
An original Thinka practice paper modelled on the structure and difficulty of that year's HKDSE paper. Not affiliated with or reproduced from the HKEAA.

Paper 1 Section A (MCQ)

Answer all 40 multiple-choice questions. All questions carry equal marks.
40 Question · 40 marks
Question 1 · MCQ
1 marks
Let \(A\) and \(B\) be two 8-bit signed integers represented in two's complement, where \(A = 01011011_2\) and \(B = 10100100_2\). Which of the following statements about the operation \(A + B\) is/are correct? (1) The result is negative. (2) No overflow occurs. (3) The hexadecimal representation of the result is \(\text{FF}_{16}\).
  1. A.(1) only
  2. B.(1) and (2) only
  3. C.(2) and (3) only
  4. D.(1), (2) and (3)

Answer

D

Worked solution

\(A = 01011011_2 = 91_{10}\). \(B = 10100100_2\). The two's complement of \(B\) is \(01011100_2 = 92_{10}\), so \(B = -92_{10}\). The sum \(A + B = 91 + (-92) = -1_{10}\). (1) \(-1_{10}\) is negative, so (1) is correct. (2) No overflow occurs since we add a positive and a negative number, so (2) is correct. (3) \(-1_{10}\) in 8-bit binary is \(11111111_2\), which is \(\text{FF}_{16}\) in hexadecimal, so (3) is correct. Thus, (1), (2) and (3) are all correct.

Marking scheme

Award 1 mark for the correct answer D. No partial marks are given.
Question 2 · MCQ
1 marks
A host has the IP address 192.168.10.45 with a subnet mask of 255.255.255.224. Which of the following is the network address (subnet address) to which this host belongs?
  1. A.192.168.10.0
  2. B.192.168.10.32
  3. C.192.168.10.45
  4. D.192.168.10.63

Answer

B

Worked solution

To find the network address, perform a bitwise AND operation between the IP address and the subnet mask. The fourth octet of the IP address is 45 (00101101 in binary). The fourth octet of the subnet mask is 224 (11100000 in binary). Performing the bitwise AND: 00101101 AND 11100000 = 00100000, which is 32 in decimal. Therefore, the network address is 192.168.10.32.

Marking scheme

Award 1 mark for the correct answer B. No partial marks.
Question 3 · MCQ
1 marks
Consider a database with two tables: Student(StudentID, Name, Class) where StudentID is the primary key, and Enrollment(StudentID, CourseID, EnrollDate) where (StudentID, CourseID) is the composite primary key, and StudentID is a foreign key referencing Student(StudentID). Which of the following operations will NOT violate any integrity constraints?
  1. A.Inserting a record into Enrollment with a StudentID value that does not exist in Student.
  2. B.Deleting a record from Student whose StudentID is currently referenced in Enrollment.
  3. C.Inserting a record into Enrollment with a null value for StudentID.
  4. D.Inserting a record into Student with a new StudentID value that does not exist in Student.

Answer

D

Worked solution

Option A violates referential integrity because the foreign key StudentID in Enrollment must refer to an existing primary key in Student. Option B violates referential integrity if there is a matching foreign key in Enrollment (unless cascading delete is configured, but by default it violates it). Option C violates entity integrity because StudentID is part of the primary key of Enrollment, and primary keys cannot contain null values. Option D is a standard insertion of a new record with a unique primary key in the primary table Student, which does not violate any integrity constraint.

Marking scheme

Award 1 mark for the correct answer D. No partial marks.
Question 4 · MCQ
1 marks
A database table Sales has columns SaleID, ProductID, Region, and Amount. What is the output of the following SQL query? SELECT Region, COUNT(SaleID) FROM Sales GROUP BY Region HAVING AVG(Amount) > 1100. [Sales Data: (1, 'P01', 'North', 1500), (2, 'P02', 'South', 2000), (3, 'P01', 'South', 1200), (4, 'P03', 'North', 800), (5, 'P02', 'North', 1000), (6, 'P01', 'North', 500)]
  1. A.Region: South, COUNT(SaleID): 2
  2. B.Region: North, COUNT(SaleID): 4
  3. C.Region: North, COUNT(SaleID): 4; Region: South, COUNT(SaleID): 2
  4. D.No records returned

Answer

A

Worked solution

First, group the records by Region: North has 4 records with amounts: 1500, 800, 1000, 500. The average (AVG) for North is (1500+800+1000+500)/4 = 3800/4 = 950. South has 2 records with amounts: 2000, 1200. The average (AVG) for South is (2000+1200)/2 = 3200/2 = 1600. The HAVING clause filters out groups where AVG(Amount) <= 1100. Thus, North is excluded (950 <= 1100) and only South is included (1600 > 1100). The query returns the Region (South) and the count of records in that group (2).

Marking scheme

Award 1 mark for the correct answer A. No partial marks.
Question 5 · MCQ
1 marks
A database relation R(A, B, C, D) has a composite primary key (A, B). Which of the following functional dependencies would violate the Second Normal Form (2NF)?
  1. A.(A, B) -> C
  2. B.C -> D
  3. C.A -> D
  4. D.(A, B) -> D

Answer

C

Worked solution

For a relation to be in 2NF, it must be in 1NF and every non-prime attribute must be fully functionally dependent on the entire primary key. If A -> D, the non-prime attribute D is functionally dependent on A, which is a proper subset of the primary key (A, B). This is a partial dependency, which violates 2NF.

Marking scheme

Award 1 mark for the correct answer C. No partial marks.
Question 6 · MCQ
1 marks
Consider the following pseudocode: A is a 1-indexed array of 5 integers: [4, 7, 2, 9, 5]. For i from 1 to 4 do: For j from 1 to 5 - i do: If A[j] > A[j+1] then Swap A[j] and A[j+1]. What is the content of the array A after the outer loop completes its first iteration (i.e., when i = 1)?
  1. A.[2, 4, 5, 7, 9]
  2. B.[4, 2, 7, 5, 9]
  3. C.[4, 2, 5, 7, 9]
  4. D.[2, 7, 4, 5, 9]

Answer

B

Worked solution

When i = 1, the inner loop index j ranges from 1 to 4. 1) j = 1: compare A[1] (4) and A[2] (7). No swap. Array: [4, 7, 2, 9, 5]. 2) j = 2: compare A[2] (7) and A[3] (2). Since 7 > 2, swap. Array becomes: [4, 2, 7, 9, 5]. 3) j = 3: compare A[3] (7) and A[4] (9). No swap. Array: [4, 2, 7, 9, 5]. 4) j = 4: compare A[4] (9) and A[5] (5). Since 9 > 5, swap. Array becomes: [4, 2, 7, 5, 9]. This is the final state after the first pass (i = 1).

Marking scheme

Award 1 mark for the correct answer B. No partial marks.
Question 7 · MCQ
1 marks
Which of the following correctly describes the roles of the Program Counter (PC) and the Memory Address Register (MAR) during the instruction fetch phase of a machine cycle?
  1. A.PC holds the address of the next instruction to be fetched, and MAR holds the memory address currently being read.
  2. B.MAR holds the data fetched from memory, and PC holds the decoded instruction.
  3. C.PC holds the address in memory currently being read, and MAR holds the next instruction.
  4. D.MAR holds the address of the next instruction, and PC holds the accumulated result of arithmetic operations.

Answer

A

Worked solution

The Program Counter (PC) keeps track of the memory address of the next instruction to be fetched. During the fetch phase, this address is copied from the PC to the Memory Address Register (MAR), which directly interfaces with the address bus to access the physical memory location currently being read. Therefore, option A is correct.

Marking scheme

Award 1 mark for the correct answer A. No partial marks.
Question 8 · MCQ
1 marks
Alice wants to send a confidential message to Bob and ensure that Bob can verify that the message indeed came from Alice (authenticity and non-repudiation). Using public-key cryptography, which keys should Alice use to (1) sign the message, and (2) encrypt the message, respectively?
  1. A.(1) Alice's private key; (2) Bob's public key
  2. B.(1) Alice's public key; (2) Bob's private key
  3. C.(1) Bob's public key; (2) Alice's private key
  4. D.(1) Bob's private key; (2) Alice's public key

Answer

A

Worked solution

To sign a message and guarantee authenticity/non-repudiation, the sender (Alice) must encrypt the message (or its hash value) using her own private key. Since only Alice knows her private key, successful decryption with Alice's public key proves Alice sent it. To ensure confidentiality, Alice must encrypt the message using the recipient's (Bob's) public key, so that only Bob can decrypt it using his private key. Thus, the correct sequence is (1) Alice's private key and (2) Bob's public key.

Marking scheme

Award 1 mark for the correct answer A. No partial marks.
Question 9 · MCQ
1 marks
A school wants to host its student management system. Instead of maintaining physical servers, the school decides to rent virtual servers and storage space from a cloud service provider, but the school's IT staff will still install and manage the operating system, databases, and application software. Which cloud service model is the school utilizing?
  1. A.Software as a Service (SaaS)
  2. B.Platform as a Service (PaaS)
  3. C.Infrastructure as a Service (IaaS)
  4. D.Database as a Service (DBaaS)

Answer

C

Worked solution

Infrastructure as a Service (IaaS) provides virtualized computing resources, such as virtual servers, storage, and networking, over the internet. The customer is responsible for installing and managing the operating system, middleware, databases, and applications. Since the school rents virtual servers and storage but manages the operating systems and databases themselves, this is an IaaS model.

Marking scheme

Award 1 mark for the correct answer C. No partial marks.
Question 10 · MCQ
1 marks
Which of the following statements comparing a compiler and an interpreter is/are correct? (1) A compiler translates the entire source code into machine code before execution, whereas an interpreter translates and executes code line-by-line. (2) An interpreted program generally runs faster than a compiled program because it does not require a compilation step. (3) If there is a syntax error in the middle of the code, a compiler will report the error before generating any executable, while an interpreter may execute the preceding lines before stopping at the error.
  1. A.(1) only
  2. B.(1) and (2) only
  3. C.(1) and (3) only
  4. D.(2) and (3) only

Answer

C

Worked solution

Statement (1) is correct: compiling translates the whole program first, while interpreting translates line-by-line during runtime. Statement (2) is incorrect: compiled programs run significantly faster than interpreted programs because they are already converted to machine code, while interpreted programs require translating overhead during execution. Statement (3) is correct: since compilers parse the entire program first, syntax errors prevent executable generation; interpreters run line-by-line, so they only halt when they actually encounter the erroneous line. Thus, (1) and (3) are correct.

Marking scheme

Award 1 mark for the correct answer C. No partial marks.
Question 11 · MCQ
1 marks
In an 8-bit two's complement representation, what is the binary representation of the decimal number \(-37\)?
  1. A.11011011
  2. B.11011010
  3. C.10100101
  4. D.11100101

Answer

A

Worked solution

To represent \(-37\) in 8-bit two's complement:
1. Express the absolute value \(37\) in binary: \(37 = 32 + 4 + 1 \Rightarrow 00100101_2\).
2. Find the one's complement by inverting all bits: \(11011010_2\).
3. Add 1 to find the two's complement: \(11011010_2 + 1 = 11011011_2\).
Thus, the correct option is A.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 12 · MCQ
1 marks
Which of the following statements about Cache Memory is/are correct?

(1) It is faster than registers inside the CPU.
(2) It stores frequently used data and instructions to speed up CPU access.
(3) It is typically larger in capacity than the main memory (RAM).
  1. A.(2) only
  2. B.(1) and (2) only
  3. C.(2) and (3) only
  4. D.(1), (2) and (3)

Answer

A

Worked solution

Statement (1) is incorrect because registers inside the CPU are the fastest memory components, faster than cache memory.
Statement (2) is correct because cache memory holds active/frequently-accessed instructions/data close to the CPU to reduce access latency.
Statement (3) is incorrect because cache memory has a much smaller storage capacity than RAM due to high cost and physical space constraints.
Therefore, only statement (2) is correct.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 13 · MCQ
1 marks
Which of the following IP addresses can be assigned to a host on a private local area network (LAN) without routing on the public Internet?

(1) 10.150.23.45
(2) 172.20.100.2
(3) 192.168.1.254
(4) 168.192.0.1
  1. A.(1) and (3) only
  2. B.(2) and (3) only
  3. C.(1), (2) and (3) only
  4. D.(1), (2), (3) and (4)

Answer

C

Worked solution

Private IP address ranges are designated for local networks:
- Class A: 10.0.0.0 to 10.255.255.255
- Class B: 172.16.0.0 to 172.31.255.255
- Class C: 192.168.0.0 to 192.168.255.255

Thus:
(1) 10.150.23.45 falls in the Class A private range.
(2) 172.20.100.2 falls in the Class B private range.
(3) 192.168.1.254 falls in the Class C private range.
(4) 168.192.0.1 is outside the private ranges (it is a public IP address).

Therefore, (1), (2), and (3) are private IP addresses.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 14 · MCQ
1 marks
In public-key cryptography, when Alice wants to send a digitally signed message to Bob to ensure non-repudiation and integrity, Alice should:
  1. A.Encrypt the message hash with Alice's private key.
  2. B.Encrypt the message hash with Bob's public key.
  3. C.Encrypt the message hash with Alice's public key.
  4. D.Encrypt the message hash with Bob's private key.

Answer

A

Worked solution

A digital signature is created by calculating the hash value of a message and encrypting that hash value with the sender's (Alice's) private key. The recipient (Bob) can verify the signature by decrypting it using Alice's public key. This ensures integrity (any modification of the message will result in a hash mismatch) and non-repudiation (only Alice, who holds her private key, could have created the signature).

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 15 · MCQ
1 marks
In a relational database, there are two tables:
`STUDENT(StudentID, StudentName, ClassID)` and `CLASS(ClassID, ClassName, RoomNo)`.
`StudentID` is the primary key of `STUDENT`, and `ClassID` is the primary key of `CLASS`. `ClassID` in `STUDENT` is a foreign key referencing `CLASS`. Which of the following operations violates referential integrity?
  1. A.Inserting a new record into CLASS with a new ClassID.
  2. B.Inserting a new record into STUDENT with a ClassID that does not exist in CLASS.
  3. C.Deleting a record from STUDENT.
  4. D.Changing the ClassName of a class in the CLASS table.

Answer

B

Worked solution

Referential integrity states that every foreign key value in a table must match an existing primary key value in the referenced table (or be NULL).
- Option A: Inserting a record into the parent table (CLASS) does not violate integrity.
- Option B: Inserting a record into the child table (STUDENT) with a ClassID that does not exist in CLASS leaves a foreign key with no matching primary key, violating referential integrity.
- Option C: Deleting a record from the child table does not affect referential integrity.
- Option D: Changing ClassName of a class in CLASS does not affect the primary key ClassID.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 16 · MCQ
1 marks
Consider the table `Sales(SalesID, ItemID, Quantity, Price)`.
What is the purpose of the following SQL statement?

```sql
SELECT ItemID, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY ItemID
HAVING SUM(Quantity * Price) > 1000;
```
  1. A.To find the list of item IDs where the unit price of any individual sale is greater than 1000.
  2. B.To calculate the total quantity sold for each item, and display those with total quantity greater than 1000.
  3. C.To find the item IDs whose total revenue (Quantity * Price) across all sales is greater than 1000.
  4. D.To display all sales records where the sales amount is greater than 1000, grouped by ItemID.

Answer

C

Worked solution

The query groups the sales records by ItemID. `SUM(Quantity * Price)` calculates the total revenue generated by each item. The `HAVING` clause filters the aggregated groups to retain only those groups where the calculated `TotalRevenue` is strictly greater than 1000.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 17 · MCQ
1 marks
A table `ProjectMember(ProjectID, EmployeeID, EmployeeName, HoursWorked)` has a composite primary key `(ProjectID, EmployeeID)`.
The functional dependencies are:
- `(ProjectID, EmployeeID) -> HoursWorked`
- `EmployeeID -> EmployeeName`

Which normal form does this table satisfy, and what is the reason?
  1. A.First Normal Form (1NF) only, because there is a partial dependency.
  2. B.Second Normal Form (2NF) only, because there are no transitive dependencies.
  3. C.Third Normal Form (3NF), because all non-key attributes are fully functionally dependent on the primary key.
  4. D.It is not even in 1NF, because it has duplicate keys.

Answer

A

Worked solution

1. The table contains atomic values, so it satisfies First Normal Form (1NF).
2. The primary key is composite: `(ProjectID, EmployeeID)`.
3. There is a partial dependency: `EmployeeID -> EmployeeName`. `EmployeeName` depends only on part of the primary key (`EmployeeID`), not the whole composite key. This violates Second Normal Form (2NF).
Therefore, the table satisfies First Normal Form (1NF) only.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 18 · MCQ
1 marks
Consider the following pseudocode:

```
Set A to [3, 8, 2, 7, 5]
Set N to 5
For i from 0 to N - 2:
For j from 0 to N - 2 - i:
If A[j] > A[j+1] Then
Swap A[j] and A[j+1]
EndIf
EndFor
EndFor
```

What is the state of array `A` after the outer loop has completed exactly 2 iterations (i.e., `i = 0` and `i = 1`)?
  1. A.[3, 2, 7, 5, 8]
  2. B.[2, 3, 5, 7, 8]
  3. C.[2, 3, 7, 5, 8]
  4. D.[2, 3, 5, 8, 7]

Answer

B

Worked solution

The pseudocode represents a Bubble Sort algorithm in ascending order.
Let's trace:
Initial state: `A = [3, 8, 2, 7, 5]`
- **First outer iteration (i = 0)**:
- `j = 0`: `A[0] > A[1]` (3 > 8 is false) -> `[3, 8, 2, 7, 5]`
- `j = 1`: `A[1] > A[2]` (8 > 2 is true) -> Swap -> `[3, 2, 8, 7, 5]`
- `j = 2`: `A[2] > A[3]` (8 > 7 is true) -> Swap -> `[3, 2, 7, 8, 5]`
- `j = 3`: `A[3] > A[4]` (8 > 5 is true) -> Swap -> `[3, 2, 7, 5, 8]`
After `i = 0`, the largest element (8) is correctly placed at the end.

- **Second outer iteration (i = 1)**:
- `j = 0`: `A[0] > A[1]` (3 > 2 is true) -> Swap -> `[2, 3, 7, 5, 8]`
- `j = 1`: `A[1] > A[2]` (3 > 7 is false) -> `[2, 3, 7, 5, 8]`
- `j = 2`: `A[2] > A[3]` (7 > 5 is true) -> Swap -> `[2, 3, 5, 7, 8]`
After `i = 1`, the array becomes `[2, 3, 5, 7, 8]`.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 19 · MCQ
1 marks
Consider the following recursive function:

```
Function f(n, k)
If n == 0 Then
Return 0
Else If n % 2 == 1 Then
Return k + f(n // 2, k * 2)
Else
Return f(n // 2, k * 2)
EndIf
EndFunction
```
Note: `//` is integer division. What is the return value of the function call `f(11, 3)`?
  1. A.33
  2. B.13
  3. C.15
  4. D.48

Answer

A

Worked solution

Let's trace the recursive function call f(11, 3):
1. `f(11, 3)`: Since `11 % 2 == 1`, returns `3 + f(5, 6)`.
2. `f(5, 6)`: Since `5 % 2 == 1`, returns `6 + f(2, 12)`.
3. `f(2, 12)`: Since `2 % 2 == 0`, returns `f(1, 24)`.
4. `f(1, 24)`: Since `1 % 2 == 1`, returns `24 + f(0, 48)`.
5. `f(0, 48)`: Since `n == 0`, returns `0`.

Working backwards:
- `f(1, 24) = 24 + 0 = 24`
- `f(2, 12) = f(1, 24) = 24`
- `f(5, 6) = 6 + f(2, 12) = 6 + 24 = 30`
- `f(11, 3) = 3 + f(5, 6) = 3 + 30 = 33`

The return value is 33. (This function computes binary multiplication \(n \times k\)).

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 20 · MCQ
1 marks
A school wants to host its own virtual learning environment (VLE) website. The IT department decides to rent virtual machines (VMs) and storage from a cloud provider. They will install and maintain the operating systems, web server software, and the VLE application themselves. Which cloud service model is the school utilizing?
  1. A.Infrastructure as a Service (IaaS)
  2. B.Platform as a Service (PaaS)
  3. C.Software as a Service (SaaS)
  4. D.Database as a Service (DBaaS)

Answer

A

Worked solution

Infrastructure as a Service (IaaS) provides virtualized computing resources (such as virtual machines, storage, and networks) over the Internet. The user has control over operating systems, storage, and deployed applications. Since the school is renting basic virtual machines/storage and configuring the OS and applications themselves, this matches the IaaS model.

Marking scheme

1 mark for the correct option. No marks for incorrect or multiple options selected.
Question 21 · MCQ
1 marks
An 8-bit register stores a signed integer using two's complement representation. If the content of the register is \(10101100_2\), what is its decimal value?
  1. A.-84
  2. B.-44
  3. C.-83
  4. D.172

Answer

A

Worked solution

In two's complement representation, the most significant bit (MSB) is the sign bit. Since the MSB is 1, the number is negative.\nTo find its magnitude:\n1. Invert all the bits: \(10101100 \rightarrow 01010011\)\n2. Add 1 to the result: \(01010011 + 1 = 01010100_2\)\n3. Convert to decimal: \(01010100_2 = 64 + 16 + 4 = 84\).\nTherefore, the value is \(-84\).

Marking scheme

Award 1 mark for the correct option A.
Question 22 · MCQ
1 marks
Consider two database tables: `STUDENT` and `CLASS`.\n`STUDENT` table: `StudentID` (Primary Key), `Name`, `ClassID` (Foreign Key referencing `CLASS.ClassID`)\n`CLASS` table: `ClassID` (Primary Key), `TeacherInCharge`\n\nWhich of the following actions will definitely violate referential integrity?\nI. Inserting a new student record with a `ClassID` that does not exist in the `CLASS` table.\nII. Deleting a class record from the `CLASS` table while some student records in the `STUDENT` table still have that `ClassID`.\nIII. Updating a student's `Name` to null.
  1. A.I only
  2. B.I and II only
  3. C.II and III only
  4. D.I, II and III

Answer

B

Worked solution

I violates referential integrity because a foreign key value must exist in the referenced table's primary key column.\nII violates referential integrity because deleting a referenced row leaves orphaned foreign keys in the referencing table.\nIII does not violate referential integrity as it only involves the `Name` attribute, which is not a foreign key.

Marking scheme

Award 1 mark for the correct option B.
Question 23 · MCQ
1 marks
Consider the following pseudocode segment:\n\n```\nX = 15\nY = 6\nWhile X != Y Do\n If X > Y Then\n X = X - Y\n Else\n Y = Y - X\n EndIf\nEndWhile\n```\n\nWhat is the final value of `X`?
  1. A.3
  2. B.6
  3. C.9
  4. D.15

Answer

A

Worked solution

This pseudocode implements the Euclidean algorithm for finding the Greatest Common Divisor (GCD) of X and Y.\nLet's trace the values of X and Y:\n- Initially: X = 15, Y = 6\n- Loop 1: X > Y (15 > 6), so X = 15 - 6 = 9\n- Loop 2: X > Y (9 > 6), so X = 9 - 6 = 3\n- Loop 3: X < Y (3 < 6), so Y = 6 - 3 = 3\n- Now X = 3 and Y = 3. Since X == Y, the loop terminates.\nThe final value of X is 3.

Marking scheme

Award 1 mark for the correct option A.
Question 24 · MCQ
1 marks
A computer has an IP address of `192.168.10.45` with a subnet mask of `255.255.255.224`. Which of the following IP addresses belongs to the same subnet as this computer?
  1. A.192.168.10.15
  2. B.192.168.10.30
  3. C.192.168.10.60
  4. D.192.168.10.65

Answer

C

Worked solution

The subnet mask is `255.255.255.224`. The block size is \(256 - 224 = 32\).\nThe subnets are in increments of 32 for the last octet:\n- Subnet 1: .0 to .31 (Usable: .1 to .30)\n- Subnet 2: .32 to .63 (Usable: .33 to .62)\n- Subnet 3: .64 to .95 (Usable: .65 to .94)\n\nSince the computer's IP address has the last octet as 45, it belongs to Subnet 2 (.32 to .63).\nAmong the options, `192.168.10.60` has its last octet (60) within this range, so it belongs to the same subnet.

Marking scheme

Award 1 mark for the correct option C.
Question 25 · MCQ
1 marks
Alice wants to send a confidential message to Bob using public-key cryptography. She also wants to ensure that Bob can verify the message indeed came from her (authentication). Which of the following describes the correct encryption process?
  1. A.Alice encrypts the message with Bob's public key, then encrypts the result with her own public key.
  2. B.Alice encrypts the message with her own private key, then encrypts the result with Bob's public key.
  3. C.Alice encrypts the message with Bob's private key, then encrypts the result with her own private key.
  4. D.Alice encrypts the message with Bob's public key, then encrypts the result with her own private key.

Answer

B

Worked solution

To achieve both confidentiality and authentication:\n1. Alice encrypts the message with her own private key. Since only Alice has her private key, any recipient who can decrypt the message using Alice's public key knows it must have come from her (Authentication).\n2. Then, Alice encrypts the resulting ciphertext with Bob's public key. Since only Bob has Bob's private key, only Bob can perform the first stage of decryption, ensuring that nobody else can read the message (Confidentiality).

Marking scheme

Award 1 mark for the correct option B.
Question 26 · MCQ
1 marks
Consider the database table `SALES` below:\n\n| SaleID | Product | Category | Amount |\n|---|---|---|---|\n| 1 | Laptop | IT | 8000 |\n| 2 | Mouse | IT | 150 |\n| 3 | Desk | Furniture | 1200 |\n| 4 | Chair | Furniture | 800 |\n| 5 | Phone | IT | 5000 |\n\nWhat is the output of the following SQL query?\n\n```sql\nSELECT Category, AVG(Amount) FROM SALES\nGROUP BY Category\nHAVING COUNT(*) > 1 AND AVG(Amount) > 1000;\n```
  1. A.Category | AVG(Amount)
    IT | 4383.33
  2. B.Category | AVG(Amount)
    IT | 4383.33
    Furniture | 1000.00
  3. C.Category | AVG(Amount)
    Furniture | 1000.00
  4. D.Empty set (No rows returned)

Answer

A

Worked solution

First, the query groups the rows by `Category`:\n1. `IT` category group has records with amounts: 8000, 150, 5000.\n - `COUNT(*)` = 3\n - `AVG(Amount)` = (8000 + 150 + 5000) / 3 = 13150 / 3 = 4383.33\n2. `Furniture` category group has records with amounts: 1200, 800.\n - `COUNT(*)` = 2\n - `AVG(Amount)` = (1200 + 800) / 2 = 1000.00\n\nNext, the `HAVING` clause filters the groups:\n- For `IT`: `COUNT(*) > 1` (3 > 1, True) and `AVG(Amount) > 1000` (4383.33 > 1000, True). It is selected.\n- For `Furniture`: `COUNT(*) > 1` (2 > 1, True) and `AVG(Amount) > 1000` (1000 > 1000, False). It is filtered out.\n\nTherefore, only the row for `IT` is returned.

Marking scheme

Award 1 mark for the correct option A.
Question 27 · MCQ
1 marks
Which of the following statements about "thrashing" in virtual memory is/are correct?\n\nI. Thrashing occurs when the operating system spends more time swapping pages in and out of secondary storage than executing actual program instructions.\nII. Increasing the capacity of physical RAM can help resolve the thrashing problem.\nIII. Defragmenting the hard disk is the most effective way to eliminate thrashing.
  1. A.I only
  2. B.I and II only
  3. C.II and III only
  4. D.I, II and III

Answer

B

Worked solution

Statement I is the definition of thrashing in virtual memory management.\nStatement II is correct because adding physical memory reduces the need for the OS to perform page swaps, resolving the source of thrashing.\nStatement III is incorrect because disk fragmentation relates to file storage layout, whereas thrashing is caused by insufficient physical RAM to hold the running programs' active pages. Defragmentation does not solve thrashing.

Marking scheme

Award 1 mark for the correct option B.
Question 28 · MCQ
1 marks
During the fetch-decode-execute cycle of a CPU, which register is updated to store the address of the next instruction to be fetched immediately after an instruction is fetched?
  1. A.Memory Address Register (MAR)
  2. B.Program Counter (PC)
  3. C.Instruction Register (IR)
  4. D.Accumulator (ACC)

Answer

B

Worked solution

During the Fetch phase, the instruction address is read from the Program Counter (PC). Immediately after the current instruction is fetched from memory, the PC is automatically incremented/updated to point to the address of the next instruction in sequence.

Marking scheme

Award 1 mark for the correct option B.
Question 29 · MCQ
1 marks
Consider a relation `BOOK_LOAN` with attributes:\n`{LoanID, StudentID, StudentName, BookID, BookTitle, LoanDate}`\n\nAssume that:\n- `LoanID` is the primary key.\n- Each student has a unique `StudentID` and a single `StudentName`.\n- Each book has a unique `BookID` and a single `BookTitle`.\n\nWhich of the following normal forms does this relation satisfy?\n\nI. First Normal Form (1NF)\nII. Second Normal Form (2NF)\nIII. Third Normal Form (3NF)
  1. A.I only
  2. B.I and II only
  3. C.II and III only
  4. D.I, II and III

Answer

B

Worked solution

1. 1NF: All attributes contain atomic values, so it satisfies 1NF.\n2. 2NF: The primary key is a single attribute (`LoanID`), so there are no partial dependencies (which are only possible with a composite primary key). Thus, it satisfies 2NF.\n3. 3NF: We have functional dependencies: `LoanID -> StudentID` and `StudentID -> StudentName`. Since `StudentID` is not a superkey and `StudentName` is not a prime attribute, there is a transitive dependency: `LoanID -> StudentName` (via `StudentID`). Similarly, `LoanID -> BookTitle` is a transitive dependency (via `BookID`). Therefore, it does not satisfy 3NF.\nHence, it satisfies I and II only.

Marking scheme

Award 1 mark for the correct option B.
Question 30 · MCQ
1 marks
A software development company wants to develop and deploy a new web application. They want to focus entirely on coding and managing the application, without worrying about managing the underlying operating systems, hardware servers, storage, or network infrastructure. Which cloud computing service model is most suitable for them?
  1. A.Infrastructure as a Service (IaaS)
  2. B.Platform as a Service (PaaS)
  3. C.Software as a Service (SaaS)
  4. D.Database as a Service (DBaaS)

Answer

B

Worked solution

Platform as a Service (PaaS) provides a pre-configured environment (including operating system, database, runtime, and server environment) which allows developers to build, test, and run their code without having to manage the underlying hardware and OS infrastructure.

Marking scheme

Award 1 mark for the correct option B.
Question 31 · MCQ
1 marks
Consider the following pseudocode:
```
Integer Array A[0..5] = {3, 8, 2, 9, 5, 4}
Integer i, temp
For i = 0 To 4 Do:
If A[i] > A[i+1] Then:
temp = A[i]
A[i] = A[i+1]
A[i+1] = temp
EndIf
EndFor
```
What is the content of array `A` after executing the pseudocode?
  1. A.{2, 3, 4, 5, 8, 9}
  2. B.{3, 2, 8, 5, 4, 9}
  3. C.{3, 8, 2, 5, 4, 9}
  4. D.{3, 2, 5, 4, 8, 9}

Answer

B

Worked solution

Let's trace the algorithm step-by-step:
- Initially, A = {3, 8, 2, 9, 5, 4}.
- i = 0: Compare A[0] (3) and A[1] (8). Since 3 > 8 is false, no swap occurs. A remains {3, 8, 2, 9, 5, 4}.
- i = 1: Compare A[1] (8) and A[2] (2). Since 8 > 2 is true, swap them. A becomes {3, 2, 8, 9, 5, 4}.
- i = 2: Compare A[2] (8) and A[3] (9). Since 8 > 9 is false, no swap occurs. A remains {3, 2, 8, 9, 5, 4}.
- i = 3: Compare A[3] (9) and A[4] (5). Since 9 > 5 is true, swap them. A becomes {3, 2, 8, 5, 9, 4}.
- i = 4: Compare A[4] (9) and A[5] (4). Since 9 > 4 is true, swap them. A becomes {3, 2, 8, 5, 4, 9}.
Thus, the final array content is {3, 2, 8, 5, 4, 9}.

Marking scheme

Award 1 mark for the correct option (B). No marks are awarded for incorrect options.
Question 32 · MCQ
1 marks
A database table `STUDENT` contains fields `StudentID`, `Name`, `Class`, and `Score`. Which of the following SQL queries can correctly display the class name and the number of students in each class who scored 60 or above?
  1. A.`SELECT Class, COUNT(*) FROM STUDENT WHERE Score >= 60 GROUP BY Class;`
  2. B.`SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class HAVING Score >= 60;`
  3. C.`SELECT Class, COUNT(Score) FROM STUDENT WHERE Score >= 60 GROUP BY StudentID;`
  4. D.`SELECT Class, SUM(Score) FROM STUDENT WHERE Score >= 60 GROUP BY Class;`

Answer

A

Worked solution

To find the number of students scoring 60 or above in each class:
1. We first filter individual student records where `Score >= 60` using the `WHERE` clause.
2. Then we group the filtered records by class using `GROUP BY Class`.
3. Finally, we use `COUNT(*)` to count the number of students in each group.
Option B is incorrect because `HAVING` can only filter groups using aggregated functions, not individual columns unless they are part of the GROUP BY clause. Option C groups by `StudentID`, which would not aggregate by class. Option D uses `SUM(Score)` which calculates the sum of scores rather than counting the number of students.

Marking scheme

Award 1 mark for the correct option (A). No marks are awarded for incorrect options.
Question 33 · MCQ
1 marks
In a relational database, table `PROJECT` has a composite primary key `(ProjID, MemberID)`. Table `MEMBER` has a primary key `MemberID`. Which of the following statements about referential integrity is/are correct?
(1) `MemberID` in `PROJECT` is a foreign key referencing `MemberID` in `MEMBER`.
(2) `MemberID` in `PROJECT` cannot contain duplicate values.
(3) A record cannot be added to `PROJECT` if its `MemberID` does not exist in `MEMBER`.
  1. A.(1) only
  2. B.(1) and (3) only
  3. C.(2) and (3) only
  4. D.(1), (2) and (3)

Answer

B

Worked solution

- Statement (1) is correct: `MemberID` in `PROJECT` is a foreign key that references the primary key `MemberID` of the `MEMBER` table.
- Statement (2) is incorrect: `MemberID` is part of a composite primary key `(ProjID, MemberID)` in `PROJECT`. Therefore, duplicate values are allowed for `MemberID` on its own, as long as each pair of `(ProjID, MemberID)` is unique.
- Statement (3) is correct: Under referential integrity constraints, a foreign key value must exist in the referenced table. Thus, a record with a non-existent `MemberID` cannot be inserted into `PROJECT`.

Marking scheme

Award 1 mark for the correct option (B). No marks are awarded for incorrect options.
Question 34 · MCQ
1 marks
Consider the following Entity-Relationship (ER) diagram representing a library system:
`[Reader] <---- (1:N) ---- [Borrow] ---- (N:1) ----> [Book]`
Which of the following descriptions about the relationship between `Reader` and `Book` is correct?
  1. A.A reader can borrow multiple books, and a book can be borrowed by multiple readers at different times.
  2. B.Each reader can only borrow one book, and each book can only be borrowed by one reader.
  3. C.The relation `Borrow` should have a composite primary key consisting of keys from both `Reader` and `Book` tables only, without any other fields allowed.
  4. D.The relationship between `Reader` and `Book` is a one-to-one (1:1) relationship.

Answer

A

Worked solution

The relation `Borrow` acts as a junction (or associative) entity table to resolve a many-to-many (N:M) relationship between `Reader` and `Book`. Therefore, a reader can borrow multiple books, and a book can be borrowed by different readers over time, which makes option A correct and options B and D incorrect. Option C is incorrect because the junction table `Borrow` can and usually does contain additional fields (e.g., `BorrowDate`, `DueDate`).

Marking scheme

Award 1 mark for the correct option (A). No marks are awarded for incorrect options.
Question 35 · MCQ
1 marks
A computer has the IP address `192.168.10.75` and the subnet mask `255.255.255.240`. Which of the following is the network address (or subnet ID) of the subnet to which this computer belongs?
  1. A.`192.168.10.0`
  2. B.`192.168.10.64`
  3. C.`192.168.10.72`
  4. D.`192.168.10.80`

Answer

B

Worked solution

To find the network address, we perform a bitwise AND operation between the IP address and the subnet mask. Since the first three octets of the mask are `255.255.255`, the first three octets of the network address remain `192.168.10`.
For the last octet:
- IP address last octet: \(75 = 01001011_2\)
- Subnet mask last octet: \(240 = 11110000_2\)
- Bitwise AND: \(01001011_2 \text{ AND } 11110000_2 = 01000000_2\), which is \(64\) in decimal.
Therefore, the network address is `192.168.10.64`.

Marking scheme

Award 1 mark for the correct option (B). No marks are awarded for incorrect options.
Question 36 · MCQ
1 marks
An audio file is recorded with the following specifications:
- Sampling rate: \(44.1 \text{ kHz}\)
- Sampling size: \(16\text{-bit}\)
- Channel: Stereo (2 channels)
- Duration: \(2\text{ minutes}\)
If no compression is applied, what is the estimated file size of the recorded audio file in megabytes (MB)? (Assume \(1 \text{ MB} = 10^6 \text{ bytes}\))
  1. A.\(5.3 \text{ MB}\)
  2. B.\(10.6 \text{ MB}\)
  3. C.\(21.2 \text{ MB}\)
  4. D.\(42.3 \text{ MB}\)

Answer

C

Worked solution

File size (in bits) = Sampling rate \(\times\) Sampling size \(\times\) Number of channels \(\times\) Duration
- Sampling rate = \(44,100 \text{ Hz}\)
- Sampling size = \(16 \text{ bits} = 2 \text{ bytes}\)
- Channels = \(2\)
- Duration = \(2 \text{ minutes} = 120 \text{ seconds}\)
File size (in bytes) = \(44,100 \times 2 \text{ bytes} \times 2 \times 120 \text{ seconds} = 21,168,000 \text{ bytes}\).
Since \(1 \text{ MB} = 10^6 \text{ bytes}\), the file size is \(21,168,000 / 10^6 = 21.168 \text{ MB}\), which is approximately \(21.2 \text{ MB}\).

Marking scheme

Award 1 mark for the correct option (C). No marks are awarded for incorrect options.
Question 37 · MCQ
1 marks
Which of the following descriptions about different types of computer memory is/are correct?
(1) Cache memory is faster than main memory (RAM) and is used to store frequently accessed data.
(2) ROM is non-volatile and is typically used to store the BIOS/UEFI boot programs.
(3) RAM is volatile, meaning its content is lost when the power is turned off.
  1. A.(1) and (2) only
  2. B.(1) and (3) only
  3. C.(2) and (3) only
  4. D.(1), (2) and (3)

Answer

D

Worked solution

All three statements are correct:
- Statement (1) is correct: Cache memory is a high-speed static RAM (SRAM) that helps the CPU access frequently used data faster than from dynamic RAM (DRAM / main memory).
- Statement (2) is correct: Read-Only Memory (ROM) is non-volatile, retaining its contents when powered down, making it suitable for the system's firmware/boot loader.
- Statement (3) is correct: Random Access Memory (RAM) is volatile and requires power to maintain data.

Marking scheme

Award 1 mark for the correct option (D). No marks are awarded for incorrect options.
Question 38 · MCQ
1 marks
Alice wants to send a confidential document to Bob over the Internet. To ensure both **confidentiality** (only Bob can read the document) and **authenticity** (Bob can verify that Alice is the sender), which of the following encryption methods should Alice use?
  1. A.Encrypt the document using Alice's private key, then encrypt the result using Bob's public key.
  2. B.Encrypt the document using Bob's private key, then encrypt the result using Alice's public key.
  3. C.Encrypt the document using Alice's public key, then encrypt the result using Bob's private key.
  4. D.Encrypt the document using Bob's public key, then encrypt the result using Alice's public key.

Answer

A

Worked solution

To achieve both confidentiality and authenticity:
1. **Authenticity / Integrity**: Alice encrypts (signs) the document using her own private key. Since only Alice has her private key, anyone decrypting it using Alice's public key knows it must have originated from Alice.
2. **Confidentiality**: Alice then encrypts the result using Bob's public key. Since only Bob has his corresponding private key, only Bob can decrypt the outer layer.
Therefore, option A is correct. (Note: In practice, to optimize performance, symmetric key cryptography is used for the bulk document, but the logic of asymmetric key wrapping remains the same).

Marking scheme

Award 1 mark for the correct option (A). No marks are awarded for incorrect options.
Question 39 · MCQ
1 marks
A software company wants to deploy a web application. Instead of managing physical servers, they decide to rent virtual machines and storage from a cloud service provider, allowing them to install their own operating systems, databases, and application software. Which cloud computing service model are they utilizing?
  1. A.Software as a Service (SaaS)
  2. B.Platform as a Service (PaaS)
  3. C.Infrastructure as a Service (IaaS)
  4. D.Database as a Service (DBaaS)

Answer

C

Worked solution

Infrastructure as a Service (IaaS) provides virtualized computing resources (servers, virtual machines, storage, and networking) over the Internet. The customer maintains full control over the operating systems, databases, and runtime environments. In contrast, Platform as a Service (PaaS) abstracts away operating system and server management, and Software as a Service (SaaS) provides ready-to-use software applications directly.

Marking scheme

Award 1 mark for the correct option (C). No marks are awarded for incorrect options.
Question 40 · MCQ
1 marks
Which of the following are the typical tasks performed by an operating system (OS)?
(1) Allocating memory space to different running applications.
(2) Managing input/output devices using device drivers.
(3) Scanning files to detect and remove computer viruses.
  1. A.(1) and (2) only
  2. B.(1) and (3) only
  3. C.(2) and (3) only
  4. D.(1), (2) and (3)

Answer

A

Worked solution

- Statements (1) and (2) are correct: Memory management (allocating RAM to programs) and device management (communicating with hardware components via drivers) are fundamental tasks of an operating system.
- Statement (3) is incorrect: Scanning and removing computer viruses is performed by antivirus software, which is classified as utility software, not a core service/task of the operating system itself.

Marking scheme

Award 1 mark for the correct option (A). No marks are awarded for incorrect options.

Paper 1 Section B (Conventional)

Answer all questions in the spaces provided.
5 Question · 40 marks
Question 1 · Structured Conventional
8 marks
An image has a resolution of \(1024 \times 1024\) pixels and is stored in 24-bit true color.

(a) (i) Calculate the uncompressed file size of the image in MB, using \(1\text{ MB} = 1024^2\text{ bytes}\). Show your calculations. (2 marks)
(ii) If the image is converted to 8-bit grayscale, what is the ratio of the file size of the grayscale image to that of the original 24-bit image? (1 mark)

(b) Consider an 8-bit system using two's complement representation.
(i) Represent the decimal number \(-18\) in 8-bit two's complement. (1 mark)
(ii) Perform the subtraction \(35 - 18\) by adding \(35\) and \(-18\) in 8-bit two's complement binary addition. Show your steps and the final result in decimal. (2 marks)

(c) Explain why UTF-8 is widely adopted over ASCII for web pages that support multilingual content, such as Chinese and English. (2 marks)

Answer

(a) (i) 3 MB (ii) 1 : 3 (or 1/3) (b) (i) 11101110 (ii) 00100011 + 11101110 = 00010001 (Decimal: 17) (c) ASCII only supports 128 characters (primarily English), whereas UTF-8 is a variable-width encoding that supports almost all worldwide languages including Chinese.

Worked solution

(a) (i) File Size = \(1024 \times 1024 \times (24 / 8)\text{ bytes} = 1024 \times 1024 \times 3\text{ bytes} = 3,145,728\text{ bytes}\). Dividing by \(1024^2\) gives \(3\text{ MB}\).
(ii) An 8-bit grayscale image uses 1 byte per pixel, while 24-bit color uses 3 bytes per pixel. The ratio is \(1 : 3\).

(b) (i) \(+18\) in 8-bit binary is `00010010`. Inverting the bits gives `11101101`. Adding 1 gives `11101110`.
(ii) \(+35\) in 8-bit binary is `00100011`. Adding \(35\) and \(-18\):
00100011
+ 11101110
----------
100010001
Discarding the overflow bit gives `00010001`, which is \(17\) in decimal.

(c) ASCII uses 7 bits and can only represent 128 characters, which is insufficient for non-English languages like Chinese. UTF-8 can represent characters from almost all languages while remaining backward compatible with ASCII.

Marking scheme

(a) (i)
- Correct calculation formula (e.g. \(1024 \times 1024 \times 3\) bytes) (1 mark)
- Correct final answer: 3 MB (1 mark)
(a) (ii)
- Correct ratio: 1 : 3 or 1/3 (1 mark)

(b) (i)
- Correct binary value: 11101110 (1 mark)
(b) (ii)
- Show correct addition of `00100011` and `11101110` with intermediate binary result (1 mark)
- Correct final binary result `00010001` and decimal equivalent 17 (1 mark)

(c)
- Explain that ASCII has a limited character set (128 characters) and cannot support Chinese (1 mark)
- Explain that UTF-8 has full multilingual support and is backward compatible with ASCII (1 mark)
Question 2 · Structured Conventional
8 marks
A school's local area network (LAN) is connected to the Internet via a router. The internal network uses the private IP address range \(192.168.1.0/24\).

(a) (i) Write down the subnet mask of \(192.168.1.0/24\) in dotted decimal notation. (1 mark)
(ii) What is the maximum number of host devices that can be assigned valid IP addresses in this subnet? Explain your answer. (2 marks)

(b) Explain the primary function of each of the following in the school's network:
(i) DHCP Server (2 marks)
(ii) NAT (Network Address Translation) running on the router (2 marks)

(c) A student tries to ping a website using its domain name (e.g., `www.school.edu.hk`), but the ping fails. However, entering the website's IP address directly in a web browser successfully loads the page. Identify the network service that is likely malfunctioning. (1 mark)

Answer

(a) (i) 255.255.255.0 (ii) 254. The remaining 8 bits allow 256 addresses, but 2 are reserved for the network address and broadcast address. (b) (i) Automatically assigns IP configuration details (IP address, DNS, gateway) to client devices dynamically. (ii) Translates internal private IP addresses to a public IP address when communicating with the Internet. (c) DNS (Domain Name System)

Worked solution

(a) (i) The CIDR notation `/24` means the first 24 bits are set to 1, which corresponds to `255.255.255.0`.
(ii) There are \(32 - 24 = 8\) host bits. \(2^8 = 256\) combinations. We subtract 2 addresses: `192.168.1.0` (network address) and `192.168.1.255` (broadcast address), leaving \(254\) usable host addresses.

(b) (i) A DHCP (Dynamic Host Configuration Protocol) server automatically and dynamically assigns network configurations (IP address, subnet mask, default gateway, and DNS servers) to connecting devices, eliminating manual setup and avoiding address conflicts.
(ii) NAT (Network Address Translation) translates private IP addresses of internal devices into a single public IP address when accessing external networks, which conserves limited public IPv4 addresses and hides internal topology for security.

(c) DNS (Domain Name System). Since the site is reachable via its IP address but not its domain name, the translation service mapping domain names to IP addresses is not functioning properly.

Marking scheme

(a) (i)
- Correct subnet mask: 255.255.255.0 (1 mark)
(a) (ii)
- Correct host number: 254 (1 mark)
- Clear explanation mentioning network address and broadcast address being reserved (1 mark)

(b) (i)
- Explain automatic assignment of IP addresses/network configuration to clients (2 marks)
(b) (ii)
- Explain translation between private and public IP addresses (1 mark)
- Mention conservation of public IP addresses or enhanced internal security (1 mark)

(c)
- Identify DNS / Domain Name System (1 mark)
Question 3 · Structured Conventional
8 marks
An online bookstore database contains two tables: `PUBLISHERS` and `BOOKS`. The schemas are defined as follows:

`PUBLISHERS(PubID, PubName, Address, Contact)`
`BOOKS(BookID, Title, Price, PubID, PublishDate)`

(a) (i) Identify the primary key and the foreign key of the `BOOKS` table. (2 marks)
(ii) State and explain the relationship between `PUBLISHERS` and `BOOKS` (e.g., 1:1, 1:N, or M:N). (2 marks)

(b) (i) A database administrator attempts to insert a book record with a `PubID` that does not exist in the `PUBLISHERS` table. State what will happen and name the integrity constraint violated. (2 marks)
(ii) Can a user insert a new book record with a null value in `BookID`? Explain your answer with reference to database integrity. (2 marks)

Answer

(a) (i) Primary Key: BookID; Foreign Key: PubID (ii) One-to-many (1:N). One publisher can publish many books, but each book is linked to only one publisher via PubID. (b) (i) The insertion will fail. This violates the Referential Integrity constraint. (ii) No. This violates the Entity Integrity constraint, which states primary keys cannot be null.

Worked solution

(a) (i) In the `BOOKS` table, `BookID` uniquely identifies each book record, so it is the Primary Key. `PubID` references the primary key of the `PUBLISHERS` table, so it is the Foreign Key.
(ii) The relationship is one-to-many (1:N). One publisher can publish multiple books, but each book is associated with exactly one publisher (via a single `PubID` field in the `BOOKS` table).

(b) (i) The insertion will fail/be rejected by the DBMS. This action violates the Referential Integrity Constraint, which requires every foreign key value in `BOOKS` to have a matching primary key value in `PUBLISHERS`.
(ii) No. The `BookID` is the primary key of `BOOKS`. According to the Entity Integrity Constraint, no primary key attribute of a relation can choose a null value, because primary keys must uniquely identify each record and must not be null.

Marking scheme

(a) (i)
- Identify Primary Key: BookID (1 mark)
- Identify Foreign Key: PubID (1 mark)
(a) (ii)
- State 1:N or One-to-many relationship (1 mark)
- Explanation: One publisher can publish multiple books, but a book can only have one publisher (1 mark)

(b) (i)
- State that insertion fails/error occurs (1 mark)
- Identify constraint: Referential Integrity (1 mark)
(b) (ii)
- Answer 'No' (1 mark)
- Explain based on Entity Integrity (primary key cannot be null) (1 mark)
Question 4 · Structured Conventional
8 marks
The following algorithm is designed to find the second largest value in an array `A` of `N` integers (where \(N \ge 2\)). The array index starts from 1.

```
1. largest = A[1]
2. second_largest = -999999
3. For i = 2 to N Do
4. If A[i] > largest Then
5. second_largest = largest
6. largest = A[i]
7. Else If A[i] > second_largest And A[i] != largest Then
8. second_largest = A[i]
9. EndIf
10. EndFor
```

(a) Trace the execution of the algorithm with \(N = 5\) and \(A = [12, 15, 8, 15, 14]\) by completing the table below. (4 marks)

| `i` | `A[i]` | `largest` | `second_largest` |
| --- | --- | --- | --- |
| Initial | - | 12 | -999999 |
| 2 | 15 | | |
| 3 | 8 | | |
| 4 | 15 | | |
| 5 | 14 | | |

(b) (i) If the input array is \(A = [10, 10, 10]\) with \(N = 3\), what is the final value of `second_largest`? (1 mark)
(ii) Explain how the condition `A[i] != largest` in line 7 ensures the algorithm works correctly when there are duplicate maximum values in the array (e.g., finding the second largest *distinct* value). (3 marks)

Answer

(a) Trace table values: - i=2: largest=15, second_largest=12 - i=3: largest=15, second_largest=12 - i=4: largest=15, second_largest=12 - i=5: largest=15, second_largest=14 (b) (i) -999999 (ii) It prevents elements equal to 'largest' from updating 'second_largest' when they fail the condition in line 4, ensuring 'second_largest' remains strictly smaller than 'largest'.

Worked solution

(a) Trace table process:
- At `i = 2`, `A[2] = 15`. Since `15 > 12`, `second_largest` gets `12`, and `largest` gets `15`.
- At `i = 3`, `A[3] = 8`. Both conditions in lines 4 and 7 are false, so no change (`largest = 15`, `second_largest = 12`).
- At `i = 4`, `A[4] = 15`. Line 4 is false (`15 > 15` is false). Line 7 is also false because `15 != 15` is false. No change.
- At `i = 5`, `A[5] = 14`. Line 4 is false (`14 > 15` is false). Line 7 is true because `14 > 12` and `14 != 15`. Thus `second_largest` becomes `14`.

(b) (i) The final value is `-999999` because all elements are equal to the initial `largest` value, and the conditions in the loop will never be met.
(ii) If we didn't check `A[i] != largest`, then when encountering a duplicate of the maximum value (such as the second `15` in `[12, 15, 8, 15, 14]`), line 7 would evaluate to true because `15 > 12`, which would overwrite `second_largest` with `15`. This would make both `largest` and `second_largest` equal to `15`. The condition `A[i] != largest` prevents this, ensuring `second_largest` only holds a value strictly smaller than `largest`, i.e., the second distinct largest value.

Marking scheme

(a)
- 1 mark for each correct row in the trace table (4 marks total):
- Row 2: largest = 15, second_largest = 12
- Row 3: largest = 15, second_largest = 12
- Row 4: largest = 15, second_largest = 12
- Row 5: largest = 15, second_largest = 14
(b) (i)
- Correct value: -999999 (1 mark)
(b) (ii)
- Explain that without the condition, duplicate maximums would overwrite `second_largest` with the maximum value (1 mark)
- Explain that `A[i] != largest` prevents `second_largest` from becoming equal to `largest` (1 mark)
- Conclude that it successfully finds the second distinct largest value (1 mark)
Question 5 · Structured Conventional
8 marks
An e-commerce platform handles confidential customer transactions and requires user login.

(a) The platform uses `https://` instead of `http://` for its web address.
(i) Identify the network protocol used to implement HTTPS. (1 mark)
(ii) Explain how this protocol protects user credentials (such as passwords) from being stolen via 'packet sniffing' on public Wi-Fi. (2 marks)

(b) To prevent brute-force attacks by automated scripts, the platform places a CAPTCHA on the login page. Explain how CAPTCHA helps distinguish between human users and automated bots. (2 marks)

(c) During the HTTPS connection setup, asymmetric encryption is used initially, but the system switches to symmetric encryption for transferring web pages.
(i) Why is asymmetric encryption used in the initial handshake rather than symmetric encryption? (2 marks)
(ii) Why is symmetric encryption preferred over asymmetric encryption for data transmission after the handshake? (1 mark)

Answer

(a) (i) SSL / TLS (ii) It encrypts communication data so that eavesdroppers on public Wi-Fi only see cipher text. (b) CAPTCHA presents tasks (e.g., character recognition, image selection) that are easy for humans but extremely difficult for computer algorithms/scripts to solve. (c) (i) Asymmetric encryption allows secure exchange of a session key without a pre-shared secret. (ii) Symmetric encryption is much faster and computationally less expensive.

Worked solution

(a) (i) SSL (Secure Sockets Layer) or TLS (Transport Layer Security).
(ii) It encrypts the payload data transferred between the user's browser and the web server. Even if an attacker intercepts packets on a public Wi-Fi network, they only see unintelligible encrypted ciphertext, making it impossible to read plaintext passwords.

(b) CAPTCHA requires solving challenges that depend on advanced cognitive abilities or pattern recognition (e.g., reading distorted text or classifying images) which are difficult for automated computer vision systems/scripts to solve but easy for human users.

(c) (i) Asymmetric encryption uses a public and private key pair, allowing the client and server to securely negotiate and exchange a session key over an unsecure channel without having a pre-shared secret key. Doing this with symmetric encryption would expose the key if intercepted.
(ii) Symmetric encryption is computationally much faster and uses far fewer system resources, making it more efficient for encrypting the large amount of webpage content transmitted after the connection is established.

Marking scheme

(a) (i)
- State TLS or SSL (1 mark)
(a) (ii)
- Explain that communication data is encrypted (1 mark)
- Explain that even if intercepted, attackers can only see ciphertext / cannot decrypt the data (1 mark)

(b)
- State that CAPTCHA presents challenges involving image recognition, semantic context, or distorted text (1 mark)
- Explain that these are simple for humans but extremely difficult for automated scripts/algorithms (1 mark)

(c) (i)
- State that asymmetric encryption allows secure session key exchange over insecure channels (1 mark)
- State that there is no need for a pre-shared secret key between client and server initially (1 mark)
(c) (ii)
- Explain that symmetric encryption is faster / has lower computational overhead (1 mark)

Paper 2 Elective (Conventional)

Answer any THREE questions out of four from your chosen elective.
3 Question · 45 marks
Question 1 · Elective Structured Conventional
15 marks
A local food delivery platform, "HK-Express-Food", wants to design a database to store information about restaurants, food items, and customer orders. Initially, a junior database designer proposes a single relation to hold all the data:

`ORDER_TEMP (OrderID, CustomerID, CustomerName, CustomerPhone, RestaurantID, RestaurantName, RestaurantAddress, OrderDate, FoodID, FoodName, UnitPrice, Quantity, DeliveryFee)`

Assume that each order is made by a single customer from a single restaurant on a specific date, and multiple food items can be ordered in one single order.

(a) Identify the primary key of `ORDER_TEMP` and explain why this relation is not in the Second Normal Form (2NF), referring to the concept of partial functional dependency. (3 marks)

(b) Identify two transitive dependencies in `ORDER_TEMP`. Explain how transitive dependency can lead to data redundancy or update anomalies. (3 marks)

(c) Decompose the relation `ORDER_TEMP` into a set of relations in the Third Normal Form (3NF). For each relation, state its name, and list its attributes, clearly underlining the primary key(s) and marking the foreign key(s) with an asterisk (*) or a hash sign (#). (6 marks)

(d) HK-Express-Food wants to add a "Coupons" feature. A coupon can be used multiple times by different customers, and a customer can use multiple different coupons over time, but each order can use at most one coupon. Suggest how the database schema should be modified to support this feature. (3 marks)

Answer

See the detailed solution and marking scheme.

Worked solution

(a)
- Primary Key: `(OrderID, FoodID)`
- Reason: There is a partial functional dependency on the primary key. Some non-prime attributes depend only on a part of the primary key. For example, `FoodID -> FoodName, UnitPrice`, and `OrderID -> CustomerID, RestaurantID, OrderDate, DeliveryFee`. Since these non-key attributes are determined by only a subset of the candidate key `(OrderID, FoodID)`, the relation is not in 2NF.

(b)
- Transitive Dependencies:
1. `OrderID -> CustomerID` and `CustomerID -> CustomerName, CustomerPhone` (Thus, `OrderID -> CustomerName, CustomerPhone` is a transitive dependency).
2. `OrderID -> RestaurantID` and `RestaurantID -> RestaurantName, RestaurantAddress` (Thus, `OrderID -> RestaurantName, RestaurantAddress` is a transitive dependency).
- Explanation: Transitive dependencies mean that details like a customer's phone number or a restaurant's address are repeated for every order they make. This leads to data redundancy. If a customer changes their phone number, multiple records must be updated (update anomaly). If some records are missed, data becomes inconsistent.

(c)
The 3NF decomposition is as follows:
1. `CUSTOMER` (under{CustomerID}, CustomerName, CustomerPhone)
2. `RESTAURANT` (under{RestaurantID}, RestaurantName, RestaurantAddress)
3. `FOOD` (under{FoodID}, FoodName, UnitPrice)
4. `ORDER` (under{OrderID}, CustomerID#, RestaurantID#, OrderDate, DeliveryFee)
5. `ORDER_ITEM` (under{OrderID#, FoodID#}, Quantity)

(d)
1. Create a new table `COUPON` (under{CouponID}, DiscountValue, ...).
2. Add `CouponID` as a foreign key to the `ORDER` relation (i.e. `ORDER` (under{OrderID}, CustomerID#, RestaurantID#, OrderDate, DeliveryFee, CouponID#)). Since each order can use at most one coupon, this foreign key can be null if no coupon is used.

Marking scheme

(a) [Total: 3 marks]
- Identify the composite primary key `(OrderID, FoodID)`. [1 mark]
- State that `FoodName`/`UnitPrice` depends on `FoodID` alone, or details like `CustomerName`/`DeliveryFee` depend on `OrderID` alone. [1 mark]
- Explain that a non-key attribute depending on a subset of the composite primary key constitutes a partial functional dependency, violating 2NF. [1 mark]

(b) [Total: 3 marks]
- Correctly identify at least one transitive dependency chain (e.g., `OrderID -> CustomerID -> CustomerName`). [1 mark]
- Explain that transitive dependency causes data redundancy (repeating identical non-key values). [1 mark]
- Explain that it leads to update anomalies (potential inconsistencies or need to perform multiple updates for one real-world change). [1 mark]

(c) [Total: 6 marks]
- `CUSTOMER` and `RESTAURANT` tables correctly designed with appropriate primary keys. [1 mark for both]
- `FOOD` table correctly designed with `FoodID` as PK. [1 mark]
- `ORDER` table correctly designed with `OrderID` as PK and `CustomerID`, `RestaurantID` as foreign keys. [2 marks]
- `ORDER_ITEM` table correctly designed with composite PK `(OrderID, FoodID)` and linking both to order and food tables. [2 marks]

(d) [Total: 3 marks]
- Identify that a new `COUPON` entity/relation is needed with `CouponID` as its PK. [1 mark]
- Correctly identify that a 1-to-many relationship exists between `COUPON` and `ORDER` (since one order has at most one coupon, but one coupon can be used in many orders). [1 mark]
- State that `CouponID` should be added to the `ORDER` table as an optional foreign key. [1 mark]
Question 2 · Elective Structured Conventional
15 marks
A fitness center "FitLife" manages its members, classes, and bookings using three relational tables:

`MEMBER (MemberID, MemberName, MemberType, JoinDate, Balance)`
- `MemberType` can be 'Gold', 'Silver', or 'Bronze'.
- `Balance` is the current prepaid monetary balance in the member's account.

`CLASS (ClassID, ClassName, Instructor, Fee, MaxCapacity)`
- `Fee` is the cost of attending the class.

`BOOKING (BookingID, MemberID, ClassID, BookingDate, Status)`
- `Status` can be 'Confirmed' or 'Cancelled'.

Write SQL statements to perform the tasks in (a) to (e).

(a) Write an SQL statement to list the names of Gold members who joined after '2023-01-01', sorted by their names in alphabetical order. (2 marks)

(b) Write an SQL statement to display the `ClassName` and the total number of confirmed bookings for each class. If a class has no confirmed bookings, display 0. (3 marks)

(c) Write an SQL statement to show the names of members who have booked at least two different classes instructed by 'Alex'. (3 marks)

(d) (i) Due to a promotion, FitLife wants to refund \$20 to the `Balance` of all Gold members who have at least one confirmed booking of the class named 'Yoga Basic'. Write an SQL statement to update the `MEMBER` table accordingly. (3 marks)
(ii) State the type of database integrity constraint that can be used to prevent `Balance` from falling below zero, and write an SQL segment to define this constraint on the `MEMBER` table. (2 marks)

(e) Create a database view named `V_CLASS_SUMMARY` that shows the `ClassID`, `ClassName`, and the percentage of occupancy for each class. (The percentage of occupancy is defined as: the total number of bookings in the `BOOKING` table for that class divided by its `MaxCapacity`, multiplied by 100). (2 marks)

Answer

See the detailed solution and marking scheme.

Worked solution

(a)
```sql
SELECT MemberName
FROM MEMBER
WHERE MemberType = 'Gold' AND JoinDate > '2023-01-01'
ORDER BY MemberName ASC;
```

(b)
```sql
SELECT C.ClassName, COUNT(B.BookingID) AS TotalConfirmed
FROM CLASS C LEFT JOIN BOOKING B
ON C.ClassID = B.ClassID AND B.Status = 'Confirmed'
GROUP BY C.ClassID, C.ClassName;
```
*(Note: `LEFT JOIN` must be used to ensure classes with 0 bookings are included, and the condition `B.Status = 'Confirmed'` must be inside the `ON` clause, not the `WHERE` clause, otherwise classes with no bookings would be filtered out).*

(c)
```sql
SELECT M.MemberName
FROM MEMBER M, BOOKING B, CLASS C
WHERE M.MemberID = B.MemberID
AND B.ClassID = C.ClassID
AND C.Instructor = 'Alex'
GROUP BY M.MemberID, M.MemberName
HAVING COUNT(DISTINCT C.ClassID) >= 2;
```

(d) (i)
```sql
UPDATE MEMBER
SET Balance = Balance + 20
WHERE MemberType = 'Gold'
AND MemberID IN (
SELECT B.MemberID
FROM BOOKING B, CLASS C
WHERE B.ClassID = C.ClassID
AND C.ClassName = 'Yoga Basic'
AND B.Status = 'Confirmed'
);
```
(ii)
- Constraint Type: Check Constraint (or Domain Constraints / Value Constraints)
- SQL Segment:
```sql
ALTER TABLE MEMBER ADD CONSTRAINT chk_balance CHECK (Balance >= 0);
-- OR simply as a table/column constraint during definition: CHECK (Balance >= 0)
```

(e)
```sql
CREATE VIEW V_CLASS_SUMMARY AS
SELECT C.ClassID, C.ClassName, (COUNT(B.BookingID) * 100.0 / C.MaxCapacity) AS OccupancyRate
FROM CLASS C LEFT JOIN BOOKING B ON C.ClassID = B.ClassID
GROUP BY C.ClassID, C.ClassName, C.MaxCapacity;
```

Marking scheme

(a) [Total: 2 marks]
- Correct `SELECT` and `WHERE` filtering clauses. [1 mark]
- Correct sorting using `ORDER BY MemberName ASC`. [1 mark]

(b) [Total: 3 marks]
- Correctly use `LEFT JOIN` (with `CLASS` on the left) to retain all classes. [1 mark]
- Correct condition placement `B.Status = 'Confirmed'` within the `ON` clause. [1 mark]
- Correct `GROUP BY` clause and aggregation `COUNT(B.BookingID)`. [1 mark]

(c) [Total: 3 marks]
- Correct table joining (`MEMBER`, `BOOKING`, `CLASS`) and filter `Instructor = 'Alex'`. [1 mark]
- Correct `GROUP BY` grouping on member attributes. [1 mark]
- Correct `HAVING` clause using `COUNT(DISTINCT C.ClassID) >= 2`. [1 mark]

(d) (i) [Total: 3 marks]
- Correct basic `UPDATE MEMBER SET Balance = Balance + 20` syntax. [1 mark]
- Filter on `MemberType = 'Gold'`. [1 mark]
- Subquery correctly filtering the bookings for 'Yoga Basic' with 'Confirmed' status. [1 mark]

(d) (ii) [Total: 2 marks]
- Correctly name the constraint as "Check Constraint". [1 mark]
- Correctly write the SQL syntax containing `CHECK (Balance >= 0)`. [1 mark]

(e) [Total: 2 marks]
- Correct view creation syntax `CREATE VIEW V_CLASS_SUMMARY AS`. [0.5 mark]
- Correct inclusion of left join and grouping attributes. [0.5 mark]
- Correct formula calculation `(COUNT(B.BookingID) * 100.0 / C.MaxCapacity)`. [1 mark]
Question 3 · Elective Structured Conventional
15 marks
An online retail store "HK-Shop" manages customer purchases using a relational database. High volumes of simultaneous transactions are executed. A simplified schema for the stock control system is:

`INVENTORY (ProductID, ProductName, StockLevel, UnitPrice)`
`ORDER_LINE (OrderID, ProductID, Quantity)`

(a) Suppose Customer A and Customer B both attempt to purchase the same product (with `ProductID` = 'P101', which currently has `StockLevel` = 1) at the exact same millisecond.
(i) Describe the concurrency problem that could occur if no transaction management (locking) is implemented. State the term for this phenomenon. (3 marks)
(ii) Explain how "Two-Phase Locking (2PL)" can prevent this concurrency issue. (2 marks)
(iii) However, locking can lead to a "deadlock" situation. Describe what a deadlock is in this context, and suggest one method the Database Management System (DBMS) can use to handle it. (3 marks)

(b) HK-Shop's database administrator (DBA) wants to optimize the performance of the following SQL query, which runs frequently:
```sql
SELECT ProductName, UnitPrice
FROM INVENTORY
WHERE UnitPrice BETWEEN 100 AND 500
ORDER BY UnitPrice DESC;
```
(i) Suggest and write an SQL statement to create an appropriate database index to speed up this query. (2 marks)
(ii) Explain how this index improves query performance. (2 marks)
(iii) State one negative impact of creating too many indexes on a database. (1 mark)

(c) Security is a crucial concern. The DBA wants to grant access rights to a newly hired customer service officer, "Lucy". Lucy should only be allowed to view (but not modify) order details, and she should not have any access to the `INVENTORY` table.
Write the SQL statement(s) to grant Lucy the appropriate privileges on the `ORDER_LINE` table. (2 marks)

Answer

See the detailed solution and marking scheme.

Worked solution

(a) (i)
- Term: Lost Update (or Race Condition / Inconsistent Retrieve)
- Description: Customer A and Customer B read the same `StockLevel` (which is 1) simultaneously. Customer A decrements it to 0 and writes back. Customer B also decrements it from 1 to 0 and writes back. As a result, both customers completed purchases, but the stock was only decremented once. One of the updates is overwritten and lost, causing an oversell.

(a) (ii)
- When Customer A starts the transaction to purchase, the database acquires an Exclusive Lock (X-lock) on the row of 'P101'. This is the Growing Phase of 2PL.
- Customer B's request for a lock on the same row is blocked and placed in a queue.
- Customer B must wait until Customer A commits the transaction and releases the lock (Shrinking Phase) before B can read the updated stock level (which is now 0), thus preventing inconsistent updates.

(a) (iii)
- Description of Deadlock: Deadlock is a situation where two or more transactions are waiting indefinitely for each other to release locks. For example, Transaction 1 locks Product P1 and waits for Product P2, while Transaction 2 locks Product P2 and waits for Product P1. Neither can proceed.
- DBMS Handling Method (either one):
1. Deadlock Detection: The DBMS maintains a wait-for graph, detects cycles periodically, and aborts (rolls back) one of the transactions (the "victim").
2. Deadlock Prevention: Apply protocols such as ordering lock acquisitions (e.g. always lock items in alphanumeric order) or using timestamp-based scheme (e.g., wound-wait or wait-die).

(b) (i)
```sql
CREATE INDEX idx_price ON INVENTORY (UnitPrice DESC);
-- OR simply:
CREATE INDEX idx_price ON INVENTORY (UnitPrice);
```

(b) (ii)
- Without an index, the DBMS must perform a Full Table Scan (O(N)), inspecting every row in the table to find matches.
- With a B-Tree index on `UnitPrice`, the index stores the values in sorted order. The DBMS can perform binary-search-like retrieval (O(log N)) to quickly locate the boundary (100) and scan the leaf nodes up to 500, avoiding a full table scan and eliminating the sorting overhead for the `ORDER BY UnitPrice DESC` clause.

(b) (iii)
- Any change to the table data (such as INSERT, UPDATE, DELETE) requires the DBMS to update the corresponding indexes, which slows down write/data manipulation operations and consumes extra disk storage space.

(c)
```sql
GRANT SELECT ON ORDER_LINE TO Lucy;
```

Marking scheme

(a) (i) [Total: 3 marks]
- Correctly name the phenomenon as "Lost Update". [1 mark]
- Describe the initial concurrent read of the same stock value by both transactions. [1 mark]
- Describe how one transaction's update overwrites and wipes out the other transaction's update, leading to logical inconsistency (overselling). [1 mark]

(a) (ii) [Total: 2 marks]
- State that 2PL ensures a transaction acquires locks before releasing any (lock growing vs shrinking phases). [1 mark]
- Explain that Customer A's exclusive lock forces Customer B's transaction to wait/block until A's transaction is committed, ensuring isolation. [1 mark]

(a) (iii) [Total: 3 marks]
- Clear explanation of circular wait (Transaction 1 holds resource needed by Transaction 2, while Transaction 2 holds resource needed by Transaction 1). [2 marks]
- State a correct DBMS handling strategy (either deadlock detection via aborting a victim, or deadlock prevention via resource ordering). [1 mark]

(b) (i) [Total: 2 marks]
- Correct use of `CREATE INDEX` syntax. [1 mark]
- Correctly specify the table `INVENTORY` and attribute `UnitPrice`. [1 mark]

(b) (ii) [Total: 2 marks]
- Explain that it avoids a full table scan by utilizing index search (e.g. B-tree search range scan). [1 mark]
- Explain that the index is already ordered, saving sorting time for the `ORDER BY` clause. [1 mark]

(b) (iii) [Total: 1 mark]
- State that too many indexes degrade the performance of DML statements (INSERT/UPDATE/DELETE) or increase storage overhead. [1 mark]

(c) [Total: 2 marks]
- Correct use of `GRANT SELECT` privilege. [1 mark]
- Correct targeting `ON ORDER_LINE TO Lucy`. [1 mark]