COMPUTER STUDIES PAPER 2 - 2019 KCSE STAREHE MOCK EXAMS (QUESTIONS AND ANSWERS)

INSTRUCTIONS TO CANDIDATES 

• Type your name and index number at the top right hand corner of each printout.
• Write your name and index number on the Dvd/Cd. .
• Answer any two
• All questions carry equal marks.
• All answers must be saved in your diskette.
• Hand in all the printout and the Dvd/Cd
• ALL fields with currency must be formatted to Ksh

1. The extract below shows a spread sheet used to record books stored in the library. The books are borrowed by students under the plan of Short and long loan borrow type. A short loan borrow type attracts a fee of Ksh 7.50 per day while the Long loan borrow type attracts a fee of Ksh 5 per day.     
                              
   1.  
      1. Create a worksheet and save the spreadsheet as Library      (1 Mark)
      2. Insert a label In the row 1 merge the cells center the label(1 Mark)
      3. Insert the data in the sheet as shown above  (19 Marks)
      4. Name the worksheet as Charge      (1 Mark)
   2.  
      1. Using the formula or functions calculate the days borrowed and charge for every Individual student (8 Marks)
      2. Create a table showing the total charge for each category and use formula to calculate the values   (8 Marks)
      3. Create a Pie chart Based on b(ii) Above                                                                                      (4 Marks)
   3.  
      1. Copy the Data in the worksheet charge to a new worksheet and rename the worksheet as category subtotal   (1Mark)
      2. Sort the data in the worksheet in the part c(i) in ascending values of borrow type         (2 Mark)
      3. Insert the subtotals in the sorted worksheet category subtotal based on Category and Charge (3 Mark)
   4. Print charge worksheet, Category subtotal and the Chart                                                               (3 Marks)

2. ABC cleaning services offers cleaning services to clients categorized as Small, Medium, Corporate and Big. The table in figure 1 represents the charges to their clients and number of cleaners depending on the client. The Administrative Charge(Ksh)/Day and Consumable fee(Ksh)/day are the charges made to the clients
   

   Category	Administrative Charge(Ksh)/Day	Consumable fee(Ksh)/day	No of Cleaners
   Small	5000	1000	4
   Medium	10000	1500	6
   Corporate	15000	2000	8
   Big	20000	2500	10

   
   The cleaners are paid a fixed amount of 500 shilling per day on each cleaning day. Invoices are sent to Clients on monthly bases. The following tables shows data for the client and invoice table
   
   
   1.  
      1. Create a database and save the database as cleaning            (1 Mark)
      2. Create the two tables Client and Invoice      (2 Marks)
      3. Create the relationship between the two tables    (1 Mark)
      4. Fill in the data in the two tables  (10 Marks)
   2.  
      1. Create a query to show Total charge made to the Clients with the following fields. CLIENT_ ID, CLIENT_NAME, CATEGORY, TELEPHONE, CITY, Cleaning Date, Month, Year, Administrative Charge (Ksh), Consumable Fee And Total Charge. Save the Query as invoice(14 Marks)
      2. Create a query Based on the query in Part b(i) above with the following fields CLIENT'S ID, CLIENT_NAME, CATEGORY, TELEPHONE, CITY, Cleaning, Date, Month, Year, Administrative Charge(Ksh), Consumable Fee And Total Charge. Save the Query as client invoice it should filter out the fields by criteria of CLIENT_ID, Month and year         (3 Marks)
      3. Create a query Based on the query in Part b(i) above which should show total annual invoices with individual monthly totals save the query as annual summary (5 Marks)
   3.  
      1. Create a report based on b(ii) Showing all the appropriate summaries Save the report as monthly invoice      (5 Marks)
         
      2. Create a report based on and b(ii) Showing all the appropriate summaries save the report as annualsummary   (5 Marks)
         
      3. Print
         • Annual Summary report (1Mark)
         • Monthly Invoice report  (1 Mark)
         • Invoice Query (1 Mark)
         • Annual Summary (1 Mark)   

MARKING SCHEME

1. The extract below shows a spread sheet used to record books stored in the library. The books are borrowed by students under the plan of Short and long loan borrow type. A short loan borrow type attracts a fee of Ksh 7.50 per day while the Long loan borrow type attracts a fee of Ksh 5 per day.
   1.  
      1. Create a worksheet and save the spreadsheet as Library                                                              (1 Mark)
         Creating a worksheet _____________________________________________1 Mk
      2. Insert a label In the row 1 merge the cells center the label                                                          ( 1 Mark)
         Label Created cell Merged and Centered_______________________________1Mk
      3. Insert the data in the sheet as shown above                                                                            ( 19 Marks)
         AdmnNo____________________________________________________________2 Mks
         Name_______________________________________________________________2 Mks
         Book_No_____________________________________________________________6 Mks
         Borrow type___________________________________________________________3mks
         Date Borrowed_________________________________________________________3Marks
         Date Returned________________________________________________________3Mks
      4. Name the worksheet as Charge                                                                                                              ( 1 Mark)
         Naming Worksheet______________________________________________________1mk
   2.  
      1. Using the formula or functions calculate the days borrowed and charge for every Individual student    ( 8 Marks)
         • Days Borrowed____________________________________________________4Mks
           =DATEDIF(G3,H3,"d")
         • Charge___________________________________________________________4Mks
           =IF(F3="short",I3*7.5,I3*5)
      2. Create a table showing the total charge for each category and use formula to calculate the values   ( 8 Marks)
         

         short	337.5
         long	290

         
         
         • Short____________________________________________________4Mks
           =SUMIF($F$3:$F$16,"short",$J$3:$J$16)
         • Long____________________________________________________4mks
           =SUMIF($F$3:$F$16,"Long",$J$3:$J$16)
      3. Create a Pie chart Based on b(ii) Above  
                                                                                         ( 4 Marks)
         Legends_________________________________________1Mks
         PieChart__________________________________________3Mks
   3.  
      1. Copy the Data in the worksheet charge to a new worksheet and rename the worksheet as category sub total   ( 1Mark)
         Cpying,Pasting Naming________________________________1Mk
      2. Sort the data in the worksheet in the part c(i) in ascending values of borrow type         (2 Mark)
         Sorting in ascending order_____________________________1Mk
      3. Insert the subtotals in the sorted worksheet category subtotal based on Category and Charge     (3 Mark)
         SubToals_____________________________________________________3mks
   4. Print charge worksheet,Categorysubtotal and the Chart                                                               ( 3 Marks)
      Each Document printing __________________________________________1Mk

