The Maisha bora Pharmacy have contracted you as a database developer for their integrated stock management systems with those of their suppliers to ensure that every drug has a reorder level upon which the supplier replenishes the stock. They get referrals from hospital for patients to purchase the drugs upon which the pay commissions to the hospital. They require the following reports per month

1. Commission reports to facilitate payments to hospitals

2. Sales reports

3. Stock level reports

4. Stock profitability reports

5. Stock expiry reports

Required:

a) To identify the data will be necessary and sufficient to provide the above reports.

(10 Marks)

b) Draw the ER diagram (5 Marks)

c) Give a criteria that will assist you in purchasing the DBMS (10 Marks)
Using the scenario in question one how would you undertake the following in MySQL
a) create the Maishabora database (2 Marks)
b) create the tables above (5 Marks)
c) Write a function to increase prices of the items that they sell by 5% (4 Marks)
d) Write a trigger to give an alert when the price of a product or quantity is changed in the database (4 Marks)
e) Identify the risks/threats and mitigation measures if the system is web-based
(10 Marks)