Database application for banking system
You are responsible to write a database application which acts as a simple banking system. This application must be able to do basic banking functions according to the specifications given below. This project must be implemented using DB2, Java, & JDBC.
Section A: Schema Definition
P1.Customer (ID, Name, Gender, Age, Pin)
P1.Account (Number, ID, Balance, Type, Status)
-- All the attributes cannot be NULL.
-- Underlined attributes is denoted as the primary key of that relation.
-- Italicized attributes is system generated.
-- Attribute types and ranges:
- ID: integer (system generate starting from 100)
- Name: varchar(15)
- Gender: char (must be M or F only !!!)
- Age: integer (>= 0)
- Pin: integer (>= 0)
- Number: integer (system generate starting from 1000)
- Balance: integer (>= 0)
- Type: char (C for Checking, S for Saving)
- Status: char (A for Active, I for Inactive)
Section B: Data Administration & Manipulation
Screen # 1 (Title – Welcome to the Self Services Banking System! – Main Menu)
- New Customer
- Customer Login
For #1, prompt for Name, Gender, Age, and Pin. System will return a customer ID if successful.
For #2, prompt for customer ID and pin to authenticate the customer. If user enters 0 for both customer ID & pin, then you will go straight to Screen #4.
Screen # 3 (Title – Customer Main Menu)
- Open Account
- Close Account
- Account Summary
For #1, prompt for customer ID, account type, and balance (Initial deposit). System will return an account number if successful.
For #2, change the status attribute to ‘I’ and empty the balance.
For #3, prompt for the account number and amount.
For #4, prompt for the account number and amount.
For #5, prompt for the source and destination account numbers and amount.
For #6, the total balance and each account number and its balance belong to the same customer.
For #7, go back to the previous menu.
Screen # 4 (Title – Administrator Main Menu)
- Account Summary for a Customer
- Report A :: Customer Information with Total Balance in Decreasing Order
- Report B :: Find the Average Total Balance Between Age Groups
Note: The only way you can get to Screen #4 is by entering 0 as the ID and 0 as the pin in the customer login screen.
For #1, same function as #6 above except that you would need to input the customer ID explicitly.
For #2, you would display the customer ID, Name, Age, Gender, and total balance in decreasing order.
For #3, you prompt for a min & max age to display Average Age and Average Balance.
For #4, go back to the previous menu.
Section C: User Interfaces
- Command line interface described in Section B.
- If you feel GUI is easier, you can do in with GUI panels.
Section D: Additional Notes:
- The special administrator ID and Pin are inserted right together after your DDLs in create.sql.
- Customer IDs are created by the customers and by the administrator.
- The customer and administrator main menus are the top level menu after an operation.
- You can open an account for someone else but you cannot close someone else’s account.
- You can deposit into other people’s accounts but you can’t withdraw from them.
- You can transfer money from your account to someone else but not the reverse.
- All the range checking can be handled in DDL, triggers, or application. Application is the easier.
- Customer account summary should not include accounts in the closed state.
- For all the administrator reports, closed accounts will not be part of the reports.
- For database connections information, use a properties file.
- Must handle error condition gracefully.
- Hint: Define a view to compute total balance and query against the view for the reports.