Information and Communication Technology 0417/21 February/March 2020: question paper and mark scheme

Question

Using a suitable software package, open the file M2120SERVICING.rtf Save the document in your work area with the file name SAFETY Make sure it is saved in the format of the software you are using. [1] EVIDENCE 1 Place in your Evidence Document a screenshot to show this file has been saved. Make sure there is evidence of the file type.

Question

Place in the header of the document: • your name, centre number and candidate number left aligned • an automated field to display today’s date right aligned. Place in the footer: • automated page numbers left aligned • the file name with its full path right aligned. Make sure that: • all items align to the page margins • no other text or placeholders appear in the header and footer areas • headers and footers are displayed on all pages. [4] The house style specifications for paragraphs are: Space before (points) Space after (points) Font size (points) Enhancement Line spacing Alignment Font style GS-Title sans serif 20 centre all capitals single 0 0 GS-Subtitle sans serif 18 right italic single 0 0 GS-Subhead serif 16 centre bold, underlined single 0 16 GS-Sectionhead serif 14 left all caps, underlined single 0 8 GS-Body serif 14 justified none single 0 14 GS-Table serif 14 left none single 0 0 GS-List serif 12 left a tick (3) bullet indented single 0 0 1 cm from the left margin These styles have been correctly applied to the document but three do not match the house style specification. In step 3 you will correct these and provide evidence.

Question

Identify and correct these three styles. [5] EVIDENCE 2 Place in your Evidence Document screenshot(s) to show the definition of each style you have corrected.

Question

Change the page layout so that all the text following the subtitle Report for home customers is displayed in two columns of equal width with a 1 centimetre space between the columns. [2]

Question

Import the image M2120AC.jpg Place this at the start of the paragraph below the section heading: REGULAR TASKS YOU CAN DO YOURSELF [1]

Question

Resize the image so that: • it is half the width of the column • the aspect ratio is maintained • text wraps around the image. Align the image to the left margin and top of the text. [3]

Question

Locate the table and format it to look like this: Service level Annual boiler service Boiler and One controls Two Central heating Three Home electrics Four Plumbing Five Drains Make sure that: • the table fits within the column width • no words split on text wrap • gridlines are visible when printed. [5]

Question

Produce a vertical bar chart using the file M2120CHART.csv to compare the number of Gas, Electric and Aircon engineers for the North and East regions only. Format the chart to include: • the title Regional Coverage • the value axis title Number of Engineers • category axis labels to identify Gas, Electric and Aircon • the category axis title Areas of Expertise • the value data labels above each bar • a legend to identify the regions. Place this chart after the paragraph that ends … how we cover the regions: Make sure that the chart fits within the column width and all data and labels are fully visible. [6]

Question

Spell check and proofread the document. Make sure that: • paper size, page orientation and margins have not been changed • there are no widows or orphans • the table and lists are not split over two columns or pages • spacing between all items is consistent. Save the document using the same file name and format used in Step 1. Print the document. [2] PRINTOUT 1 Make sure that you have entered your name, centre number and candidate number on your document. [Total: 29] Task 3 – Database You are now going to prepare some reports for the company. These are based on tables containing data about customers and engineers. Engineers can be qualified to service gas, electric and/or air conditioning systems. Make sure all currency values display the same currency symbol and are set to 2 decimal places. Dates are to be imported as day month year.

Question

Using a suitable database package, import the file M2120CUSTOMERS.csv Use these field names and data types: Field name Data type Notes Store Title Text GivenName Text FamilyName Text CustomerNumber Number A unique reference code StreetAddress Text City Text PostCode Text EmailAddress Text TelephoneNumber Text Date Date/time Display as dd-MMM-yy e.g. 01-Jan-20 This is the date of the service visit Session Text Service Text Meaning of these codes: G = Gas, E = Electric, A = Air conditioning Price Numeric/currency Display with a currency symbol Store as currency to 2 decimal places to 2 decimal places EngineerRef Text Set a primary key using an appropriate field from the table. Save the data. [4] EVIDENCE 3 Take screenshot evidence to show the primary key, field names and data types used in the table and place this in your Evidence Document.

Question

Import the file M2120ENGINEERS.csv Use these field names and data types: Field Name Data Type Notes EngNo Text This must be a unique code FirstName Text LastName Text Gas Boolean/logical Display as Yes/No Electric Boolean/logical Display as Yes/No Aircon Boolean/logical Display as Yes/No GasRegNo Text Gas registration number Set a primary key using an appropriate field from the table. Save the data. [2] EVIDENCE 4 Take screenshot evidence to show the primary key, field names and data types used in the table and place this in your Evidence Document.

Question

An engineer has joined the company and a record of their personal details and skills needs to be entered into the database. Create a user-friendly columnar data entry form and use it to enter this data as a new record: EngNo FirstName LastName Gas Electric Aircon GasRegNo E27 Ewan Barrett Yes Yes No GRN541643 [4] EVIDENCE 5 Take screenshot evidence of the form with the new record displayed and place this in your Evidence Document.

