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

This document consists of 5 printed pages and 3 blank pages. DC (LEG/SG) 163094/4 © UCLES 2019 [Turn over Cambridge Assessment International Education Cambridge International Advanced Subsidiary and Advanced Level * 3 0 8 6 4 5 0 2 3 2 * INFORMATION TECHNOLOGY 9626/02 Paper 2 Practical May/June 2019 2 hours 30 minutes Additional Materials: Candidate Source Files READ THESE INSTRUCTIONS FIRST DO NOT WRITE IN ANY BARCODES. Carry out every instruction in each task. Save your work using the file names 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 either the internet or any email system during this examination. You must save your work in the correct file format as stated in the tasks. If work is saved in an incorrect file format, you will not receive marks for that task.

2 9626/02/M/J/19 © UCLES 2019 You will create a short video to advertise the New Year celebrations that will take place this year in the town of Ellmau. The original clips were filmed on 31 December 2018 by GBRvideo in Ellmau, Austria. All files produced must be of a professional standard. Create a new document called Evidence followed by your candidate number, for example: Evidence9999 Place your name, centre number and candidate number in the header of this document. Save this document. You have been supplied with the following source files: Ellmau1.mp4 Ellmau2.mp4 EllmauArms.png Opera.mp3 1 Open and examine the file Ellmau1.mp4 and set the image ratio to 16:9. Remove the first 4.8 seconds of the clip. Trim the clip so that only 10 seconds remain. [4] 2 Take a snapshot of the first frame from the video clip. Add the file EllmauArms.png in the top right corner to create a background image for a title sequence, like this: [5] 3 Place this title background at the start of your video and display it for 10 seconds. [2] 4 Add the text Celebrate the New Year with us to the title in a white sans serif font like this: Add an appropriate animation to place this text. [5]

3 9626/02/M/J/19 © UCLES 2019 [Turn over 5 Take a snapshot of the last frame of the video clip to create a background image for a caption. Create a caption and display it for 7 seconds. Place this at the end of the video. Ensure that the caption text is easy to read with three lines of text identifying the: • location • fireworks at midnight • date of the event. Add a different, appropriate effect to place the caption. [9] 6 Place the video clip Ellmau2.mp4 after the caption. Use a 2-second transition to change from the caption background to the video clip. [2] 7 Select an appropriate background and transition for the credits. Place this at the end of the video and display it for 7 seconds. Add appropriate credits to match the brief. Include your name, centre number and candidate number. [10] 8 Evidence 1 Export your video in mp4 format with the filename Ellmau_1_ followed by your centre number_ candidate number, for example: Ellmau_1_ZZ999_9999 [2] 9 Open and examine the file Opera.mp3 in appropriate editing software. Remove the end of the clip so that only 43 seconds remain. [2] 10 Edit this file so that it has an appropriate fade in and fade out. [4] 11 Evidence 2 Save this audio clip in mp3 format with the filename Opera2_ followed by your centre number_ candidate number, for example: Opera2_ZZ999_9999 [1] 12 Add this soundtrack to your video so that they start and finish at the same time. [1] 13 Evidence 3 Export your video in wmv format with the filename Ellmau_2_ followed by your centre number_ candidate number, for example: Ellmau_2_ZZ999_9999 [1]

4 9626/02/M/J/19 © UCLES 2019 14 Evidence 4 Export or convert your video into avi format with the filename Ellmau_3_ followed by your centre number_candidate number, for example: Ellmau_3_ZZ999_9999 [2] Employees in Ellmau who work with tourists have to be registered. The types of venue that need to register are hotels, restaurants and ski schools. The data is held in two data files which are stored in different formats. All reports must fit on a single portrait page wide when printed on A4 paper with text at least 12 points high. All files produced must be of a professional standard. The client has decided that all database field names must be short, meaningful, consistent in style and contain no spaces or underscores. 15 Using suitable software, open and examine the data in the files: EllmauEmployees1.csv EllmauEmployees2.txt These files need to be merged together so that they can be used to develop a database. Evaluate, using less than 100 words, the suitability of these data files for this purpose. [6] Evidence 5 Place your evaluation in your Evidence Document. 16 Manipulate and merge the two files examined in step 15. Examine the Venue type data and use this to create the Venue type code for each employee using: • H for Hotel • R for Restaurant • S for Ski school. The age and date of birth of the employees will not be used in the database. Save the new file in comma separated value format, with the file name Employees followed by your candidate number, for example: Employees9999 [3]

5 9626/02/M/J/19 © UCLES 2019 17 Using suitable software, open and examine the data in the file: EllmauJobs.csv Use this and the file you saved in step 16 to create a relational database normalised to third normal form (3NF). [42] Evidence 6 Save your database as Ellmau_ followed by your centre number_candidate number, for example: Ellmau_ZZ999_9999 18 Create a report to identify how many chefs and how many trainee chefs work at each venue. The report must be in a tabular format with gridlines and have the venue names and addresses as row headings. Do not include any totals. [4] Evidence 7 Save your database. 19 Name another IT application that could store the original data without using a database. Explain the advantages of a relational database for this solution, compared to this other method you have named. [5] Evidence 8 Place your answers in your Evidence Document. Save your Evidence Document.

6 9626/02/M/J/19 © UCLES 2019 BLANK PAGE

7 9626/02/M/J/19 © UCLES 2019 BLANK PAGE

8 9626/02/M/J/19 © UCLES 2019 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. BLANK PAGE

This document consists of 9 printed pages. © UCLES 2019 [Turn over Cambridge Assessment International Education Cambridge International Advanced Subsidiary and Advanced Level INFORMATION TECHNOLOGY 9626/02 Paper 2 Practical May/June 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 May/June 2019 series for most Cambridge IGCSE™, Cambridge International A and AS Level and Cambridge Pre-U components, and some Cambridge O Level components.

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 2 of 9 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 May/June 2019 © UCLES 2019 Page 3 of 9 Video Ellmau video Image ratio of software set to 16:9 1 mark First 4.8 seconds of video removed 1 mark All elements fill the window 1 mark Title frame/s Snapshot of first frame extracted 1 mark « used at start as background image 1 mark « with Ellmau coat of arms added with transparency 1 mark « top right of screen and clearly visible 1 mark « with appropriate proportions and covers most of light 1 mark Title frame with coat of arms 10 seconds duration 1 mark Title text Animation effect gives time to read text 1 mark Title text Celebrate the New Year with us 1 mark Top left of screen and clearly visible 1 mark Large easily read white sans-serif font 1 mark Video 1 Smooth transition into video / no movement 1 mark End of video cut 1 mark « so only 10 seconds of video remain 1 mark Snapshot of last frame extracted 1 mark Set as the caption background 1 mark Placed after video 1 mark Caption Different animation effect for caption 1 mark Caption frames 7 seconds duration 1 mark Caption in visible font with good contrast 1 mark Caption text includes Ellmau & Austria – 1 mark 2nd block includes Fireworks at midnight 1 mark 3rd block includes 31st December 2019 1 mark Video 2 Clip Ellmau2 placed after caption 1 mark 2-second transition between the caption and the clip 1 mark Credits Appropriate transition for credits background 1 mark Appropriate background for credits 1 mark « placed at end of second video clip 1 mark « of 7 seconds duration 1 mark Filmed by – GBRvideo 1 mark Location – Ellmau 1 mark Country – Austria 1 mark Date filmed – 31/12/2018 1 mark Cand. name & numbers in credits in appropriate format 1 mark Appropriate blank line(s) as spacing between credits 1 mark Movie saved/exported as Ellmau_1_ZZ999_9999 1 mark In mp4 format 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 4 of 9 Audio file Opera.mp3 Video File Ellmau_2 Video File Ellmau_3 Evaluation: Evaluation of 2 data files for merging 6 from: Most columns in same order and contain same data Forename and Surname columns are reversed in 2 data sets DOB and Age columns contain different data types Age should never be used as a field as it constantly changes Where age has been given DOB cannot be calculated Address columns all have the same column heading Both files contain blank rows which need to be removed Both files stored in (unformatted) text format Remove header row from second data set as files are merged 1 mark each Max 6 Forename Surname Arne Bircher Jon Haby Thomas Tobler Tim Frueh Dekke Heitz Simone Aebi Luca Rim Lara Forrer John Zwinky Romina Von Gunten Arenwald Wurgler Julian Bruster Sofia Walp Arda Hegler Nathan Ganz Alessandro Messerli Luca Roser Andri Jud Matteo Nufer Laura Haener Livio Stager Giulia Ramseyer Noah Truby Raphael Michlig Luca Gisi Matteo Isch Alice Thoeny Leonardo Jud Fabian Ging Leon Joost Nico Lichty Leandro Laqua Mattia Polt Camille Oberlin Matteo Hilty Mia Leupp Nico Gantert Lukas Lutz Audio file End of clip removed 1 mark «cut to 43 seconds 1 mark Fade in present 1 mark «with appropriate duration for length of sound clip 1 mark Fade out present 1 mark «with appropriate duration for length of sound clip 1 mark Audio clip saved as Opera2_ZZ999_9999.mp3 1 mark Ellmau_3_ Export or conversion as Ellmau_3_ZZ999_9999 1 mark In avi format 1 mark Ellmau_2_ Soundtrack added as specified 1 mark Movie saved as Ellmau_2_ZZ999_9999.wmv 1 mark Employees.csv Merge 2 documents into Employees9999.csv 1 mark Remove date of birth and age columns 1 mark With data integrity for Forename and Surname intact 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 5 of 9 Tobias Mayer Maximilian Leitner Alexander Berger David Fuchs Jonas Eder Simon Fischer Jakob Schmid Elias Winkler Florian Weber Sebastian Schwarz Julian Maier Paul Schneider Felix Reiter Fabian Mayr Leon Brunner Raphael Wimmer Philipp Lackner Moritz Wieser Daniel Koch Matthias Stadler Luca Weiss Nico Krenn Niklas Konig Michael Kaiser Samuel Bohm Noah Mair Dominik Hauser Gabriel Hofbauer Benjamin Kaufmann Johannes Winter Marcel Riegler Lorenz Ortner Thomas Hackl Manuel Kern Valentin Kogler Jan Fink Stefan Seidl Konstantin Maurer Christoph Mayrhofer Matteo Posch Leo Schober Kilian Karner Marco Reisinger Clemens Hofmann Andreas Resch Markus Fritz Adrian Riedl Martin Konig Fabio Kaiser Max Bohm Oliver Mair Jonathan Hauser Christian Hofbauer Luis Kaufmann Nicolas Winter Emil Riegler Yannick Ortner Tim Hackl Ben Kern Sarah Kogler Anna Fink Hannah Seidl Lena Maurer Leonie Mayrhofer Julia Posch Sophie Schober Marie Karner Emma Reisinger Laura Hofmann Johanna Resch Katharina Fritz Valentina Riedl Lea Konig Lara Kaiser Sophia Bohm Mia Mair Lisa Hauser Jana Hofbauer Emilia Kaufmann Magdalena Winter Amelie Riegler Viktoria Ortner Elena Hackl Clara Kern Nina Kogler Alina Fink Emily Seidl Lina Maurer Helena Mayrhofer Theresa Posch Isabella Schober Miriam Karner Lilly Reisinger Selina Hofmann Marlene Resch Luisa Fritz Vanessa Riedl Larissa Strobl Emely Holler Maja Schwaiger Anja Rainer Jasmin Neubauer Pia Jager Anna-Lena Grabner Annika Weis Maria Hartl Nora Friedl Valerie Brandstatter Franziska Horvath Lukas Klein Arda Frank Nathan Hutter Alessandro Mullner Luca Unger Andri Hager Matteo Kofler Laura Lindner Livio Kainz Mia Wiesinger Tobias Schweiger Raphael Hoffmann Nico Sommer Matteo Unger Alice Hager Leonardo Kofler Fabian Lindner Leon Kainz Nico Wiesinger Leandro Schweiger Mattia Hoffmann Camille Sommer

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 6 of 9 Evidence 8 Alternative solution: Spreadsheet 1 mark Advantages of relational database: 4 from: No duplication of data« «quicker to edit/amend/delete data «referential integrity/only need to edit/amend/delete data once « «less chance of errors/omissions No need for different worksheets/all data in same place Relational database has more powerful search facilities 1 mark each Max 4 Employees Appropriate table name: no Ellmau or Nos 1 mark Forename (or similar) 1 mark « set as alphanumeric/text 1 mark Surname (or similar) 1 mark « set as alphanumeric/text 1 mark JobCode (or similar) 1 mark « set as alphanumeric/text 1 mark VenueCode (or similar) 1 mark « set as alphanumeric/text 1 mark No additional fields 1 mark All fieldnames short and meaningful 1 mark Primary key on ID as Autonumber 1 mark All tables All fieldnames contain no spaces or underscores 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 7 of 9 Jobs Appropriate table name: no Ellmau 1 mark JobCode (or similar) 1 mark « set as alphanumeric/text 1 mark JobDescription (or similar) 1 mark « set as alphanumeric/text 1 mark Primary key on JobCode 1 mark Venue Appropriate table name: not Ellmau 1 mark VenueCode (or similar) 1 mark « set as alphanumeric/text 1 mark VenueName (or similar) 1 mark « set as alphanumeric/text 1 mark TypeCode (or similar) – not Type as not 3NF 1 mark « set as alphanumeric/text or numeric 1 mark Primary key on VenueCode 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 8 of 9 VenueType Appropriate table name 1 mark TypeCode (or similar) may be text/numeric 1 mark « set to match data type of Venue.TypeCode 1 mark VenueType (or similar) 1 mark « set as alphanumeric/text 1 mark Primary key on TypeCode 1 mark Relationship Jobs.JobCode to Employees.JobCode 2 marks «1 to Many 1 mark Relationship Venue.VenueCode to Employees.VenueCode 2 marks «1 to Many 1 mark

9626/02 Cambridge International AS/A Level – Mark Scheme PUBLISHED May/June 2019 © UCLES 2019 Page 9 of 9 Relationship VenueType.TypeCode to Venue.TypeCode 2 marks «1 to Many 1 mark Report Chef OR Trainee Chef only 1 mark Venue Name and Address as row headings 1 mark Job Description (not code) as column headings 1 mark Correct counts and gridlines with no totals 1 mark