Question

an institution that would like to upgrade to a higher bandwidth is offered a 10% discount of the cost of the bandwidth they are upgrading to.
all institutions using the internet services are required to pay by the end of the current month.
an institution which fails to pay is surcharged a fine of 15% of the amount as an overdue fine.
the internet services will be disconnected if the amount due and the fine will not have been paid by the 10th day of the subsequent month. once an institution pays the monthly bill and the overdue fine, a fee of ksh 1,000 is surcharged for re - connection.
develop a fully documented database management system for azani company that would perform the following tasks.

1. register institutions requiring the services together with personal details of the contact person.
2. capture the following payments:

(a) registration fees;
(b) installation fees;
(c) monthly payments.

1. generate the following:

(a) the registered institutions;
(b) list of defaulters;
(c) list of institutions with disconnection issues;
(d) details of infrastructural requirements for each institution.

1. perform the following computation:

(a) total installation cost for each institution;
(b) cost of personal computers and lan services for institutions with assorted services;
(c) total monthly charges for upgraded internet services;
(d) total monthly charges for internet services, overdue fines and re - connection fees generated from each category of institution;
(e) aggregate amount for each service sorted by an institution.

1. generate appropriate reports.

Answer:

s (for the computational parts):

• 4(a): \(T_{install}=I\times(1 - D)+\sum_{i = 1}^{n}C_i\) (where \(I\) is base installation cost, \(D\) is discount factor, \(C_i\) are other installation costs)
• 4(b): \(T_{pc - lan}=P\times n_p+L\times n_l\) (or \(P\times n_p + L\) depending on LAN cost structure)
• 4(c): \(M_{discounted}=B\times0.9\)
• 4(d): \(T_{total}=B\times0.9+(B\times0.9\times0.15)+R\) (where \(R = 1000\) if re - connection is needed, else \(R = 0\))
• 4(e): \(A_{agg}=\sum_{i = 1}^{k}C_i\) (sum of costs of all services for the institution)

For the non - computational tasks (like registering institutions, capturing payments, generating reports etc.), a database management system (DBMS) like MySQL, PostgreSQL or a software like Microsoft Access can be used. The steps for these non - computational tasks would involve:

1. Registering Institutions: Design a table with columns for institution details (name, address, contact person details etc.) in the DBMS. Use SQL commands like CREATE TABLE and INSERT to create the table and add institution records.
2. Capturing Payments: Design separate tables for registration fees, installation fees, and monthly payments. For example, a registration_fees table with columns like institution_id, amount, payment_date; similar tables for installation_fees and monthly_payments. Use INSERT statements to add payment records.
3. Generating Lists/Details:

• Registered Institutions: Use a SELECT * FROM institutions (where institutions is the table with institution details) query to get the list of registered institutions.
• List of Defaulters: Query the monthly_payments table (or a combined table of payments) to find institutions where the payment date is beyond the end of the current month. For example, SELECT institution_id FROM monthly_payments WHERE payment_date > LAST_DAY(CURRENT_DATE) (in SQL, depending on the DBMS, the function to get the last day of the month may vary).
• List of Institutions with Disconnection Issues: Query the table (say disconnection_status) where the disconnection status is 'disconnected' (you need to design a table to track disconnection status with columns like institution_id, disconnection_date, reconnection_date etc.).
• Details of Infrastructural Requirements: Design a table infrastructural_requirements with columns like institution_id, number_of_pcs, lan_requirements etc. and use SELECT queries to retrieve the details.

1. Generating Reports: Use reporting tools available in the DBMS (like report generators in Access) or export the query results to a format like CSV or PDF and then format them to generate reports.

Final Answers (for computational parts):

• 4(a): \(\boldsymbol{T_{install}=I\times(1 - D)+\sum_{i = 1}^{n}C_i}\)
• 4(b): \(\boldsymbol{T_{pc - lan}=P\times n_p+L\times n_l}\) (or relevant variation based on cost structure)
• 4(c): \(\boldsymbol{M_{discounted}=0.9B}\)
• 4(d): \(\boldsymbol{T_{total}=0.9B+(0.9B\times0.15)+R}\) (with \(R = 1000\) if re - connection is applicable)
• 4(e): \(\boldsymbol{A_{agg}=\sum_{i = 1}^{k}C_i}\)

(For non - computational tasks, the implementation depends on the chosen DBMS and the specific design of the database tables and queries as outlined above)