-- MySQL Script generated by MySQL Workbench
-- Thu May 8 12:17:48 2025
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema bankdb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema bankdb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bankdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `bankdb` ;
-- -----------------------------------------------------
-- Table `bankdb`.`branch`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`branch` (
`BranchID` INT NOT NULL AUTO_INCREMENT,
`BranchName` VARCHAR(100) NULL DEFAULT NULL,
`Location` VARCHAR(255) NULL DEFAULT NULL,
`ManagerName` VARCHAR(100) NULL DEFAULT NULL,
`ContactNumber` VARCHAR(15) NULL DEFAULT NULL,
PRIMARY KEY (`BranchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`customer` (
`CustomerID` INT NOT NULL AUTO_INCREMENT,
`FullName` VARCHAR(100) NULL DEFAULT NULL,
`DateOfBirth` DATE NULL DEFAULT NULL,
`Gender` VARCHAR(10) NULL DEFAULT NULL,
`Address` VARCHAR(255) NULL DEFAULT NULL,
`ContactNumber` VARCHAR(15) NULL DEFAULT NULL,
`Email` VARCHAR(100) NULL DEFAULT NULL,
`BranchID` INT NULL DEFAULT NULL,
PRIMARY KEY (`CustomerID`),
INDEX `BranchID` (`BranchID` ASC) VISIBLE,
CONSTRAINT `customer_ibfk_1`
FOREIGN KEY (`BranchID`)
REFERENCES `bankdb`.`branch` (`BranchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`account` (
`AccountID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`BranchID` INT NULL DEFAULT NULL,
`AccountType` VARCHAR(50) NULL DEFAULT NULL,
`Balance` DECIMAL(15,2) NULL DEFAULT NULL,
`OpenDate` DATE NULL DEFAULT NULL,
`Status` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`AccountID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
INDEX `BranchID` (`BranchID` ASC) VISIBLE,
CONSTRAINT `account_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`),
CONSTRAINT `account_ibfk_2`
FOREIGN KEY (`BranchID`)
REFERENCES `bankdb`.`branch` (`BranchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`card`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`card` (
`CardID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`CardType` VARCHAR(50) NULL DEFAULT NULL,
`CardNumber` VARCHAR(20) NULL DEFAULT NULL,
`ExpiryDate` DATE NULL DEFAULT NULL,
`CVV` INT NULL DEFAULT NULL,
`Status` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`CardID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
CONSTRAINT `card_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`atmtransaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`atmtransaction` (
`ATMTransactionID` INT NOT NULL AUTO_INCREMENT,
`CardID` INT NULL DEFAULT NULL,
`AccountID` INT NULL DEFAULT NULL,
`TransactionDate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`Amount` DECIMAL(15,2) NULL DEFAULT NULL,
`TransactionType` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`ATMTransactionID`),
INDEX `CardID` (`CardID` ASC) VISIBLE,
INDEX `AccountID` (`AccountID` ASC) VISIBLE,
CONSTRAINT `atmtransaction_ibfk_1`
FOREIGN KEY (`CardID`)
REFERENCES `bankdb`.`card` (`CardID`),
CONSTRAINT `atmtransaction_ibfk_2`
FOREIGN KEY (`AccountID`)
REFERENCES `bankdb`.`account` (`AccountID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`employee` (
`EmployeeID` INT NOT NULL AUTO_INCREMENT,
`FullName` VARCHAR(100) NULL DEFAULT NULL,
`Position` VARCHAR(50) NULL DEFAULT NULL,
`BranchID` INT NULL DEFAULT NULL,
`ContactNumber` VARCHAR(15) NULL DEFAULT NULL,
`Email` VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (`EmployeeID`),
INDEX `BranchID` (`BranchID` ASC) VISIBLE,
CONSTRAINT `employee_ibfk_1`
FOREIGN KEY (`BranchID`)
REFERENCES `bankdb`.`branch` (`BranchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`transaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`transaction` (
`TransactionID` INT NOT NULL AUTO_INCREMENT,
`AccountID` INT NULL DEFAULT NULL,
`TransactionDate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`Amount` DECIMAL(15,2) NULL DEFAULT NULL,
`TransactionType` VARCHAR(20) NULL DEFAULT NULL,
`Description` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`TransactionID`),
INDEX `AccountID` (`AccountID` ASC) VISIBLE,
CONSTRAINT `transaction_ibfk_1`
FOREIGN KEY (`AccountID`)
REFERENCES `bankdb`.`account` (`AccountID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`audit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`audit` (
`AuditID` INT NOT NULL AUTO_INCREMENT,
`TableName` VARCHAR(50) NULL DEFAULT NULL,
`Action` VARCHAR(50) NULL DEFAULT NULL,
`ActionDateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`EmployeeID` INT NULL DEFAULT NULL,
`TransactionID` INT NULL DEFAULT NULL,
PRIMARY KEY (`AuditID`),
INDEX `EmployeeID` (`EmployeeID` ASC) VISIBLE,
INDEX `TransactionID` (`TransactionID` ASC) VISIBLE,
CONSTRAINT `audit_ibfk_1`
FOREIGN KEY (`EmployeeID`)
REFERENCES `bankdb`.`employee` (`EmployeeID`),
CONSTRAINT `audit_ibfk_2`
FOREIGN KEY (`TransactionID`)
REFERENCES `bankdb`.`transaction` (`TransactionID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`creditscore`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`creditscore` (
`CreditScoreID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`Score` INT NULL DEFAULT NULL,
`LastUpdated` DATE NULL DEFAULT NULL,
PRIMARY KEY (`CreditScoreID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
CONSTRAINT `creditscore_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`forex`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`forex` (
`ForexID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`ExchangeRate` DECIMAL(10,4) NULL DEFAULT NULL,
`FromCurrency` VARCHAR(10) NULL DEFAULT NULL,
`ToCurrency` VARCHAR(10) NULL DEFAULT NULL,
`AmountExchanged` DECIMAL(15,2) NULL DEFAULT NULL,
`ExchangeDate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ForexID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
CONSTRAINT `forex_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`frauddetection`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`frauddetection` (
`FraudID` INT NOT NULL AUTO_INCREMENT,
`AccountID` INT NULL DEFAULT NULL,
`DetectionDate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`FraudType` VARCHAR(50) NULL DEFAULT NULL,
`AmountFlagged` DECIMAL(15,2) NULL DEFAULT NULL,
`Description` TEXT NULL DEFAULT NULL,
`Status` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`FraudID`),
INDEX `AccountID` (`AccountID` ASC) VISIBLE,
CONSTRAINT `frauddetection_ibfk_1`
FOREIGN KEY (`AccountID`)
REFERENCES `bankdb`.`account` (`AccountID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`insurance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`insurance` (
`InsuranceID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`InsuranceType` VARCHAR(50) NULL DEFAULT NULL,
`CoverageAmount` DECIMAL(15,2) NULL DEFAULT NULL,
`Premium` DECIMAL(15,2) NULL DEFAULT NULL,
`StartDate` DATE NULL DEFAULT NULL,
`EndDate` DATE NULL DEFAULT NULL,
PRIMARY KEY (`InsuranceID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
CONSTRAINT `insurance_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`investment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`investment` (
`InvestmentID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`InvestmentType` VARCHAR(50) NULL DEFAULT NULL,
`Amount` DECIMAL(15,2) NULL DEFAULT NULL,
`InterestRate` DECIMAL(5,2) NULL DEFAULT NULL,
`StartDate` DATE NULL DEFAULT NULL,
`EndDate` DATE NULL DEFAULT NULL,
PRIMARY KEY (`InvestmentID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
CONSTRAINT `investment_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`loan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`loan` (
`LoanID` INT NOT NULL AUTO_INCREMENT,
`CustomerID` INT NULL DEFAULT NULL,
`BranchID` INT NULL DEFAULT NULL,
`LoanType` VARCHAR(50) NULL DEFAULT NULL,
`LoanAmount` DECIMAL(15,2) NULL DEFAULT NULL,
`InterestRate` DECIMAL(5,2) NULL DEFAULT NULL,
`LoanTerm` INT NULL DEFAULT NULL,
`Status` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`LoanID`),
INDEX `CustomerID` (`CustomerID` ASC) VISIBLE,
INDEX `BranchID` (`BranchID` ASC) VISIBLE,
CONSTRAINT `loan_ibfk_1`
FOREIGN KEY (`CustomerID`)
REFERENCES `bankdb`.`customer` (`CustomerID`),
CONSTRAINT `loan_ibfk_2`
FOREIGN KEY (`BranchID`)
REFERENCES `bankdb`.`branch` (`BranchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `bankdb`.`loanpayment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bankdb`.`loanpayment` (
`PaymentID` INT NOT NULL AUTO_INCREMENT,
`LoanID` INT NULL DEFAULT NULL,
`PaymentDate` DATE NULL DEFAULT NULL,
`PaymentAmount` DECIMAL(15,2) NULL DEFAULT NULL,
PRIMARY KEY (`PaymentID`),
INDEX `LoanID` (`LoanID` ASC) VISIBLE,
CONSTRAINT `loanpayment_ibfk_1`
FOREIGN KEY (`LoanID`)
REFERENCES `bankdb`.`loan` (`LoanID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
