Here’s a full set of *relational database tables* for the *Commercial Bank ERD,
based on the normalized schema up to **3NF*. This includes primary keys (PK),
foreign keys (FK), and datatypes assuming use with an RDBMS like MySQL or
PostgreSQL.
---
## 🔹 1. Customer
| Column Name | Data Type | Constraints |
| ----------- | ------------ | ------------------- |
| CustomerID | INT | PK, AUTO\_INCREMENT |
| FirstName | VARCHAR(50) | NOT NULL |
| LastName | VARCHAR(50) | NOT NULL |
| DOB | DATE | NOT NULL |
| Phone | VARCHAR(15) | |
| Email | VARCHAR(100) | UNIQUE |
| Address | VARCHAR(255) | |
---
## 🔹 2. Account
| Column Name | Data Type | Constraints |
| ----------- | ------------- | ------------------------- |
| AccountID | INT | PK, AUTO\_INCREMENT |
| AccountType | VARCHAR(20) | NOT NULL |
| Balance | DECIMAL(12,2) | DEFAULT 0 |
| OpenDate | DATE | NOT NULL |
| CustomerID | INT | FK → Customer(CustomerID) |
---
## 🔹 3. CustomerAccount (Handles joint accounts)
| Column Name | Data Type | Constraints |
| ----------- | --------- | ----------------------------- |
| CustomerID | INT | FK → Customer(CustomerID), PK |
| AccountID | INT | FK → Account(AccountID), PK |
---
## 🔹 4. Transaction
| Column Name | Data Type | Constraints |
| --------------- | ------------- | ----------------------- |
| TransactionID | INT | PK, AUTO\_INCREMENT |
| AccountID | INT | FK → Account(AccountID) |
| TransactionDate | DATETIME | NOT NULL |
| Amount | DECIMAL(12,2) | NOT NULL |
| TransactionType | VARCHAR(20) | NOT NULL |
| Description | TEXT | |
---
## 🔹 5. Branch
| Column Name | Data Type | Constraints |
| ----------- | ------------ | ------------------- |
| BranchID | INT | PK, AUTO\_INCREMENT |
| BranchName | VARCHAR(100) | NOT NULL |
| Address | VARCHAR(255) | |
| Phone | VARCHAR(15) | |
---
## 🔹 6. Employee
| Column Name | Data Type | Constraints |
| ----------- | ------------ | --------------------- |
| EmployeeID | INT | PK, AUTO\_INCREMENT |
| FirstName | VARCHAR(50) | NOT NULL |
| LastName | VARCHAR(50) | NOT NULL |
| Position | VARCHAR(50) | NOT NULL |
| BranchID | INT | FK → Branch(BranchID) |
| Email | VARCHAR(100) | UNIQUE |
---
## 🔹 7. Loan
| Column Name | Data Type | Constraints
|
| ------------ | ------------- |
--------------------------------------------------- |
| LoanID | INT | PK, AUTO\_INCREMENT
|
| CustomerID | INT | FK → Customer(CustomerID)
|
| LoanAmount | DECIMAL(12,2) | NOT NULL
|
| InterestRate | DECIMAL(5,2) | NOT NULL
|
| StartDate | DATE | NOT NULL
|
| EndDate | DATE |
|
| Status | VARCHAR(20) | CHECK (Status IN ('Active', 'Closed',
'Defaulted')) |
---
## 🔹 8. Card
| Column Name | Data Type | Constraints |
| ----------- | ----------- | ----------------------- |
| CardID | INT | PK, AUTO\_INCREMENT |
| CardType | VARCHAR(20) | NOT NULL |
| AccountID | INT | FK → Account(AccountID) |
| IssueDate | DATE | NOT NULL |
| ExpiryDate | DATE | NOT NULL |
---
### ✅ Notes:
* Primary Keys (PK) uniquely identify each row.
* Foreign Keys (FK) enforce referential integrity.
* Composite keys are used in the CustomerAccount table to support many-to-many
relations.
* ENUMs or CHECK constraints can be used to restrict fields like TransactionType
and Status.
---
Would you like this schema as **SQL CREATE TABLE statements** or as a *downloadable
file*?
