COMPUTER STUDIES PAPER 2 - KCSE 2019 MARANDA MOCK EXAMINATION

« Previous Topic
COMPUTER STUDIES PAPER 1 - KCSE 2019 MARANDA MOCK EXAMINATION

Next Topic »
GEOGRAPHY PAPER 1 - KCSE 2019 MARANDA MOCK EXAMINATION

Download PDF for future reference Get on Whatsapp for 50/-

QUESTION ONE

The data below shows a spreadsheet for loan applicants from a youth fund from a certain county.

MEMBER NO	NAME	DATE OF BIRTH	DATE OF APPLICATION	GENDER	AGE	AMOUNT APPLIED
A001	ABC	1/3/1992	1/1/2016	M		Ksh 200,000.00
B001	CDE	2/6/1999	3/1/2016	F		Ksh 600,000.00
C008	FGH	5/6/1992	3/1/2016	M		Ksh 400,000.00
K001	JKL	2/7/1990	7/1/2016	F		Ksh 700,000.00
S007	MNO	2/9/1960	7/1/2016	M		Ksh 600,000.00
Z006	KRS	4/8/1992	2/2/2016	M		Ksh 500,000.00
A008	TUV	4/8/1960	3/2/2016	M		Ksh 700,000.00
B005	CED	2/6/1995	5/2/2016	F		Ksh 600,000.00
C011	HGF	5/6/1990	10/2/2016	M		Ksh 400,000.00
K012	LKJ	2/7/1970	6/1/2016	M		Ksh 700,000.00
S019	NOM	2/9/1991	4/6/2016	M		Ksh 600,000.00

Type the data as shown in the spreadsheet above name the worksheet as Original save the workbook as application. (14 Marks)

Copy the worksheet named original above to another worksheet in the same workbook and name it as Evaluation.
1. Fill the column for the (AGE) which is the difference between DATE OF APPLICATION and DATE OF BIRTH in years to the nearest whole number. (6 Marks)
2. Create a validation in the DATE OF APPLICATION such that it should be after DATE OF BIRTH. (2 Marks)
3. Format all columns having currency Data type to Kenya shilling. (2 Marks)
4. Create the columns COMMENT and AMT AWARDED. The COMMENT is based on the following
  - I f the gender is male and the age by the data of applicable is greater than 30 years the COMMENT is INELIGIBLE otherwise ELIGIBLE.
  - Amount awarded if a person is eligible if 80% of the amount applied.
5. Fill in the two columns for the AMOUNT AWARDED and COMMENT using the formula. If an applicant is INELIGIBLE the amount awarded is left blank. (10 Marks)
Copy the worksheet named Evaluation above to another worksheet in the same workbook and name it as final.
1. Filter out data in the final in the final worksheet leaving out those who have been awarded a loan. (2 Marks)
2. Create a table showing the total amount awarded to all Males and total awarded to Females and total for both male and females. In the sample table below fill in the values using conditional if statement (6 Marks)
3. Draw a pie chart representing the total amount for males awarded and females warded(2 Marks)
  
  AMOUNT(Ksh)
  
  MALE
  
  FEMALE
  
  Total
Print the following: (4 Marks)
1. Original worksheet
2. Evaluate worksheet
3. Final worksheet
4. The pie chart

QUESTION TWO
The table below an extract of a manual data structure system used by a librarian of a particular school

