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

This document consists of 4 printed pages. IB14 11_9713_02/2RP © UCLES 2014 [Turn over *3165127277* Cambridge International Examinations Cambridge International Advanced Subsidiary and Advanced Level APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/02 Paper 2 Practical Test October/November 2014 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 2014 9713/02/O/N/14 You work for the University of Tawara and are going to create a database to analyse data about student courses and accommodation. Dates are to be stored in dd/mm/yyyy format. 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: N14courses.csv N14halls.csv N14quals.csv N14students.csv Create a new database and import these files. Some field names, key fields and data types are shown below. Where they are not given, choose your own using the same naming conventions as shown. Use this information to help you create the tables: [31] Students Courses Field name Type Field name Type Student_ID Faculty Alphanumeric Student_Type Code Alphanumeric Award Family_Name Full_Time Accom Course_Title Telephone Birth_Date Course_Code Halls Tutor_Code Field name Type Accom Residence Qualifications Address_1 Field name Type Alphanumeric Code Qualification Zip_Code denotes primary key

3 © UCLES 2014 9713/02/O/N/14 [Turn over 3 Include in your evidence document screenshots that show the structure of the four tables showing all of the field names, data types and key fields. 4 Establish appropriate relationships to link the four tables to create a relational database. Include in your evidence document screenshots that show the relationships between these tables. Make sure that there is evidence of each relationship type. [9] 5 The Students.Student_Type field contains codes to show where the student is from and how their University course is to be funded. The possible categories are: Student_Type Meaning O Overseas students paying for themselves IG Indigenous (local) students funded by a grant IP Indigenous (local) students paying for themselves H Honorary students where no fee is payable Only these values are allowed within this field. Make sure that the database checks this data as it is entered, not allowing invalid entries and informs the user if this has occurred. [6] 6 The University will not accept students born after the 31st August 1993. Make sure that the database checks this data as it is entered, not allowing invalid entries and informs the user if this has occurred. [6] 7 Include in your evidence document screenshots that show clearly how you have restricted the data entry in steps 5 and 6 and any text displayed to the user. 8 Save and print your evidence document. 9 Create a new document named: CentreNumber_CandidateNumber_Testing e.g. ZZ999_99_Testing 10 Set the page size to A4 and the orientation to landscape. Set all margins to 3 centimetres. [3] 11 Place an automated filename with the full file path on the left in the header. Make sure the header aligns to the left margin of the page. [1] 12 Place your name, Centre number and candidate number on the right in the footer. Make sure the footer aligns to the right margin of the page. [1] 13 Enter the text Validation testing as a heading in a bold, 36 point, centre aligned serif font. [3] 14 Below the heading create the following test table. [5] Fieldname Students.Student_Type Test type Data chosen Type of data Expected outcome Actual outcome IG

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. 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 2014 9713/02/O/N/14 15 Complete this test table for the validation rule you set in the Students.Student_Type field. If the actual outcome of the test is an error message, take a screen shot of the message and place it in the correct cell of the table. [10] 16 Create and complete another test table to test your second validation rule. Use a similar table structure with consistent formatting. If the actual outcome of the test is an error message, take a screen shot of the message and place it in the correct cell of the table. [17] 17 Save and print this document. 18 Most students in the university live in a hall of residence. All rooms in the halls of residence called Eaves Hall, Surfers Lodge, White Knights and Dilbridge II are going to have new carpets fitted. Students living in these halls will need to be contacted. Create a report which lists the names of these students as well as the name, full address and zip code of the hall of residence. This list must be grouped by the name of the hall of residence. Ensure the full address and zip code are also displayed in the group header. Add a suitable title to the report. Ensure that your name, Centre number and candidate number are added to the header of the report. Print this report so that it fits on a single page wide. [7] 19 The new carpets will be fitted in the summer break where rooms are empty. All students are on holiday except for those in the Construction Faculty, those studying Archaeology or those studying for a Masters degree. Refine the report to include only the students who are on holiday. Your report should include fields that show details of these students and also their course, faculty and qualification. Print your report. [6] 20 Examine the contents of the Halls table. Extract from all the data only the information relating to students who live in the halls of residence within the University of Tawara. Create a new report that counts how many students from each Faculty are in each Hall. Do not include any totals in this report. Add a suitable title to the report. Place your name, Centre number and candidate number in the header of the report. Print the report on a single page. [15] 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 October/November 2014 series 9713 APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 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 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 October/November 2014 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 – October/November 2014 9713 02 © Cambridge International Examinations 2014 Evidence document Students Table created Correct table name 1 mark Student_ID 1 mark Fore/First_Name 1 mark All fieldnames as specified 3 marks Birth_Date as Date field 1 mark Telephone field set to alphanumeric 1 mark All other field types all alphanumeric 3 marks Primary key created on Student_ID field 1 mark Courses Table created Correct table name 1 mark Faculty field set alphanumeric 1 mark Code field set alphanumeric 1 mark Award field set alphanumeric 1 mark Full_Time field set as Boolean 1 mark Course_Title field set alphanumeric 1 mark Primary key created …on Code field 1 mark Halls Table created Correct table name 1 mark Accom field set alphanumeric 1 mark Residence field set alphanumeric 1 mark Address_1 field set alphanumeric 1 mark Address_2 field set alphanumeric 1 mark Address_3 field set alphanumeric 1 mark Zip_Code field set alphanumeric 1 mark Primary key created …on Accom field 1 mark Qualifications Table created Correct table name 1 mark Code field set alphanumeric 1 mark Qualification field set alphanumeric 1 mark Primary key created on Code field 1 mark

Page 3 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Evidence document Correct fields 2 marks One-to-many relationship 1 mark Correct fields 2 marks One-to-many relationship 1 mark Correct fields 2 marks One-to-many relationship 1 mark

Page 4 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Evidence document Validation Correct field 1 mark Check for all 4 entries 2 marks OR between each 1 mark Appropriate text 1 mark Including parameters 1 mark Correct field 1 mark < 1 mark 01/09/1993 1 mark Use of # # to signify dates 1 mark Appropriate text 1 mark Including parameters 1 mark

Page 5 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 C:\path\filename A Candidate, XX999,9999 © Cambridge International Examinations 2014 Validation testing Fieldname Students.Student_Type Test type Lookup check Data chosen Type of data Expected outcome Actual outcome O Normal Accepted O Accepted/Stored IG IG Accepted/Stored IP IP Accepted/Stored H H Accepted/Stored Any suitable response Abnormal Error message Header Filename & path left aligned 1 mark Footer Name & numbers right aligned 1 mark 8 × 4 table with gridlines 1 mark Text 100% accurate 1 mark Correct cells merged 3 marks Page size – A4 1 mark Margins all 3cm 1 mark Orientation - Landscape 1 mark Heading 100% accurate 1 mark 36 point, serif 1 mark Centre aligned, bold 1 mark Test type Lookup check 1 mark Normal data 1 mark 3 Correct examples 2 marks Expected to work 1 mark Works 1 mark Abnormal data 1 mark Correct example 1 mark Expected to be rejected 1 mark Rejected – screen shot 1 mark

Page 6 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 C:\path\filename A Candidate, XX999,9999 © Cambridge International Examinations 2014 Fieldname Students.Birth_Date Test type Range check / Limit check Data chosen Type of data Expected outcome Actual outcome 31/8/1993 Normal Accepted 31/08/1993 stored / accepted 12/12/1977 12/12/1977 stored / accepted 1/9/1993 Abnormal Error message 25/12/2013 31/8/1993 Extreme Accepted 31/8/1993 stored / accepted Fieldname Students.Birth_Date 1 mark Test type Range/Limit check 1 mark Normal data 1 mark 2 Correct examples 1 mark Expected to work 1 mark Works 1 mark Abnormal data 1 mark 2 Correct examples 1 mark Expected to be rejected 1 mark Rejected 1 mark Extreme data 1 mark Correct example 1 mark Expected to work 1 mark Works 1 mark 8 x 4 table with gridlines 1 mark Correct cells merged 1 mark Consistency of styles (1st table) 1 mark

Page 7 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Halls where new carpets will be fitted by A Candidate, ZZ999, 9999 Residence Address_1 Address_2 Address_3 Zip_Code Dilbridge II University of Tawara Dockside Port Peppard 4309 Thora Weissmuller Steffen Johnson Dhruv Dev Eaves Hall University of Tawara Central Appleton 4309 Tracy Royle Graham White Dietmar Strauss Antonio Rangel Amy Nichols Karla Maier Claire Turner Jens Maloi Stuart Davies Sandra Cooper Summer Kacaratchi Tomas Jacobs Kratika Gupta Didier Watson Karoline Strauss Peter Perfection Louis Claes Elliot Cotterill Siddharth Gad Holly Chase Peter Kelly Annie Rennie Pat Pushing Olga Schneider Surfers Lodge University of Tawara Mile End Stannerley 4306 Jonas Verdonk Des Coupland Karin Nacht Fritz Bumgarner Greg Maury Dave Exley Karsten Vogel Peggy Hedges Nadine Ross Peter Damji Angelina Reliant Tracy Cushing Liontin Arrowsmith Taran Hafez Tracy Black Steve Miller Header: Appropriate title & candidate details 1 mark Correct fields: Residence, 2 name fields, 3 address & zip code 1 mark Search: Dilbridge II, Eaves Hall, Surfers Lodge & White Knights 2 marks Grouping: Residence 1 mark With address in group header 1 mark Printed single page wide & fully visible 1 mark

Page 8 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Residence Address_1 Address_2 Address_3 Zip_Code White Knights University of Tawara Dunescroft 4306 Dawid Jones Pilar Pagan Nikos Nicolaides Suzanne Miller Toni Fernandez Jeremy Pinoir Pablo Garrido Evert Bayer Eugenio Lopez Victor Castro Oral Strauss Holly Jenkinson Friedhelm Beyer Friederike Trommler Juan Suarez Traugott Wexler Michael Royle Jim Roberts Maria Schiffer Karl Roth Fatima Hegde Gerhardt Weissmuller Gunther Schmitt Rafael Lopez

Page 9 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Refined list of halls where new carpets will be fitted by A Candidate, ZZ999, 9999 Residence Address_1 Address_2 Address_3 Zip_Code Dilbridge II University of Tawara Dockside Port Peppard 4309 Dhruv Dev Graphic Communication Arts Bachelor of Arts Thora Weissmuller History History Bachelor of Arts Eaves Hall University of Tawara Central Appleton 4309 Antonio Rangel History of Art and History History Bachelor of Arts Peter Perfection German and Economics German Bachelor of Arts Dietmar Strauss French and Management Studies French Bachelor of Arts Summer Kacaratchi Food marketing & Business Economics Economics Bachelor of Science Jens Maloi Graphic Communication Arts Bachelor of Arts Graham White History of Art History Bachelor of Arts Peter Kelly Ancient History and History History Bachelor of Arts Holly Chase Graphic Communication Arts Bachelor of Arts Kratika Gupta English Literature English Bachelor of Arts Elliot Cotterill Art & Psychology Arts Bachelor of Arts Sandra Cooper Art Arts Bachelor of Arts Didier Watson Art Arts Bachelor of Arts Amy Nichols Agriculture Agriculture Bachelor of Science Olga Schneider History of Art History Bachelor of Arts Header: Appropriate but changed title 1 mark Fields: Extra 3 fields included 1 mark Search: Refined with no Masters, Archaeology or Construction 3 marks Printed single page wide & fully visible 1 mark

Page 10 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 Surfers Lodge University of Tawara Mile End Stannerley 4306 Steve Miller English Language and Literature English Bachelor of Arts Des Coupland Graphic Communication Arts Bachelor of Arts Dave Exley Business Economics Economics Bachelor of Arts Nadine Ross Animal Science Agriculture Bachelor of Science Peggy Hedges English Literature with French English Bachelor of Arts Liontin Arrowsmith Ancient History History Bachelor of Arts Karsten Vogel French and German French Bachelor of Arts Peter Damji French Studies and English Language French Bachelor of Arts Tracy Cushing Philosophy and Italian Philosophy Bachelor of Arts Tracy Black German and Management Studies German Bachelor of Arts Karin Nacht Italian Studies Italian Bachelor of Arts Jonas Verdonk English Language and Literature English Bachelor of Arts White Knights University of Tawara Dunescroft 4306 Gerhardt Weissmuller German and Economics German Bachelor of Arts Rafael Lopez Philosophy and Classical Studies Philosophy Bachelor of Arts Pilar Pagan Mathematics and Statistics Mathematics Bachelor of Science Fatima Hegde Law Law Bachelor of Law Holly Jenkinson Italian and Econmics Italian Bachelor of Arts Evert Bayer Graphic Communication Arts Bachelor of Arts Traugott Wexler Agriculture Agriculture Bachelor of Science Toni Fernandez Graphic Communication Arts Bachelor of Arts Maria Schiffer Business and Management Economics Bachelor of Arts Juan Suarez German and History German Bachelor of Arts Michael Royle English Language English Bachelor of Arts Eugenio Lopez History of Art History Bachelor of Arts

Page 11 Mark Scheme Syllabus Paper Cambridge International AS/A Level – October/November 2014 9713 02 © Cambridge International Examinations 2014 by A Candidate, ZZ999, 9999 Number of students from each Faculty in each Hall of Residence Faculty Broughton Halls Denley Hall Dilbridge II Dilbridge Road East Hall Eaves Hall Manley Hall Sibley Hall Surfers Lodge White Knights Agriculture 1 1 1 1 Arts 3 1 6 1 1 3 Construction 9 4 1 1 2 3 1 2 5 Economics 15 4 1 4 2 1 4 2 1 English 7 2 2 2 3 2 French 3 5 2 1 2 2 German 3 1 2 1 2 History 10 5 1 1 6 7 6 2 5 Italian 1 2 1 1 1 1 Law 4 4 1 2 Mathematics 1 1 1 Philosophy 2 1 1 1 1 1 Header: Candidate details present 1 mark Appropriate title 1 mark Crosstab query or pivot table used 1 mark Axes: Halls & Faculty 2 marks Correct Count of students 8 marks No row or column totals visible 1 mark No column for Not in UoTB Halls 1 mark