Notes

CREATE DATABASE BankDB;
USE BankDB;


CREATE TABLE tbl_branch (
branchID INT PRIMARY KEY AUTO_INCREMENT,
br_name VARCHAR(50),
br_city VARCHAR(50),
br_balance DECIMAL(10, 2)
);


CREATE TABLE tbl_customer (
customerID INT PRIMARY KEY AUTO_INCREMENT,
cs_name VARCHAR(50),
cs_street VARCHAR(100),
cs_city VARCHAR(50)
);


CREATE TABLE tbl_account (
accountNo INT PRIMARY KEY AUTO_INCREMENT,
ac_balance DECIMAL(10, 2),
branchID INT,
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);


CREATE TABLE tbl_loan (
loanNo INT PRIMARY KEY AUTO_INCREMENT,
branchID INT,
ln_amount DECIMAL(10, 2),
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);


CREATE TABLE tbl_depositor (
customerID INT,
accountNo INT,
dp_date DATE,
PRIMARY KEY (customerID, accountNo),
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),


FOREIGN KEY (accountNo) REFERENCES tbl_account(accountNo)
);


CREATE TABLE tbl_borrower (
customerID INT,
loanNo INT,
bo_date DATE,
PRIMARY KEY (customerID, loanNo), -- Composite Primary Key
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),
FOREIGN KEY (loanNo) REFERENCES tbl_loan(loanNo)
);


CREATE TABLE tbl_employee (
employeeID INT PRIMARY KEY AUTO_INCREMENT,
emp_Name VARCHAR(50),
dob DATE,
street VARCHAR(100),
city VARCHAR(50),
nid VARCHAR(20)
);


CREATE TABLE tbl_posting (
postingID INT PRIMARY KEY AUTO_INCREMENT,
branch_Name INT,
start_at DATE,
end_at DATE,
position VARCHAR(50),
FOREIGN KEY (employeeName) REFERENCES tbl_employee(employeeName),
FOREIGN KEY (branchName) REFERENCES tbl_branch(branchName)
);


INSERT INTO tbl_branch (br_name, br_city, br_balance)
VALUES ('Main Branch', 'City A', 100000.00),
('West Branch', 'City B', 150000.00);


INSERT INTO tbl_customer (cs_name, cs_street, cs_city)
VALUES ('John Doe', '123 Elm St', 'City A'),
('Jane Smith', '456 Oak St', 'City B');


INSERT INTO tbl_account (ac_balance, branchID)
VALUES (5000.00, 1),
(3000.00, 2);


INSERT INTO tbl_loan (branchID, ln_amount)
VALUES (1, 20000.00),
(2, 15000.00);


INSERT INTO tbl_depositor (customerID, accountNo, dp_date)
VALUES (1, 1, '2024-09-23'),
(2, 2, '2024-09-22');


INSERT INTO tbl_borrower (customerID, loanNo, bo_date)
VALUES (1, 1, '2024-09-21'),
(2, 2, '2024-09-20');


INSERT INTO tbl_employee (employee_name, dob, street, city, nid)
VALUES ('Alice Johnson', '1985-08-15', '789 Pine St', 'City A', 'NID123456'),


('Bob Brown', '1990-06-20', '321 Maple St', 'City B', 'NID654321');


INSERT INTO tbl_posting (employeeID, branchID, start_at, end_at, position)
VALUES (1, 1, '2023-01-01', '2024-12-31', 'Manager'),
(2, 2, '2023-05-01', NULL, 'Cashier');


SELECT * FROM tbl_customer;

SELECT a.accountNo, a.ac_balance, b.br_name, c.cs_name
FROM tbl_account a
JOIN tbl_branch b ON a.branchID = b.branchID
JOIN tbl_depositor d ON a.accountNo = d.accountNo
JOIN tbl_customer c ON d.customerID = c.customerID;


SELECT l.loanNo, l.ln_amount, b.br_name, c.cs_name
FROM tbl_loan l
JOIN tbl_branch b ON l.branchID = b.branchID
JOIN tbl_borrower bo ON l.loanNo = bo.loanNo
JOIN tbl_customer c ON bo.customerID = c.customerID;


SELECT * FROM tbl_employee;


SELECT p.postingID, e.employee_name, b.br_name, p.start_at, p.end_at, p.position
FROM tbl_posting p
JOIN tbl_employee e ON p.employeeID = e.employeeID
JOIN tbl_branch b ON p.branchID = b.branchID;