Publish Date: 2022-12-24

Database Management Systems

Introduction

This blog discusses our database management design specifically tailored for credit card data. We start with a visual representation of the Entity Relationship Diagram (ERD) to depict our data architecture. Following this, we proceed to data importation where multiple tables are created to categorize the data, thereby making it easier for users to retrieve the data they need. The data on these tables is sourced from a main table.

Data Import

Firstly, I will CREATE a database and USE this database to implement changes.

Then, I will CREATE the primary table to house the entire database and specify the data type for each column.

Next, I’ll LOAD the CSV file into this main table. The data will be separated by commas "," and each new line will be indicated with a "\n". The first row will be ignored.

Following that, I will CREATE the account table, which SELECTS certain columns from the main table. I will also ADD table IDs to this table to facilitate easier connections with other tables.

Similarly, I’ll CREATE new tables namely: Customer_Information, Credit_Score, Payment, and Record.

DROP DATABASE IF EXISTS creditcard_1;
CREATE DATABASE creditcard_1;
USE creditcard_1;

CREATE TABLE IF NOT EXISTS `ALL_Credit`(
  ... [Column definitions omitted for brevity]
);

LOAD DATA LOCAL INFILE '/Users/hongyigao/Documents/ITC6000/Final/train.csv'
INTO TABLE `ALL_Credit`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- Creating additional tables from main table
CREATE TABLE `Account_Information` AS SELECT `Num_Bank_Accounts`, `Num_Credit_Card`, `Num_of_Loan`, `Type_of_Loan`  FROM `ALL_Credit`;
ALTER TABLE `Account_Information` ADD COLUMN `Account_Table_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

CREATE TABLE `Customer_Information` AS SELECT `Customer_ID`, `Name`, `Age`, `SSN`, `Occupation`, `Annual_Income`, `Monthly_Inhand_Salary` FROM `ALL_Credit`;
ALTER TABLE `Customer_Information` ADD COLUMN `Customer_Table_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

CREATE TABLE `Credit_Score` AS SELECT `Credit_Score`, `Credit_History_Age` FROM `ALL_Credit`;
ALTER TABLE `Credit_Score` ADD COLUMN `Score_Table_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

CREATE TABLE `Payment` AS SELECT `Payment_Behaviour`, `Total_EMI_per_month`, `Amount_invested_monthly`, `Monthly_Balance`, `Outstanding_Debt`, `Payment_of_Min_Amount` FROM `ALL_Credit`;
ALTER TABLE `Payment` ADD COLUMN `Payment_Table_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

CREATE TABLE `Record` AS SELECT `Month` FROM `ALL_Credit`;
ALTER TABLE `Record` ADD COLUMN `Record_Table_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Next, we join the tables using the following command:

SELECT r.Record_Table_ID, r.Month, ci.Customer_Table_ID, cs.Score_Table_ID, p.Payment_Table_ID
FROM `Record` r
JOIN `Customer_Information` ci ON r.Record_Table_ID=ci.Customer_Table_ID
JOIN `Credit_Score` cs ON r.Record_Table_ID=cs.Score_Table_ID
JOIN `Payment` p ON r.Record_Table_ID=p.Payment_Table_ID;

Here are some sample queries that can be run on the database:

Find the number of credit cards for customers who have a credit score of ‘Good’ and an annual income greater than $50,000:

SELECT ai.Num_Credit_Card,cs.Credit_Score,ci.Annual_Income
FROM `Account_Information` ai
JOIN `Credit_Score` cs ON ai.Account_Table_ID=cs.Score_Table_ID
JOIN `Customer_Information` ci ON ai.Account_Table_ID=ci.Customer_Table_ID
WHERE cs.Credit_Score LIKE "%Good%" AND ci.Annual_Income >50000;

Find the payment behaviour and monthly balance for customers who have a credit score of ‘Good’, sorted in descending order by monthly balance:

SELECT p.Payment_Behaviour, p.Monthly_Balance, p.Payment_Table_ID, cs.Credit_Score
FROM `Payment` p
JOIN `Credit_Score` cs ON p.Payment_Table_ID=cs.Score_Table_ID
WHERE cs.Credit_Score LIKE "%Good%"
ORDER BY Monthly_Balance DESC;

Find the names and occupations is Engineer of customers who have a credit score of ‘Good’ and Payment_Behavior of ‘High_spent_Large_value_payments’, sorted in descending order by name.

SELECT ci.Name, ci.Occupation, p.Payment_Behaviour, cs.Credit_Score
FROM `Customer_Information` ci
JOIN `Payment` p on p.Payment_Table_ID=ci.Customer_Table_ID
JOIN `Credit_Score`cs On ci.Customer_Table_ID=cs.Score_Table_ID
WHERE cs.Credit_Score LIKE "%Good%" AND p.Payment_Behaviour LIKE "%High_spent_Large_value_payments%" AND ci.Occupation="Engineer"
ORDER BY Name DESC;

Conclusion

In wrapping up, working on this project has truly opened my eyes to the power and flexibility of database management systems, particularly MySQL. Crafting a system to handle credit card data from scratch, using an Entity-Relationship Diagram (ERD) to map it out, gave me a hands-on understanding of data structures and their importance in organizing complex information.

What really struck me was the experience of building tables, starting from our central ALL_Credit table and branching out to specialized tables. The transformation from a CSV file to a set of interrelated tables was quite an experience. It was like putting together a puzzle - each table adding a piece to the larger picture, giving us the ability to quickly access and analyze the data we needed.

Performing SQL queries to extract meaningful data was a highlight. From counting credit cards for certain customer profiles to understanding payment behavior trends, each query felt like a new exploration, a chance to discover something unexpected in our data.