💳 Real-Time Banking Database System

🔹 Short Description
A Real-Time Banking Database System implemented in Oracle/MySQL, supporting:

• 🔐 Secure account management
• 💸 Transactions
• 🏦 Loans
• 📜 Audit logging
• 🚨 Fraud detection

📌 Backend: Work in progress – APIs and frontend integration coming soon.

---

🎯 Purpose

This project is my academic project to explore:

• ✅ How a database works in a backend system
• ✅ How data is processed securely and reliably
• ✅ How ACID principles ensure consistency & reliability
• ✅ How to design real-world banking schemas & transactions

---

📑 Table of Contents

• 📌 Project Overview
• ✨ Features
• 🛠 Tech Stack
• 🗄 Database Schema
• 📊 ER Diagram
• 🔒 ACID Transactions
• ⚙️ Setup Instructions
• ▶️ Usage
• 🚀 Future Enhancements

---

📌 Project Overview

This project simulates a core banking database system, handling:
👤 Customer accounts, 💰 financial transactions, 🏦 loans, 🏢 branches, 👨‍💼 employees, and 📜 audit logs.

It emphasizes:

• 🔐 Data integrity
• ⚡ Reliability
• 🔒 Security using ACID-compliant transactions

---

✨ Features

• 👤 Customer Management – Securely store/manage customer info
• 🏦 Account Management – Savings, Current, Loan accounts per customer
• 💳 Transactions – Deposit, Withdrawal, Transfer (with rollback on failure)
• 💰 Loan Management – Track loan accounts, interest, and status
• 📜 Audit Logging – Log every transaction for security & traceability
• 🚨 Fraud Detection – Alerts for suspicious activity
• 🏢 Branch & Employee Management – Store branch info & employees

---

🛠 Tech Stack

Layer	Technology
🗄 Database	Oracle Live SQL / MySQL / Supabase (Cloud DB)
📝 Query Language	SQL, PL/SQL
🔄 Transaction Mgmt	ACID (COMMIT, ROLLBACK)
📊 Diagram Tool	Mermaid (ER Diagrams)
⚙️ Backend	Node.js (ongoing development)

---

🗄 Database Schema

Core Tables:

• 👤 Customers – Customer details
• 🏦 Accounts – Account info and balances
• 💳 Transactions – Money movements
• 💰 Loans – Loan details
• 🏢 Branches – Branch details
• 👨‍💼 Employees – Employee records
• 📜 AuditLogs – Transaction audit trail
• 🚨 FraudAlerts – Suspicious alerts
• 💳 Cards – Debit/Credit card info

---

📊 ER Diagram

erDiagram
    CUSTOMERS ||--o{ ACCOUNTS : owns
    ACCOUNTS ||--o{ TRANSACTIONS : has
    ACCOUNTS ||--o{ LOANS : holds
    BRANCHES ||--o{ EMPLOYEES : employs
    ACCOUNTS ||--o{ CARDS : linked_to
    ACCOUNTS ||--o{ AUDITLOGS : logs
    TRANSACTIONS ||--o{ FRAUDALERTS : triggers

    CUSTOMERS {
        int customer_id PK
        string name
        string email
        string phone
        string kyc_id
        timestamp created_at
    }

    ACCOUNTS {
        int account_id PK
        int customer_id FK
        enum account_type
        decimal balance
        timestamp created_at
    }

    TRANSACTIONS {
        int txn_id PK
        int account_id FK
        enum txn_type
        decimal amount
        timestamp txn_time
        int related_account FK
    }

    LOANS {
        int loan_id PK
        int account_id FK
        decimal loan_amount
        decimal interest_rate
        date issue_date
        date due_date
        enum status
    }

    BRANCHES {
        int branch_id PK
        string branch_name
        string address
        string ifsc_code
    }

    EMPLOYEES {
        int employee_id PK
        int branch_id FK
        string name
        enum role
        date hire_date
    }

    AUDITLOGS {
        int log_id PK
        string action
        int account_id FK
        text description
        timestamp log_time
    }

    FRAUDALERTS {
        int alert_id PK
        int txn_id FK
        int account_id FK
        string alert_type
        timestamp created_at
        enum status
    }

    CARDS {
        int card_id PK
        int account_id FK
        enum card_type
        string card_number
        date expiry_date
        int cvv
    }

Loading