Cambridge A Level Applied Information and Communication Technology 9713 — 2011 Oct/Nov Paper 4 · Variant 1

This document consists of 3 printed pages and 1 blank page. IB11 11_9713_04/2RP © UCLES 2011 [Turn over *3544735655* UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS General Certificate of Education Advanced Subsidiary Level and Advanced Level APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/04 Paper 4 Practical Test October/November 2011 2 hours 30 minutes Additional Materials: Candidate Source Files READ THESE INSTRUCTIONS FIRST Make sure that your Centre number, candidate number and name are written at the top of this page and are clearly visible on every printout, before it is sent to the printer. DO NOT WRITE IN ANY BARCODES. Carry out every instruction in each task. At the end of the exam put this Question Paper and all your printouts into the Assessment Record Folder. 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 © UCLES 2011 9713/04/O/N/11 You are working for RockICT Concert Travel. You are required to modernise the existing data handling system. All documents published must be of a professional standard and suit the business context. 1 (a) You are required to provide evidence of your work, including screen shots at various stages. Each screen shot should clearly show the relevant evidence. Create a document named: CentreNumber_CandidateNumber_Evidence.rtf e.g. ZZ999_99_Evidence.rtf Place your name, Centre number and candidate number in the header of your evidence document. (b) At present, all customer details including credit card information is contained in a single flat file database. The data from the flat file database has been exported as CUSTOMER DETAILS.CSV The 1st four digits of the 16 digit credit card number represent the code for the issuing bank. Extract these four digits into a new column headed CCIssuer. Include evidence of your method in your evidence document. (c) For security reasons you are required to create a relational database. Ensure that customer details, credit card details and issuing bank codes are in separate tables. Details of the issuing bank codes are contained in CCISSUERS.CSV Include details of the tables and explanations of the relationships created in your evidence document. [20] 2 The Bank of Tawara has changed some conditions of use. It no longer provides free card replacement whilst travelling or fraud protection for use outside the EU. Use the file NEW BOOKINGS.CSV to identify those customers who have already used a Bank of Tawara card to book a concert trip in 2012. Include extensive detail of your method in your evidence document. Present the data in a Report entitled Bank of Tawara Notifications. Include only the fields: Customer Id, GivenName, Surname and Destination. Group the report by Destination and sort in alphabetical order by Surname. Ensure your name, Centre number and candidate number appear immediately below the data and there is no page footer. Print the report and export it as NOTIFICATIONS.RTF [30]

3 © UCLES 2011 9713/04/O/N/11 3 (a) You are required to automate the inclusion of the report in a word processed document. Open NOTIFICATIONS.RTF Create a macro to: • copy the title and data from NOTIFICATIONS.RTF • open ADMIN NOTES.RTF • go to the Insert_Here bookmark • paste the title and data • print the document. (b) Ensure the macro contains no unnecessary instructions or code. Include evidence of the macro in your evidence document. (c) Run the macro to produce the document. [15] 4 (a) You are now required to mail merge letters to the customers who have already booked using Bank of Tawara credit cards, informing them of the changes to the conditions of use. Include details of your selection method in your evidence document. Use the TAWARA CUSTOMER.RTF as the main document and insert the mergefields as specified. Place your name, Centre number and candidate number where shown. Use a conditional field to insert the following text for those customers with Non-EU destinations only: You will also have no protection against fraudulent use. (b) Print a copy of the merge document showing all the field codes. Perform the mail merge to create the new document. Save and print the new document. [25] Write today’s date in the box below. Date

4 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. University of 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. © UCLES 2011 9713/04/O/N/11 BLANK PAGE

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the October/November 2011 question paper for the guidance of teachers 9713 APPLIED ICT 9713/04 Paper 4 (Practical Test B), maximum raw mark 90 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 must be read in conjunction with the question papers and the report on the examination. • Cambridge will not enter into discussions or correspondence in connection with these mark schemes. Cambridge is publishing the mark schemes for the October/November 2011 question papers for most IGCSE, GCE Advanced Level and Advanced Subsidiary Level syllabuses and some Ordinary Level syllabuses.

Page 2 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – October/November 2011 9713 04 © University of Cambridge International Examinations 2011 Mark 1 (b) Separate Bank Codes and Customer Codes from the given credit card numbers Evidence of: Text to Cols, Left(), Right(), parse import etc. 5 (c) Create a relational database from the given files Create table of Customer details Customer name and contact details only – No Ccard info (Bank or Customer number) 2 Primary Key (Customer Id) 1 Create table of Credit Card data Just CC info (no personal data) – Bank Code – Bank Name 3 Primary Key (Customer Id) 1 Create table of credit card issuers Just CCI info (Code & Bank Name) 1 Primary Key (Code) 1 Create suitable relationships between the tables e.g. Customer Details[Customer id]– Cust.Ccard[Customer id] 1 e.g. CCIssuer[Code]–Cust.Ccard[CCIssuer] 1 Explanation of why One-to-One relationship is appropriate 2 Explanation of why Many-to-One relationship is appropriate 2 Create suitable relationships between the tables [20] 2 Create a report Create table of "New Bookings" data New Table (2 fields & Primary Key = Customer Id) 3 New Relationships with Cdetails & Ccard tables 2 Evidence of suitable Query 1 Evidence of selection of correct Fields (inc. "hidden" fields) 2 Evidence of correct criteria (5102 or *Taw* etc.) 1 Print & export the report Correct fields shown 4 Correct Grouping ( EU and Non-EU) 2 Correct ordering (Ascending Surname) 2 Correct results 5 Candidate details in correct place ("Report" footer) 1 No "Page" footer shown 1 Correct Title (Bank of Tawara Notifications) accuracy marks 2 All labels and data visible 4 Print & export the report [30]

Page 3 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – October/November 2011 9713 04 © University of Cambridge International Examinations 2011 3 (a) Create a Macro to include the exported report in a document Evidence of Macro created 1 Evidence of code to "Select and Copy" from exported report 2 Evidence of code to "open" correct file (code to "Activate Window"=1 mark only) 2 Evidence of code to "find bookmark" 1 Evidence of code to paste & format data in correct place 2 Evidence of code to Print correct document 1 (b) Evidence of the removal of unnecessary code from subroutines) 5 (c) Correct Printout provided 1 [15] 4 (a) Select recipients for a mail merge Evidence of selection of recipients by criteria ( e.g. Database query or Word processor filter) 1 Inclusion of correct additional fields (Address, City, Post code) 1 Evidence of correct selection criteria (5102 or *Taw* etc.) 1 (b) Show evidence of the inclusion of the correct field codes Printout showing: Datefield, Mergefields, Conditional field (Screenshot = –1) 3 Correct Conditional field parameters (e.g. Destination=Non-EU, resultant text as given, Null text) 4 Perform the mail merge Date shown & correct format 2 GivenName & Surname shown with correct spacing 3 All Address fields shown 3 One Address field per line 1 Printed documents for correct recipients 5 Candidate details in correct place on documents 1 Perform the mail merge [25] [90]