Notes

It seems you want to clarify the use of primary keys in the `Employee` and `Posting` tables. Let’s emphasize the primary keys in the context of your SQL schema. Below is the updated SQL code with a clear focus on the primary keys for the `Employee` and `Posting` tables:

```sql
-- 1. Create Database
CREATE DATABASE BankDB;
USE BankDB;

-- 2. Create Tables

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

-- Customer Table
CREATE TABLE tbl_customer (
customerID INT PRIMARY KEY AUTO_INCREMENT, -- Primary Key
cs_name VARCHAR(50),
cs_street VARCHAR(100),
cs_city VARCHAR(50)
);

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

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

-- Depositor Table
CREATE TABLE tbl_depositor (
customerID INT,
accountNo INT,
dp_date DATE,
PRIMARY KEY (customerID, accountNo), -- Composite Primary Key
FOREIGN KEY (customerID) REFERENCES tbl_customer(customerID),
FOREIGN KEY (accountNo) REFERENCES tbl_account(accountNo)
);

-- Borrower Table
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)
);

-- Employee Table
CREATE TABLE tbl_employee (
employeeID INT PRIMARY KEY AUTO_INCREMENT, -- Primary Key
employee_name VARCHAR(50),
dob DATE,
street VARCHAR(100),
city VARCHAR(50),
nid VARCHAR(20) -- National ID
);

-- Posting Table
CREATE TABLE tbl_posting (
postingID INT PRIMARY KEY AUTO_INCREMENT, -- Primary Key
employeeID INT,
branchID INT,
start_at DATE,
end_at DATE,
position VARCHAR(50),
FOREIGN KEY (employeeID) REFERENCES tbl_employee(employeeID),
FOREIGN KEY (branchID) REFERENCES tbl_branch(branchID)
);

-- 3. Insert Sample Data

-- Insert into Branch
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 Customer
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 Account
INSERT INTO tbl_account (ac_balance, branchID)
VALUES (5000.00, 1),
(3000.00, 2);

-- Insert into Loan
INSERT INTO tbl_loan (branchID, ln_amount)
VALUES (1, 20000.00),
(2, 15000.00);

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

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

-- Insert into Employee
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 Posting
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');

-- 4. Query Data

-- Fetch all customers
SELECT * FROM tbl_customer;

-- Fetch account details with branch and customer info
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;

-- Fetch loan details with borrower and branch info
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;

-- Fetch employee details
SELECT * FROM tbl_employee;

-- Fetch postings with employee and branch info
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;
```

### Key Points:
- **Primary Keys**: Each table has a designated primary key:
- `tbl_branch`: `branchID`
- `tbl_customer`: `customerID`
- `tbl_account`: `accountNo`
- `tbl_loan`: `loanNo`
- `tbl_depositor`: Composite primary key (`customerID`, `accountNo`)
- `tbl_borrower`: Composite primary key (`customerID`, `loanNo`)
- `tbl_employee`: `employeeID`
- `tbl_posting`: `postingID`

This ensures that each record in every table can be uniquely identified. If you need further clarifications or modifications, feel free to ask!