Notes

CREATE DATABASE kazi_dbms;
USE kazi_dbms;


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),
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),
FOREIGN KEY (loanNo) REFERENCES tbl_loan(loanNo)
);

CREATE TABLE tbl_employee (
employeeID INT PRIMARY KEY AUTO_INCREMENT,
em_name VARCHAR(50),
em_dob DATE,
em_street VARCHAR(100),
em_city VARCHAR(50),
em_nid VARCHAR(20)
);

CREATE TABLE tbl_posting (
postingID INT PRIMARY KEY AUTO_INCREMENT,
employeeID INT,
branchID INT,
po_startAt DATE,
po_endAt DATE,
po_position VARCHAR(20),
FOREIGN KEY (employeeID) REFERENCES tbl_employee(employeeID),
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);


INSERT INTO tbl_branch (br_name, br_city, br_balance)
VALUES
('Main Branch', 'Dhaka', 750000.00),
('West Branch', 'Sylhet', 960000.00);

INSERT INTO tbl_customer (cs_name, cs_street, cs_city)
VALUES
('Kazi', '123 Elephant St', 'Dhaka'),
('Halim', 'Baily Road', 'Sylhet');


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 (employeeName, em_dob, em_street, em_city, em_nid)
VALUES
('John Doe', '1985-05-15', '456 Main St', 'Dhaka', '123456789'),
('Jane Smith', '1990-03-20', '789 South St', 'Sylhet', '987654321');

INSERT INTO tbl_posting (employeeID, branchID, po_startAt, po_endAt, po_position)
VALUES
(1, 1, '2024-01-01', '2024-12-31', 'Manager'),
(2, 2, '2024-06-01', '2024-11-30', 'Assistant');

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.employeeName, b.br_name, p.po_startAt, p.po_endAt, p.po_position
FROM tbl_posting p
JOIN tbl_employee e ON p.employeeID = e.employeeID
JOIN tbl_branch b ON p.branchID = b.branchID;


-- path: "D:xampp7mysqlbinKazikazi_dbms.sql"