A startup Local Bank (Bank Lesotho) has decided to implement an online system to manage customers’ details, loans, inventory and sales of its different products. You have been requested to ensure that the following information is captured in their proposed database using SQL Server 2012:
a). The Bank has outlets country wide including Maseru mall, Pioneer Mall, Sefika Mall etc. Each location is identified by its Location ID, address, Town/City contact number and business type can either be for corporate customers/private clients and normal banking customers.
b) The bank has many employees at different positions where each employee has an employee number, location, first name, last name, job title (Teller, Manager, marketers etc.) address City/town contact number.
c) The bank has customers who have account numbers located at any of the branches nationwide with the bank and the customers can either be a corporate/private client or ordinary customers. The system should provide running customer balances at the end of each month and also minimum, average and maximum values of the running balance for each customer.
d). the main operation of the bank is to open accounts for customers, customers are given account numbers, they can save or withdraw money from their accounts and there are different types of account (Savings, current, call account etc.).
e). After opening accounts with the bank, the customers can apply for loans or overdraft from the bank which is subject to approval or rejection depending on the credit worthiness of the individual customers. Loans have a monthly repayment amount, interest rates, loan period and a deadline for payment.
f). Customers can either apply for debit or credit card with the bank to facilitate transactions.
g). Transactions should have transaction ID, type of transactions, Date, amount, and Balance.
Outputs
The system should have a functionality to display the report on:
1. Display relevant information on customers who have accounts in the bank.
2. Display bank balances of individual customers.
3. Display information on loan applications approval status.
4. Display information on how much interest will be paid on money loaned from the bank
5. Display customers who are defaulting on loans taken.
The following stored procedures are required:
1. Create a stored procedure that will return all transactions for a specific customer.
2. The system should ensure that if the customers are defaulting on loans it should be recorded in loans table and an appropriate message should be displayed.
Implementing Business Rules
1. The maximum unsecured loan amount by a normal customer should be less than 1,000,000 if applications surpasses 1,000,000 a message should be displayed.
2. Withdrawal should not be possible on zero balance (No negative Balance in account).
3. If a customer has a loan he should not qualify for another loan.
4. A message should be displayed when your loan has almost finished 3 months to completion.
