Cambridge A Level Information Technology (from 2017) 9626 — 2017 Feb/March Paper 1 · Variant 2

This document consists of 15 printed pages and 1 blank page. DC (NF) 134291/3 © UCLES 2017 [Turn over Cambridge International Examinations Cambridge International Advanced Subsidiary and Advanced Level * 0 0 4 1 9 6 7 7 1 5 * INFORMATION TECHNOLOGY 9626/12 Paper 1 Theory February/March 2017 1 hour 45 minutes Candidates answer on the Question Paper. No Additional Materials are required. READ THESE INSTRUCTIONS FIRST Write your Centre number, candidate number and name in the spaces at the top of this page. Write in dark blue or black pen. You may use an HB pencil for any diagrams, graphs or rough working. Do not use staples, paper clips, glue or correction fluid. DO NOT WRITE IN ANY BARCODES. Answer all questions. At the end of the examination, fasten all your work securely together. The number of marks is given in brackets [ ] at the end of each question or part question. Any businesses described in this paper are entirely fictitious.

2 9626/12/F/M/17 © UCLES 2017 1 Tick the four most accurate statements regarding the quality of information. P The more information that is collected the higher its quality. Data collected from a third party always becomes high quality information. The accuracy of the information collected affects its quality. Information which costs a lot to collect is always high quality information. A great deal of detail can sometimes lead to poor quality information. Irrelevant information leads to poor quality information. The problems arising from a lack of good quality information can be overcome by making your filters more refined. Good quality of information is always obtained from small groups of people. Information collected 100 years ago is always good quality information. The more complete the collected information is, the better its quality. [4] 2 Tick the four statements which most accurately refer to encryption. P Encryption is used by hackers to discover passwords. Encrypting a hard disk prevents viruses from deleting the information. Encryption always makes use of a public key and a private key. Encryption is the scrambling of the text in a message. Encryption is the process of encoding messages in such a way that only authorised parties can understand them. Encryption prevents messages from being intercepted. Encrypted information can only be understood if decrypted. Encryption has speeded up internet browsing by using secure sites. The recipient can only decrypt the message with the key provided by the originator. All memory sticks are encrypted. [4]

3 9626/12/F/M/17 © UCLES 2017 [Turn over 3 A school secretary has been handed some data by a student: Prasad, Anoushka, SR132516, 52, 01/09/2000 Explain why they are regarded as just items of data. In your explanation give a possible context for the data and describe how this would make the data become information. … … … … … … … … … … … [5]

4 9626/12/F/M/17 © UCLES 2017 4 There are many types of user interface in computerised systems. Three types are a graphical user interface, a dialogue interface and a gesture based interface. Evaluate these types of user interface. … … … … … … … … … … … … … … … … … … … [8]

5 9626/12/F/M/17 © UCLES 2017 [Turn over 5 Avtar is a recently appointed network manager in a school. In his role he will need to use a variety of utility software. (a) Describe, in detail, each of the following utilities. (i) anti-virus software: … … … … … … … … … [4] (ii) data compression: … … … … … … [3] (iii) disk defragmentation: … … … … … … [3]

6 9626/12/F/M/17 © UCLES 2017 (iv) disk formatting: … … … … … [2] (b) There are a number of computers in the school which are not networked at the moment. Discuss the advantages and disadvantages of creating a new network using these computers. … … … … … … … … … … … … … … … … … … … [8]

7 9626/12/F/M/17 © UCLES 2017 [Turn over 6 Amisha, the Management Information System (MIS) manager has just purchased some MIS software for her company. Explain how it can be used by Amisha to help the company run more efficiently. … … … … … … … … … … … … … … … [6]

8 9626/12/F/M/17 © UCLES 2017 7 Atat Iron Ltd uses computers to process its payroll. The company pays its workers weekly which involves the updating of a master file. Two sets of data are shown below. The first set represents part of a transaction file containing workers’ ID numbers and the hours worked by those workers in a particular week. The second set represents part of the master file used by the company. This shows the workers’ ID numbers, departments they work in and the rate per hour at which they are paid in Indian Rupees ( ). Transaction file Master file Workers_ID_number Hours_worked Workers_ID_number Department Hourly_rate ( ) 047006 40 031597 Extrusion 62 486439 40 047006 Cold rolling 55 592786 38 131654 Extrusion 62 758789 40 235804 Foundry 50 512759 37 239412 Foundry 50 869891 40 243303 Hot rolling 58 471584 38 471584 Cold rolling 55 131654 40 486439 Tube production 65 243303 40 500368 Extrusion 62 235804 35 512759 Tube production 65 592786 Foundry 50 758789 Tube production 65 869891 Extrusion 62 942378 Hot rolling 58 (a) Describe what processes must happen before the updating can begin. … … … … … [2]

9 9626/12/F/M/17 © UCLES 2017 [Turn over (b) Using this data, explain how a transaction file is used to update a master file in a payroll system. You may assume that the only transaction being carried out is the calculation of the weekly pay before tax and insurance deductions. … … … … … … … … … … … … … … [6]

10 9626/12/F/M/17 © UCLES 2017 (c) When a new worker is added to the master file, the data must be validated. His hourly rate will be 50 Rupees. Without using a type check, describe three other validation checks you would develop to make sure all the data entered is sensible. 1. … … … … … 2. … … … … … 3. … … … … … [6]

11 9626/12/F/M/17 © UCLES 2017 [Turn over 8 Below is part of a spreadsheet Jose has created to model his personal finances.  A B 1 $2,600 Wages/salary 2 3 $120 Utilities 4 $100 Satellite/TV 5 $80 Phone 6 $30 Pet food 7 $1,000 Mortgage/rent 8 $80 Internet 9 $100 Insurance 10 $75 Hair/beauty 11 $550 Groceries 12 $150 Eating out 13 $100 Clothing 14 $40 Cinema 15 $160 Car/travel 16 17 $2,585 Total outgoings 18 19 $15 Balance Using only the sort feature explain how you would be able to get the following values in the given cells. (i) $80 in cell A11 with Internet in cell B11 and $1,000 in cell A3. … … … … … … [3] (ii) $100 in cell A8 with Clothing in cell B8. … … … … … … [3]

12 9626/12/F/M/17 © UCLES 2017 9 Theresa has created a database query which searches for the surname “Johnson”. Surjit has told her that this is a static parameter query. Explain what is meant by static parameter query and why she should change to using a dynamic parameter query. … … … … … … … … … … … … … … [6]

13 9626/12/F/M/17 © UCLES 2017 [Turn over 10 Below is an extract from a database of students who have chosen to study Maths at AS Level. The database is in unnormalised (0NF) form. Student number Name Second subject Third subject 1001 Eashan Wadhwa Chemistry Physics 1002 Karan Bansal Physics Chemistry 1003 Manan Malik Chemistry Biology 1004 Neel Bhatia Biology Chemistry 1005 Ridhima Shekhar History Biology 1006 Rohin Pannu Geography History 1007 Saransh Anand History Geography 1008 Shraddha Aggarwal Biology Chemistry 1009 Rekha Pooja Physics Chemistry 1010 Sundar Rakshana Physics Chemistry Describe the characteristics of the data above which make it unnormalised and explain how it can be transformed into first normal form (1NF). … … … … … … … … … … … … … … [6]

14 9626/12/F/M/17 © UCLES 2017 11 Below is an extract from a spreadsheet showing the wages paid in one week to a group of workers. Their wages depend on the type of job they do as well as the number of hours worked. Cells C2 to E4 contain the data which is referred to in the lower part of the spreadsheet.  A B C D E F G 1 Standard rate per hour 2 L Labourer 55 3 Se Semi-skilled 65 4 Sk Skilled 75 5 6 Worker ID Job Code Job type Hours worked this week Rate per hour paid Wage paid this week 7 8 12478L L Labourer 40 55 2200 9 13691R Se Semi-skilled 38 65 2470 10 21463R Se Semi-skilled 40 65 2600 11 29816M L Labourer 36 55 1980 12 36479L Sk Skilled 34 75 2550 13 49786R Sk Skilled 40 75 3000 14 52376M Se Semi-skilled 36 65 2340 15 18964L L Labourer 35 55 1925 16 13578R L Labourer 40 55 2200 17 38945M L Labourer 40 55 2200 18 36249M Se Semi-skilled 40 65 2600 19 (a) Write down the formula which should be used in cell C8 in order to display the Job type using the Job Code in the preceding column. The formula should be in a form that can be easily replicated. = … … [5] You can use the space below for any working you need.

15 9626/12/F/M/17 © UCLES 2017 (b) The spreadsheet contains different formulae in different columns. Explain the other formulae used in the spreadsheet which enable the data in the Wage paid this week column to be displayed. The formulae should be in a form so that they can be easily replicated. … … … … … … … … … … … … … … [6]

16 9626/12/F/M/17 © UCLES 2017 BLANK PAGE 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 International Examinations Copyright Acknowledgements Booklet. This is produced for each series of examinations and is freely available to download at www.cie.org.uk after the live examination series. Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of Cambridge Local Examinations Syndicate (UCLES), which is itself a department of the University of Cambridge.

® IGCSE is a registered trademark. This document consists of 9 printed pages. © UCLES 2017 [Turn over Cambridge International Examinations Cambridge International Advanced Subsidiary and Advanced Level INFORMATION TECHNOLOGY 9626/12 Paper 1 Theory March 2017 MARK SCHEME Maximum Mark: 90 Published This mark scheme is published as an aid to teachers and candidates, to indicate the requirements of the examination. It shows the basis on which Examiners were instructed to award marks. It does not indicate the details of the discussions that took place at an Examiners’ meeting before marking began, which would have considered the acceptability of alternative answers. Mark schemes should be read in conjunction with the question paper and the Principal Examiner Report for Teachers. Cambridge will not enter into discussions about these mark schemes. Cambridge is publishing the mark schemes for the March 2017 series for most Cambridge IGCSE®, Cambridge International A and AS Level components and some Cambridge O Level components.

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 2 of 9 Question Answer Marks 1 The more information that is collected always improves its quality. Data collected from a third party always becomes high quality information. The accuracy of the information collected affects its quality. 3 Information which costs a lot to collect is always high quality information. A great deal of detail can sometimes lead to poor quality information. 3 Irrelevant information leads to poor quality information. 3 The problems arising from a lack of good quality information can be overcome by making your queries more refined. Good quality of information is always obtained from small groups of people. Information collected 100 years ago is always good quality information. The more complete the collected information is, the better its quality. 3 4 Question Answer Marks 2 Encryption is used by hackers to discover passwords. Encrypting a hard disk prevents viruses from deleting the information. Encryption always makes use of a public key and a private key. Encryption is the scrambling of the text in a message. 3 Encryption is the process of encoding messages in such a way that only authorised parties can understand them. 3 Encryption prevents messages from being intercepted. Encrypted information can only be understood if decrypted. 3 Encryption has speeded up internet browsing by using secure sites. The recipient can only decrypt the message with the key provided by the originator. 3 All memory sticks are encrypted. 4

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 3 of 9 Question Answer Marks 3 Five from: They are just a collection of text, numbers and symbols« «with no meaning A possible context is that the data is about the student and represents« «their family name, first name, student id, their latest maths mark and their date of birth (three marks for five reasonable fields, two marks for four reasonable fields, one mark for two or three reasonable fields) 5 Question Answer Marks 4 Eight from: Users with physical handicaps may not be able to use a keyboard or mouse« «or control their limbs accurately« «could speak into a microphone to control a device For reasons of hygiene a doctor may not be allowed to touch a display or device Appropriate gestures/speaking into a microphone are/is a more hygienic way to control the device Gestures may be a quicker way of initiating a response from a device Certain gestures may not be socially acceptable which is not a problem with the other interfaces Some gestures may be unintentional but still initiate a reaction from the device Dialogue interface allows hands free control ensuring safety when driving« «whereas gestures/GUI would need at least one hand to leave the steering wheel Dialogue interface can be more expensive to develop compared with a GUI Dialogue interface may not operate reliably if there is background noise or user has a cold/strong dialect/accent« «whereas GUI/gestures will still be reliable GUIs tend to be more accurate than gestures/dialogue Dialogue interface often requires a training session with user« «GUI/gestures can be taught through manuals Gesture based is less effective when several users/background movement involved One mark is available for an appropriate reasoned opinion 8

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 4 of 9 Question Answer Marks 5(a)(i) Anti-virus software – Four from: Software used to prevent, detect and remove malicious software Can protect from: malicious Browser Helper Objects (BHOs), browser hijackers, ransomware, keyloggers, backdoors, rootkits, Trojan horses, worms, malicious LSPs, dialers, fraudtools, adware and spyware (must have at least two) Signature-based detection compares the contents of a file to its database of known malware signatures Heuristic-based detection detects malware based on characteristics typically used in known malware code Behavioural-based detection: is based on the behavioural fingerprint of the malware at run-time« «is only able to detect malware after they have starting doing their malicious actions Sandbox detection based on behavioural-based detection« «but doesn’t detect the behavioural fingerprint at run time« «it executes the programs in a virtual environment logging what actions the program performs Gives user options to delete or quarantine files/asks if user wants virus deleted Scans computer/files/disk for viruses 4 5(a)(ii) Data compression – Three from: Encoding information using fewer bits than the original representation Can be either lossy or lossless Lossless compression reduces number of bits by identifying repeated patterns of data No information is lost in lossless compression Lossy compression reduces number of bits by identifying unnecessary information Storage space required for file/file size is reduced 3 5(a)(iii) Disk defragmentation – Three from: Organises the contents of the disk into the smallest number of contiguous regions/fragments Attempts to create larger regions of free space using compaction Some defragmentation utilities try to keep smaller files within a single directory together The movement of the hard drive's read/write heads over different areas of the disk when accessing fragmented files is slower« «compared to accessing the entire contents of a non-fragmented file sequentially 3 5(a)(iv) Disk formatting – Two from: Prepares a data storage device for initial use Organises the tracks on a disk into sectors A new disk medium is fully prepared in order to store files The first stage is low-level formatting followed by« «partitioning which makes the data storage device visible to an operating system« followed by high-level formatting which generates a new file system 2

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 5 of 9 Question Answer Marks 5(b) To be marked as a level of response: Level 3 (7–8 marks) Candidates will analyse and evaluate both sides of the argument and discuss/consider different reasons as to why the computers should be networked or not The issues raised will be justified The information will be relevant, clear, organised and presented in a structured and coherent format Subject specific terminology will be used accurately and appropriately There will need to be a reasoned conclusion to gain full marks Level 2 (4–6 marks) Candidates will analyse and evaluate both sides of the argument and discuss/consider different reasons as to why the computers should be networked or not although development of some of the points will be limited to one aspect of the question For the most part the information will be relevant and presented in a structured and coherent format Subject specific terminology will be used appropriately and for the most part correctly There may be a conclusion Level 1 (1–3 marks) Candidates may only address one side of the argument, and give basic reasons Answers may be simplistic with little or no relevance There will be little or no use of specialist terms Level 0 (0 marks) Response with no valid content Advantages may include e.g.: Easier for students to share work/files in group projects Easier for network manager to roll out new software Easier for teachers to monitor students when working Peripherals can be shared so fewer needed leading to lower cost in the future Can access the internet more easily School intranet would become available to all students more frequently Disadvantages may include e.g.: Easier for students to share work which could lead to copying Bigger server may be required to cope with extra computers so more expensive Extra network points may be needed which leads to more cost If the server breaks down student work might become inaccessible Viruses are easier to transmit from one computer to another 8

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 6 of 9 Question Answer Marks 6 Six from: Provides past, present and prediction information Helps in decision making MIS manager typically analyses business problems MIS manager designs and maintains computer applications to solve the organisation's problems Helps with project management Managers use management information systems to gather and analyse information about various aspects of the organisation« «such as personnel, sales, inventory and production Management information systems are used to create reports on aspects such as sales, revenue and production Management information systems are used to create charts (on aspects such as sales, revenue and production) These reports are provided at regular intervals to managers at all levels to help them evaluate their companies' performance By comparing daily, weekly or monthly reports to previous reports, managers are able to spot trends, such as revenue growth or reduction By creating charts, can see trends such as revenue growth or reduction 6 Question Answer Marks 7(a) Two from: Transaction file is validated Transaction file must be sorted« «in same order as master file/sorted on Workers ID number 2 7(b) Six from: First record in the transaction file read belonging to 047006 Reads first record in the old master file belonging to 031597 These two records are compared If records do not match computer writes master file record to new master file Records do not match so next record of master file is read 047006 If it matches transaction is carried out Computer calculates the pay rate of pay × no. of hours worked, 55 × 40« «using rate of pay 55 from master file« «using hours worked 40 from transaction file Processed record is written to new master file Next record 131654 is read from transaction file then compared to next master file record 131654 This continues until the last record from the transaction file record 869891 is read After processing the last record of the transaction file 869891 all the remaining old master file records are written to the new master file in this case, one record 942378 6

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 7 of 9 Question Answer Marks 7(c) Three matched pairs from: Length check on Workers ID number 1 Must be exactly 6 characters long 1 Lookup check on Department 1 Must be one of Foundry, Cold rolling, Tube production, Extrusion or Hot rolling 1 Range check on hourly rate 1 E.g. be between 50 and 65 1 Question Answer Marks 8(i) Three from: Select A3:B15« «Sort in descending order of column A« «Add a level« «Sort in ascending order of column B 3 8(ii) Three from: Select A3:B15« «Sort in ascending/descending order of column A «Add a level «Sort in ascending order of column B 3 Question Answer Marks 9 Static parameter query is a query that is fixed 1 Five from: Every time that the query is run it will search for the surname “Johnson” If Theresa wants to look for another surname she will need to open up the query in design view« «and change the surname criteria to that name With a dynamic parameter query she could type in a different surname/data each time Every time the query is run a dialogue box would appear asking her to type in the surname This would save the time of designing the query every time she wanted to find a surname 5

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 8 of 9 Question Answer Marks 10 Two from: Data such as Second subject and Third subject is repeated Data is non-atomic as Name can be further subdivided Four from: Split name into first name and second name Each record to have two entries, one per subject e.g. Student number First name Second name Subject 1001 Eashan Wadhwa Chemistry 1001 Eashan Wadhwa Physics There will still be columns with the same data in some rows but each row as a whole will be unique Create a new table called subject Each subject would have to have a unique id Fields would be subject name and subject_id 6 Question Answer Marks 11(a) =VLOOKUP(B8,C$2:D$4,2) VLOOKUP() – 1 mark B8, – 1 mark C2:D4 – 1 mark 2 – 1 mark Use of appropriate absolute referencing – 1 mark 5

9626/12 Cambridge International AS/A Level – Mark Scheme PUBLISHED March 2017 © UCLES 2017 Page 9 of 9 Question Answer Marks 11(b) Six from: In order to calculate the wage paid this week the Rate per hour paid has to be known then is multiplied by the hours worked this week– 1 mark Cell E8 should contain the rate per hour paid for a labourer – 1 mark =VLOOKUP(B8,C$2:E$4,3) VLOOKUP(B8«) – 1 mark C2:E4 – 1 mark 3 – 1 mark Use of appropriate absolute referencing in VLOOKUP – 1 mark Involves looking up Job Code in cells C2 to E4 and returns rate per hour from third column/column E Cell F8 should contain wage paid this week which is hours worked multiplied by rate per hour – 1 mark D8*E8 – 1 mark 6