ER Design Project: Banking System

Requirement

A bank has branches, each with a code, a name, a city and total assets. Customers have a customer number, a name, an address and a contact number, and are registered at exactly one home branch.

Accounts have a number, an opening date and a balance, and are held at exactly one branch. An account may be a savings account, with an interest rate and a minimum balance, or a current account, with an overdraft limit. Every account is one or the other and never both.

An account may be jointly held by several customers, and a customer may hold several accounts. The date a customer was added to an account is recorded.

Every transaction on an account records a date and time, a type of either deposit or withdrawal, and an amount. A transfer is recorded as two transactions.

Loans have a number, an amount and a type, are made by one branch, and may be taken jointly by several customers. Loan repayments record a date and an amount, and a repayment has no meaning without its loan.

Step 1 — Entities

Step 2 — The generalisation

              +-------------+
              |   ACCOUNT   |   acc_no, opened_on, balance
              +------+------+
                     |
                    / 
                   / d    disjoint
                  +-----+
                  =======   TOTAL (double line)
                 /       
        +----------+   +----------+
        | SAVINGS  |   | CURRENT  |
        +----------+   +----------+
        interest_rate   overdraft_limit
        min_balance

  DISJOINT because an account is one or the other, never both.
  TOTAL    because every account must be one of them.

Step 3 — Relationships

Account participation in HOLDS is total: an account must have at least one holder. That is a rule the schema cannot express with a foreign key alone, and it is noted here so it can be enforced deliberately.

Step 4 — The relational schema

  branches   ( branch_code PK, branch_name NOT NULL, city, assets )

  customers  ( cust_no PK, cust_name NOT NULL
             , street, city, pincode, contact
             , home_branch NOT NULL -> branches )

  accounts   ( acc_no PK
             , opened_on NOT NULL
             , balance   NOT NULL DEFAULT 0
             , acc_type  NOT NULL   -- savings or current
             , branch_code NOT NULL -> branches )

  savings_accounts ( acc_no PK -> accounts ON DELETE CASCADE
                   , interest_rate NOT NULL
                   , min_balance   NOT NULL )

  current_accounts ( acc_no PK -> accounts ON DELETE CASCADE
                   , overdraft_limit NOT NULL )

  account_holders  ( acc_no  -> accounts
                   , cust_no -> customers
                   , added_on NOT NULL
                   , PK ( acc_no, cust_no ) )

  transactions ( txn_id PK
               , acc_no NOT NULL -> accounts
               , txn_datetime NOT NULL
               , txn_type NOT NULL      -- deposit or withdrawal
               , amount NOT NULL )

  loans      ( loan_no PK, amount NOT NULL, loan_type
             , branch_code NOT NULL -> branches )

  borrowers  ( loan_no -> loans, cust_no -> customers
             , PK ( loan_no, cust_no ) )

  repayments ( loan_no -> loans ON DELETE CASCADE
             , payment_no
             , paid_on NOT NULL, amount NOT NULL
             , PK ( loan_no, payment_no ) )

  10 tables.

Step 5 — The balance question

The requirement lists balance as an attribute of ACCOUNT, and it also lists every transaction. That is a stored value which is also derivable, and it deserves a deliberate decision.

The banking answer is to store it and protect it: update the balance and insert the transaction inside one transaction, so they cannot disagree, and reconcile periodically. This is the first place in the path where atomicity is not a theoretical benefit but the entire design.

Never let an application update the balance and insert the transaction as two separate operations. A crash between them creates money or destroys it. Phase 9 explains exactly why the two statements must be one atomic unit.

Verification questions

1. What is the balance of an account, and does the transaction history agree with it?
2. Which customers jointly hold a given account, and since when?
3. Which accounts at a branch are savings accounts below their minimum balance?
4. What is the outstanding amount on a loan?
5. Which customers hold both an account and a loan at the same branch?

Common mistakes

• Modelling the customer to account relationship as 1:N, which makes joint accounts impossible.
• Storing a single transaction row for a transfer instead of two, losing which account each side affected.
• Putting interest_rate on every account and leaving it null for current accounts.
• Forgetting that repayments are weak, and giving them an independent key.
• Deleting closed accounts. Banking records are retained; use a status instead.

Practice

1. Add a transfer entity that links two transactions and enforces that the amounts match.
2. Write the constraint needed to enforce that every account has at least one holder, and say why a foreign key cannot do it.
3. Design the reconciliation query that finds accounts whose stored balance disagrees with their transaction history.

Conclusion

Ten tables, one disjoint total generalisation, two many to many relationships and one weak entity. The idea worth carrying forward is the balance decision: when a value is both stored and derivable, the design must say how the two are kept in agreement.