Advanced Data Base project
Budget: $10 – $30 USD
Project Scenario:
The Khalat Loans company is a grassroots organization that gives out loans and gets paid back over installment terms running between 3 and 12 months.
Paying back within 6 months attracts 5% interest; any payment between 6 and 12 months attracts 10%. Each loan account has an employee in charge who will earn interest.
There are three managers within the organization who do not earn any interest. The two departments are Small Loans (SM-LNS), for loans up to 6 months, and Large Loans (LG-LNS), for the other loan borrowing. One of the three managers is the Chief Executive.
Activities: The Khalat Loans must be automated. It works as follows:
1. Debtors are the people contracting loans from the company. They first register into a debtor’s register before they can take out a loan.
2. When a debtor gets a loan, his loan amount is entered into an Accounts register where an installment plan and date of contraction is entered. A debtor can have more than one loan.
3. An entry is made to identify the employee attached to a particular loan. This employee can be from the SM-LNS department or the LG-LNS department. Managers do not participate. Managers are shareholders or owners of the company.
4. A transaction is set up for each account and installments are automatically calculated, dates determined for repayment and interest incorporated in the repayments. This interest can be written in the Accounts table (or derived on demand according to the length of payment). However, the transaction must be indicated clearly in the transaction table.
To do list:
1. Create a suitable UML ERD and analyze any linkages or table relations that may need to be refined. As an example, notice that departments are suggested but do not appear in the script.
2. Create audit tables that will hold historic data and activity information for any deletions, updates, and insertions into relevant tables. Important data requiring audit tables include employees, transactions, accounts, and retirement of accounts. These have to be shown in the ERD.
3. Suggest how best to implement this database. Where possible, confirm whether or not this database would ensure that all activities are auditable. List any defaults for the tables; that is, overdue payments.
4. Explore and recommend implementations for adding applications to the database, such as interfaces, forms, and reports. These could include but are not limited to ODBC, programming interfaces, and other tools.
The Khalat Loans company is a grassroots organization that gives out loans and gets paid back over installment terms running between 3 and 12 months.
Paying back within 6 months attracts 5% interest; any payment between 6 and 12 months attracts 10%. Each loan account has an employee in charge who will earn interest.
There are three managers within the organization who do not earn any interest. The two departments are Small Loans (SM-LNS), for loans up to 6 months, and Large Loans (LG-LNS), for the other loan borrowing. One of the three managers is the Chief Executive.
Activities: The Khalat Loans must be automated. It works as follows:
1. Debtors are the people contracting loans from the company. They first register into a debtor’s register before they can take out a loan.
2. When a debtor gets a loan, his loan amount is entered into an Accounts register where an installment plan and date of contraction is entered. A debtor can have more than one loan.
3. An entry is made to identify the employee attached to a particular loan. This employee can be from the SM-LNS department or the LG-LNS department. Managers do not participate. Managers are shareholders or owners of the company.
4. A transaction is set up for each account and installments are automatically calculated, dates determined for repayment and interest incorporated in the repayments. This interest can be written in the Accounts table (or derived on demand according to the length of payment). However, the transaction must be indicated clearly in the transaction table.
To do list:
1. Create a suitable UML ERD and analyze any linkages or table relations that may need to be refined. As an example, notice that departments are suggested but do not appear in the script.
2. Create audit tables that will hold historic data and activity information for any deletions, updates, and insertions into relevant tables. Important data requiring audit tables include employees, transactions, accounts, and retirement of accounts. These have to be shown in the ERD.
3. Suggest how best to implement this database. Where possible, confirm whether or not this database would ensure that all activities are auditable. List any defaults for the tables; that is, overdue payments.
4. Explore and recommend implementations for adding applications to the database, such as interfaces, forms, and reports. These could include but are not limited to ODBC, programming interfaces, and other tools.