Bank Management System

By /

In a recent project, I tried to created a modern banking system that integrates essential components such as robust database design, secure user authentication, and flexible account management. This system is structured into three main areas: user management, account handling, and transaction processing, all supported by a well-designed database schema that accommodates various account types, including Savings, Fixed Deposit, and Loan accounts.

Key Features

The banking system includes several notable features for the proposed system:

  • Secure user registration and authentication
  • Support for multiple account types (Savings, Fixed Deposit, Loan)
  • Automated interest calculations
  • Transaction history tracking
  • Real-time balance management
  • Loan repayment processing
  • Maturity date calculations for fixed deposits

Class diagram for the proposed system

Classes

  • Account
    This entity represents account ID, account type, balance, and other account-related information. Following accounts inherit from the parent Account class.
    • SavingsAccount
      Account to achieve withdraw/ deposit transaction processes and check with interest allocated to savings account.
    • LoanAccount
      Loan account recieve loans with a loan interest in a given period of time.Users can repay loans and get remaining balance of the loan
    • FixedAccount
      Users can create fixed Deposit accounts according to a selected rate for selected period of time. Users can calculate the interest earned.
  • User
    The username, password, email address, and other user-specific information are all contained in this entity.
  • Transaction
    This entity records details about the user’s financial transactions, such as the transactionID, amount, date, and other pertinent information.

Relationships

  • Multiplicity
    • There is an association between User and Account, as a user can have multiple
      accounts.
    • The association between Transaction and Account has a multiplicity of * to 1, meaning an account can have multiple transactions.
  • Generalization
    • The Generalized Class Account (Superclass) will be at the base of the triangle (pointed to by the arrow).Specialized Classes SavingsAccount, fixedAccount and loan Account (Subclass) will be at the point where the arrow originates.
  • Composition
    • The relationship between Account and Transaction is a composition, as a transaction cannot exist without an account.

Class diagram for the proposed system

Entities

  • User
    Represents the customer or account holder, with attributes like userId, userName,password, and email. useId is the primary key.
  • Account
    The core entity, which can be of various types such as LoanAccount, FixedDepositAccount, and SavingsAccount. Accounts have attributes like accountNumber, accountType, balance, and other account-specific details. accountNumber is the primary key. Holds the userId as the foreign key by the references of users table.
  • Transaction:
    Records the financial transactions performed on the accounts, with attributes like transactionId, transactionType, transactionAmount, and transactionDate.Holds userId as the foreign key.

Relationships and Assumptions

  • The relationships shown include:
    • A User can create one or more Accounts (1-to-N relationship).
    • An Account can have multiple associated Transactions (1-to-N relationship).
    • The different Account types (Loan, Fixed Deposit, Savings) inherit from the base Account entity, demonstrating generalization.
  • Some key attributes of the Account entity types:
    • LoanAccount: loanAmount, loanTerm, loanInterest, remainingBalance
    • FixedDepositAccount: fixedAmount, fixedTerm, fixedInterest, fixedDepositDate, maturityDate(Derived attribute calculated by fixed deposit date and fixedterm)
    • SavingsAccount: savingsInterest, minimumBalance

Normalization

  • First Normal Form (1NF):
    The schema already appears to be in 1NF since:
    All attributes contain atomic values
    Each column contains values of the same domain
    Each row is unique (through primary keys userid, accountNumber, transactionId)
  • Second Normal Form (2NF):
    Current tables and their candidate keys:
    User (userid)
    Account (accountNumber)
    Transaction (transactionId)
    LoanAccount, FixedDepositAccount, SavingAccount (inherited from Account)
    Need to ensure no partial dependencies exist.

    The current structure looks good for 2NF since:
    All non-key attributes are fully dependent on their primary keys
    The inheritance structure properly separates different account types
  • Third Normal From (3NF) – To implement
    Key improvements to make during normalization:
    Separate the inheritance hierarchy into proper tables
    Establish clear relationships with foreign keys
    Remov any potential transitive dependencies
    Maintained data integrity through proper constraints
  • The benefits of normalized structure:
    Reduced data redundancy
    Better data integrity
    Easier maintenance
    More flexible querying capabilities
    Clear separation of concerns between different account types

