MANGU HIGH SCHOOL TRIAL 2 MOCK 2021 COMPUTER STUDIES PRACTICAL MARKING SCHEME

1. The following table contains data extracted from an employees’ payroll file maintained by a certain company.

(a)  (i)         Create a database file and save as Company.                       (Award 1 mk)

(ii)        Create a table named Workers containing the fields in the table.

            (¹/₂mk x 6 fields, and ½ mk for saving as Workers) (Award Max 3 mks)

(iii)       Choose and set an appropriate field as a primary key.                  (Award ½ mk)

(b)  (i)         Create a form named WorkersForm.

                  – Form  title (award 1¹/₂ mk)

                  – Controls (award 3 mks)

                  – Layout (award 2¹/₂ mks)

      (ii)       Use the WorkersForm to enter the data above into the Workerstable.                                                                                                                        (Award 5 mks)

(c) Create queries to determine:

• number of people with basic salary greater than 32,000/= .Save the query as Basic                                                                                                                    

(Query 1 mk, correct criteria 1 mk and saving as Basic 1 mk) (Award Max 3 mks)

• number of people with basic salary less than 45,000/= and come from computer 

department. Save the query as Computer.                                          

(Query 1 mks, criteria for salary 1¹/₂mks, criteria for department 1¹/₂mks and saving as Computer 1mk) (Award Max 5 mks)

• those whose name begin with letter ‘M’ or end in letter ‘S’. Save the query as Names                                                                                                                    

(Query ½ mk, criteria 2mks and saving as Names ½ mk.)(Award max 3 mks)

(d) (i) Create a query to determine those employees who will earn more than 50,000/= if there is an increment of basic pay by 10%. Save the query as SalaryIncrement.

  (Query ½ mk, calculated field 2 mks, criteria 1 mk and saving as SalaryIncrement ½ mk) (Award Max 4 mks)

(ii) Create a query to determine the year of birth of each employee from the current year. Save as YearOfBirth.                           

                                              

  (Query ½ mk, calculated field for year of birth 2 mks and saving as YearOfBirth ½ mk) (Award Max 3 mks)

(e)  (i) Create a report from Workerstable to display all the data in the table. Save as WorkersReport.                                                            (Award 3 mks)

        (ii)Modify the WorkersReport in (e)(i) above as follows:

1. Add the “EMPLOYEE SALARY DETAILS”as the report title. Use font size 19 pts, bold, underline and centre it across the columns containing data.

(Correct report title 1 mk, font size ½ mk, bold ½ mk, underline ½ mk and centre alignment ½ mk) (Award max 3 mks)

1. Display the employees records according to their department and show the total amount the company spend on each department as salary and the total amount spend on paying all the employees. Save as WorkersReport_2.

  (Displaying employees records according to departments –2mks

Departmental salary subtotal – 2 mks

Grand total on salaries – 1 ½ mks

Saving as WorkersReport_2 – ½ mk) (award Max 6 mks)

(f)    Print:                                                                                                          

• Workers table;       (award 1 mk)
• YearOfBirth query;            (award 1 mk)
• WorkersReport_2.                  (award 1 mk)

•  
• (i) Enter marks to the spreadsheet                             ( award 12mks)

(ii) Insert two blank rows at the top of the worksheet.                                  

(Award 1x 2 = 2 mks)

(iii)            Enter the following title and subtitle in the blank rows respectively; KERICHO COMPUTER COLLEGE and APPLICANTS FILE.        (Award 1 x 2=2 mks)

(iv) Centre the title and subtitle across the columns that contain data.(award 1 x 2=2 mks)

• Using functions, compute:

(i)  the mean for each student and format it to zero decimal place.(award 3 mks)

(ii) the position of each student.               (Award 2 mks)

(iii)            the highest score for each subject.      (Award 2 mks)

(Deny marks if the candidate used a formula instead of a function)

• The college wishes to analyze the applicants’ data in order to find those applicants who qualify for admission to pursue a course in IT. Successful candidates MUST meet the following minimum requirements;
  • Must have scored a mean of 45 marks and above;
  • Must have scored 60 marks and above in Mathematics;
  • Must have scored 50 marks and above in either English or Kiswahili.

      Enter an appropriate function in cell I4 and copy it to other cells to determine whether the student qualifies for admission. If the student qualifies, the function should display ‘Successful’. Otherwise it should display ‘Unsuccessful’.              (Award 12 mks)   

• Create a function to find the number of applicants who are successful. (award 3 mks)
• Copy the entire worksheet to sheet 2 and rename it as Successful Applicants.

(Copying entire worksheet to sheet 2 – (award 3 mks)

Renaming sheet 2 as Successful Applicants – (award 1 mk)

• Filter the ‘Successful Applicants’ sheet to display the records of those applicants who are successful.                                                                                      (award 4 mks)
• Print:                                                                                                              
• Sheet1;
• Successful Applicants Sheet;

 (1 x 2=(award 2 mks)