You have been hired to develop a prototype account management system for DebtsFlus State Bank. The system is to manage all savings, checking, and pocket accounts at the bank for customers of DebtsFlus. This includes the following tasks.

•Maintaining balance information for all customer accounts,

•Maintaining information on bank customers, the owners of the accounts,

•Processing transactions (deposits, withdrawals, payments, etc.),

•Generating monthly reports and updating accounts with monthly interest, and

•Providing a simulated ATM-App (Automated Teller Machine-Application) interface.

Your system is to be implemented within the Oracle DBMS environment using Java and the JDBC interface to the Oracle DBMS. You have to demonstrate your system to the teaching staff of this course on CSIL computers (running the Linux operating system).


2.1Accounts

An account is a repository of money owned by one or a set of customers. If there is more than one owner, one of them is designated as the primary owner (who, among other things, will receive monthly statements) and all other owners are co-owners who will have all owner’s privilege except for receiving month statements. Associated with each account is a unique account ID number (an integer), and a list of transactions made during the month. Also associated with each account is a bank branch name in which the account is held.

Accounts come in three flavors: checking, savings, and pocket. There are two sub-flavors of checking accounts: student checking and interest checking. There is only one kind of savings accounts and one kind of pocket accounts.

The following rules apply to all accounts:

1.When a checking or savings account is first created, it must have a positive amount of money in its balance (this should be recorded in the transaction history as a deposit).

2.When a pocket account is created the customer must already have a checking or savings account with a positive balance. The customer also selects the account (checking or savings) to be linked to the pocket account.

3.No transaction can make an account balance to go below $0.00. Any transaction that removes more than the available balance must fail.

4.Any transaction that makes a checking or savings account balance to $0.01 or less automatically closes the account. When an account is closed, the account is not removed from the database until after a final statement is generated (at the end of the month). No transactions (including deposits) are permitted on a closed account.

5.At the end of each month, all open accounts earn interest on their balances. The rate of interest varies with the type of account.

2.2Checking Accounts

In addition to the general account rules, all chec/ngaccounts observe the following:

1.Interest checking has an initial 5.5% annual rate (for simplicity, the monthly interest rate is just the annual rate divided by 12) but can be changed. Student chechng has an iniHal 0.0% annual rate. It should be simple to change these values in your prototype system (bank policies change quite often).

2.The following transactions are valid on a checking account: deposit, withdrawal, transfer, wire, writecheck, and accrue-interest.

2.3Savings Accounts

heh e e c

1.The initial annual interest rate is 7.5%.

2.The following transactions are valid on a savings account: deposit, withdrawal, transfer, wire, and accrue-interest. Note that no checks can be written for a savings account.

2.4Pocket Accounts

Pocket accounts are used to make flexible payments (e.g., from cell phones) to vendors, or other customer’s pocket accounts. The general account rules and the follow rules apply:

1.The interest rate is 0.0%.

2.A flat $5 monthly fee is applied on the first transaction of the month. (If there is no transaction in a month, the monthly fee is waived.)

3.The following transactions are valid on a savings account: top-up, purchase, collect, and pay-friend.

2.5Customers

A customer is an individual with a name, a (unique) tax identification number, and an address. Every customer has a set of owned (or jointly owned) accounts. A customer should be only kept in the system if she or he owns one or more accounts.

