Cambridge A Level Information Technology (from 2017) 9626 — 2023 Oct/Nov Paper 2 · Variant 0

This document has 8 pages. Any blank pages are indicated. [Turn over Cambridge International AS & A Level INFORMATION TECHNOLOGY 9626/02 Paper 2 Practical October/November 2023 2 hours 30 minutes You will need: Candidate source files (listed on page 2) INSTRUCTIONS ● Carry out every instruction in each task. ● Save your work using the file names given in the task as and when instructed. ● You must not have access to either the internet or any email system during this examination. ● You must save your work in the correct file format as stated in the tasks. If work is saved in an incorrect file format, you will not receive marks for that task. INFORMATION ● The total mark for this paper is 90. ● The number of marks for each question or part question is shown in brackets [ ]. DC (LK/CT) 315812/3 © UCLES 2023 * 5 8 8 3 0 6 9 5 2 6 *

2 9626/02/O/N/23 © UCLES 2023 You have been supplied with the following source files: n23climate.csv n23soil.csv You will create a spreadsheet for a quantity surveyor to calculate the amount of concrete required for the foundations for a wall or building. You must use the most efficient method to solve each task. All documents produced must be of a professional standard and suit the business context. All costs must be displayed in dollars ($). 1 metre = 1000 millimetres. 1 Create a new spreadsheet that looks like this: A 1 2 3 4 5 6 7 8 9 10 11 12 Freestanding wall Building Soil type: Climate type: Length of wall: Enter in metres in metres in metres in metres cubic metres 2 Y 13 14 15 16 17 18 Width: Depth: Length: Volume: Concrete cost: Length of building: Width of building: Enter in metres Enter in metres in metres in metres in metres cubic metres 4 3 Width: Depth: Length: Volume: Concrete cost: B C D E F G QS-Assist What are you constructing? By A Candidate, ZZ999, 9999 Soil and climate data entry Cost calculator – freestanding brick walls Freestanding wall data entry Foundations – footings Cost calculator – rectangular buildings Data entry Foundations – footings Merge cells as shown. Centre align all cells in columns B and F. Place your name, centre number and candidate number in the merged cell E2:G2. Save your spreadsheet with the file name QSA_ followed by your centre number_candidate number, e.g. QSA_ZZ999_9999 [4] 2 Examine the data in the files: n23climate.csv n23soil.csv Import these files into your workbook, using appropriate worksheet names. The data in these worksheets will be used by formulae you create in your main spreadsheet. Format the Frost factor data as percentage values. [2]

3 9626/02/O/N/23 © UCLES 2023 [Turn over 3 In the worksheet created in step 1 make sure that cells B3 and B4 can only contain a blank cell or the letter Y in upper or lower case. Add appropriate text for the user. Do not include a drop down menu. [6] 4 In cells B6 and B7 make sure that users can select from drop down lists the soil type and climate type. Use the worksheets imported in step 2. [7] 5 Enter a function in cell B14 to look up the width of the footing in metres. Use the soil data imported in step 2. [7] 6 Enter a function in cell B15 to look up the depth of the footing in metres. Use the soil data imported in step 2. [3] 7 Edit the formula placed in step 6 to increase the depth of the footing by the Frost factor percentage. Use the climate data imported in step 2. Depth of footing = Value depending on soil type × (1 + Frost factor percentage) [7] 8 Enter a formula in cell B16 to calculate the length of the footing in metres. Length of footing = Length of the wall + Width of the footing – 0.1 [1] 9 Enter a formula in cell B17 to calculate the volume of the footing rounded up to the nearest 0.1 m3. Volume = Width × Depth × Length [3] 10 Place formulae in cells F14 and F15 to copy the values from cells B14 and B15. [1] 11 Enter a formula in cell F16 to calculate the length of the rectangular footing in metres. Length of footing = 2 × (Length of the building + Width of the building) – 0.4 [1] 12 Enter a formula in cell F17 to calculate the volume of the footing rounded up to the nearest 0.1 m3. Volume = Width × Depth × Length [1] 13 Enter formulae in cells B18 and F18 to calculate the cost of the concrete, rounded to and displayed to the nearest dollar. Concrete costs $124.3654 per cubic metre. [3] 14 Edit the formulae placed in steps 5 to 13 so that if the soil is not suitable for strip foundations appropriate messages are displayed in these cells. [4]

4 9626/02/O/N/23 © UCLES 2023 15 Place formulae in your spreadsheet so that when the letter Y is placed in cell: • B3, the contents of cells in the range E8:G18 are not visible • B4, the contents of cells in the range A8:C18 are not visible. Save this spreadsheet as QSA1_ followed by your centre number_candidate number, e.g. QSA1_ZZ999_9999 [8] 16 Test your spreadsheet with the following test data. Save each test as a spreadsheet with the given file name followed by your centre number_candidate number, e.g. QSA2_ZZ999_9999 File name and respective Test data QSA2_ A freestanding wall 12 metres long built on gravel in a climate that is never frosty. QSA3_ A building 4 metres long and 3 metres wide built on soft chalk in a climate that is never frosty. QSA4_ A building 4 metres long and 3 metres wide built on granite in a climate that is never frosty. QSA5_ A building 4 metres long and 3 metres wide built on granite in a climate that is sometimes frosty. [11] 17 There is a problem in the design of this spreadsheet. Identify this problem. Enter in cell A20 of QSA5 what the problem is and how you would resolve this problem. Note you do not have to correct the problem in your spreadsheet. Save the new spreadsheet as QSA6_ followed by your centre number_candidate number, e.g. QSA6_ZZ999_9999 [3]

5 9626/02/O/N/23 © UCLES 2023 The quantity surveyor provides services for each customer, such as calculating the amount and cost of concrete or the number of bricks required for a project. He sends an invoice to customers when he has finished the work to ask for payment. The quantity surveyor wants to use a database to store details of each invoice and the customer the invoice will be sent to. Each customer can be sent more than one invoice, but each invoice is sent to only one customer. Each invoice: • has a unique invoice number • contains the name and address of the customer • identifies the project • contains details of the service provided for the project • contains the cost of this service. 18 Draw a conceptual entity relationship diagram for this database. Save this in portable document format as ERD_ followed by your centre number_candidate number, e.g. ERD_ZZ999_9999 [18]

6 9626/02/O/N/23 © UCLES 2023 BLANK PAGE

7 9626/02/O/N/23 © UCLES 2023 BLANK PAGE

8 9626/02/O/N/23 © UCLES 2023 Permission to reproduce items where third-party owned material protected by copyright is included has been sought and cleared where possible. Every reasonable effort has been made by the publisher (UCLES) to trace copyright holders, but if any items requiring clearance have unwittingly been included, the publisher will be pleased to make amends at the earliest possible opportunity. To avoid the issue of disclosure of answer-related information to candidates, all copyright acknowledgements are reproduced online in the Cambridge Assessment International Education Copyright Acknowledgements Booklet. This is produced for each series of examinations and is freely available to download at www.cambridgeinternational.org after the live examination series. Cambridge Assessment International Education is part of Cambridge Assessment. Cambridge Assessment is the brand name of the University of Cambridge Local Examinations Syndicate (UCLES), which is a department of the University of Cambridge. BLANK PAGE