SQL Scripts for database creation and Table Design

CREATE DATABASE bank_management;

USE bank_management;

Table users

  • CREATE TABLE users(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
    );

Table transactions

  • CREATE TABLE transactions(
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT,
    type VARCHAR(20) NOT NULL,
    amount DECIMAL (10.2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    );

Table savingsAccounts

  • CREATE TABLE savingsAccounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    balance DECIMAL(10, 2),
    interest_rate DECIMAL(5, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    );

Table fixedDepositAccount

  • CREATE TABLE fixedDepositAccount (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    deposit_amount DECIMAL(10,2),
    term_months INT,
    interest_rate DECIMAL(5,2),
    start_date DATE,
    maturity_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    );

Table loanAccount

  • CREATE TABLE loanAccount (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    loan_amount DECIMAL(10,2),
    remaining_balance DECIMAL(10,2),
    interest_rate DECIMAL(5,2),
    loan_term INT,
    start_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    );

Trigger to set maturity_date derived attribute

  • DELIMITER $$
    CREATE TRIGGER before_insert_fixedDepositAccount
    BEFORE INSERT ON fixedDepositAccount
    FOR EACH ROW
    BEGIN
    IF NEW.start_date IS NULL THEN
    SET NEW.start_date = CURDATE();
    END IF;

    SET NEW.maturity_date = DATE_ADD(NEW.start_date, INTERVAL NEW.term_months MONTH);
    END$$
    DELIMITER ;

SQL Queries

Methods to execute SQL statements in java

Featureexecute()executeQuery()executeUpdate()
SQL StatementsAny typeSELECT statementsINSERT, UPDATE, DELETE, CREATE, ALTER
Return Valueboolean (TRUE for ResultSet, FALSE otherwise)ResultSet objectint (number of rows affected)
PurposeGeneral executionRetrieve dataModify data
  • User Registration
    • User insertion user table
      INSERT INTO users (username, password, email) VALUES (?, ?, ?);
    • User verifying
      SELECT COUNT(*) FROM users WHERE username = ? OR email = ?;
  • User Login
    • User Authentication
      SELECT * FROM users WHERE username = ? AND password = ?;
    • User Account Verification
      SELECT account_type FROM accounts WHERE user_id = ?
  • Create Account
    • savings account
      INSERT INTO savingsAccounts (user_id, balance, interest_rate) VALUES (?, ?, ?)”;
    • fixed account
      INSERT INTO fixedDepositAccount (user_id, deposit_amount,interest_rate, start_date) VALUES (?, ?, ?, ?, CURDATE());
    • loan account
      INSERT INTO loanAccount (user_id, loan_amount, remaining_balance, interest_rate, loan_term , start_date) VALUES (?, ?, ?, ?, ?, CURDATE());
  • Transaction
    • get account_id by user_id
      SELECT account_id FROM savingsAccounts WHERE user_id = ?
    • Process transaction
      Withdraw
      UPDATE savingsAccounts SET balance = balance – ? WHERE account_id = ? AND balance >= ?

      Deposit
      UPDATE savingsAccounts SET balance = balance + ? WHERE account_id = ?
    • Save Transaction
      INSERT INTO transactions ( account_id, type, amount, transaction_date) VALUES (?, ?, ?, ?);
  • Balance Inquiry
    • Savings account balance
      SELECT account_id, balance, interest_rate FROM savingsAccounts WHERE user_id = ?”;
  • FixedDepoDetails
    • Fetch fixed deposit details for the user_id
      SELECT account_id, maturity_date, deposit_amount, interest_rate, start_date FROM fixedDepositAccount WHERE user_id = ?;
  • LoanRepayment Details
    • Fetch account_id from the user_id
      SELECT account_id FROM loanAccount WHERE user_id = ?;
    • Get remaining balance of the account
      SELECT remaining_balance FROM loanAccount WHERE account_id = ?;
    • Update remaining balance
      UPDATE loanAccount SET remaining_balance = remaining_balance – ? WHERE account_id = ?;

Project Resources

Visit Github

Visit Google Drive

Related posts:

  1. AgriSense IoT
  2. Food Aplication
  3. Productive Garbage Disposal Application
  4. iOS Health Application

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top