BANK LOAN MANAGEMENT SYSTEM

MINI PROJECT REPORT SUBMITTED IN PARTIAL FULFILLMENT

OF THE REQUIREMENTS FOR THE AWARD OF THE
DEGREE OF BACHELOR OF ENGINEERING

MINI PROJECT IN ElECTRONICS AND COMMUNICATION ENGINEERING

Nov 2024 OF THE ANNA UNIVERSITY

PROJECT
WORK

Submitted by
HRITHICK RAM.M – 722810621043

BATCH
2023 - 2027
Under the Guidance of
Dr.N.Rajesha.,
Associate Professor,ECE.

Department of Mechanical Engineering

Sri Eshwar College of Engineering
(An Autonomous Institution – Affiliated to Anna University)
COIMBATORE – 641 202
 Sri Eshwar College of Engineering
(An Autonomous Institution – Affiliated to Anna University)
COIMBATORE – 641 202

BONAFIDE CERTIFICATE

Certified that this Report titled “BANK LOAN MANAGEMENT SYSTEM” is the

bonafide work of

HRITHICK RAM.M 722810621043

who carried out the project work under my supervision.

----------------------------------------- -----------------------------------------
SIGNATURE SIGNATURE
Dr.N.Shanmugasundaram, M.E., Ph.D., Dr.N.Rajesha.,
HEAD OF THE DEPARTMENT SUPERVISOR
Electronics and Communication Engineering, Associate Professor,
Sri Eshwar College of Engineering, Department of ECE,
Coimbatore – 641 202. Sri Eshwar College of Engineering,
Coimbatore – 641 202.

Submitted for the Autonomous Semester End Mini Project Viva-Voce held on

…………………..

_________________________ _________________________

INTERNAL EXAMINER EXTERNAL EXAMINER

 TABLE OF CONTENTS

TITLE
CHAPTER PAGE NO.
NO.
ABSTRACT 4
INTRODUCTION 5
1
1.1 OBJECTIVES 5

1.2 SCOPE OF THE PROJECT 5

SYSTEM ANALYSIS AND

2 6
SPECIFICATION
6
2.1 PROBLEM DESCRIPTION
2.2 FUNCTIONAL REQUIREMENT - HARDWARE & 6
SOFTWARE
2.3 NON – FUNCTIONAL REQUIREMENT 7
8
3 SYSTEM DESIGN
ER DIAGRAM 8
3.1
USE CASE DIAGRAM 8
3.2
SCHEMA DIAGRAM 9
3.3
PROJECT DESCRIPTION 10
4
4.1 MODULE GAMEHUB : REAL TIME 10
MULTIPLAYER AND LOBBY SYSTEM
4.1. 1. MODEL
4.1.2 DATA ACCESS CONTROL
4.1.3 CONTROLLER
4.1.4 SERVICE 10
4.1.5 EXCEPTION
4.1.6 UTILITY
4.2 JDBC CONNECTIVITY 11

5 RESULT AND OUTPUT 12

6 CONCLUSION & FUTURE ENHANCEMENT 14

7 REFERENCES 15
8 ANNEXURE 16
ABSTRACT

The Bank Loan Management System is a console-based software solution designed to

automate and streamline the handling of customer loan applications. The system addresses
the challenges of manual loan management by enabling efficient and accurate loan
processing. Key features include loan application submission, dynamic interest calculation,
approval or rejection of applications, and secure data storage using JDBC. Built with object-
oriented programming principles such as inheritance and polymorphism, it supports multiple
loan types like HomeLoan and PersonalLoan. The system ensures robust exception handling,
data integrity, and scalability, making it an efficient and user-friendly tool for banks to
manage their loan processes. Developed using object-oriented programming principles, the
system employs inheritance and polymorphism to support diverse loan types such as
HomeLoan, PersonalLoan, and CarLoan, each with distinct interest calculation methods. The
system also incorporates file handling for backups and uses collections like ArrayList to
efficiently manage loan applications.
 CHAPTER 1

INTRODUCTION

1.1 OBJECTIVES