Question

Create a one-to-many relationship between EngNo in the engineer table and EngineerRef in the customer table. [1] EVIDENCE 6 Take screenshot evidence of the relationship and place this in your Evidence Document.

Question

Using fields from the customer table, produce a tabular report that: • shows only the fields CustomerNumber, Title, FamilyName, StreetAddress, PostCode, TelephoneNumber, Date, Session, Service, Price and EngineerRef in this order with data and labels displayed in full. Do not group the data • selects only visits on the date 05-Mar-20 • sorts the data into ascending order of EngineerRef and ascending order of Session • has a page orientation of landscape • fits on a single page wide • calculates the total price charged and displays this as currency below the Price column • has a label Daily total to the left of this number • includes the report title Daily Service Visits • has your name, centre number and candidate number displayed on every page of the report. [10] Save and print your report. PRINTOUT 2 Make sure you have entered your name, centre number and candidate number on the report. EVIDENCE 7 Place in your Evidence Document a screenshot showing the formula used to calculate the daily total.

Question

Using fields from both tables, produce a tabular report that: • shows only the fields Title, FamilyName, CustomerNumber, StreetAddress, PostCode, Date, Session, Service, FirstName, LastName, Gas and GasRegNo in this order with data and labels displayed in full. Do not group the data • selects only the records where the ○ PostCode starts with PE ○ Session is AM ○ Service is G or GE • sorts the data in ascending order of LastName • calculates and displays at the bottom of the report the number of visits for this session • displays a label Engineer visits to be made to the left of this number • fits on a single landscape page • includes the title Morning Gas Service Schedule at the top of the report • displays your name, centre number and candidate number at the top right of the report. [10] Save and print this report. PRINTOUT 3 Make sure you have entered your name, centre number and candidate number on the report. EVIDENCE 8 Place in your Evidence Document a screenshot showing the formula used to calculate the number of visits for this session. [Total: 31] Task 4 – Mail merge You are going to produce letters for engineers who will attend a training course.

Question

Use the file M2120MAIL.rtf as the master document for the mail merge and the file M2120QUALS.csv as the data source. Replace in the master document: • <Date> with a field to display today’s date • <Your Name> with your own name • the text and chevrons with the merge fields from the data source file as follows: Text and chevrons to replace Replace with merge field <TTL> Title <FN> Name <LN> Last_Name <ADD1> Street <ADD2> Town <ADD3> Postcode <REG> Region <CR> Course_Ref <CSE> Course <SD> Start_Date <DUR> Duration <TT> Tutor <LOC> Location Make sure the spacing, punctuation and alignment in the original master document are maintained. Include your name, centre number and candidate number in the footer of the document. Save the master document. Display the field codes. EVIDENCE 9 Take screenshot evidence showing the date field code. Place this screenshot in your Evidence Document. Spell check and proofread the master document. Print the master document. PRINTOUT 4 Print your master document with the merge field codes displayed. Make sure your name, centre number and candidate number appear on the document. [6]

Question

Set an automated filter to select the records where the region is South or East EVIDENCE 10 Place in your Evidence Document screenshot(s) showing your automated selection method. [2]

Question

Merge and print only the letters for the selected engineers. PRINTOUT 5 Print the letters resulting from the merge. Make sure your name, centre number and candidate number appear on the documents. [1] [Total: 9] Task 5 – Presentation You are going to create a short presentation. All slides must have a consistent layout and formatting. Unless otherwise instructed, slides must be formatted to display a title and bulleted list.

Question

Create a presentation of 5 slides using the file M2120PRES.rtf [1]

Question

Design a master slide for the presentation with a minimum of 4 features and apply this to all slides. These may include: • slide numbers • lines • colour or design theme • animations • transitions. Your name, centre number and candidate number must appear on the slides. [5] EVIDENCE 11 Place in your Evidence Document screenshot(s) to show the features you have applied to the master slide.

Question

Format the first slide so that: • a title slide layout is applied with no bullet • the title and subtitle text are centred in the middle of the slide. Enter your name after the text Presentation by: [1]

Question

Move the last slide with the title Safety in the home to become the second slide. [1]

Question

Add the following text as presenter/speaker notes for the slide with the title Gas appliances Carbon monoxide is a colourless and odourless gas. Print only this slide as presenter/speaker notes. [2] PRINTOUT 6 Print the single slide showing the presenter/speaker notes. Make sure your name, centre number and candidate number appear on the slide.

Question

Print the presentation in a format showing 6 slides to the page. [1] PRINTOUT 7 Print the presentation in a format that would show 6 slides to the page. Make sure your name, centre number and candidate number appear on the presentation. [Total: 11] Task 6 – Printing the Evidence Document You now need to print your Evidence Document. PRINTOUT 8 Make sure your name, centre number and candidate number appear on every page of your Evidence Document. Save and close your Evidence Document.