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

This document consists of 6 printed pages and 2 blank pages. IB10 11_9713_04/5RP © UCLES 2010 [Turn over *4232885159* 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 2010 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 2010 9713/04/O/N/10 Scenario You work for the International Business and ICT College. You have been supplied with some files which contain the information about the modules and tutors for a computer science diploma and the students who have applied for the course. The diploma consists of 4 compulsory modules and 1 of 5 optional modules. You will create an efficient data handling system in order to carry out administrative tasks including processing applications, producing reports and mail merging notifications. All documents required should be produced to a professional standard and suit the business context. You will provide evidence of your work, including screen shots, at various stages. Use a document file named: Centrenumber_candidatenumber_evidence.rtf e.g. ZZ987_82_evidence.rtf Make sure your Centre number, candidate number and name are included in the header of this document.

3 © UCLES 2010 9713/04/O/N/10 [Turn over You have been provided with the following files: COMPUTER SCIENCE DIPLOMA 2010.RTF NEW STUDENTS.CSV TUTORS.CSV NOTIFY.RTF Examine the contents of these files and consider how you would construct an efficient relational database. You will need to extract data from the relevant word processed document. 1 Import the data files into a database application. Ensure there is no unnecessary duplication of data in the tables. Ensure Boolean fields are formatted as Yes/No or Check Box. Include in your evidence document screen shots of the field formats and the primary keys used. [6] 2 Establish relationships between the tables to enable data to be selected from multiple tables. In order to establish suitable relationships you will need to add an index field manually to one or more tables. Include in your evidence document screen shots of the relationships created and for each relationship explain why this type is used. [13]

4 © UCLES 2010 9713/04/O/N/10 3 Prepare a report displaying all the students whose qualifications have not yet been checked. Display only the fields Surname, Forename and Student id in this order. Sort this data into ascending order of Surname. Provide evidence of your selection method in your evidence document. Name the report Unchecked Insert the text Please request evidence of the qualifications claimed by the following applicants: into the report header. Ensure your name, Centre number and candidate number are in the report footer. Print the report. [12] 4 Export the report to a word processing application. Convert the data into a table with visible gridlines. Insert a field with an automated file name into the footer of the document and ensure your name, Centre number and candidate number are also in the footer. Save the document as Unchecked Print the document. [6] 5 Produce letters to the applicants whose qualifications have been checked but have not yet been notified. Include evidence of your selection methods in your evidence document. You will need to customise the content of the letter according to whether their application has been approved. Use NOTIFY.RTF as the template for the mail merge. The document contains instructions which should be replaced with the fields as specified. Save the main document as Notifications in a format that will preserve field codes. Ensure your name, Centre number and candidate number are in the footer and print a copy of the main document showing all field codes. Ensure evidence of the use of the conditional fields is visible. Merge the data to a new document and print it. [22]

5 © UCLES 2010 9713/04/O/N/10 [Turn over 6 Tutors require class lists of students who will attend the optional module they teach. Prepare a report that will display a suitable prompt for the tutor surname and display a list of students for their optional modules. The report should show the tutor’s Surname, the Module Codes and provide the Student id, Title, Forename, Surname and Gender in this order. Sort this data into ascending order of Surname. Provide evidence of your selection method in your evidence document. Ensure your name, Centre number and candidate number are in the footer of the report. Save the report as Tutor Class List. Print the class list for Tutor Dikpah. [14] 7 The lists should also be supplied in a spreadsheet format so tutors may create their own mark sheets by adding extra columns when needed. Provide a list of students for Tutor Pugh from a spreadsheet in the following format: Tutors_Surname Module Code Student id Title Forename Surname Gender Grade Pugh Ensure your name, Centre number and candidate number are only visible in the header of the document. Include details of your export method in your evidence document. Print the document in portrait orientation and ensure all data and labels are visible. [5] 8 You are now required to create a form or switchboard to act as a menu. Users should be able to select the following functions: Display the report listing all applicants whose qualifications are yet to be checked (task3) Display a list of students to be notified about their applications (task 5) Display the report showing the option module class list for a tutor (task 6) Each item on the menu should be described in sufficient detail for any user to understand the functions. Provide evidence of the operation of each menu item in your evidence document. [12]

6 © UCLES 2010 9713/04/O/N/10 Write today’s date in the box below. Date

7 © UCLES 2010 9713/04/O/N/10 BLANK PAGE

8 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 2010 9713/04/O/N/10 BLANK PAGE

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the October/November 2010 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. • CIE will not enter into discussions or correspondence in connection with these mark schemes. CIE is publishing the mark schemes for the October/November 2010 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 A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 Mark Setup Primary keys [3] 1-Y/N, 2-Format, 3-all Boolean fields + evidence of format [3] [6] Relationships inc.Tutor IDs in Modules tbl (Names removed) [5] Correct relationships [4] use of: Module code ,tutor id, option Module. Remove name and add Tutor id Correct explanations (Type + why) [4] [13] Qualifications Evidence of correct selection [2] Report Surname, Forename, Id (Only & all Visible) [4] Surnames Ascending [2] "Unchecked" as title [2] Only from printout "Please request..." 100% correct in title [2] [12] Export Export to WP + Correct footer (full filename) [2] Convert to table with gridlines (H & V) [2] Adjust to correct layout( Spacing,Rows,Cols) [2] [6] Notifications Selection Qualifications checked ="True" [2] Query or filter only Notified ="False" [2]

Page 3 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 Letters Address fields(single lines,format,aligned,all) [4] Title, Surname & , ONLY + format [2] if then else meet/do not meet [2] use of "approved field" and inserted correctly if then else successful/unsuccessful [2] date format [1] Shown in printout [1] Correct 5 letters [5] Correct footers [1] [22] Tutor lists Selection Parameter & suitable prompt [2] Query or filter only Approved = true [2] Report Grouped by tutor [1] Grouped by Option Module [1] Id,Title,Forename,Surname,Gender ONLY [5] Ascending on Student Surname [1] Correct order of fields & all visible [1] Only from printout Correct footer [1] [14] Export Details of method [1] Correct Results and Layout [1] Grade Column added [1] Correct Gridlines [1] Name in header only [1] [5] Menu/Switchboard Suitable Title added [2] Evidence of valid actions [3] Correct 3 items [3] only if actions valid Suitable descriptions [3]

Page 4 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 Layout Layout & all visible [1] [12] [90]

Page 5 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 Setup Charges Linked [2] Cost formula [1] Sub-total formula [1] discount code linked [3] discount formula [3] total formula [1] Customer fields lookup [6] Hyperlink [2] Table saved [1] [20] Quotes data entry [4] non-blanks filter [2] Data linked [2] quote_22.rtf saved [1] data amended [2] quote republished [2] quote_22a.rtf saved [1] [14] Update letter Data amended [1] data linked [1] data source [1] merge fields [5] if-then field [1] condition 1 [1] condition 2 [1] updmain.rtf saved [1] valid selection method [1]

Page 6 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 merge to new document [1] updmerge.rtf saved [1] [15] Address labels data source Merge fields [5] label propagated [1] labelmain.rtf saved [1] Valid selection method [1] merge to new document [1] labelmerge.rtf saved [1] [10] Menu Title 4 items [4] suitable text [2] suitable explanations [4] links shown [4] Saved [1] [15] Option1 Macro written/recorded [8] AutoOpen named AutoOpen [2] Autolabels saved [1] Menu item added [2] explanatory text [2] MillsMenu2 saved [1] [16]

Page 7 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 04 © UCLES 2010 Option 2 User Guide Introduction 8 marks Loading 4 Selection 4 Examples 4 Troubleshooting info 4 16 Marks awarded for each section Full and clear coverage 4 Clear coverage, some gaps 3 Very brief coverage with gaps/errors 2 Minimal coverage, many errors 1 90