The primary objective of the Bank Loan Management System is to streamline and automate
the processes involved in loan management. This includes handling loan applications,
approvals, and repayment tracking with minimal manual intervention. By reducing errors
and ensuring efficiency, the system aims to improve the accuracy of calculations related to
interest rates, EMIs, and due payments. It also focuses on enhancing customer experience by
providing a user-friendly platform where customers can apply for loans, check their status,
and manage their repayments seamlessly.

1.2 SCOPE OF THE PROJECT

The Bank Loan Management System is designed to encompass all key aspects of loan
management. It facilitates loan application submission, document validation, and the
automated calculation of eligibility and repayment terms. The system supports bank
officials in reviewing and processing loan applications efficiently while maintaining a
record of approvals and rejections. Customers can manage their loan repayments through
the platform, ensuring timely payments and receipt generation. It provides a centralized
database for storing customer profiles, loan histories, and repayment details, which are
essential for analytics and decision-making. The system ensures secure access to sensitive
financial data through encryption and controlled user permissions.
 CHAPTER 2
SYSTEM ANALYSIS AND SPECIFICATION

2.1 PROBLEM DESCRIPTION

The Bank Loan Management System seeks to address several challenges faced by
traditional banking systems in processing and managing loan applications. Existing systems
often involve complex, manual workflows prone to inefficiencies, errors, and delays.
Customers may experience prolonged processing times, inaccurate loan calculations, and
lack of transparency throughout the approval process. Moreover, handling different types of
loans—such as personal loans, home loans, and car loans—requires distinct procedures,
making the management process cumbersome for banking staff.

2.2 SOFTWARE REQUIREMENTS:

 Environment: JDK 8+, Eclipse IDE, MySQL Server.
 Libraries: JDBC for database connectivity, file handling for reports.
 Operating System: Compatible with Windows, macOS, or Linux.

Hardware Requirements:

 Processor: Dual-core or higher.

 RAM: 4GB minimum.
 Storage: 500MB free space.

Network: Internet for remote database access

2.3 NON-FUNCTIONAL REQUIREMENT

1. Performance:
The system must process loan applications, calculate interest, and approve or reject
loans quickly and accurately.

2. Scalability:
The system must be able to scale as the bank grows, handling increasing numbers of
loan applications and customer records without performance degradation

3. Reliability:
The Bank Loan Management System should be stable, ensuring consistent operation
even under high workloads.

4. Usability:
The user interface should be simple, intuitive, and easy to navigate for both customers
and bank employees.
Security:
Sensitive customer data, such as personal information and loan details, must be
securely stored and transmitted.
5. Maintainability:
Code should be modular and well-documented for easy updates.

6. Portability:
The system should work on Windows, macOS, and Linux

7. Availability:
The system should be accessible 24/7 for users across time zones.
 CHAPTER 3

SYSTEM DESIGN

3.1 ER DIAGRAM

Figure 3.1 ER Diagram for Loan Management System

3.2 USE CASE DIAGRAM

Figure 3.2 Use Case Diagram for Loan Management System

3.3 SCHEMA DIAGRAM

Figure 3.3 Scheme Diagram for Loan Management System

 CHAPTER 4
PROJECT DESCRIPTION

4.1 MODULE DESCRIPTION

4.1.1 Model

The Model module serves as the foundation for defining the system's core data
entities, which include classes such as Customer, Loan, and LoanApplication.
These classes encapsulate properties and behaviors that represent the attributes and
operations of these entities. For instance, the Customer class contains fields like
name, ID, and contact details, while the Loan class includes fields for loan type,
interest rate, and loan tenure.

4.1.2. DAO (Data Access Object)

The DAO module acts as the bridge between the application logic and the
database. It provides methods to perform CRUD operations (Create, Read, Update,
Delete) for the system’s entities, such as adding new customers, updating loan
details, retrieving loan applications, and deleting invalid records. Classes in this
module use JDBC for database connectivity, ensuring secure and efficient data
transactions..

4.1.3 Controller

The Controller module is responsible for managing interactions between the user
interface (console commands) and the backend services. It interprets user actions
such as applying for a loan, calculating interest, or approving an application, and
directs these actions to the appropriate service or DAO methods.