2. ABC cleaning services offers cleaning services to clients categorized as Small,Medium,Corporate and Big. The table in figure 1 represents the charges to their clients and number of cleaners depending on the client. The Adminstrative Charge(Ksh)/Day and Consumable fee(Ksh)/day are the charges made to the clients
   

   Category	Administrative Charge(Ksh)/Day	Consumable fee(Ksh)/day	No of Cleaners
   Small	5000	1000	4
   Medium	10000	1500	6
   Corporate	15000	2000	8
   Big	20000	2500	10

   
   The cleaners are paid a fixed amount of 500 shilling per day on each cleaning day.Invoices are sent to Clients on monthly bases.The following tables shows data for the client and invoice table
   1.  
      1. Create a database and save the database as cleaning            ( 1 Mark)
         Creating a database_____________________________________________1Mk
      2. Create the two tables Client and Invoice   
               (2Marks)
         @Table 1Mk____________________________________________________2 Mks
      3. Create the relationship between the two tables    (1 Mark)
         
      4. Fill in the data in the two tables                                                                                                  (10 Marks)
         • Table Client
         • Client_Id____________________________________________1mk
         • Client_Name_________________________________________2Mk
         • Category_____________________________________________1Mk
         • Telephone____________________________________________2 Mk
         • City__________________________________________________1Mk
         • Table Invoice
         • Cleaning Date_____________________________________________2Mks
         • Clint_Id__________________________________________________1Mk
   2.  
      1. Create a query to show Total charge made to the Clients with the followingfields. CLIENT_ ID, CLIENT_NAME, CATEGORY, TELEPHONE, CITY, Cleaning_Date,Month, Year,AdminstrativeCharge(Ksh), ConsumableFee And TotalCharge. Save the Query as invoice(14 Marks)
         • Month: Month([Cleaning_Date]) _____________________________________2Mk
         • year: Year([Cleaning_Date]) ___________________________________________2Mk
         • AdminstrativeCharge: IIf([CATEGORY]="Small",5000,IIf([CATEGORY]="Medium",10000,IIf([CATEGORY]="corporate",15000,IIf([CATEGORY]="Big",20000,"Error in Entry"))))________________6 Mks
         • ConsumableFee: IIf([CATEGORY]="Small",1000,IIf([CATEGORY]="Medium",1500,IIf([CATEGORY]="corporate",2500,IIf([CATEGORY]="Big",3000,"Error in Entry"))))___________________2Mks
         • TotalCharge: [ConsumableFee]+[AdminstrativeCharge] _____________________2Mks
      2. Create a query Based on the query in Part b(i) above with the following fields CLIENT'S ID, CLIENT_NAME, CATEGORY, TELEPHONE, CITY, Cleaning_Date,Month, Year,AdminstrativeCharge(Ksh), ConsumableFee And TotalCharge. Save the Query as clientinvoice it should filter out the fields by criteria of CLIENT_ID,Month And year (3 Marks)
         
         
         • Criteria
         • Client_Id______________________________________________1Mk
         • Month________________________________________________1mk
         • Year__________________________________________________Mk
      3. Create a query Based on the query in Part b(i) above which should show total annual invoices with individual monthly totals save the query as annualsummary(5 Marks)
         
   3.  
      1. Create a report based on b(ii) Showing all the appropriate summaries Save the report as monthly invoice   ( 5 Marks)
         • Correct title____________________________________________1mks
         • Headers And Lables______________________________________1Mk
         • Details DATA___________________________________________2Mks
         • Summary______________________________________________1Mk
      2. Create a report based on and b(ii) Showing all the appropriate summaries save the report as annualsummary   ( 5 Marks)
         Year______________________________________________________1Mk
         Properly Labelled Mnths______________________________________1Mk
         Row Totals_______________________________________________2mk
         Grand Total_______________________________________________1Mk
      3. Print all the reports and the queries                                                                              ( 4 Marks)
         Print
         • Annual Summay report________________________________1Mk
         • Monthly Invoice report________________________________1 Mk
         • Invoice Query_________________________________________1Mk
         • AnnualSummary______________________________________1Mk