COMPUTER STUDIES PAPER 2 - 2019 MOKASA II MOCK EXAMINATION

1. 1. 1. Using a database management system, create a database file named ANTIGUA_ENT_LTD to store the data below.  (1mark)
         CommodityTable

      4. Commodity_ID	Commodity_Name	Retail_Cost
         C10	Apple VinegarSuper	400
         C20	Assorted Sandwich	300
         C30	Sweetener Max	130
         C40	Soda 1L	120
         C50	UPS APC 2	5000
         C60	Persil Detergent	130
         C70	Minute maid	120
         C80	Pizza XL	1000
         C90	Sacho Springs water 5L	650
         C100	UPS APC 1	3000

         SuppliersTable
         

         Supplier_ID	Supplier_Name	Town
         S-11	Antigua Foods	Nakuru
         S-12	Prime Fast Food	Nairobi
         S-13	Gilanis Mart	Nakuru
         S-14	Power Dynamics Ltd	Eldoret
         S-15	Panda Energy	Nakuru
         S-16	SAHICO

         OrderTable
         

         Order_ID	Commodity_ID	Supplier_ID	Order_Date
         101	C10	S-11	12/04/19
         102	C20	S-12	22/04/19
         103	C30	S-11	12/04/19
         104	C40	S-11	02/05/19
         105	C50	S-14	12/04/19
         106	C60	S-16	22/08/18
         107	C70	S-17	19/08/18
         108	C80	S-12	12/04/18
         109	C90	S-13	11/05/18
         110	C100	S-15	15/04/18

         Identify a field to be assigned as primary key for each table.(ii) Create tables named: Commodity_Table, Suppliers_Table and Order_Table in the database created in
      5. to store the information aboveassigning appropriate data types for each field.   (14marks)
      6. reate relationships among the tables and enforce referential integrity.                            (3 marks)
      7. Capture the number of units for the Commodities as shown below in the Commodity Table.             (3 marks)

c. Create a query named RETAILCOST to display Commodity name, retail price, number of units, suppliers’ name and calculate the total retail price for each Commodity. (4 marks)

d. Create a query named CUSTOM_QRto display name of each Commodity, retail price and number of units ordered. The query should contain Commodities whose suppliers name start with

     letter “P” and Total Retail Price is between Ksh.4000 and less than Ksh.50,000.     (5 marks)

e. Create a report namedSUPPLIERS_RPT to display Commodity name, suppliers’ name, total retail price and date of order.  (4 marks)

• Group records per suppliers’ town
• Title the report as “ANTIGUA SUPPLIERS DETAILS”
• Show the Total Retail Prices per supplier.

f. Create a report named ORDERS_2019 to display Commodity name, retail price, suppliers’ name, town and items ordered in the year 2018 only. Show the number of suppliers. 

g. Create a pie chart to display Total Retail Prices and their respective Commodities.  (2 marks)

h. Create a form namedCOMMODITIES_2018 used to enter data in the databaseto appear as shown below (5 marks)

1. Print the following                                     (4 marks)
   • Tables:CommodityTable
   • Queries:RETAIL_COST and CUSTOM_QR
   • Reports: SUPPLIERS _RPT