4.1.4 Service

The Service module contains the core business logic of the application. It processes
user requests, applies rules and computations, and coordinates actions between the
Controller and DAO layers. For instance, it calculates interest rates for loans based
on their type and duration.

4.1.5. Exception

The Exception module is designed to handle errors and unexpected situations that
may arise during the operation of the Bank Loan Management System. This
module includes custom exception classes such as InvalidLoanAmountException,
CustomerNotFoundException, and DatabaseConnectionException, which are used
to handle specific scenarios like entering an invalid loan amount.

4.1.6 Utility

The Utility module provides a set of reusable functions and helper methods to
support the application’s operations. This module includes functionality such as
input validation, date and time formatting, and mathematical calculations. For
instance, utility methods ensure that loan amounts and durations fall within
acceptable ranges, customer input fields (like name and contact information)
adhere to the required format, and interest rates are calculated accurately based on
specific formulas.

4.2 JDBC CONNECTIVITY

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCConnection {
private static final String URL:"jdbc:mysql://localhost:3306/GameHub";
private static final String USER = "root";
private static final String PASSWORD = "root";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
 CHAPTER 5

RESULT AND OUTPUT

RESULT

The Bank Loan Management System has been successfully implemented,

achieving its objective of automating and streamlining the loan management
process for banks. The system processes loan applications efficiently by allowing
users to input customer details, choose loan types, and specify loan parameters
such as amounts and durations. It dynamically calculates interest rates based on
predefined formulas, ensuring accurate and transparent repayment schedules for
different loan types. The system also provides a reliable approval or rejection
mechanism based on loan eligibility criteria, improving decision-making for bank
employees. By using JDBC for data storage, the system ensures secure and
consistent handling of customer and loan information. Additionally, robust
exception handling minimizes errors and enhances the overall reliability of the
application. The result is a scalable, efficient, and user-friendly solution for
modern banking operations.
5.1 USER INTERFACE DESIGN

5.2 APPLY LOAN APPLICATION

5.2 VIEW LOAN APPLICATION

 CHAPTER 6

CONCLUSION AND FUTURE ENHANCEMENT

CONCLUSION

The Bank Loan Management System successfully addresses the challenges faced
by banks in managing loan applications, interest calculations, and approval
processes. By automating key tasks, the system reduces manual effort, minimizes
errors, and ensures efficiency in handling customer requests. The use of object-
oriented programming principles such as inheritance, polymorphism, and
encapsulation has allowed the system to maintain a high degree of flexibility,
enabling support for multiple loan types with distinct interest rate calculations. The
integration of JDBC for secure data storage and file handling for backups ensures
that customer and loan data are stored reliably and can be recovered when needed.

FUTURE ENHANCEMENTS:

While the current implementation of the Bank Loan Management System meets its
intended objectives, there are several areas for future improvement and expansion:

1. Graphical User Interface (GUI):

Transitioning from a console-based application to a GUI would enhance the
user experience, allowing bank employees to interact with the system in a
more intuitive and visually appealing manner. A web-based or desktop GUI
could provide a modern interface with easy navigation and faster access to
key features.

2. Integration with Credit Scoring Systems:

To improve loan approval accuracy, the system could integrate with external
credit scoring systems. This integration would allow the system to
automatically assess a customer's creditworthiness and recommend loan
eligibility based on real-time data.

3. Machine Learning for Loan Prediction:

Incorporating machine learning algorithms could help predict loan approval
probabilities based on historical data. This predictive feature could assist
 bank employees in making data-driven decisions by analyzing patterns in
loan applications, customer profiles, and past loan outcomes.

4. Mobile Application Development:

Expanding the system’s functionality to a mobile platform would allow
customers to apply for loans, track their application status, and receive
notifications in real-time. This would increase accessibility for customers
and enhance their overall experience with the bank’s loan services.

CHAPTER 7

REFERENCES

1.https://docs.oracle.com/en/java/
2. https://spring.io/guides/gs/spring-boot
3. https://sourceforge.net/
4. https://www.javatpoint.com
 ANNEXURE

LOAN MANAGEMENT SYSTEM:

package loanManagement;
import java.util.*;
import java.io.*;

public class LoanManagementSystem {

private static ArrayList<LoanApplication> loanApplications = new ArrayList<>();
private static ArrayList<Customer> customers = new ArrayList<>();

public static void main(String[] args) {

loanApplications = (ArrayList<LoanApplication>)
FileUtils.loadLoanApplicationsFromFile();

Scanner scanner = new Scanner(System.in);

while (true) {
System.out.println("Bank Loan Management System");
System.out.println("1. Apply for Loan");
System.out.println("2. View Loan Applications");
System.out.println("3. Exit");
System.out.print("Enter your choice: ");
int choice = scanner.nextInt();

switch (choice) {
case 1:
applyForLoan(scanner);
break;
case 2:
viewLoanApplications();
break;
case 3:
System.exit(0);
break;
default:
System.out.println("Invalid choice! Please try again.");
}
}
}

private static void applyForLoan(Scanner scanner) {

System.out.print("Enter Customer ID: ");
 int customerId = scanner.nextInt();

System.out.print("Enter Loan Amount: ");

double loanAmount = scanner.nextDouble();

System.out.print("Enter Loan Term (in years): ");

int loanTerm = scanner.nextInt();

System.out.print("Enter Loan Type (1 for Home Loan, 2 for Car Loan): ");
int loanType = scanner.nextInt();

Loan loan;
if (loanType == 1) {
loan = new HomeLoan(loanAmount, loanTerm);
} else if (loanType == 2) {
loan = new CarLoan(loanAmount, loanTerm);
} else {
System.out.println("Invalid loan type.");
return;
}

LoanApplication loanApplication = new LoanApplication(customerId, loanType,

loanAmount, loanTerm);
loanApplication.setInterestAmount(loan.calculateInterest());
loanApplication.setTotalAmount(loan.calculateTotalAmount());

loanApplication.setApplicationId(loanApplications.size() + 1);

loanApplications.add(loanApplication);

FileUtils.saveLoanApplicationToFile(loanApplication);
FileUtils.saveLoanSubmissionLog("Loan application submitted for Customer ID: " +
customerId);

System.out.println("Loan application submitted successfully.");

}

private static void viewLoanApplications() {

if (loanApplications.isEmpty()) {
System.out.println("No loan applications found.");
return;
}
 for (LoanApplication loanApp : loanApplications) {
System.out.println("Loan Application ID: " + loanApp.getApplicationId());
System.out.println("Customer ID: " + loanApp.getCustomerId());
System.out.println("Loan Amount: " + loanApp.getLoanAmount());
System.out.println("Loan Term: " + loanApp.getLoanTerm());
System.out.println("Interest Amount: " + loanApp.getInterestAmount());
System.out.println("Total Amount: " + loanApp.getTotalAmount());
System.out.println("Status: " + loanApp.getStatus());
System.out.println("Application Date: " + loanApp.getApplicationDate());
System.out.println("----------------------------------------------------");
}
}
}
LOAN APPLICATION

package loanManagement;
import java.util.Date;

public class LoanApplication {

private int applicationId;
private int customerId;
private int loanTypeId;
private double loanAmount;
private int loanTerm;
private String status;
private double interestAmount;
private double totalAmount;
private Date applicationDate;

// Constructor to create a LoanApplication object

public LoanApplication(int customerId, int loanTypeId, double loanAmount, int loanTerm) {
this.customerId = customerId;
this.loanTypeId = loanTypeId;
this.loanAmount = loanAmount;
this.loanTerm = loanTerm;
this.status = "Pending";
this.applicationDate = new Date();
}

// Getters and Setters for the fields

public int getApplicationId() {

return applicationId;
}
public void setApplicationId(int applicationId) {
this.applicationId = applicationId;
}

public int getCustomerId() {

return customerId;
}

public void setCustomerId(int customerId) {

this.customerId = customerId;
}

public int getLoanTypeId() {

return loanTypeId;
}

public void setLoanTypeId(int loanTypeId) {

this.loanTypeId = loanTypeId;
}

public double getLoanAmount() {

return loanAmount;
}

public void setLoanAmount(double loanAmount) {

this.loanAmount = loanAmount;
}

public int getLoanTerm() {

return loanTerm;
}

public void setLoanTerm(int loanTerm) {

this.loanTerm = loanTerm;
}

public String getStatus() {

return status;
}

public void setStatus(String status) {

this.status = status;
}

public double getInterestAmount() {

return interestAmount;
 }

public void setInterestAmount(double interestAmount) {

this.interestAmount = interestAmount;
}

public double getTotalAmount() {

return totalAmount;
}

public void setTotalAmount(double totalAmount) {

this.totalAmount = totalAmount;
}

public Date getApplicationDate() {

return applicationDate;
}

public void setApplicationDate(Date applicationDate) {

this.applicationDate = applicationDate;
}
}

FILE UTILS:
package loanManagement;
import java.io.*;
import java.util.*;

public class FileUtils {

public static void saveLoanApplicationToFile(LoanApplication loanApplication) {

try (BufferedWriter writer = new BufferedWriter(new FileWriter("loan_applications.txt",
true))) {
writer.write("Application ID: " + loanApplication.getApplicationId() + "\n");
writer.write("Customer ID: " + loanApplication.getCustomerId() + "\n");
writer.write("Loan Type ID: " + loanApplication.getLoanTypeId() + "\n");
writer.write("Loan Amount: " + loanApplication.getLoanAmount() + "\n");
writer.write("Loan Term: " + loanApplication.getLoanTerm() + "\n");
writer.write("Interest Amount: " + loanApplication.getInterestAmount() + "\n");
writer.write("Total Amount: " + loanApplication.getTotalAmount() + "\n");
writer.write("Status: " + loanApplication.getStatus() + "\n");
writer.write("Application Date: " + loanApplication.getApplicationDate() + "\n");
writer.write("---------------------------------------------\n");
} catch (IOException e) {
System.out.println("Error writing to file: " + e.getMessage());
}
 }

public static void saveLoanSubmissionLog(String message) {

try (BufferedWriter logWriter = new BufferedWriter(new
FileWriter("loan_applications_log.txt", true))) {
logWriter.write(new Date() + " - " + message + "\n");
} catch (IOException e) {
System.out.println("Error writing to log file: " + e.getMessage());
}
}

public static List<LoanApplication> loadLoanApplicationsFromFile() {

List<LoanApplication> loanApplications = new ArrayList<>();
try (BufferedReader reader = new BufferedReader(new
FileReader("loan_applications.txt"))) {
String line;
LoanApplication currentApplication = null;
while ((line = reader.readLine()) != null) {
if (line.startsWith("Application ID:")) {
if (currentApplication != null) {
loanApplications.add(currentApplication);
}
currentApplication = new LoanApplication(0, 0, 0, 0); // Temp loan application
currentApplication.setApplicationId(Integer.parseInt(line.split(":")[1].trim()));
} else if (line.startsWith("Customer ID:")) {
currentApplication.setCustomerId(Integer.parseInt(line.split(":")[1].trim()));
} else if (line.startsWith("Loan Type ID:")) {
currentApplication.setLoanTypeId(Integer.parseInt(line.split(":")[1].trim()));
} else if (line.startsWith("Loan Amount:")) {
currentApplication.setLoanAmount(Double.parseDouble(line.split(":")[1].trim()));
} else if (line.startsWith("Loan Term:")) {
currentApplication.setLoanTerm(Integer.parseInt(line.split(":")[1].trim()));
} else if (line.startsWith("Interest Amount:")) {
currentApplication.setInterestAmount(Double.parseDouble(line.split(":")
[1].trim()));
} else if (line.startsWith("Total Amount:")) {
currentApplication.setTotalAmount(Double.parseDouble(line.split(":")[1].trim()));
} else if (line.startsWith("Status:")) {
currentApplication.setStatus(line.split(":")[1].trim());
} else if (line.startsWith("Application Date:")) {
currentApplication.setApplicationDate(new Date(line.split(":")[1].trim()));
}
}

if (currentApplication != null) {
 loanApplications.add(currentApplication);
}
} catch (IOException e) {
System.out.println("Error reading from file: " + e.getMessage());
}
return loanApplications;
}
}

CAR LOAN:

package loanManagement;
public class CarLoan extends Loan {
public CarLoan(double loanAmount, int loanTerm) {
super(loanAmount, loanTerm);
this.interestRate = 7.0;
}

@Override
public double calculateInterest() {
return loanAmount * interestRate * loanTerm / 100;
}

@Override
public double calculateTotalAmount() {
return loanAmount + calculateInterest();
}
}

HOME LOAN:
package loanManagement;
public class HomeLoan extends Loan {
public HomeLoan(double loanAmount, int loanTerm) {
super(loanAmount, loanTerm);
this.interestRate = 5.5;
}

@Override
public double calculateInterest() {
return loanAmount * interestRate * loanTerm / 100;
}

@Override
public double calculateTotalAmount() {
return loanAmount + calculateInterest();
}
}
DB CONNECTION:
package loanManagement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class DBConnection {

private static final String URL = "jdbc:mysql://localhost:3306/bank_loan_db";

private static final String USER = "root";
private static final String PASSWORD = "Hrithick@06";

public static Connection getConnection() throws SQLException {

try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
throw new SQLException("Unable to establish connection to the database", e);
}
}

public static void insertLoanApplication(LoanApplication loanApplication) {

String query = "INSERT INTO LoanApplications (customer_id, loan_type_id,
loan_amount, loan_term, status, interest_amount, total_amount, application_date) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

try (Connection conn = getConnection(); PreparedStatement stmt =

conn.prepareStatement(query)) {
stmt.setInt(1, loanApplication.getCustomerId());
stmt.setInt(2, loanApplication.getLoanTypeId());
stmt.setDouble(3, loanApplication.getLoanAmount());
stmt.setInt(4, loanApplication.getLoanTerm());
stmt.setString(5, loanApplication.getStatus());
stmt.setDouble(6, loanApplication.getInterestAmount());
stmt.setDouble(7, loanApplication.getTotalAmount());
stmt.setDate(8, new java.sql.Date(loanApplication.getApplicationDate().getTime()));

int rowsInserted = stmt.executeUpdate();

if (rowsInserted > 0) {
System.out.println("Loan application inserted successfully.");
 }
} catch (SQLException e) {
System.out.println("Error inserting loan application: " + e.getMessage());
}
}