ADM NO	S NAME	CLASS	BOOK NO	TITLE	BORROW TYPE	DATE BORROWED	DATE RETURNED
123	MIKE	1A	B001	COMPTER STUDIES BK 1	SHORT	2-1-2016	12-1-2016
456	JOHN	1B	A002	COMPREHENSIVE ENG BK 1	LONG	1-2-2016	17-1-2016
789	ADREW	1C	B003	KIE MATHEMATICS BK 1	LONG	1-3-2016	14-3-2016
987	JAMES	2A	D004	LONGHORN GEOGRAPHY BK2	SHORT	1-4-2016	8-4-2016
654	JACOB	2B	K005	KISWAHILI SHAIRI BK2	SHORT	1-5-2016	9-5-2016
321	NANCY	2C	B006	MATHEMATICS BK2	SHORT	2-3-2016	10-3-2016
879	MARY	3A	C005	HISTORY BK3	LONG	2-4-2016	17-4-2016
564	MERCY	3B	K009	FOUNDATION CHEMISTRY BK 1	LONG	2-5-2016	14-5-2016
213	PETER	3C	H001	KISWAHILI LUGHA BK 2	LONG	1-4-2016	18-4-2016
123	MIKE	1A	K005	KISWAHILI SHAIRI BK2	SHORT	1-5-2016	19-5-2016
456	JOHN	1B	B006	MATHEMATICS BK2	SHORT	1-4-2016	5-4-2016
789	ADREW	1C	C005	HISTORY BK3	SHORT	1-4-2016	7-4-2016
987	JAMES	2A	K009	FOUNDATION CHEMISTRY BK 1	LONG	1-5-2016	8-5-2016
654	JACOB	2B	H001	KISWAHILI LUGHA BK 2	LONG	1-4-2016	22-4-2016

The library charges 2 shillings per every book borrowed per day, if the book is not returned in time it attracts a penalty of 5 Shilling per day. The short loan is a maximum of seven days while the long loan is 14 days

From the table above create a database called library.
1. Create THREE tables Student(ADMNO as primary key) and Book(BOOK_NO as primary key) and Borrow( Borrow_id as primary key which is auto number). ( 6 Marks)
2. Create relationship between the three tables ( 2 Marks)
3. Fill in the data in the three tables (15 Marks)
Create a the following queries
1. Query named chargesqry for all charges for books on short loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,NORMAL_CHARGE,PENALTY_CHARGE,TOTAL_CHARGE)( 10 Marks)
2. Query named shortchargesqry for all charges for books on short loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,TOTAL_CHARGE) ( 2 Marks)
3. Query named longchargesqry for all charges for books on long loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,TOTAL_CHARGE) ( 2 Marks)
4. Create a report showing the total amount which the library has earned between any two dates on short loan save it as shortbetweendatesrpt format the currency data type to Kenya shillings ( 6 Marks)
5. Create a report showing the total charged to a student for both long and short loan borrowing in one report save it as studentchrgrpt format the currency data type to Kenya shillings ( 4 Marks)
Print the following ( 4 Marks)
1. All tables with data
2. Both query with data
3. Shortbetweendatesrpt
4. studentchrgrpt

MARKING SCHEME

1. The data below shows a spreadsheet for loan applicants from a youth fund. Type the data as shown in the spreadsheet above name the worksheet as Original save the workbook as application. (14 Marks)
  Each column for raw data 2mks@ -------------------------12mks
  Saving 2mks
2. Copy the worksheet named original above to another worksheet in the same workbook and name it as Evaluation. Fill the column for the (AGE) which is the difference between DATE OF APPLICATION and DATE OF BIRTH in years to the nearest whole number. (6 Marks)
  =(D2-C2)/365.25 ------------------------------------------------------4mks
  Drping to other cells in the column ------------------------------------2mks
  1. Create a validation in the DATE OF APPLICATION such that it should not accept any entry whose age is below 18 years by the date of application. (2 Marks)
  2. Fill in the two columns for the AMOUNT AWARDED and COMMENT using the formula. If an applicant is INELIGIBLE the amount awarded is left blank.                                                  ( 10 Marks)
    =IF(AND(F2>30,E2="M"),"INELIGIBLE","ELIGIBLE")     4mks
    =IF(H2="ELIGIBLE",0.8*G2,"")                                          2mks
    Filling in the other value in columns                              4mks
