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

This document consists of 4 printed pages. IB09 11_9713_04/5RP © UCLES 2009 [Turn over *0000000000* 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 2009 2 hours 30 minutes Additional Materials: Candidate Source Files READ THESE INSTRUCTIONS FIRST Make sure that your Centre number, candidate number and name are clearly visible on every printout, before it is sent to the printer. Carry out every instruction in each task. Before each printout you should proof-read the document to make sure that you have followed all the instructions correctly. At the end of the assignment put 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 2009 9713/04/O/N/09 [Turn over Scenario You work for a company called “Luxury Power Boats” which organises cruises. At the moment the data concerning the boats for hire is held in several spreadsheets. The company would like you to create a relational database with automated features for sorting, searching, producing reports and mail merging documents. The required output includes a menu or switchboard and the exporting of data to a word processing application. The files you will need for this are: BOATNAMES.CSV BOATSPECS.CSV ENQUIRIES.CSV ENQUIRYREPY.RTF PB.JPG ROUTES.CSV The data is supplied in the csv files. You will need to organise the data using the most efficient and appropriate structure. You are required to provide evidence of your work, including screen shots, at various stages. Use a document file named: Centrenumber_candidatenumber_evidence.rtf e.g. ZZ987_0082_evidence.rtf Make sure your Centre number, candidate number and name are included in the header of this document.

3 © UCLES 2009 9713/04/O/N/09 [Turn over 1 You are going to create a relational database. Open the csv files in a spreadsheet application to inspect and manipulate them. Note that: • HireP/W is the charge for hiring the boat for a week • Guests is the maximum number of passengers which the boat will accommodate • some data appears in more than one table. Import the data files into a database application ensuring: • fields have appropriate data types • tables have appropriate primary keys. Include in your evidence document screen shot(s) showing the design of each table. [12] 2 Establish relationships between the tables to enable efficient searches that display data from more than one table. Include in your evidence document screen shot(s) showing the relationships you created between tables. [3] 3 In your evidence document give details of the type of each relationship created and explain why this type of relationship is appropriate. [9] 4 Create a search for a customer enquiry. When run, this will display a suitable prompt for the Region to be visited. The search should display the results for all boats in the chosen Region Include the Region, BoatModel, HireP/W, BoatName, and all the fields in the BoatSpecs table. Save this search as Cruise Enquiry Include in your evidence document a screen shot showing the search criteria. [5] 5 Prepare a grouped report in portrait layout for this search. Include the fields Region, Guests, BoatModel, BoatName, Length, Speed, HireP/W, Crew and Air-Con in this order. Ensure that: • the Region is listed only once • the report is further grouped by the number of Guests sorted into descending order • each group of Guests is sorted into descending order of Length then descending order of Speed • all labels and data are fully visible • the report has the heading Cruise Enquiry • your name, Centre number and candidate number are in the footer of the report. Run the report for the Ionian region. Print the report. [13]

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 2009 9713/04/O/N/09 6 Prepare a report in portrait layout for all the cruises available. Include Region, BoatModel, BoatName and HireP/W Group the report by HireP/W and sort the data into ascending order of Region Ensure that: • all labels and data are fully visible • the report has the heading Regional Rates • your name, Centre number and candidate number are in the footer of the report. Print the report. [10] 7 Prepare a report to list the boats available for a customer. The report should prompt for the ContactName and display only the ContactName, Region, BoatModel, BoatName, and HireP/W fields. Group the report appropriately to ensure that the ContactName and Region are listed once only. The other data should be sorted in ascending order of HireP/W Ensure that: • all labels and data are fully visible • the report has the heading Charter Enquiry • your name, Centre number and candidate number are in the footer of the report. Save the report as Charter Enquiry [8] 8 Create a menu to make all three reports available to a user. Give each menu option an appropriate label and ensure that the reports open in preview mode. Include a screen shot of your menu in your evidence document. Include screen shot(s) of your code, macro or switchboard items table in your evidence document. [6] 9 Run the Charter Enquiry report for Ms Mattche. Export the report as MATTCHE.RTF Open the file in a suitable application. Add the company logo PB.JPG to the top right of the page. Ensure your name, candidate number and Centre number are only included in the footer of the document. Save and print the document. [4] 10 Using the file ENQUIRYREPLY.RTF as a mail merge template, prepare a letter to be sent to each customer with details of the cruise they requested. Where the customer has requested jet skis the following text should automatically be inserted Please note that due to new regulations jet skis are no longer available. Ensure your name, candidate number and Centre number are included in the footer of the document. Print a copy of the document showing the field codes. Merge the letters to a new document and print the letters. [20]

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the October/November 2009 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 2009 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 2009 9713 04 © UCLES 2009 BoatNames P Key = BoatName/P Key = ID (1) [2] BoatModel = Text, Others = Integer [1] Air-Con = Y/N T/F (step 5) [1] Boatspecs P Key = BoatModel/P Key = ID (1) [2] Transposed [1] Field Y/N (+Format) [2] Enquires P Key = ContactName/P Key = ID (1) [2] 1 Import data Routes P Key = Region or ID [1] [Total: 12] BoatSpecs BoatNames [1] BoatNames Routes [1] 2 Relationships Enquiries Routes or BoatNames [1] [Total: 3] On BoatModel [1] 1 – Many [1] BoatSpecs to BoatNames >1 Boat each Spec [1] On Region [1] 1 – Many [1] BoatName to Routes >1 Boat each Region [1] On Region [1] 1 – Many [1] Enquiries to Routes >1 enquiry each Region [1] On Region (1) Indeterminate (1) 3 Explain or Enquiries to BoatNames Why (1) [Total: 9]

Page 3 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2009 9713 04 © UCLES 2009 Region, BoatModel, HireP/W , BoatName Length, Speed, Crew ,Guests, Air-Con, Double, Twin, [1] Correct Fields 2nd BoatModel (missing) [1] Region Parameter [?] [1] Region Prompt Suitable [1] 4 Search Name Cruise Enquiry [1] [Total: 5] Grouping Region [1] Guests (Descending) [1] Sorted Length (Descending) [1] Speed (Descending) [1] Report Correct Fields [1] Region, Guests, BoatModel, BoatName Order Length, Speed, HireP/W, Crew, Air-Con [1] Labels & Data Visible [1] Portrait Aligned [1] Data Type Air-Con = Y/N T/F [1] Heading Cruise Enquiry [1] Result Correct Data [2] 5 Printed Ionian Footer Name etc. [1] [Total: 13] Report Grouping HireP/W [1] Sorted Region (Ascending) [2] Correct Fields Region, BoatModel, BoatName, HireP/W [1] Labels & Data Visible [1] Portrait Aligned [1] Heading Regional Rates [1] Footer Name etc. [1] 6 Printed Regional Rates Result Correct data [2] [Total: 10]

Page 4 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2009 9713 04 © UCLES 2009 7 Report Correct Fields ContactName, Region, BoatModel BoatName, HireP/W [2] Marked from Grouping Contact Name [1] Step 9 printout Region [1] Sorted HireP/W [1] Labels & Data Visible [1] Heading Charter Enquiry [1] Footer Name etc. [1] [Total: 8] 8 Menu Form/Switchboard Appropriate Labels [3] code/table Shown/Works [3] [Total: 6] 9 Export Report Data Correct [2] Logo Top right [1] Footer Name etc. moved [1] [Total: 4] 10 Mail Merge Selection Works (correct data) [4] Date [1] Logo Top right [1] Address fields Correct [2] No word wrapping [1] ContactName Included [2] Region Inserted [1] Spacing [1] Days 1–7 Inserted [2] Conditional inserts If then else [1] Jet ski text [1] Field Codes Visible [1] Inc Conditional [1] Footer Name etc. [1] [Total: 20]