中文（繁體）
中文（简体）
English
S6 ICT Practice Paper: Databases, SQL and Data Representation (12 Questions, 100 Marks, 75 Minutes)
S6 ICT: Databases, SQL and Data Representation
S6 ICT: bitmap and audio file sizes, compression, transmission time, keys, SQL, normalisation, subnetting, check digits. 12 questions, 100 marks, 75 minutes, answers included.
Download Question paper PDF | Answer key PDF
Paper details Level: S6 | Subject: ICT | Time: 75 minutes | Marks: 100 | Questions: 12 | Answers included
Section A: Data Representation and Compression (34 marks)
Section A
| # | Question | Answer and marking notes |
|---|---|---|
| 1 | 1. An uncompressed bitmap is 4096 × 3072 pixels with a colour depth of 24 bits. (a) Find the file size in MiB (1 MiB = 2^20 bytes). (b) If the colour depth is reduced to 8 bits, what is the new file size in MiB? (8 marks) | 1. (a) Bytes = 4096 × 3072 × 24/8 = 37,748,736; divide by 2^20 = 36.0 MiB. (b) 8 bits is one third of 24 bits, so the size is 36.0/3 = 12.0 MiB. [Marking: formula 2, answer 2; (b) ratio 2, answer 2] |
| 2 | 2. The 24-bit uncompressed bitmap in Question 1 (36.0 MiB) is saved as a JPEG file of only 2.4 MiB. (a) Find the compression ratio (uncompressed size : compressed size) in the form “n : 1”. (b) Find the percentage of space saved (correct to 1 decimal place). (c) Explain why JPEG is unsuitable for line drawings or screenshots of text. (8 marks) | 2. (a) Ratio = 36.0 ÷ 2.4 = 15 : 1. (b) Saving = (36.0 – 2.4) ÷ 36.0 × 100% = 93.3%. (c) JPEG applies the discrete cosine transform and discards high-frequency detail, so sharp edges of lines and text show ringing/blocking artefacts; use PNG or GIF instead. [Marking: (a) 2, (b) 2, (c) 4 (artefacts 2, alternative format 2)] |
| 3 | 3. A stereo audio recording uses a sampling rate of 44100 Hz, a resolution of 16 bits and 2 channels for 180 seconds. (a) Find the size of the uncompressed WAV file in MiB (3 significant figures). (b) If the same recording is stored as a 128 kbps MP3, find its size in MiB. (c) Why can lossy compression greatly reduce file size while remaining acceptable? (9 marks) | 3. (a) Bytes = 44100 × 16/8 × 2 × 180 = 31,752,000.0; divide by 2^20 = 30.3 MiB. (b) 128000 bit/s × 180 s ÷ 8 = 2,880,000.0 bytes = 2.75 MiB. (c) Lossy compression uses auditory masking and psychoacoustic models to discard components the ear barely perceives, so the file shrinks a lot with little audible difference. [Marking: (a) 4, (b) 2, (c) 3] |
| 4 | 4. The uncompressed bitmap in Question 1 (36.0 MiB) must be sent over a network. (a) Find the minimum transmission time in seconds at 20 Mbps (3 significant figures). (b) How long would the same file take over an 8 Mbps uplink? (c) Recalculate (a) if only 80% of the bandwidth is available for data. (9 marks) | 4. (a) Bits = 37,748,736 × 8 = 301,989,888; ÷ 20,000,000 = 15.1 s. (b) ÷ 8,000,000 = 37.7 s. (c) Effective bandwidth = 20 Mbps × 0.8 = 16 Mbps, so time = 301,989,888 ÷ 16,000,000 = 18.9 s. [Marking: (a) 3, (b) 3, (c) 3] |
Section B: Databases and SQL (34 marks)
Section B
| # | Question | Answer and marking notes |
|---|---|---|
| 5 | 5. A tutorial centre stores data in two relations: STUDENT(StudentID, Name, Phone) and LESSON(LessonID, StudentID, Subject, Date, Fee). (a) State the primary key of each table. (b) State the foreign key in LESSON and the table it references. (c) Give two reasons why Phone is unsuitable as the primary key of STUDENT. (8 marks) | 5. (a) STUDENT: StudentID; LESSON: LessonID. (b) LESSON.StudentID is the foreign key referencing STUDENT.StudentID. (c) A phone number may be shared, may change and may be blank, whereas a primary key must be unique, stable and never null; use a system-generated StudentID. [Marking: (a) 2, (b) 3, (c) 3 (1.5 per reason)] |
| 6 | 6. Using the tables in Question 5, write SQL statements to: (a) list the names of all students taking “Chemistry” together with the lesson dates; (b) list each StudentID with the total fees paid, showing only students whose total exceeds 5000, sorted from highest to lowest total. (8 marks) | 6. (a) SELECT S.Name, L.Date FROM STUDENT S JOIN LESSON L ON S.StudentID = L.StudentID WHERE L.Subject = ‘Chemistry’ ORDER BY L.Date; (b) SELECT StudentID, SUM(Fee) AS Total FROM LESSON GROUP BY StudentID HAVING SUM(Fee) > 5000 ORDER BY Total DESC; [Marking: (a) SELECT / JOIN / WHERE 1 each; (b) GROUP BY 2, HAVING 2, ORDER BY 1] |
| 7 | 7. Five students in a class scored 60, 72, 68, 80 and 70 in a test. (a) Write an SQL statement to find the class average. (b) Calculate the average (1 decimal place). (c) Write an SQL statement to count how many students scored 70 or above, and state the result. (9 marks) | 7. (a) SELECT AVG(Score) FROM RESULT; (b) Total = 350; average = 350 ÷ 5 = 70.0. (c) SELECT COUNT(*) FROM RESULT WHERE Score >= 70; the result is 3. [Marking: (a) 3, (b) 3, (c) SQL 2 + answer 1] |
| 8 | 8. The following unnormalised record stores lesson data: (StudentID, StudentName, Subject, TutorName, TutorRoom, Date, Fee). Each student has one tutor per subject and each tutor always uses one room. (a) Identify one partial dependency and one transitive dependency. (b) Decompose the record into tables in third normal form (3NF), stating the primary and foreign keys. (c) Explain how normalisation reduces update anomalies. (9 marks) | 8. (a) Partial dependency: StudentName depends only on StudentID, part of the composite key (StudentID, Subject). TutorRoom depends only on TutorName. Transitive dependency: TutorName -> TutorRoom. (b) STUDENT(StudentID, StudentName) key StudentID; TUTOR(TutorName, TutorRoom) key TutorName; ENROL(StudentID, Subject, TutorName, Date, Fee) key (StudentID, Subject, Date) with foreign keys StudentID -> STUDENT and TutorName -> TUTOR. (c) Each fact is stored once, so a change touches one row and inconsistent copies (insert, delete and update anomalies) cannot arise. [Marking: (a) 3, (b) 4, (c) 2] |
Section C: Networks, Security and Data Validation (32 marks)
Section C
| # | Question | Answer and marking notes |
|---|---|---|
| 9 | 9. A network uses the IPv4 address 192.168.20.0 with subnet mask 255.255.255.192. (a) Write the CIDR notation. (b) Find the number of addresses per subnet and the number of usable hosts. (c) Write the network address and broadcast address of the first subnet. (d) Give one reason for subnetting. (8 marks) | 9. (a) /26, i.e. 192.168.20.0/26. (b) Host bits = 32 – 26 = 6; each subnet has 2^6 = 64 addresses and 62 usable hosts. (c) Network address 192.168.20.0; broadcast address 192.168.20.63. (d) It shrinks broadcast domains and improves security, administration and address utilisation. [Marking: (a) 2, (b) 2, (c) 2, (d) 2] |
| 10 | 10. A website processes logins with the code: sql = “SELECT * FROM USER WHERE Name = ‘” + name + “‘ AND Pwd = ‘” + pwd + “‘”. (a) Explain what happens when an attacker enters “admin’ –” as the name. (b) Name this vulnerability. (c) Suggest two defences, and explain what HTTPS does and does not protect in this situation. (8 marks) | 10. (a) The condition becomes Name = ‘admin’ –‘ AND Pwd = ‘…’; everything after — is a comment, so the password check disappears and the attacker logs in as admin without a password. (b) SQL injection. (c) Use parameterised queries / prepared statements so input is data, not code; add input validation, least-privilege database accounts and generic error messages. HTTPS only encrypts data in transit (stopping eavesdropping and man-in-the-middle attacks); it cannot stop SQL injection executed on the server. [Marking: (a) 3, (b) 1, (c) 4 (1.5 per defence + 1 for HTTPS)] |
| 11 | 11. An identification number uses nine digits d1 d2 … d9 plus a check digit d10, where S = sum of di × (11 – i) for i = 1 to 9 and d10 = (11 – S mod 11) mod 11 (10 is written as X). (a) For d1…d9 = 0, 3, 0, 6, 4, 0, 6, 1, 5, find S and the check digit. (b) State one type of error a check digit detects and one it cannot detect. (8 marks) | 11. (a) S = 0×10 + 3×9 + 0×8 + 6×7 + 4×6 + 0×5 + 6×4 + 1×3 + 5×2 = 130; S mod 11 = 9; d10 = 11 – 9 = 2, so the complete number is 0306406152. (b) It detects single-digit errors and most adjacent digit transpositions because the weights differ; it cannot detect two compensating errors that cancel in the weighted sum, and it cannot restore the correct digits. [Marking: (a) 4 (working 2, answer 2), (b) 4] |
| 12 | 12. (a) Convert the binary number 11011011 to decimal and hexadecimal. (b) The ASCII code of the letter A is 65; write it in 8-bit binary and in hexadecimal. (c) Using even parity, add a parity bit to the 7-bit data 1011001 and explain how the receiver detects a single-bit error. (8 marks) | 12. (a) 11011011 = 128+64+16+8+2+1 = 219, i.e. DB in hexadecimal. (b) 65 = 01000001 in binary = 41 in hexadecimal. (c) 1011001 already has four 1s (even), so the parity bit is 0 and 01011001 is sent; the receiver counts the 1s and an odd count reveals a single-bit error (detection only, no correction). [Marking: (a) 3, (b) 2, (c) 3] |
Find a tutor — freeTutor Registration →
A TutorZone practice paper; original questions and passages, not publisher material.