Also associated with each customer is a unique PIN (Personal Identification Number), which is a 4-digit string (i.e., leading 0 is acceptable). The PIN is used by the customer to access the accounts she owns, using an ATM-App (interface). The PIN should be private data: it should not be possible to write a program (e.g., SQL) that reads the PIN value for a customer. Rather, only two functions should have access to an account’s PIN, VerifyPinCP/AO and SetPin(OWP/M New PIN). The function VerifyPin(P/A9 returns true if PIN is the correct PIN for the customer. SetPin(OldPIN, NewPIN) changes a customer’s PIN to NewPIN, if OldPIN is the customer’s current PIN. When a new customer is created in the system, the PIN is initialized to 1717.

2.6Transactions

Transactions are actions that move money into and out of accounts, and from one account to another. A transaction can be generated by interaction of a customer with an ATM-App, or by an action taken by a bank teller.

The following transaction types are allowed in the system:

Deposit: Add money to the checking or savings account balance.

Top-Up: Move a specified amount of money from the linked chec/ng/savings account to the pocket account.

Withdrawal: Subtract money from the checking or savings account balance.

Purchase: Subtract money from the pocket account balance.

Transfer: Subtract money from one savings or checking account and add it to another. A transfer can only occur between two accounts that have at least one owner in common. If the transfer was requested by a customer, she or he must be an owner of both accounts. Fur伍ermore, tUe amounttc be moved should not exceed $2,000.

Collect: Move a specified amount of money from the pocket account back to the linked checking/savings account, there will be a 3% fee assessed.

Pay-Friend: Move a specified amount of money from the pocket account to a specified customer’s pocket account.

Wire: Subtract money from one savings or checking cccount and add it to another. The customer that requests this action must be an owner of the account from which the money is subtracted. There is a 2% fee for this action.

Write-Check: Subtract money from the checking account. Associated with a check transaction is a check number. (Note that a check cannot be written from all account types.)

Accrue-Interest: Add money to the checking or savings account. The amount added is the monthly interest rate times the average daily balance for the month (e.g., an account with balance $30 for 10 days and $60 for 20 days in a 30-day month has an average daily balance of $50, not $45!). Interest is added at the end of each month.

Associated with every transaction is the date of the transaction and the account(s) involved (in addition to any information specific to the transaction; e.g., check number). This information will be included in the monthly statement for each account.

Transactions may fail for various reasons. For example, a transaction fails if any of the accounts involved are closed or if more money is deducted than is available in the account.

All successful transactions on an account should be recorded for the account and printed in the monthly statement for the account.

2.7ATM-App Interface

Your system needs to provide a simulated ATM-App (Automated Teller Machine or mobile application) interface. (For simplicity, these two interfaces are combined for this project.) The ATM-App interface should query for a PIN. If the PIN is successfully verified, the ATM-App should allow the customer to make any of the following transactions:

•Deposit, top-up,

•Withdrawal, purchase,

•Transfer (between accounts with a common owner only), collect,

•Wire, pay-friend.

If the customer owns more than one account, she should be prompted for the account(s) the transaction should access.

Optionally, you may want to include a “quick cash” option, which automatically withdraws some preselected amount(s) from a pre-selected account. If there is no pre-selected amount or account, no withdrawal should occur. Similarly, a “quick refill” option can be provided for pocket accounts.

2.8Bank Teller Interface

The Bank Teller Interface allows bank employees to manage customer accounts. The following options should be available:

Enter Check Transaction: Submit a check transaction for an account.

Generate Monthly Statement: Given a customer, do the following for each account she owns (including accounts which have closed but have not been deleted): generate a list of all transactions which have occurred in the last month. This statement should list the names and addresses of all owners of the account. The initial and final account balance is to be included. If thesum ofthe balances of the accounts of which the customer is the primary owner exceeds $100,000, a message should be included in the statement to warn the customer that the limit of the insurance has been reached.

List Closed Accounts: Generate a list of all accounts which have closedif the last month.

Generate Government Drug and Tax Evasion Report (DTER): By federal law, any deposits over $10,000 for a single customer in one month must be reported to the government. Generate a list of all customers which have a sum of deposits, transfers and wires during the current month, over all owned accounts (active or closed), of over $10,000. (How to handle joint accounts?)

Customer Report: Generate a list of all accounts associated with a particular customer and indicate whether the accounts are open or closed.

Add Interest: For all open accounts, add the appropriate amount of monthly interest to the balance. There should be a record in your database that interest has been added this month. So a repeated “Add Interest” transaction would report a warning and do nothing else.

Create Account: Given an account type and other necessary information (e.g. owners, initial balance), create a new account with the specified characteristics. Note that this operation may introduce new customers to the bank. You may consider a create customer operation, but as far as the bank operations are concerned, customer creation is a part of account creation.

Delete Closed Accounts and Customers: Remove from the database all closed accounts and remove all customers who do not own any accounts (because their accounts have closed).

Delete Transactions: Delete the list of transactions from each of the accounts, in preparation for a new month of processing.

2.9Test, Debug, and Demo Operations

The following operations should also be provided in your system. They are not a functional part of your system but they are needed to test and debug your system and also needed for the demo.

•Set a new date, you can assume that the new date is later than all dates recorded in the database, and

•Set a new interest rate for a given type of accounts.

You may choose any particular ways these operations are done. You may also assume that the bank is open every day.

3REQUIREMENTS

Your prototype system should have user interface(s) for the ATM-App and Bank Teller interfaces. It is not necessary to have your interfaces accessible from a web browser, even though access through the web seems logical and desirable. In designing the GUIs of your system, keep in mind the principle of “simple” and