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

This document consists of 5 printed pages and 3 blank pages. IB13 06_9713_02/2RP © UCLES 2013 [Turn over *0360031854* 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 2013 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 2013 9713/02/M/J/13 You work for the University of Tawara. You are going to develop a database to record and extract information regarding the students, their courses and their personal tutors. 1 You must provide evidence of your work, including screenshots 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 Look at the data in the files J13STUDENT.TXT, J13COURSE.TXT and J13TUTOR.TXT Design your database structure with appropriate field names, key fields and data types. Some of this information is shown below. J13STUDENT J13COURSE Field name Type Field name Type Student_ID Code First_Name Course_Title Faculty Alphanumeric Level Alphanumeric Full_Time Boolean Alphanumeric Zip_Code Telephone J13TUTOR Birth_Date Field name Type Course_Code Code Tutor_Code Room Email Salary Numeric: Currency, $ with 2 decimal places Contract Numeric: Decimal with 1 decimal place denotes key field 3 Using a suitable software package, import the files to create your database. 4 Include in your evidence document screenshots that show the structure of the tables including the field types and key fields. [37]

3 © UCLES 2013 9713/02/M/J/13 [Turn over 5 Establish the following relationship: J13COURSE.Code 1 — ∞ J13STUDENT.Course_Code [2] 6 Establish an appropriate relationship to link the table J13TUTOR to the other data. [3] 7 Include in your evidence document screenshots that show the relationships between these tables. Make sure that there is evidence of the relationship type. 8 The Contract field contains the portion of the week that each tutor works. This value must be greater than 0 and the maximum value that can be stored is 1. Make sure that the database checks this data as it is entered, allowing only valid entries. If the data entered is rejected, a message should be displayed explaining the range of acceptable values. In your evidence document show clearly how you have restricted the data entry and any text displayed to the user. [6] 9 Examine the data in the Level field. Extract a list of the levels and place this in your evidence document sorted into alphabetical order. Ensure there are no duplicate entries. No other data is allowed in this field. Make sure the database checks this data as it is entered, allowing only valid entries. If the data entered is rejected, a message should be displayed explaining the list of acceptable values. In your evidence document show clearly how you have restricted the data entry and any text displayed to the user. [9] 10 Save and print your evidence document. 11 Create a new document named: CentreNumber_CandidateNumber_Testing.rtf e.g. ZZ999_99_Testing.rtf 12 Set this into landscape orientation. [1] 13 In the footer of this document place: • your name, Centre number and candidate number on the left • an automated filename with the full file path on the right. Make sure that these align to the page margins. [2] 14 Enter the text Testing the data entered is valid as a heading in a bold, 32 point, centre aligned, sans-serif font. [5]

4 © UCLES 2013 9713/02/M/J/13 15 Below the heading create a test table that will be used for 5 pieces of test data, and looks like this: Fieldname Test type Range check Data chosen Type of data Expected outcome Actual outcome [3] 16 Add a second test table that looks like this: Fieldname Test type Lookup check Data chosen Type of data Expected outcome Actual outcome [4] 17 Complete the test tables for the Tutor.Contract and Course.Level fields. If the actual outcome of the test is an error message, take a screenshot of this message and place it in the correct cell of the table. [14] 18 Save and print this document.

5 © UCLES 2013 9713/02/M/J/13 19 Rooms 2 to 10 are going to be closed and all students using them need to be contacted. Extract and print a report grouped by room number, then tutor names. Apart from these two fields, include only the students’ names, course titles and telephone numbers, sorted into ascending order on the second name, then first name. Ensure that it is clear which names belong to the tutors and which belong to the students. Add a suitable title to the report. Ensure that your name, Centre number and candidate number are added to the header of the report before you print it. Ensure the report fits on a single page wide. Print the report. [9] 20 Calculate the number of students enrolled in each Faculty area. Only include Faculties where students have enrolled. Sort this data alphabetically by Faculty. Display this data as a table in your evidence document. [8] 21 Create a chart that shows the number of students enrolled in each Faculty area. Do not include a Faculty if less than 20 students have been enrolled. Fully label your chart and add your chart to your evidence document. [7] 22 One document is held on a shared network drive within the University. You will manage this document, but other users will also work on it. In your evidence document, explain how you can control this document so that it can be edited by the multiple users. [10] 23 Save and print your evidence document. Write today’s date in the box below. Date

6 © UCLES 2013 9713/02/M/J/13 BLANK PAGE

7 © UCLES 2013 9713/02/M/J/13 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 2013 9713/02/M/J/13 BLANK PAGE

CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the May/June 2013 series 9713 APPLIED INFORMATION & 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 May/June 2013 series for most IGCSE, GCE Advanced Level and Advanced Subsidiary Level components and some Ordinary Level components.

Page 2 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 9713 – June 2013 AS Level – Paper 2 – Practical Test No marks to be awarded for any printout not containing the candidate name, candidate number and centre number

Page 3 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Step 2 Candidate name, centre number and candidate number STUDENT Table created Appropriate table name 1 mark Appropriate field name – Second name 1 mark Appropriate field names – 3 address fields 3 marks Field types (1 mark per field) 11 marks Primary key on Student_ID field 1 mark

Page 4 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 COURSE Table created Appropriate table name 1 mark Appropriate field names 1 mark Field types (1 mark per field) 5 marks Key on Code field 1 mark

Page 5 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 TUTOR Table created Appropriate table name 1 mark Appropriate field name – First name 1 mark Appropriate field name – Second name 1 mark Field types (1 mark per field) 7 marks Contract field set to 1dp 1 mark Key on Code field 1 mark

Page 6 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Correct fields 1 mark One-to-many 1 mark

Page 7 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Correct fields 2 marks One-to-many 1 mark Correct field 1 mark >0 1 mark AND 1 mark <=1 1 mark Appropriate text 1 mark Including parameters 1 mark

Page 8 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Acceptable data for Course.Level field BA BEng BSc LLB LLM MA MChem MEng MEnvSci MMath MPharm MSc Rule Text Evidence of all 12 data sets 3 marks Alphabetical sort 1 mark Correct field 1 mark All 12 correct entries in rule 1 mark OR 1 mark Appropriate text 1 mark Including parameters 1 mark

Page 9 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 Name, centre and candidate number 9713_s13_ms_2 © Cambridge International Examinations 2013 Testing the data entered is valid Fieldname Tutor.Contract Test type Range check Data chosen Type of data Expected outcome Actual outcome 0.2 Normal Accepted 0.2 stored/accepted 0.5 0.5 stored/accepted 0 Abnormal Error message 1.1 1 Extreme Accepted 1 stored/accepted 8 x4 table with gridlines 1 mark Text 100% accurate 1 mark Correct cells merged 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 Footer Name & numbers left align 1 mark File name & path right align 1 mark Orientation - Landscape 1 mark Heading 100% accurate 1 mark 32 point 1 mark Bold 1 mark Centre aligned 1 mark Sans-serif 1 mark

Page 10 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 Name, centre and candidate number 9713_s13_ms_2 © Cambridge International Examinations 2013 Fieldname Course.Level Test type Lookup check Data chosen Type of data Expected outcome Actual outcome BA Normal Accepted BA stored/accepted BEng BEng stored/accepted BSc BSc stored/accepted LLB LLB stored/accepted LLM LLM stored/accepted MA MA stored/accepted MChem MChem stored/accepted MEng MEng stored/accepted MEnvSci MEnvSci stored/accepted MMath MMath stored/accepted MPharm MPharm stored/accepted MSc MSc stored/accepted Trash Abnormal Error message All 12 in Normal data 1 mark One item of Abnormal 1 mark No table split over page 1 mark 16 x4 table with gridlines 1 mark Text 100% accurate 1 mark Correct cells merged 1 mark Correct cells merged 1 mark

Page 11 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 Name, centre and candidate number 9713_s13_ms_2 © Cambridge International Examinations 2013 Search: Rooms 2,4,10 2 marks Grouping: Room 1 mark Then Tutor 1 mark Sorting: Ascending Surname within group 1 mark Printed single page wide 1 mark Header: Appropriate title & candidate details 1 mark Tutor & student details differentiated 1 mark Correct fields: Tutor names, Room, Student names Tel, Course title 1 mark

Page 12 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 Name, centre and candidate number 9713_s13_ms_2 © Cambridge International Examinations 2013

Page 13 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 Name, centre and candidate number 9713_s13_ms_2 © Cambridge International Examinations 2013

Page 14 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Number of students enrolled in each Faculty: Agriculture 4 Arts 20 Computing 28 Construction 9 Economics 47 English 26 French 23 Geography 3 German 8 History 54 Italian 10 Law 15 Mathematics 7 Philosophy 11 0 10 20 30 40 50 60 Arts Computing Economics English French History Number of students enrolled Faculty Faculties with 20 or more students Correct calculated totals: 7 marks Award 1 mark for each correct pair No Politics, Psychology or Science Faculty visible 1 mark Extracted >=20 2 marks Appropriate chart type 2 marks Appropriate title 1 mark Appropriate titles & labels 2 marks

Page 15 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 02 © Cambridge International Examinations 2013 Step 23 Content (K & U) – Maximum 10 marks Accept different solutions like: Set document as a ‘shared document’… …with read-write permissions for all …set full permissions for me Other users track changes They submit their documents with (suggested) tracked changes …initials/staff codes As administrator I will review their changes... …and accept changes… …reject each change Set document permissions to read only for other users They save in own user area with read/write permissions Their version of document merged with master copy I will review again each change after the merge Saving documents/files with version numbers/identifier (eg initials) of editor …use of sequential version numbering systems Other suitable ideas: ‘Comments’ used to review original Comments inserted Comments removed by me Compare & Merge