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

This document consists of 4 printed pages. DC (ST) 95409/2 © UCLES 2015 [Turn over Cambridge International Examinations Cambridge International Advanced Subsidiary and Advanced Level * 8 4 2 1 9 1 9 6 2 8 * APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/02 Paper 2 Practical Test May/June 2015 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. Printouts with handwritten candidate information will not be marked. 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 9713/02/M/J/15 © UCLES 2015 You work for Tawara Airlines and will create a database to analyse data about their flights. Dates are to be displayed in dd/mm/yyyy format. All times are to be displayed in hh:mm format and are set to GMT (Greenwich Mean Time). You must use the most efficient method to solve each task. 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. ZZ999_99_Evidence.rtf Place your name, Centre number and candidate number in the header of your Evidence Document. 2 Examine the data in the files: J15CrewLeaders.csv J15Flight.csv J15FlightCode.csv J15FlightCrew.csv Create a new database and import these files. Tawara Airlines have decided that all field names must be short, meaningful, consistent in style and contain no spaces. Some table names, field names, key fields and data types are shown below. Where they are not given, choose your own. Use this information to help you create the tables: [42 ] FlightCode Flight Field name Data type Field name Data type Flight_No Alphanumeric Flight_No D_Code Alphanumeric Depart_Date A_Code Depart Alphanumeric Arrive_Date Alphanumeric Crew Crew Field name Data type CrewLeaders Forename Alphanumeric Field name Data type Crew denotes primary key

3 9713/02/M/J/15 © UCLES 2015 [Turn over 3 Place in your Evidence Document screenshots to show the structure of the four tables, including the field names, data types and key fields. 4 Establish appropriate relationships to link the four tables to create a relational database. Place in your Evidence Document screenshots to show the relationships between these tables and each relationship type. [9] 5 Create a report of all Tawara Airways flights in and out of Paris between the 5th January 2015 and the 25th January 2015. Do not include these dates. Display only the flight numbers, airport names, dates and times for both the departures and arrivals of these flights. Add a suitable title to the report. Make sure that your name, Centre number and candidate number are placed in the footer. Place in your Evidence Document screenshots that show how you extracted this data. Print this report. [11] 6 Your manager wants a list of full names of all flight crews who have flown into or out of Hong Kong. Create a report which lists the names of these crew members as well as their Payroll number and the flight numbers of both their flights into and out of Hong Kong. Group this report by the crew code (which is a single letter) and include the payroll number of crew leader in the group header. Within each crew code, group the report by the flights showing the departure airport code and arrival airport code. Please note: crew members may appear more than once on this list. Add a suitable title to the report. Make sure that the text Report prepared by: followed by your name, Centre number and candidate number are added to the header. Prepare this report so that it fits on a single portrait page wide and that the details of each crew do not split over two pages. Where possible, fit more than one crew on a single page. Print this report. [15] 7 Create a report to be presented to your manager. This must count the number of flights between each of the airports. Display the codes for the departure airports as row headings, and codes for the destination airports as column headings. Do not include the total number of flights to and from each airport. Place your name, Centre number and candidate number in the footer. Print this report on a single portrait page, showing gridlines in the table. [8]

4 9713/02/M/J/15 © UCLES 2015 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. 8 Refine the results of your search in step 6 to create a chart showing how many times each of these crew members visited Hong Kong. Crew members who have visited Hong Kong have flown in to and out from this airport. Show in your Evidence Document: • how you calculated this data • the results displayed as a table • the results displayed as a chart. [12] Tawara Airlines classes any flight that takes longer than 7 hours 30 minutes as a Long Haul flight. 9 Using an appropriate software package and the file J15Flight.csv calculate the duration of each flight. Extract all long haul flights with a departure date between the 16th and 25th January 2015. Do not include these dates. Sort this extract into ascending order of departure date then into descending order of duration. Add an appropriate image and title to your extract. Make sure that your name, Centre number and candidate number are in the header. Print on a single page, evidence of how you calculated the duration of each flight. Print on a single page, this extract displayed as a table. [23] 10 Save and print your Evidence Document. Write today’s date in the box below Date

® IGCSE is the registered trademark of Cambridge International Examinations. CAMBRIDGE INTERNATIONAL EXAMINATIONS Cambridge International Advanced Subsidiary and Advanced Level MARK SCHEME for the May/June 2015 series 9713 APPLIED INFORMATION & COMMUNICATION TECHNOLOGY 9713/02 Paper 1 (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 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 May/June 2015 series for most Cambridge IGCSE®, Cambridge International A and AS Level components and some Cambridge O Level components.

Page 2 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Evidence document FlightCode Table created Correct table name 1 mark Flight_No 1 mark Alphanumeric 1 mark Set as Primary key 1 mark D_Code as alphanumeric 1 mark A_Code 1 mark Alphanumeric 1 mark Depart as alphanumeric 1 mark Arrive 1 mark Alphanumeric 1 mark Flight Table created Correct table name 1 mark ID field 1 mark …Set as Primary Key 1 mark Depart_Date 1 mark Date format 1 mark Depart_Time 1 mark Time format 1 mark Arrive_Date in date format 1 mark Arrive_Time in time format 1 mark Fleet_No 1 mark Numeric 1 mark Crew in Alphanumeric format 1 mark

Page 3 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Evidence document Correct fields 2 marks …One-To-Many 1 mark Crew Table created Correct table name 1 mark Fieldname 1 mark Alphanumeric 1 mark Set as Primary Key 1 mark Forename as alphanumeric 1 mark Fieldname 1 mark Alphanumeric 1 mark Fieldname 1 mark Alphanumeric 1 mark Fieldname 1 mark Boolean 1 mark CrewLeaders Table created Correct table name 1 mark Crew in Alphanumeric format 1 mark Lead_Crew 1 mark Alphanumeric 1 mark Either field set as Primary Key 1 mark

Page 4 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Evidence document Search: Depart Paris OR Arrive Paris 3 marks AND 1 mark >#05/01/2015# 1 mark AND 1 mark <#25/01/2015# 1 mark Correct fields 2 marks …One-To-Many 1 mark Correct fields 2 marks …One-To-Many 1 mark

Page 5 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Flights arriving or departing from Paris Orly airport between 5th January 2015 and 25th January 2015 Flight Departure Date & Time Arrival Date & Time Depart Arrive TA015 16/01/2015 16:30 16/01/2015 17:45 London Heathrow Paris Orly TA016 16/01/2015 19:10 16/01/2015 20:25 Paris Orly London Heathrow Report by A Candidate, XX999, 9999 No of flights to and from each airport D_Code AMS ATL CGK DUB HKG LHR MAD ORY PEK SIN SYD AMS 6 ATL 6 CGK 6 DUB 6 6 6 6 6 HKG 6 6 LHR 6 12 6 3 3 MAD 3 ORY 3 PEK 6 SIN 6 SYD 6 Report created by A Candidate, XX999, 9999 Header: Appropriate title 1 mark Search: Correct 2 results 1 mark Fields as shown 1 mark Name & Candidate details in the footer 1 mark Format: Both date fields dd/mm/yyyy 2 marks Both Time fields hh:mm 2 marks Header: Appropriate title 1 mark Format: Single portrait page & all fully visible 1 mark Gridlines visible 1 mark Crosstab/Pivot table: D_Code as row headings 1 mark A_Code as column headings 1 mark Count as mathematical operation 1 mark Correct values 1 mark Name & Candidate details in the footer 1 mark

Page 6 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Crew members who have flown to or from Hong Kong Report prepared by: A Candidate, XX999, 9999 Crew_Code Lead_Crew Flight_No A_Code D_Code Payroll_No Forename Surname C AC00060 TA029 HKG LHR AC00086 Krystal Read AC00158 Kevin White AC00140 Stuart Guttermann AC00086 Krystal Read AC00060 Anthony Campbell AC00022 Billy Green AC00022 Billy Green AC00220 Justin Brooklands AC00220 Justin Brooklands AC00140 Stuart Guttermann AC00060 Anthony Campbell AC00022 Billy Green AC00060 Anthony Campbell AC00086 Krystal Read AC00140 Stuart Guttermann AC00158 Kevin White AC00220 Justin Brooklands AC00158 Kevin White TA032 LHR HKG AC00086 Krystal Read AC00158 Kevin White AC00140 Stuart Guttermann AC00220 Justin Brooklands AC00060 Anthony Campbell AC00022 Billy Green AC00060 Anthony Campbell AC00086 Krystal Read AC00140 Stuart Guttermann AC00220 Justin Brooklands AC00158 Kevin White AC00022 Billy Green AC00022 Billy Green AC00158 Kevin White AC00140 Stuart Guttermann AC00086 Krystal Read AC00060 Anthony Campbell AC00220 Justin Brooklands Header: Appropriate title 1 mark Report prepared by: & candidate details 1 mark Search: A_Code OR D_Code HKG 3 marks Grouping level 1: …Crew_Code 1 mark …With Lead_Crew in group header 1 mark Grouping level 2: …Flight_No 1 mark …With A_Code in group header 1 mark …With D_Code in group header 1 mark Report detail: …Payroll_No, Forename & Surname only 1 mark All fields present & fully visible 1 mark Orientation portrait 1 mark Printed single page wide 1 mark No crew split over 2 pages 1 mark

Page 7 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Crew_Code Lead_Crew Flight_No A_Code D_Code Payroll_No Forename Surname TA059 HKG LHR AC00158 Kevin White AC00086 Krystal Read AC00158 Kevin White AC00220 Justin Brooklands AC00220 Justin Brooklands AC00158 Kevin White AC00140 Stuart Guttermann AC00086 Krystal Read AC00220 Justin Brooklands AC00060 Anthony Campbell AC00022 Billy Green AC00140 Stuart Guttermann AC00060 Anthony Campbell AC00060 Anthony Campbell AC00086 Krystal Read AC00022 Billy Green AC00022 Billy Green AC00140 Stuart Guttermann TA062 LHR HKG AC00140 Stuart Guttermann AC00060 Anthony Campbell AC00086 Krystal Read AC00158 Kevin White AC00220 Justin Brooklands AC00140 Stuart Guttermann AC00220 Justin Brooklands AC00022 Billy Green AC00140 Stuart Guttermann AC00220 Justin Brooklands AC00086 Krystal Read AC00060 Anthony Campbell AC00022 Billy Green AC00086 Krystal Read AC00060 Anthony Campbell AC00158 Kevin White AC00022 Billy Green AC00158 Kevin White E AC00079 TA030 SYD HKG AC00058 Joe Norfolk AC00021 Udoka Onyancha AC00058 Joe Norfolk AC00079 Jenna Hoy AC00111 Wai Wai Hnin Su AC00212 Sonja Steinle AC00105 Min Wang AC00079 Jenna Hoy AC00105 Min Wang AC00212 Sonja Steinle AC00111 Wai Wai Hnin Su AC00021 Udoka Onyancha

Page 8 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Crew_Code Lead_Crew Flight_No A_Code D_Code Payroll_No Forename Surname TA061 HKG SYD AC00021 Udoka Onyancha AC00212 Sonja Steinle AC00021 Udoka Onyancha AC00111 Wai Wai Hnin Su AC00105 Min Wang AC00079 Jenna Hoy AC00212 Sonja Steinle AC00058 Joe Norfolk AC00111 Wai Wai Hnin Su AC00105 Min Wang AC00079 Jenna Hoy AC00058 Joe Norfolk F AC00231 TA031 HKG SYD AC00018 Angela Akula AC00231 Kanya Charoenkul AC00046 Jimmy Lee AC00017 Ruksana Gopaul AC00231 Kanya Charoenkul AC00073 Laura Macdonald AC00231 Kanya Charoenkul AC00017 Ruksana Gopaul AC00018 Angela Akula AC00046 Jimmy Lee AC00046 Jimmy Lee AC00018 Angela Akula AC00017 Ruksana Gopaul AC00073 Laura Macdonald AC00073 Laura Macdonald TA060 SYD HKG AC00046 Jimmy Lee AC00017 Ruksana Gopaul AC00018 Angela Akula AC00046 Jimmy Lee AC00073 Laura Macdonald AC00231 Kanya Charoenkul AC00231 Kanya Charoenkul AC00018 Angela Akula AC00017 Ruksana Gopaul AC00073 Laura Macdonald AC00018 Angela Akula AC00046 Jimmy Lee AC00073 Laura Macdonald AC00231 Kanya Charoenkul AC00017 Ruksana Gopaul T AC00154 TA030 SYD HKG AC00002 Kurtis Brown AC00279 Davi Jayme AC00167 Jimmy O'Brien AC00154 Joshua Bui AC00069 Dougie Ryder AC00126 Vishnu Patel

Page 9 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Crew_Code Lead_Crew Flight_No A_Code D_Code Payroll_No Forename Surname U AC00090 TA061 HKG SYD AC00004 Christine Hull AC00089 Naomi Murray AC00090 Bex Young AC00104 Liu Teo AC00128 Mei-Mei Sun AC00156 Lois Thomas Evidence document Search: Using previous query as data set 1 mark Grouping: Payroll number (only unique field) 1 mark Calculation: Count the number of duplicates in Payroll_No 1 mark Divided by 2 2 marks Correct values shown in tabular form 2 marks

Page 10 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Evidence document 0 1 2 3 4 5 6 7 Number of visits Visits to Hong Kong by Air Crew Crew names Chart: Appropriate chart type 1 mark Appropriate title 1 mark Correct values shown 1 mark Both names fully visible 1 mark Appropriate labels 1 mark

Page 11 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Evidence document Flight duration If statement 1 mark B? = D?, 1 mark E? – C?, 1 mark “24:00” 1 mark Speech marks around 24:00 1 mark …-C? 1 mark …+ 1 mark …E44 1 mark Printed on a single page & fully visible 1 mark

Page 12 Mark Scheme Syllabus Paper Cambridge International AS/A Level – May/June 2015 9713 02 © Cambridge International Examinations 2015 Name & Candidate details in the header 1 mark Appropriate title includes Long Haul 1 mark includes correct 2 dates 1 mark Appropriate formatting for the title 1 mark Appropriate image selection 1 mark Appropriate position/aspect ratio 1 mark Flight duration >7:30 1 mark >16th January 1 mark <25th January 1 mark Sort on Departure Date 1 mark …Ascending 1 mark then: on Duration 1 mark …Descending 1 mark Single page & fully visible 1 mark