Cambridge A Level Applied Information and Communication Technology 9713 — 2010 May/June Paper 2 · Variant 1

This document consists of 4 printed pages. IB10 06_9713_02/RP © UCLES 2010 [Turn over *9143901348* UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS General Certificate of Education Advanced Subsidiary Level and Advanced Level APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/02 Paper 2 Practical Test May/June 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 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 2010 9713/02/M/J/10 You are going to develop a database to record and extract information for a small garage selling cars. The database contains details of customers and used cars. The database must meet the following requirements: • Each car has a unique car ID which is used to identify the car within the garage. • Each customer has a unique customer ID which is a 5 character code. The first character is a letter which identifies the category of customer, using P for personal customers or B for business customers. • The telephone number of each customer is in the format of 5 digits, followed by a space then 6 digits. • The field Address3 will be restricted to only Essex, Suffolk or Cambridgeshire. • The garage does not purchase cars for less than £500 or sell cars for less than £1000. • The garage does not sell cars made before 1991. 1 You are required to provide evidence of your work, including screen shots at various stages. Create a document named: CentreNumber_CandidateNumber_Evidence.rtf e.g. ZZ987_82_Evidence.rtf Place your name, Centre number and candidate number in the header of your evidence document. 2 Look at the data in the files JXCARS.TXT and JXCUST.CSV Use these files to create a relational database. Use the most appropriate record structure, primary key field/s and relationship/s. Include in your evidence document screenshots that show the structure of the tables, showing all of the field types, primary key/s and relationship/s between these tables. [18] 3 Validate the data entry to meet the garage’s requirements. Include in your evidence document screenshots that show how this validation has been achieved. [15] 4 Save and print your evidence document. You are going to prepare a report from the database. 5 Your report will include only cars sold to business customers. You will need to calculate the profit for each car sold. Show only the make, purchase price, sale price, date sold, company names and profit, sorted into ascending order of date sold. Display your name, Centre number and candidate number in the header. Add the title Cars sold to business customers Calculate the total profit and display it at the end of the report. Format all currency values in pounds (sterling) to 2 decimal places. Save and print this report. [18]

3 © UCLES 2010 9713/02/M/J/10 [Turn over You are going to prepare a report and a graph or chart showing the average sale price of more expensive cars. 6 Calculate the average sale price of each make of car for all cars in the database. Select only the makes of car where the average price is more than £15,000. Sort this data into ascending order of the make. [3] 7 Use this extract to prepare a report. Add the title Average sale price of luxury cars to this report. Place your name, Centre number and candidate number in the header. Save and print this report. [3] 8 Use this extract to produce a graph or chart comparing the average sale price of each make. Select the most appropriate type of graph or chart to display the data. Ensure that labels are fully displayed. Place your name, Centre number and candidate number on the chart. Save and print this chart. [6] You are going to prepare a report comparing the average profit for the sale of vehicles to both personal and business customers. 9 Produce a report which shows the average profit per sale for each of the categories of customer (personal and business). Place your name, Centre number and candidate number in the header. Save and print this report. [6] The garage is planning to send advertising leaflets to every house in the region. The region comprises six areas. You are going to prepare a spreadsheet to calculate how much it would cost. This spreadsheet will be used to model the costs depending upon the supplier chosen. 10 Using a suitable software package, load the file JXLEAFLET.CSV 11 In cell A2 enter the name of one of the suppliers. These can be found in cells A7 to A11. [1] 12 In cell B21 enter a formula to calculate the total number of properties in the region. [1] 13 In cell C15 enter a formula that will display the fixed fee for the supplier selected in cell A2. This formula will look up the chosen supplier in the supplier table and return the corresponding Fixed Fee. [4] 14 In cell D15 enter a formula that will display the rate charged for each delivery for the supplier selected in cell A2. This formula will look up the chosen supplier in the supplier table and choose the correct rate, depending on the number of properties for that area and return the corresponding rate from the supplier table. For example: If the supplier chosen was X Mail and the area was Cambridge, this should return the rate for this supplier for a town of 27000 properties which would give a rate of 0.002 for each delivery. [15]

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 2010 9713/02/M/J/10 15 In cell E15 enter a formula that will display the cost of the deliveries. This formula will multiply the number of properties by the rate. [1] 16 In cell F15 enter a formula that will display the total cost for this area. This formula will add the fixed fee to the delivery charge. [1] 17 Replicate the formulae entered in steps 13 to 16 for all areas. [1] 18 In cell F21 enter a formula that will display the total cost of the deliveries to all of the areas. [1] 19 Format only the cells in the Fixed fee, Total cost and Delivery columns, to currency in pounds (sterling) with 2 decimal places. [1] 20 Enter your name, Centre number and candidate number in the footer of the page. [1] 21 By changing the chosen supplier in cell A2, use the spreadsheet model to find the supplier with the lowest total cost to distribute leaflets to all of the areas. Save and print all details of only this supplier, adjusting the page layout if necessary so that the whole table fits on a single page. Make sure that the contents of all cells are fully visible. [4] 22 Print this sheet showing formulae instead of values. Ensure that all formulae and labels are fully visible. The manager is required to publish a range of documents about the garage. 23 The manager says: “Documents for publication need to have accurate content and be error free”. You must prepare a report for the manager to explain in your own words what this means and how you can achieve this. Your report must be between 100 and 400 words. Place your name, Centre number and candidate number on the report. Save and print this report. [20]

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the May/June 2010 question paper for the guidance of teachers 9713 APPLIED ICT 9713/02 Paper 2 (Practical Test A), maximum raw mark 120 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 May/June 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 AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 2 Candidate name, Centre number and candidate number Candidate name, Centre number and candidate number Table created Appropriate table name 1 mark Accept JXCars ? Field names meaningful & short 2 mark (Field names may vary) Field types (1 mark per field) 9 marks If currency shown as numeric with evidence of currency in general tab then award Primary key correct 1 mark Table created Appropriate table and field names 1 mark Field types all correct 1 mark Primary key correct 1 mark No marks to be awarded for any printout not containing the candidate name, candidate number and Centre number

Page 3 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Candidate name, Centre number and candidate number Customers.CustomerID 1 mark Cars.CustID 1 mark One-to-many 1 mark

Page 4 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 3 Cars.PurchasePrice Cars.SalePrice Rule correct 1 mark Appropriate validation text 1 mark Rule correct 1 mark Appropriate validation text 1 mark

Page 5 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Cars.Year Cars.CustID Greater than 1990 1 mark Appropriate validation text 1 mark 1 character 1 mark 4 numbers 1 mark Allow these 2 marks if customer table shown like this and car table not shown

Page 6 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Customers.CustomerID Customers.Address3 Customers.Telephone Same input mask as cars table 1 mark Input mask 5 numbers 1 mark Space (may be shown as \) 1 mark 6 numbers 1 mark Essex 1 mark OR Suffolk 1 mark OR Cambridgeshire 1 mark

Page 7 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 5 Candidate name, Centre number and candidate number Cars sold to business customers Make PurchasePrice SalePrice DateSold Company Profit Citroen £3,550.00 £4,405.00 13/01/2009 RS Electrical £855.00 Vauxhall £5,995.00 £7,095.00 27/01/2009 Clacton Mobility Company £1,100.00 Porsche £39,995.00 £44,495.00 08/02/2009 Banana Bay Imports £4,500.00 BMW £15,995.00 £18,095.00 16/02/200#9 Bridon Wire £2,100.00 Alfa Romeo £10,995.00 £12,595.00 28/02/2009 Toy Emporium £1,600.00 Vauxhall £6,100.00 £7,215.00 12/03/2009 Derby Road Timber £1,115.00 Ford £1,400.00 £2,045.00 14/03/2009 SF Martial Arts £645.00 Skoda £5,995.00 £7,095.00 19/04/2009 Top Tractors £1,100.00 Ford £2,995.00 £3,795.00 25/05/2009 TD Sports £800.00 Alfa Romeo £12,495.00 £14,245.00 26/05/2009 AT Logistics £1,750.00 Audi £15,495.00 £17,545.00 28/05/2009 SF Martial Arts £2,050.00 Ford £2,695.00 £3,465.00 02/07/2009 Dynamite Fireworks £770.00 Mercedes £35,995.00 £40,095.00 13/07/2009 RS Electrical £4,100.00 Renault £3,995.00 £4,895.00 04/08/2009 Suffolk Steel £900.00 Ford £1,995.00 £2,695.00 26/08/2009 Steel Stockholders £700.00 Ford £2,995.00 £3,795.00 28/08/2009 JC Building Supplies £800.00 Vauxhall £3,995.00 £4,895.00 01/09/2009 Niceday Nursery £900.00 Alfa Romeo £7,995.00 £9,295.00 21/09/2009 SF Martial Arts £1,300.00 Vauxhall £3,500.00 £4,355.00 09/10/2009 P Dane Plumbing £855.00 Ford £6,250.00 £7,375.00 10/10/2009 Indian Curry Cuisine £1,125.00 Ford £2,995.00 £3,795.00 30/10/2009 EFL Schools Ltd £800.00 Toyota £13,995.00 £15,895.00 18/11/2009 Tilly's Pantry £1,900.00 £31,765.00 £67,700 f/t if no business search Search Sold 1 mark AND business customers 1 mark Profit New field 1 mark Correct calculation 2 marks Sterling & 2dp 1 mark Total Correct calculation 1 mark Sterling, 2dp & position 1 mark Must be below profit column Sorted Ascending on DateSold 1 mark PurchPr Sterling & 2dp 1 mark SalesPrice Sterling & 2dp 1 mark Name and numbers in header 1 mark Title 100% correct 1 mark Fields: Make 1 mark PurchasePrice 1 mark SalePrice 1 mark DateSold 1 mark Company 1 mark Each mark awarded only if label and data fully visible Subtract marks for additional fields (NOT Profit)

Page 8 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 7 Candidate name, Centre number and candidate number Average sale price of luxury cars Make Avg Of SalePrice Audi £17,545.00 Jaguar £17,270.00 Lotus £16,455.00 Mercedes £18,745.00 Porsche £48,895.00 Range Rover £17,545.00 Toyota £17,728.33 TVR £24,700.00 Step 8 Candidate name, Centre number and candidate number Title Appropriate & meaningful 1 mark Luxury required do not allow AVG Header Name and numbers 1 mark Chart type Appropriate 2 marks Axis Labels/scale fully visible 1 mark Values Correct 1 mark Include follow through of searching Data Correct averages on make 2 marks Average > 15000 1 mark Ascending order of make 1 mark Title 100% correct 1 mark Header Name and numbers 1 mark

Page 9 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 9 Candidate name, Centre number and candidate number Average profit Query SaleType Avg Of Profit B £1,443.86 P £1,437.40 Key Please note b is the code for business customers and p is the code for personal customers. Step 21 Chosen supplier PriviPost SUPPLIER TABLE Supplier Fixed fee Number of properties 0 2000 20000 AB Leaflets £25.00 0.03 0.02 0.012 PriviPost £150.00 0.03 0.02 0.006 Royal Mail £0.00 0.03 0.022 0.015 Tendring Leaflets £50.00 0.03 0.03 0.015 X Mail £1,000.00 0.03 0.005 0.002 AREA TABLE Area Properties Fixed fee Rate Delivery Total cost Cambridge 27000 £150.00 0.006 £162.00 £312.00 Chelmsford 41000 £150.00 0.006 £246.00 £396.00 Clacton 13200 £150.00 0.02 £264.00 £414.00 Colchester 39000 £150.00 0.006 £234.00 £384.00 Hadleigh 1600 £150.00 0.03 £48.00 £198.00 Ipswich 29200 £150.00 0.006 £175.20 £325.20 151000 £2,029.20 Candidate name, Centre number and candidate number Business Correct calculation 2 marks -1 if incorrect formatting Personal Correct calculation 2 marks -1 if incorrect formatting Header Name and numbers 1 mark Labels Showing business/personal 1 mark May include a key In footer 1 mark Supplier Name copied or referenced 1 mark Selection PriviPost & total correct 4 marks

Page 10 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Step 16 Chosen supplier =A8 SUPPLIER TABLE Supplier Fixed fee Number of properties 0 2000 AB Leaflets 25 0.03 0.02 PriviPost 150 0.03 0.02 Royal Mail 0 0.03 0.022 Tendring Leaflets 50 0.03 0.03 X Mail 1000 0.03 0.005 AREA TABLE Area Properties Fixed fee Rate Cambridge 27000 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B15>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B15>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) Chelmsford 41000 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B16>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B16>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) Clacton 13200 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B17>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B17>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) Colchester 39000 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B18>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B18>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) Hadleigh 1600 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B19>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B19>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) Ipswich 29200 =LOOKUP($A$2,$A$7:$A$11,$B$7:$B$11) =IF(B20>$E$6,LOOKUP($A$2,$A$7:$A$11,$E$7:$E$11),IF(B20>$D$6,LOOKUP($A$2,$A$7:$A$11,$D$7:$D$11),LOOKUP($A$2,$A$7:$A$11,$C$7:$C$11))) =SUM(B15:B20) Fixed Fee LOOKUP function used 1 mark Correct absolute reference 1 mark Correct absolute range 1 mark Correct return column 1 mark Total number of properties Correct SUM 1 mark Rate Nested if 1 mark Correct condition 1 mark LOOKUP function used 1 mark Correct absolute reference 1 mark Correct absolute range 1 mark Correct return column/range 1 mark Correct condition 1 mark LOOKUP function used 1 mark Correct absolute reference 1 mark Correct absolute range 1 mark Correct return column/range 1 mark Else LOOKUP function used 1 mark Correct absolute reference 1 mark Correct absolute range 1 mark Correct return column/range 1 mark

