Database Name: BankMgmtDB
1. ER Diagram – Entities & Relationships
Entities and Attributes:
•Customers (CustomerID, Name, Email, Phone, Address)
•Employees (EmployeeID, Name, Email, RoleID, AccessLevel)
•Roles (RoleID, RoleName)
•Accounts (AccountID, CustomerID, AccountType, Balance, OpenDate)
•Transactions (TransactionID, AccountID, TransactionType, Amount, TransactionDate)
•AccessLogs (LogID, EmployeeID, Action, Timestamp)
Relationships:
•One Customer can have multiple Accounts.
•One Account can have many Transactions.
•One Employee has one Role.
•Access Logs track employee actions.
2. SQL Script Structure
A. Database & Table Creation
CREATE DATABASE BankMgmtDB;
USE BankMgmtDB;
-- 1. Roles
CREATE TABLE Roles (
RoleID INT PRIMARY KEY,
RoleName VARCHAR(50) NOT NULL
);
-- 2. Customers
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address TEXT
);
-- 3. Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
RoleID INT,
AccessLevel INT CHECK (AccessLevel BETWEEN 1 AND 5),
FOREIGN KEY (RoleID) REFERENCES Roles(RoleID)
);
-- 4. Accounts
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
CustomerID INT,
AccountType VARCHAR(20) CHECK (AccountType IN ('Savings', 'Checking', 'Loan')),
Balance DECIMAL(12,2) NOT NULL,
OpenDate DATE NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
-- 5. Transactions
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY,
AccountID INT,
TransactionType VARCHAR(10) CHECK (TransactionType IN ('Credit', 'Debit')),
Amount DECIMAL(10,2) NOT NULL,
TransactionDate DATE NOT NULL,
FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);
-- 6. AccessLogs
CREATE TABLE AccessLogs (
LogID INT PRIMARY KEY,
EmployeeID INT,
Action TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
B. Sample Data Insertion
INSERT INTO Roles VALUES (1, 'Teller'), (2, 'Manager'), (3, 'Admin');
INSERT INTO Customers VALUES
(1, 'Alice Smith', '
[email protected]', '1234567890', 'NY'),
(2, 'Bob Jones', '
[email protected]', '2345678901', 'LA'),
...
;
INSERT INTO Employees VALUES
(1, 'Eve Thomas', '
[email protected]', 2, 4),
(2, 'John Doe', '
[email protected]', 3, 5),
...
;
INSERT INTO Accounts VALUES
(101, 1, 'Savings', 120000.00, '2024-01-10'),
(102, 2, 'Checking', 8000.00, '2024-02-14'),
...
;
INSERT INTO Transactions VALUES
(1001, 101, 'Debit', 1000.00, '2024-03-01'),
(1002, 101, 'Credit', 5000.00, '2024-03-05'),
...
;
INSERT INTO AccessLogs VALUES
(1, 1, 'Logged in', '2024-04-01 09:00:00'),
(2, 2, 'Modified Account 102', '2024-04-01 10:00:00'),
...
;
C. SQL Queries
1.Customer names and account types:
SELECT c.Name, a.AccountType
FROM Customers c
JOIN Accounts a ON c.CustomerID = a.CustomerID;
2.Total balance per customer:
SELECT c.Name, SUM(a.Balance) AS TotalBalance
FROM Customers c
JOIN Accounts a ON c.CustomerID = a.CustomerID
GROUP BY c.Name;
3.Accounts with balance > 50,000:
SELECT * FROM Accounts WHERE Balance > 50000;
4.Transaction count per account:
SELECT AccountID, COUNT(*) AS TransactionCount
FROM Transactions
GROUP BY AccountID;
5.Transactions for a given customer:
SELECT c.Name, t.*
FROM Customers c
JOIN Accounts a ON c.CustomerID = a.CustomerID
JOIN Transactions t ON a.AccountID = t.AccountID
WHERE c.CustomerID = 1;
6.Employees with access level 4 or 5:
SELECT * FROM Employees WHERE AccessLevel IN (4, 5);
D. Views
-- 1. CustomerAccountsView
CREATE VIEW CustomerAccountsView AS
SELECT c.Name, a.AccountID, a.AccountType, a.Balance
FROM Customers c
JOIN Accounts a ON c.CustomerID = a.CustomerID;
-- 2. TransactionSummaryView
CREATE VIEW TransactionSummaryView AS
SELECT a.AccountID, COUNT(t.TransactionID) AS TotalTransactions, SUM(t.Amount) AS TotalAmount
FROM Accounts a
JOIN Transactions t ON a.AccountID = t.AccountID
GROUP BY a.AccountID;
E. Testing
1.Simulate debit on low balance:
-- Try to debit $50000 from account with $100
INSERT INTO Transactions VALUES (999, 103, 'Debit', 50000, '2025-05-25');
-- Business logic in app layer or triggers would typically block this.
2.Join tables for reports: Already demonstrated in views and queries.
3.Test email uniqueness:
-- Should fail due to duplicate email
INSERT INTO Customers VALUES (99, 'Test', '[email protected]', '999', 'Test Address');
Bank Management System - Database Design Report
Database Name: BankMgmtDB
This
system simulates a basic yet functional banking environment, supporting operations like customer account
management, employee roles, and transaction tracking with built-in data security and auditing.
1.Schema Overview
Entities and Attributes:
- Customers: Stores customer details (ID, name, email, phone,
address). Email is enforced as unique to maintain integrity and avoid duplicates.
- Employees:
Captures employee info along with their Access Level (range 1 to 5) and role (linked via
RoleID). Unique email constraint applies here too.
- Roles: Defines employee roles such as Admin,
Manager, or Teller.- Accounts: Each customer can hold multiple accounts. Account Type is
constrained to 'Savings', 'Checking', or 'Loan'. The balance is tracked and initialized with
proper values.
- Transactions: Records every account activity with Transaction Type
limited to 'Credit' or 'Debit'. Linked to Accounts.
- Access
Logs: Captures employee activity for auditing. Stores action description and timestamp
2. Key Constraints and Logic
-
Primary Keys (PK): Assigned to each table's ID fields (e.g., CustomerID, AccountID, TransactionID).
- Foreign Keys (FK): Maintain referential integrity. For instance, CustomerID in Accounts, RoleID in
Employees, and AccountID in Transactions.
- CHECK Constraints: Enforce valid values for:
- AccountType: 'Savings', 'Checking', 'Loan’
- TransactionType: 'Credit', 'Debit’
- AccessLevel: must be between 1 and 5
- NOT
NULL Constraints: Applied on all mandatory fields to avoid missing critical information.
- UNIQUE Constraints: Prevent duplicate email addresses in both Customers and Employees.
3. Relationships (ER Diagram Overview)
- One
Customer -> Many Accounts
- One Account -> Many Transactions
- One Employee -> One
Role
- One Employee -> Many AccessLogs
These relationships were carefully mapped
using foreign keys and are visualized in the ER diagram (not shown here, but available upon request).
4. Views and Queries
- Views:
- Customer Accounts View: Summarizes customer details and account
types. - Transaction Summary View: Aggregates
transactions by account. - Sample
Queries:
- Listing customer balances
- Filtering
accounts with high balances
- Displaying employee access rights
- Generating transaction reports per account or
customer
5. Testing and Security
- Simulated debit on low-balance
accounts to test logical enforcement. - Attempted
duplicate inserts to confirm unique constraints.
- Role-based access via Access Level allows future implementation of permissions.
Conclusion
This schema is modular, scalable,
and designed for integrity and extensibility. Future enhancements may include support for loan schedules, online
payments, and encrypted authentication for secure access.
