Cambridge A Level Information Technology (from 2017) 9626 — 2019 Oct/Nov Paper 2 · Variant 1

This document consists of 5 printed pages and 3 blank pages. DC (CE/CB) 164150/4 © UCLES 2019 [Turn over * 8 1 7 1 8 4 5 7 3 5 * INFORMATION TECHNOLOGY 9626/02 Paper 2 Practical October/November 2019 2 hours 30 minutes Additional Materials: Candidate Source Files: Employees.txt Ski1.png Skischule1.mp4 Skischule2.mp4 READ THESE INSTRUCTIONS FIRST DO NOT WRITE IN ANY BARCODES. Carry out every instruction in each task. Save your work using the file name given in the task as and when instructed. The number of marks is given in brackets [ ] at the end of each task or part task. Any businesses described in this paper are entirely fictitious. You must not have access to the internet or any email system during this examination. You must save your work in the correct format as stated in the tasks. If work is saved in an incorrect format, you will not receive marks for that task. Cambridge Assessment International Education Cambridge International Advanced Subsidiary and Advanced Level

2 9626/02/O/N/19 © UCLES 2019 You will create a short video to attract families to Ellmau for winter holidays. The video will advertise a ski school for children to encourage them to try skiing. The original clips were filmed by TMBvideo in Ellmau, Austria. All files produced must be of a professional standard. Create a new document called Evidence_followed by your centre number_candidate number. For example: Evidence_ZZ999_9999 Place your name, centre number and candidate number in the header of this document. Save this document. 1 Create a new video and set the image ratio to 16:9 Create title frames with a white background and a copy of the image Ski1.png down each side of each frame. Add the text Ellmau Kinder-Skischule in a red, 60 point, sans-serif font, like this: Add an appropriate effect to place this text. Display this title for 7 seconds. [13] 2 Open and examine the file Skischule1.mp4 Trim the clip so that only the first 8 seconds remain. Remove the soundtrack from the clip. Place the clip after the title frames. [4] 3 Open and examine the file Skischule2.mp4 Trim the clip so that only the first 13 seconds remain. Remove the soundtrack from the clip. Place the clip at the end of the video. Add a 3-second transition effect between the two video clips. [4]

3 9626/02/O/N/19 © UCLES 2019 [Turn over 4 Take a snapshot of the last frame of the video clip to create a background image for both the caption and the credits. Place this at the end of the video and display this for the final 14 seconds (7 seconds for the caption and 7 seconds for the credits). Create the caption and ensure that the caption text is easy to read with three lines of text identifying: • who the ski school is for • what is its purpose • the location of the ski school. Add appropriate credits to match the brief. Include your name, centre number and candidate number. [14] 5 [2] 6 Describe five benefits of normalisation. Evidence 2 Place your answer in your Evidence Document. [5] Evidence 1 Export your video in mp4 format with the filename SkiSchool_1_followed by your centre number_candidate number. For example: SkiSchool_1_ZZ999_9999

4 9626/02/O/N/19 © UCLES 2019 7 A query has been designed to help a company, which sells cars, to search their database. Analyse and evaluate this query: Field: Table: Sort: Show: Criteria: or: RegistrationPlate Cars Make Cars [Enter vehicle make] [Enter vehicle model] [Enter code for fuel type] Model Cars Fuel Cars Colour Cars Engine size Cars [6] Evidence 3 Place your analysis and evaluation in your Evidence Document. Employees in Ellmau who work with tourists have to be registered. This data will be stored in a database. All reports must be designed to fit on an A4 page in portrait orientation and be a single page wide. All text must be at least 12 points high. All reports produced must be of a professional standard. The client has decided that all database field names must be short, meaningful and consistent in style. Field names may contain underscores but not spaces. 8 Using suitable software, open and examine the data in the file: Employees.txt Use this data to create a relational database normalised to third normal form (3NF). [39]

5 9626/02/O/N/19 © UCLES 2019 9 A local business wants to present awards on 1 December 2019 to younger employees, who, on this date, will have been in the same employment for over 10 years. Create a report with an appropriate title, containing details of employees born after 1990 so that it is grouped and formatted like this: [15] Report prepared by A Candidate ZZ999 9999 Administration/Office Skischule Skiverleih Kinderpiste Kapellenweg 8 6352 Ellmau Snowboard Instructor Skischule Skiverleih Kinderpiste Kapellenweg 8 6352 Ellmau Chambermaid AktivHotel Hochfilzer Dorf 34 6352 Ellmau Vanessa Schweiger 25/04/1991 20/04/2009 Marlene Hofmann 27/03/1991 12/12/2008 Konstantin Hofbauer 27/06/1991 21/01/2007 10 Create a report to display only the employees who are trainees. Identify, for each job described, how many trainees work in each place of employment. The report must have an appropriate title, display gridlines, be in a tabular format and display the place of employment as row headings. Do not include totals for each place of employment. [8] Evidence 4 Export this report in portable document format (pdf) as Trainee_followed by your centre number_candidate number. For example: Trainee_ZZ999_9999 Evidence 5 Save your database as Nov19_followed by your centre number_candidate number. For example: Nov19_ZZ999_9999 Save your Evidence Document.

6 9626/02/O/N/19 © UCLES 2019 BLANK PAGE

7 9626/02/O/N/19 © UCLES 2019 BLANK PAGE

8 9626/02/O/N/19 © UCLES 2019 BLANK PAGE 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 Assessment International Education Copyright Acknowledgements Booklet. This is produced for each series of examinations and is freely available to download at www.cambridgeinternational.org after the live examination series. Cambridge Assessment International Education is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of the University of Cambridge Local Examinations Syndicate (UCLES), which itself is a department of the University of Cambridge.

This document consists of 8 printed pages. © UCLES 2019 [Turn over Cambridge Assessment International Education Cambridge International Advanced Subsidiary and Advanced Level INFORMATION TECHNOLOGY 9626/02 Paper 2 Practical October/November 2019 MARK SCHEME Maximum Mark: 110 Published 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 International will not enter into discussions about these mark schemes. Cambridge International is publishing the mark schemes for the October/November 2019 series for most Cambridge IGCSE™, Cambridge International A and AS Level components and some Cambridge O Level components.

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 2 of 8 Generic Marking Principles These general marking principles must be applied by all examiners when marking candidate answers. They should be applied alongside the specific content of the mark scheme or generic level descriptors for a question. Each question paper and mark scheme will also comply with these marking principles. GENERIC MARKING PRINCIPLE 1: Marks must be awarded in line with: • the specific content of the mark scheme or the generic level descriptors for the question • the specific skills defined in the mark scheme or in the generic level descriptors for the question • the standard of response required by a candidate as exemplified by the standardisation scripts. GENERIC MARKING PRINCIPLE 2: Marks awarded are always whole marks (not half marks, or other fractions). GENERIC MARKING PRINCIPLE 3: Marks must be awarded positively: • marks are awarded for correct/valid answers, as defined in the mark scheme. However, credit is given for valid answers which go beyond the scope of the syllabus and mark scheme, referring to your Team Leader as appropriate • marks are awarded when candidates clearly demonstrate what they know and can do • marks are not deducted for errors • marks are not deducted for omissions • answers should only be judged on the quality of spelling, punctuation and grammar when these features are specifically assessed by the question as indicated by the mark scheme. The meaning, however, should be unambiguous. GENERIC MARKING PRINCIPLE 4: Rules must be applied consistently e.g. in situations where candidates have not followed instructions or in the application of generic level descriptors. GENERIC MARKING PRINCIPLE 5: Marks should be awarded using the full range of marks defined in the mark scheme for the question (however; the use of the full mark range may be limited according to the quality of the candidate responses seen). GENERIC MARKING PRINCIPLE 6: Marks awarded are based solely on the requirements as defined in the mark scheme. Marks should not be awarded with grade thresholds or grade descriptors in mind.

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 3 of 8 Video Ellmau video Image ratio of software set to 16:9 1 mark Title frames Title background – white 1 mark Image ski1.jpg present on left edge 1 mark Image ski1.jpg present on right edge 1 mark No cropping of both images 1 mark Title frame at start with white background 1 mark Title 7 seconds duration 1 mark Title text Ellmau Kinder-Skischule 1 mark Middle centre of screen and clearly visible 1 mark Red 60 point sans-serif font 1 mark Effect added for title text animation « 1 mark « effect does not hinder readability 1 mark « effect added to give sufficient time to read text within 7 secs 1 mark Video 1 Skischule1.mp4 placed after title frames 1 mark End of video cut 1 mark Only 8 seconds of video remain 1 mark Soundtrack removed from clip 1 mark Video 2 Skischule2.mp4 placed after Skischule1 1 mark End of video cut so 13 seconds remain 1 mark Soundtrack removed from clip 1 mark Single 3 second transition effect between 2 videos 1 mark Caption Snapshot of last frame extracted in appropriate format 1 mark Placed after video clip 1 mark Caption frame 7 seconds duration 1 mark Caption in visible font with reasonably good contrast 1 mark Caption text includes Children’s Ski School (or similar) 1 mark Caption text includes Learn to ski (or similar) 1 mark Caption text includes Ellmau 1 mark Caption text includes Austria 1 mark Credits Credits frame duration 7 seconds 1 mark Filmed by TMBvideo 1 mark Location Ellmau 1 mark Country Austria 1 mark Appropriate blank line/s as spacing between credits 1 mark Candidate name and numbers in credits in appropriate format 1 mark Movie saved/exported as SkiSchool_1_ZZ999_9999 1 mark In mp4 format 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 4 of 8 Normalisation Describe five benefits of normalisation Five descriptions from: 1 The database does not have redundant data, it is smaller in size so less money needs to be spent on storage 2 As there is less data to search through, it is much faster to run a query on the data 3 As there is no redundant data there is better data integrity and less risk of mistakes 4 As there is no redundant data there is less chance of storing two or more different copies of the data 5 One change can be made which can instantly be cascaded across any related records 6 Data is grouped logically within the database structure 7 Normalised databases only contain atomic data 1 mark each Max 5 Evaluation Analyse and evaluate the given dynamic query Six from: 1 It is a dynamic parameter query 2 It is easier for a user than hard coding the vehicle make, model and fuel type into a static query 3 A reason why less user expertise is needed. 4 The user only has to complete three dialogue boxes. 5 Setting up a query in this way means that it is more flexible than a static parameter query 6 It can be used over and over again without having to ever go back into the query design view. 7 Using this dynamic query limits the search criteria to only these three fields. 1 mark each Max 6 Database Employer Appropriate table name for Employer / Place 1 mark Employer_Code / Place_Code (or similar) 1 mark « Set as alphanumeric/text/numeric 1 mark « Set as primary key 1 mark Employer_Name / Place / Venue (or similar) 1 mark « Set as alphanumeric/text 1 mark Address1 field 1 mark « Set as alphanumeric/text 1 mark Address2 field set as alphanumeric/text 1 mark Address3 field set as alphanumeric/text 1 mark Address4 field set as alphanumeric/text 1 mark Short meaningful consistent fieldnames 1 mark Fieldnames contain no spaces 1 mark 23 records 1 mark « with unique data 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 5 of 8 Employees Appropriate table name for Employee 1 mark Surname (or similar) 1 mark « Set as alphanumeric / text 1 mark Forename (or similar) 1 mark « Set as alphanumeric / text 1 mark Date_Of_Birth (or similar) 1 mark « Set in Date / Time format 1 mark Date_Employed (or similar) 1 mark « Set in Date / Time format 1 mark Job (description) 1 mark « Set as alphanumeric 1 mark Employer_Code / Place_Name 1 mark « Data type matches Employer.EmployerCode 1 mark No additional fields 1 mark All fieldnames contain no spaces 1 mark All fieldnames short, meaningful and consistent 1 mark Compound Key set on Name and DOB fields 3 marks 169 records 1 mark Relationship Employer.Employer_Code to Employee.Employer_Code 2 marks « 1 to Many 1 mark Query for report: Date_Of_Birth 1 mark >#31/12/1990# 1 mark AND 1 mark Date_Employed 1 mark <#02/12/2009# 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 6 of 8 Report: Appropriate title for report 1 mark Largest font, black background with white font 1 mark Grouped by job description 1 mark Secondary grouping by employer’s / place name 1 mark « with 4 address lines in secondary group 1 mark Detail row contains Forename and Surname 1 mark Forename & Surname dark grey background and white sans-serif font 1 mark Detail row contains DOB then Date job started 1 mark DOB and Date job started grey background and white sans-serif font 1 mark Correct 11 records extracted 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 7 of 8

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED October/November 2019 © UCLES 2019 Page 8 of 8 Report 2: Wildcard search for Trainee 1 mark Job description as column headings 1 mark Employer names as row headings 1 mark Count as function 1 mark No totals 1 mark Appropriate title 1 mark Gridlines present 1 mark Exported as Trainee_ZZ999_9999.pdf 1 mark