3. Copy the worksheet named Evaluation above to another worksheet in the same workbook and name it as final.
  1. Filter out data in the final in the final worksheet leaving out those who have been awarded a loan. (2 Marks)
  2. Create a table showing the total amount awarded to all Males and total awarded to Females and total for both male and females. In the sample table below fill in the values using conditional if statement (6 Marks)
    
    TOTAL
    
    MALE
    
    Ksh 1,680,000.00
    
    FEMALE
    
    Ksh 1,520,000.00
    
    Total
    
    Ksh 3,200,000.00
    
    =SUMIF(E2:E12,"M",I2:I12)-----------------------------------3mks
    =SUMIF(E2:E12,"F",I2:I12) -----------------------------------3mks
    Total -------------------------------------------------------2mks
  3. Draw a pie chart representing the total amount for males awarded and females warded. (2 Marks)
4. Print the following: (4 Marks)
  1. Original worksheet
  2. Evaluate worksheet
  3. Final worksheet
  4. The pie chart
    @ 1 mk for printing

The table below an extract of a manual data structure system used by a librarian of a particular school

ADMNO	S NAME	CLASS	BOOK NO	TITLE	BORROW TYPE	DATE BORROWED	DATE RETURNED
123	MIKE	1A	B001	COMPTER STUDIES BK 1	SHORT	2-1-2016	12-1-2016
456	JOHN	1B	A002	COMPREHENSIVE ENG BK 1	LONG	1-2-2016	17-1-2016
789	ADREW	1C	B003	KIE MATHEMATICS BK 1	LONG	1-3-2016	14-3-2016
987	JAMES	2A	D004	LONGHORN GEOGRAPHY BK2	SHORT	1-4-2016	8-4-2016
654	JACOB	2B	K005	KISWAHILI SHAIRI BK2	SHORT	1-5-2016	9-5-2016
321	NANCY	2C	B006	MATHEMATICS BK2	SHORT	2-3-2016	10-3-2016
879	MARY	3A	C005	HISTORY BK3	LONG	2-4-2016	17-4-2016
564	MERCY	3B	K009	FOUNDATION CHEMISTRY BK 1	LONG	2-5-2016	14-5-2016
213	PETER	3C	H001	KISWAHILI LUGHA BK 2	LONG	1-4-2016	18-4-2016
123	MIKE	1A	K005	KISWAHILI SHAIRI BK2	SHORT	1-5-2016	19-5-2016
456	JOHN	1B	B006	MATHEMATICS BK2	SHORT	1-4-2016	5-4-2016
789	ADREW	1C	C005	HISTORY BK3	SHORT	1-4-2016	7-4-2016
987	JAMES	2A	K009	FOUNDATION CHEMISTRY BK 1	LONG	1-5-2016	8-5-2016
654	JACOB	2B	H001	KISWAHILI LUGHA BK 2	LONG	1-4-2016	22-4-2016

From the table above create a database called library.

Create THREE tables Student(ADMNO as primary key) and Book(BOOK_NO as primary key) and Borrow( Borrow_id as primary key which is auto number). ( 6 Marks)

2 Marks each table design
Create relationship between the three tables ( 2 Marks)

2 Marks

Fill in the data in the three tables ( 15 Marks)

BOOK
BOOK_NO	TITLE
A002	COMPREHENSIVE ENG BK 1
B001	COMPTER STUDIES BK 1
B003	KIE MATHEMATICS BK 1
B006	MATHEMATICS BK2
C005	HISTORY BK3
D004	LONGHORN GEOGRAPHY BK2
H001	KISWAHILI LUGHA BK 2
K005	KISWAHILI SHAIRI BK2
K009	FOUNDATION CHEMISTRY BK 1

( 5 Marks each)

STUDENT
ADMNO	s_NAME	CLASS
123	MIKE	1A
213	PETER	3C
321	NANCY	2C
456	JOHN	1B
564	MERCY	3B
654	JACOB	2B
789	ADREW	1C
879	MARY	3A
987	JAMES	2A

