---
title: "SQL: create tables for bank schema"
subject: "Database Management System"
chapter: 3
key: "semester_6/database_management_system:sql-create-tables-bank"
source: https://www.bctengineer.com/q/semester_6/database_management_system/sql-create-tables-bank
site: BCT Engineering
---

# SQL: create tables for bank schema

Database Management System · Chapter 3: Relational Languages & Relational Model

- Asked in: year not given
- Video: [1. Creating Bank database tables using mysql | Bank Database Schema](https://www.youtube.com/watch?v=Y6laCSukAIY) — CodersArts
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/sql-create-tables-bank
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-3

## Question

Consider database schema:
Account (acc_no, branch_name, balance)
Branch (branch_name, branch_city, assets)
Customer (customer_name, customer_street, customer_city)
Loan (loan_no, branch_name, amount)
Depositor (customer_name, acc_no)
Borrower (customer_name, loan_no)
Create tables for the above schema (with primary keys), list all the tables, and insert values into table account.

## Short answer

Create Branch, Account, Customer, Loan, Depositor, Borrower with primary keys and foreign keys. List tables with SHOW TABLES; insert Branch rows first, then insert Account rows.

## Answer

#### Create tables with primary keys
```sql
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
```sql
SHOW TABLES;
```
```text
Account, Borrower, Branch, Customer, Depositor, Loan
```

#### Insert values into Account
Branch rows must exist first (foreign key):
```sql
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;
```
```text
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.)

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. Why are Branch and Customer created first?
   A. Other tables have foreign keys referring to them
   B. They are smaller
   C. Alphabetical order
   D. They have no keys
   Answer: A. Referenced tables must exist first. (Key point: Order of creation)

2. Why do Depositor and Borrower have a composite PRIMARY KEY?
   A. They have no key
   B. Single attribute is not allowed
   C. Each row is identified by the pair of attributes
   D. For speed
   Answer: C. Neither column alone is unique. (Key point: Composite key)

3. Why insert Branch rows before Account rows?
   A. Account.branch_name is a foreign key to Branch
   B. Branch is alphabetically earlier
   C. Account has no key
   D. SQL requires it
   Answer: A. A foreign key value must exist in the parent. (Key point: Insert order)

## Related in Database Management System

- [SQL: create and use bank database](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-create-database-bank) — Database Management System, chapter 3
- [SQL: branch city with assets over 1000000](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-branch-assets-million) — Database Management System, chapter 3
- [SQL: customer, branch, account details](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-natural-join-details) — Database Management System, chapter 3
- [SQL: customer name and address](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-address) — Database Management System, chapter 3
- [SQL: customers with account in Dharan](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customers-dharan-branch) — Database Management System, chapter 3
- [SQL: customer names beginning with su](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-customer-name-like-su) — Database Management System, chapter 3