Notes

-- Create tbl_branch
CREATE TABLE tbl_branch (
branchId INT AUTO_INCREMENT PRIMARY KEY,
br_name VARCHAR(100) NOT NULL,
br_city VARCHAR(100) NOT NULL
);
-- Create tbl_customer
CREATE TABLE tbl_customer (
customerId INT AUTO_INCREMENT PRIMARY KEY,
cs_name VARCHAR(100) NOT NULL,
cs_street VARCHAR(255),
cs_city VARCHAR(100) NOT NULL
);
-- Create tbl_account
CREATE TABLE tbl_account (
accountNo INT AUTO_INCREMENT PRIMARY KEY,
ac_balance DECIMAL(10, 2) NOT NULL,
branchId INT NOT NULL,
FOREIGN KEY (branchId) REFERENCES tbl_branch(branchId)
);
-- Create tbl_loan
CREATE TABLE tbl_loan (
loanNo INT AUTO_INCREMENT PRIMARY KEY,
branchId INT NOT NULL,
ln_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (branchId) REFERENCES tbl_branch(branchId)
);
-- Create tbl_depositor
CREATE TABLE tbl_depositor (
customerId INT NOT NULL,
accountNo INT NOT NULL,
dp_date DATE NOT NULL,
PRIMARY KEY (customerId, accountNo),
FOREIGN KEY (customerId) REFERENCES tbl_customer(customerId),
FOREIGN KEY (accountNo) REFERENCES tbl_account(accountNo)
);
-- Create tbl_borrower
CREATE TABLE tbl_borrower (
customerId INT NOT NULL,
loanNo INT NOT NULL,
bo_date DATE NOT NULL,
PRIMARY KEY (customerId, loanNo),
FOREIGN KEY (customerId) REFERENCES tbl_customer(customerId),
FOREIGN KEY (loanNo) REFERENCES tbl_loan(loanNo)
);
-- Insert data into tbl_branch
INSERT INTO tbl_branch (br_name, br_city)
VALUES ('Sylhet Main', 'Sylhet'),
('Moulvibazar Branch', 'Moulvibazar'),
('Habiganj Branch', 'Habiganj'),
('Sunamganj Branch', 'Sunamganj');
-- Insert data into tbl_customer
INSERT INTO tbl_customer (cs_name, cs_street, cs_city)
VALUES ('Rahim Ahmed', 'Chowhatta Road', 'Sylhet'),
('Karim Ullah', 'Station Road', 'Sylhet'),
('Fatima Begum', 'Zindabazar', 'Sylhet'),
(
'Anwar Hossain',
'Sreemangal Road',
'Moulvibazar'
),
('Sadia Khatun', 'Nabiganj', 'Habiganj'),
('Jamil Choudhury', 'Jagannathpur', 'Sunamganj');
-- Insert data into tbl_account
INSERT INTO tbl_account (ac_balance, branchId)
VALUES (15000.00, 1),
(25000.00, 1),
(10000.00, 2),
(18000.00, 3),
(22000.00, 4),
(12000.00, 2);
-- Insert data into tbl_loan
INSERT INTO tbl_loan (branchId, ln_amount)
VALUES (1, 50000.00),
(2, 30000.00),
(3, 40000.00),
(4, 25000.00),
(1, 35000.00);
-- Insert data into tbl_depositor
INSERT INTO tbl_depositor (customerId, accountNo, dp_date)
VALUES (1, 1, '2024-12-01'),
(2, 2, '2024-11-30'),
(3, 3, '2024-12-01'),
(4, 4, '2024-11-29'),
(5, 5, '2024-12-01'),
(6, 6, '2024-11-30');
-- Insert data into tbl_borrower
INSERT INTO tbl_borrower (customerId, loanNo, bo_date)
VALUES (1, 1, '2024-11-25'),
(2, 5, '2024-11-26'),
(3, 2, '2024-11-27'),
(4, 3, '2024-11-28'),
(5, 4, '2024-11-29');