BORROW
BORROW ID	BORROW TYPE	DATE BORROWED	DATE RETURNED	BOOK NO	ADMNO
1	SHORT	02-Jan-16	12-Jan-16	B001	123
2	LONG	01-Feb-16	17-Feb-16	A002	456
3	LONG	01-Mar-16	14-Mar-16	B003	789
4	SHORT	01-Apr-16	08-Apr-16	D004	987
5	SHORT	01-May-16	09-May-16	K005	654
6	SHORT	02-Mar-16	10-Mar-16	B006	321
7	LONG	02-Apr-16	17-Apr-16	C005	879
8	LONG	02-May-16	14-May-16	K009	564
9	LONG	01-Apr-16	18-Apr-16	H001	213
10	SHORT	01-May-16	19-May-16	B001	123
11	SHORT	01-Apr-16	05-Apr-16	A002	456
12	SHORT	01-Apr-16	07-Apr-16	B003	789
13	LONG	01-May-16	08-May-16	D004	987
14	LONG	01-Apr-16	22-Apr-16	K005	654

Create a the following queries

Query named chargesqry for all charges for books on short loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,NORMAL_CHARGE,PENALTY_CHARGE,TOTAL_CHARGE)( 10 Marks)

short charge qry
ADMNO	NAME	CLASS	BOOK NO	TITLE	BORROW TYPE	DATE BORROWED	DATE RETURNED	DAYS	EXTRA DAYS	EXTRA COST	NORMAL COST	TOTAL COST
123	MIKE	1A	B001	COMPTER STUDIES BK 1	SHORT	02-Jan-16	12-Jan-16	10	3	15	20	35
987	JAMES	2A	D004	LONGHORN GEOGRAPHY BK2	SHORT	01-Apr-16	08-Apr-16	7	0	0	14	14
654	JACOB	2B	K005	KISWAHILI SHAIRI BK2	SHORT	01-May-16	09-May-16	8	1	5	16	21
321	NANCY	2C	B006	MATHEMATICS BK2	SHORT	02-Mar-16	10-Mar-16	8	1	5	16	21
123	MIKE	1A	B001	COMPTER STUDIES BK 1	SHORT	01-May-16	19-May-16	18	11	55	36	91
456	JOHN	1B	A002	COMPREHENSIVE ENG BK 1	SHORT	01-Apr-16	05-Apr-16	4	-3	0	8	8
789	ADREW	1C	B003	KIE MATHEMATICS BK 1	SHORT	01-Apr-16	07-Apr-16	6	-1	0	12	12

DAYS: ([DATE_RETURNED]-[DATE_BORROWED])                                                                                                                                   ( 2 Marks)
EXTRA_DAYS: IIf([BORROW TYPE]="SHORT",[DAYS]-7,IIf([BORROW TYPE]="LONG",[DAYS]-14))   ( 2 Marks)
EXTRA_COST: IIf([EXTRA_DAYS]<=0,0,IIf([EXTRA_DAYS]>0,[EXTRA_DAYS]*5))                                               ( 2 Marks)
NORMAL_COST: [DAYS]*2                                                                                                                    ( 2 Marks)
TOTAL_COST: [NORMAL_COST]+[EXTRA_COST]                                                                                                                           ( 2 Marks)

Query named shortchargesqry for all charges for books on short loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,TOTAL_CHARGE) ( 2 Marks)
Query named longchargesqry for all charges for books on long loan the query should have the following fields(ADMNO,S_NAME,BOOK_NO,TITLE,BORROW TYPE,DATE_BORROWED,DATE_RETURNED,NO_OF_DAYS_BORROWED,TOTAL_CHARGE) ( 2 Marks)
Create a report showing the total amount which the library has earned between any two dates on short loan save it as shortbetweendatesrpt format the currency data type to Kenya shillings ( 2 Marks)
Create a report showing the total charged to a student for both long and short loan borrowing in one report save it as studentchrgrpt format the currency data type to Kenya shillings ( 4 Marks)