SQSS 2018 P2

1 SQSS 1NT EOY 2016 Computer Applications [Turn over o FOR EXAMINER’S USE Total 50 READ THESE INSTRUCTIONS FIRST DO NOT WRITE IN ANY BARCODES. Approved calculators are allowed. Answer all questions. All tasks must be done in the computer laboratory. You are not allowed to bring in or take out any pieces of work or materials on paper or electronic media or in any other form. Programs are to be written in Python. Save your work using the file name given in the question as and when necessary. The number of marks is given in brackets [ ] at the end of each question or part question. The total number of marks for this paper is 50. This document consists of 7 printed pages, including this cover page. [Turn over SHUQUN SECONDARY SCHOOL Computing 7155/02 Paper 2 Practical (lab-based) 13 September 2018 2 hours 30 minutes Additional Materials: Electronic version of LIBRARY.xlsx data file Electronic version of RAINFALL.py file Electronic version of BMI.py file Insert Quick Reference Glossary SHUQUN SECONDARY SCHOOL 2018 Preliminary Examination Secondary 4 Express Candidate Name: Candidate Index Number: Class:

2 SQSS 4E Prelim 2018 Computing [Turn_over Task 1 (10 marks) A community library uses a spreadsheet to record the books borrowed by its members. You are required to complete the setting up the spreadsheet to record the books that are overdue. Open the file LIBRARY. You will see the following data. Save the file as MYLIBRARY_<your name>_<centre number>_<index number>. 1 In cell B3 enter a function to return today’s date. [1] 2 The second to fifth digits of the Borrower ID indicates the date of birth of the borrower. For example, Borrower ID F19900762 indicates that the date of birth of the borrower is 1990. Enter a formula to calculate the age of the borrower and use it to complete the Age column. [2] 3 Use an appropriate function to search for the Item Type in the Loan Type table and use it to complete the Due Date column in the Borrowers List table. [2]

3 SQSS 4E Prelim 2018 Computing [Turn_over 4 Use a conditional statement to check if a borrowed item was overdue, if an item was overdue put the number of days overdue in the Days Overdue column else If an item had not been returned or was not due yet put a value of 0. Complete the Days Overdue column. [2] 5 If an item was overdue for more than 60 days a status value of SUSPENDED is shown in the Status column else if it was overdue for more than 30 days but less than or equal to 60 days, a status value of WARNING is shown. Otherwise an OK status value is shown [2] 6 In the Status column, format the cells to automatically show a red background when the status value is SUSPENDED and a yellow background when the status value is WARNING. [1] Save and close your file.

4 SQSS 4E Prelim 2018 Computing [Turn_over Task 2 (10 marks) The following program prompts the user