2021 NASS Computing P2

Name: Register no: Class: NGEE ANN SECONDARY SCHOOL O PRELIMINARY EXAMINATION COMPUTING 7155/02 Paper 2 (Lab-based) 1 August 2021 2 hr 30 min Additional materials: Electronic version of LIBRARY.xlsx file Electronic version of SANDWICH.py file Electronic version of FACTORIAL.py file Insert Quick Reference Glossary Instructions to Candidates Write your name, register number and class at the top of this page. 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. For Examiner’s Use

2 NAS/2021/PRELIM/4E/COMPUTING(7155/02) Checked by student: _____________________ Date: _________ This document consists of 9 printed pages and 1 blank page. Task 1 A private library uses spreadsheet to keep a record of its books, including generation of book IDs, location of books and their loan status. Open the file LIBRARY.xlsx. You will see the following data. Save the file as LIBRARY_<class>_<index number>_<your name>.xlsx 1 In cell G1, enter an appropriate formula that will automatically generate the current date. [1] 2 The Book ID is generated by using the function CONCATENATION. The Genre, Shelf ID and a random 5 digit number are joined together using CONCATENATION. In cell C25, use the CONCATENATION function together with an appropriate function to generate a Book ID. [1]

3 NAS/2021/PRELIM/4E/COMPUTING(7155/02) 3 Each book loan has a duration of 14 days. An extension on the book loan adds another 7 days. Books that are not loaned out will have a dash ‘-’ in their Loan Expiry Date. In cells G4 to G25, enter a formula that uses appropriate functions to show the Loan Expiry Date. If the book is not on loan, the Loan Expiry Date should show only a dash ‘-’. [2] 4 In cells H4 to H25, enter a formula that uses appropriate functions to show the status of the book. “On-Loan” for books loaned out but not due. “Overdue” for books loaned out and passed the loan expiry date. “available” for books not loaned out. [2] 5 In cells H4 to H25, use a formatting tool to change the colour of the status. Yellow for books On-Loan Red for books Overdue Green for books available [1] 6 The first 5 characters of the Book ID indicates which shelf the books are kept. In cells I4 to I25, enter a formula that uses appropriate functions to show the shelves where the books should be. [2] 7 The Inventory table shows the total number of books that the library has for each Genre. In cells L23 to L28,