For my COPA course assessment, I have given the topic of credit history and loan record management. In this project, I built a relational database with three tables: Banks, Customers, and Credit Records. These tables store and connect data with each other using SQL.
The project focuses on credit history management, not complete banking operations. It gives a centralized view of customer loans, payment records, due amounts, and credit status.
In real life, customers often have accounts and loans in different banks, and tracking all those records separately is difficult. A centralized system is needed where customer details, bank details, and loan information are stored in one place. This is exactly what this project does.
Whether a loan is active, closed, overdue, or defaulted, everything is available in one place, making it easy to find out which customer has taken what kind of loan, from which bank, and what the current repayment status is.
The project consists of three main tables:
Banks and Customers are not directly connected to each other. Credit Records acts as a bridge table between them.
For example, Customer A may have a loan from SBI, another loan from HDFC, and another from ICICI. So one customer can have multiple loan records. Similarly, one bank can issue loans to many different customers. This creates a One-to-Many relationship on both sides.
In real life, banks and companies do not disclose how they calculate credit scores, and the actual formula is very complicated.
In this project, I have used a simplified but logical model.
Every customer starts with a default credit score of 1000, which represents a perfect repayment history with no missed payments. For every missed EMI payment, 20 points are deducted. The formula is:
Credit Score = 1000 − (Missed Payments × 20)
Since the database does not store EMI duration or installment amount, due amount is calculated using an estimated penalty model. Each missed payment carries a penalty equal to 2 percent of the total loan amount. The formula is:
Due Amount = Loan Amount × 0.02 × Missed Payments
Payment status is decided based on the number of missed payments:
When a customer takes a loan from a bank, a PAN card and at least one bank account are required. But in real life, PAN numbers, bank account numbers, and phone numbers are confidential. In this project, all of these were generated randomly for demo purposes.
Another challenge was that writing thousands of customer records and credit history entries manually was not possible within the time available. That is why I used AI tools to assist with data generation.
In a relational database, a JOIN query is used to combine data from multiple tables and display it in a single result. Using JOIN queries on this database, the following types of reports can be generated:
These are just some examples.
The following tools were used to build this project in an accessible way:
The project has been deployed using the following ways.
The result will be shown in the format of tables. Also, sensitive information such as PAN numbers, bank account numbers, and phone numbers will be hidden from the result.