This document explains the key concepts and steps in designing a database.
1. Database Schema
Definition:
- A database schema is the blueprint of how data is organized in a database.
- It defines tables, columns, data types, keys, and relationships.
Example (Core Banking):
Customers Table:
| Column |
Type |
Key |
Description |
| CustomerID |
INT |
PK |
Unique ID for customer |
| Name |
VARCHAR |
|
Customer’s name |
| Email |
VARCHAR |
UNIQUE |
Customer email |
| Phone |
VARCHAR |
|
Customer phone number |
Accounts Table:
| Column |
Type |
Key |
Description |
| AccountID |
INT |
PK |
Unique account ID |
| CustomerID |
INT |
FK |
Links to Customers |
| AccountType |
VARCHAR |
|
Savings / Checking |
| Balance |
DECIMAL |
|
Current balance |
2. Building Schema
Steps to Build a Schema:
- Identify Entities: Customers, Accounts, Transactions, Loans.
- Define Attributes: Columns for each entity (Customer Name, Account Balance).
- Set Keys: Primary keys for unique identification, foreign keys for relationships.
- Define Relationships: 1:1, 1:N, M:N between tables.
- Decide Data Types: INT, VARCHAR, DECIMAL, DATE, etc.
- Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT.