PROJECT

Description

The Fraud Detection System is an advanced data analysis project designed to identify fraudulent credit card transactions by leveraging structured databases and SQL queries. It focuses on detecting suspicious activity in financial transactions by analyzing transaction data, customer demographics, and credit card details. The system uses a series of SQL-based joins and queries to consolidate and examine data across multiple tables, including transaction records, customer profiles, and fraud flags.

Core Components:

  • Transaction Data – Captures details about each transaction, such as the transaction amount, date, credit card number, and customer ID.
  • Fraud Detection – Links each transaction to a fraud flag, marking it as either fraudulent or legitimate.
  • Customer Information – Includes data about the customer’s age, segment, and credit history to assess their transaction behavior and potential risk.
  • Credit Card Information – Stores details about credit limits and cardholder information, which help in determining high-risk transactions.

By combining these components, the system is able to analyze patterns in the data, such as unusual spending behavior, large transactions, or patterns indicative of fraud. The project also enables businesses and financial institutions to gain insights into customer spending habits, identify high-risk transactions, and reduce the likelihood of financial losses due to fraud.

The Fraud Detection System can be further enhanced through the use of data visualization tools such as Power BI or Tableau, which present the data in an easy-to-understand format, highlighting trends and anomalies. Additionally, the system can incorporate machine learning models for predictive analysis, improving its ability to flag fraudulent transactions in real time.

This project serves as a comprehensive solution for fraud detection, utilizing a combination of data science techniques, relational database management, and automation to protect both consumers and businesses from financial crimes.

Python Code

SQL

                    
                        -- Create a new database called 'fraud_project'
CREATE DATABASE fraud_project;

-- Select the 'fraud_project' database to use it for queries and table creation
USE fraud_project;

-- Retrieve all records from the 'customerbase' table
SELECT * 
FROM customerbase;

-- Retrieve transaction details along with fraud status
SELECT transactionbase.transaction_id,
       transactionbase.transaction_date, 
       transactionbase.transaction_value,
       fraudbase.fraud_flag
FROM transactionbase
LEFT JOIN fraudbase 
       ON transactionbase.transaction_id = fraudbase.transaction_id;
       
-- Retrieve all records from the 'customerbase' table again
SELECT *
FROM customerbase;

-- Create a new table 'bank_info' by joining multiple tables to consolidate data
CREATE TABLE bank_info AS
SELECT customerbase.cust_id,             -- Customer ID
       transactionbase.transaction_id,    -- Transaction ID
       customerbase.age,                  -- Customer Age
       customerbase.customer_segment,     -- Customer Segment
       transactionbase.transaction_date,  -- Date of transaction
       transactionbase.transaction_value, -- Transaction amount
       cardbase.credit_limit,             -- Credit card limit
       fraudbase.fraud_flag               -- Fraud status (flag)
FROM customerbase
INNER JOIN cardbase  
       ON customerbase.cust_id = cardbase.cust_id  -- Match customers with their cards
INNER JOIN transactionbase 
       ON cardbase.card_number = transactionbase.credit_card_id  -- Match cards with transactions
LEFT JOIN fraudbase  
       ON transactionbase.transaction_id = fraudbase.transaction_id;  -- Attach fraud details if available

-- Drop the 'bank_info' table (removes it permanently)
DROP TABLE bank_info;

-- Convert 'transaction_date' format to a standard date format
UPDATE bank_info
SET transaction_date = STR_TO_DATE(transaction_date, '%d-%b-%y');

-- Ensure 'transaction_date' is stored as a proper DATE type
UPDATE bank_info
SET transaction_date = DATE(transaction_date);

-- Retrieve all records from 'bank_info' after modifications
SELECT *
FROM bank_info;

-- Show the structure of the 'bank_info' table
DESCRIBE bank_info;

-- Modify the 'fraud_flag' column to store text values instead of boolean
ALTER TABLE bank_info
MODIFY COLUMN fraud_flag VARCHAR(3);

-- Update fraud flag values: change '1' to 'YES' for better readability
UPDATE bank_info
SET fraud_flag = 'YES'
WHERE fraud_flag = 1;

-- Update fraud flag values: change '0' to 'NO'
UPDATE bank_info
SET fraud_flag = 'NO'
WHERE fraud_flag = '0';

-- Retrieve all transactions flagged as fraudulent
SELECT *
FROM bank_info
WHERE fraud_flag = 'YES';

-- Count the number of fraudulent transactions
SELECT COUNT(*)
FROM bank_info 
WHERE fraud_flag = 'YES';

Documentation