Page 11 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 20000 0.012 0.006 0.015 0.015 0.002 Delivery Total cost =B15*D15 =C15+E15 =B16*D16 =C16+E16 =B17*D17 =C17+E17 =B18*D18 =C18+E18 =B19*D19 =C19+E19 =B20*D20 =C20+E20 =SUM(F15:F20) Candidate name, centre number and candidate number Delivery Properties * Rate 1 mark Total Cost Fixed Fee + Rate 1 mark Total Cost for Region Correct SUM 1 mark Replication All 4 correct 1 mark

Page 12 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Content (K & U) – Maximum 12 marks What is it: ‘Document for publishing’ • Word processed • Desktop Published • Portable document format [Max 3] ‘Accurate’ • Check what has been intended to be entered is entered • Check it matches original brief/target audience [Max 1] ‘Error free’ • Check that data contains no mistakes [1] Accuracy can be achieved by: • Verification o using double entry o visual verification (not proof reading) [Max 3] Error free can be achieved by: • Automated o Spell check
Each word is spelt as in local language dictionary o Grammar check
Syntax/structure of language/ensuring correct sentence construction
Correct spelling for the context used/accept examples like: there or their [Max 5] • Manual o Copy-editing
Does it make sense
Ensures it matches pre-defined/corporate style o Proof reading
Does it make sense (if not given above)
Does it match the original brief/intended audience [Max 5] Other tools used: • Breaks (Page/column/section) to control • Widows/orphan control • Split lists • Split tables • Split rows within tables • Use of templates • Application of styles/consistent font/line/paragraph spacing • Dictionary/Thesaurus to locate alternative words Allow track changes and auto-correction if used appropriately [Max 3] [Maximum 12]

Page 13 Mark Scheme: Teachers’ version Syllabus Paper GCE AS/A LEVEL – May/June 2010 9713 02 © UCLES 2010 Practical skills – Maximum 8 marks These practical skills will only be awarded marks if there are more than 100 words present. Word processed document with: • consistent line spacing • appropriate margins include hanging/indented paragraphs • suitable font styles • suitable font sizes (>10 and <15) • correct spelling, punctuation and grammar penalise for wrong target audience • <400 words • single page • appropriate title Including style/size – must mention ‘accurate’ and ‘error free’. Candidate name, Centre number and candidate number