SQL: create tables for bank schema

Create tables with primary keys

CREATE TABLE Branch (
  branch_name VARCHAR(30) PRIMARY KEY,
  branch_city VARCHAR(30),
  assets      DECIMAL(12,2)
);
CREATE TABLE Account (
  acc_no      INT PRIMARY KEY,
  branch_name VARCHAR(30),
  balance     DECIMAL(12,2),
  FOREIGN KEY (branch_name) REFERENCES Branch(branch_name)
);
CREATE TABLE Customer (
  customer_name   VARCHAR(30) PRIMARY KEY,
  customer_street VARCHAR(40),
  customer_city   VARCHAR(30)
);
CREATE TABLE Loan (
  loan_no     INT PRIMARY KEY,
  branch_name VARCHAR(30),
  amount      DECIMAL(12,2),
  FOREIGN KEY (branch_name) REFERENCES Branch(branch_name)
);
CREATE TABLE Depositor (
  customer_name VARCHAR(30),
  acc_no        INT,
  PRIMARY KEY (customer_name, acc_no),      -- composite key
  FOREIGN KEY (customer_name) REFERENCES Customer(customer_name),
  FOREIGN KEY (acc_no) REFERENCES Account(acc_no)
);
CREATE TABLE Borrower (
  customer_name VARCHAR(30),
  loan_no       INT,
  PRIMARY KEY (customer_name, loan_no),
  FOREIGN KEY (customer_name) REFERENCES Customer(customer_name),
  FOREIGN KEY (loan_no) REFERENCES Loan(loan_no)
);

Each table is created after the tables it refers to: Branch before Account and Loan, and Customer, Account and Loan before Depositor and Borrower.

List all tables

SHOW TABLES;

Account, Borrower, Branch, Customer, Depositor, Loan

Insert values into Account

Branch rows must exist first (foreign key):

INSERT INTO Branch VALUES ('Putalisadak', 'Kathmandu', 9000000),
                          ('Lakeside', 'Pokhara', 4000000);

INSERT INTO Account (acc_no, branch_name, balance)
VALUES (101, 'Putalisadak', 25000),
       (102, 'Putalisadak', 8000),
       (103, 'Lakeside', 51000);

SELECT * FROM Account;

acc_no | branch_name | balance
101    | Putalisadak | 25000.00
102    | Putalisadak |  8000.00
103    | Lakeside    | 51000.00

(The statements were tested for syntax and results in SQLite; SHOW TABLES is MySQL-specific.)