Notes

-- Create the database
CREATE DATABASE db_bank;

-- Switch to the database
USE db_bank;

-- Create tbl_branch
CREATE TABLE tbl_branch (
branch_id INT AUTO_INCREMENT PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
branch_address VARCHAR(255) NOT NULL
);

-- Create tbl_account
CREATE TABLE tbl_account (
account_id INT AUTO_INCREMENT PRIMARY KEY,
branch_id INT NOT NULL,
account_number VARCHAR(50) NOT NULL UNIQUE,
account_type ENUM('Savings', 'Current') NOT NULL,
balance DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (branch_id) REFERENCES tbl_branch(branch_id)
);

-- Create tbl_depositor
CREATE TABLE tbl_depositor (
depositor_id INT AUTO_INCREMENT PRIMARY KEY,
account_id INT NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (account_id) REFERENCES tbl_account(account_id)
);

-- Create tbl_loan
CREATE TABLE tbl_loan (
loan_id INT AUTO_INCREMENT PRIMARY KEY,
branch_id INT NOT NULL,
loan_amount DECIMAL(10, 2) NOT NULL,
loan_type ENUM('Personal', 'Home', 'Car', 'Education') NOT NULL,
FOREIGN KEY (branch_id) REFERENCES tbl_branch(branch_id)
);

-- Create tbl_borrower
CREATE TABLE tbl_borrower (
borrower_id INT AUTO_INCREMENT PRIMARY KEY,
loan_id INT NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (loan_id) REFERENCES tbl_loan(loan_id)
);

-- Create tbl_customer
CREATE TABLE tbl_customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
customer_address VARCHAR(255),
phone_number VARCHAR(15),
email VARCHAR(100),
UNIQUE(phone_number),
UNIQUE(email)
);