public static List<LoanApplication> getAllLoanApplications() {

List<LoanApplication> loanApplications = new ArrayList<>();
String query = "SELECT * FROM LoanApplications";

try (Connection conn = getConnection(); Statement stmt = conn.createStatement()) {

ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
LoanApplication loanApplication = new LoanApplication(
rs.getInt("customer_id"),
rs.getInt("loan_type_id"),
rs.getDouble("loan_amount"),
rs.getInt("loan_term")
);
loanApplication.setApplicationId(rs.getInt("application_id"));
loanApplication.setInterestAmount(rs.getDouble("interest_amount"));
loanApplication.setTotalAmount(rs.getDouble("total_amount"));
loanApplication.setStatus(rs.getString("status"));
loanApplication.setApplicationDate(rs.getDate("application_date"));

loanApplications.add(loanApplication);
}
} catch (SQLException e) {
System.out.println("Error retrieving loan applications: " + e.getMessage());
}
return loanApplications;
}

public static void updateLoanStatus(int applicationId, String status) {

String query = "UPDATE LoanApplications SET status = ? WHERE application_id = ?";

try (Connection conn = getConnection(); PreparedStatement stmt =

conn.prepareStatement(query)) {
stmt.setString(1, status);
stmt.setInt(2, applicationId);

int rowsUpdated = stmt.executeUpdate();

if (rowsUpdated > 0) {
System.out.println("Loan application status updated successfully.");
}
 } catch (SQLException e) {
System.out.println("Error updating loan status: " + e.getMessage());
}
}
}

SQL QUERIES

-- Step 1: Create the database

CREATE DATABASE IF NOT EXISTS bank_loan_db;
USE bank_loan_db;

-- Step 2: Create Customers Table

CREATE TABLE IF NOT EXISTS Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(15),
address VARCHAR(255),
dob DATE
);

-- Step 3: Create LoanTypes Table

CREATE TABLE IF NOT EXISTS LoanTypes (
loan_type_id INT AUTO_INCREMENT PRIMARY KEY,
loan_name VARCHAR(50) NOT NULL,
interest_rate DECIMAL(5,2) NOT NULL
);

-- Step 4: Create LoanApplications Table

CREATE TABLE IF NOT EXISTS LoanApplications (
application_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
loan_type_id INT,
loan_amount DECIMAL(12,2) NOT NULL,
loan_term INT NOT NULL, -- term in years
status VARCHAR(50) DEFAULT 'Pending',
interest_amount DECIMAL(12,2),
total_amount DECIMAL(12,2),
application_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY (loan_type_id) REFERENCES LoanTypes(loan_type_id)
);

-- Step 5: Create LoanRepayments Table

CREATE TABLE IF NOT EXISTS LoanRepayments (
 repayment_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT,
repayment_date DATE,
amount_paid DECIMAL(12,2),
balance DECIMAL(12,2),
FOREIGN KEY (application_id) REFERENCES LoanApplications(application_id)
);

-- Step 6: Create LoanStatus Table

CREATE TABLE IF NOT EXISTS LoanStatus (
status_id INT AUTO_INCREMENT PRIMARY KEY,
status VARCHAR(50) NOT NULL
);

-- Step 7: Insert Sample Data into LoanTypes

INSERT INTO LoanTypes (loan_name, interest_rate) VALUES
('Home Loan', 5.50),
('Car Loan', 7.25),
('Personal Loan', 10.00);

-- Step 8: Insert Sample Data into Customers

INSERT INTO Customers (name, email, phone, address, dob) VALUES
('John Doe', '[email protected]', '555-1234', '123 Elm Street, Springfield, IL', '1985-06-
15'),
('Jane Smith', '[email protected]', '555-5678', '456 Oak Avenue, Springfield, IL', '1990-
09-25'),
('Robert Brown', '[email protected]', '555-8765', '789 Pine Road, Springfield, IL',
'1980-12-10');

-- Step 9: Insert Sample Data into LoanStatus

INSERT INTO LoanStatus (status) VALUES
('Pending'),
('Approved'),
('Rejected');

-- Step 10: Insert Sample Data into LoanApplications

INSERT INTO LoanApplications (customer_id, loan_type_id, loan_amount, loan_term, status,
interest_amount, total_amount, application_date)
VALUES
(1, 1, 200000, 20, 'Pending', 22000.00, 222000.00, '2024-11-01'),
(2, 2, 30000, 5, 'Approved', 10500.00, 40500.00, '2024-10-20'),
(3, 3, 15000, 3, 'Rejected', 4500.00, 19500.00, '2024-09-15');

-- Step 11: Insert Sample Data into LoanRepayments (for repayment tracking)
INSERT INTO LoanRepayments (application_id, repayment_date, amount_paid, balance)
VALUES
(1, '2024-11-10', 5000.00, 217000.00),
(2, '2024-10-25', 15000.00, 25500.00);

-- Step 12: Query to fetch all Loan Applications with Customer and Loan Type information
SELECT
la.application_id,
c.name AS customer_name,
lt.loan_name AS loan_type,
la.loan_amount,
la.loan_term,
la.status,
la.interest_amount,
la.total_amount,
la.application_date
FROM LoanApplications la
JOIN Customers c ON la.customer_id = c.customer_id
JOIN LoanTypes lt ON la.loan_type_id = lt.loan_type_id;

-- Step 13: Query to update the status of a loan application (example: changing status to
'Approved')
UPDATE LoanApplications
SET status = 'Approved'
WHERE application_id = 1;

-- Step 14: Query to add a loan repayment (for example, adding a repayment entry for
application_id = 1)
INSERT INTO LoanRepayments (application_id, repayment_date, amount_paid, balance)
VALUES
(1, '2024-11-15', 2000.00, 215000.00);