Skip to content
Database design for personal loans


Personal loans are a lucrative revenue stream for banks. The typical interest rate of a two year loan in the United Kingdom is around 10%. This might not sound like a lot, but in September 2022 alone UK consumers borrowed around £1.5 billion, which would mean approximately £300 million in interest generated by banks over two years!

You have been asked to work with a bank to clean and store the data they collected as part of a recent marketing campaign, which aimed to get customers to take out a personal loan. They plan to conduct more marketing campaigns going forward so would like you to set up a PostgreSQL database to store this campaign's data, designing the schema in a way that would allow data from future campaigns to be easily imported.

They have supplied you with a csv file called "bank_marketing.csv", which you will need to clean, reformat, and split, in order to save separate files based on the tables you will create. It is recommended to use pandas for these tasks.

Lastly, you will write the SQL code that the bank can execute to create the tables and populate with the data from the csv files. As the bank are quite strict about their security, you'll save SQL files as multiline string variables that they can then use to create the database on their end.

You have been asked to design a database that will have three tables:

client

columndata typedescriptionoriginal column in dataset
idserialClient ID - primary keyclient_id
ageintegerClient's age in yearsage
jobtextClient's type of jobjob
maritaltextClient's marital statusmarital
educationtextClient's level of educationeducation
credit_defaultbooleanWhether the client's credit is in defaultcredit_default
housingbooleanWhether the client has an existing housing loan (mortgage)housing
loanbooleanWhether the client has an existing personal loanloan

campaign

columndata typedescriptionoriginal column in dataset
campaign_idserialCampaign ID - primary keyN/A - new column
client_idserialClient ID - references id in the client tableclient_id
number_contactsintegerNumber of contact attempts to the client in the current campaigncampaign
contact_durationintegerLast contact duration in secondsduration
pdaysintegerNumber of days since contact in previous campaign (999 = not previously contacted)pdays
previous_campaign_contactsintegerNumber of contact attempts to the client in the previous campaignprevious
previous_outcomebooleanOutcome of the previous campaignpoutcome
campaign_outcomebooleanOutcome of the current campaigny
last_contact_datedateLast date the client was contactedA combination of day, month, and the newly created year

economics

columndata typedescriptionoriginal column in dataset
client_idserialClient ID - references id in the client tableclient_id
emp_var_ratefloatEmployment variation rate (quarterly indicator)emp_var_rate
cons_price_idxfloatConsumer price index (monthly indicator)cons_price_idx
euribor_three_monthsfloatEuro Interbank Offered Rate (euribor) three month rate (daily indicator)euribor3m
number_employedfloatNumber of employees (quarterly indicator)nr_employed
import pandas as pd
import numpy as np

# Read in bank_marketing.csv as a pandas DataFrame.
bank_df = pd.read_csv('bank_marketing.csv')
print(bank_df.head(),bank_df.info())

# Split the data into three DataFrames using information provided about the desired tables as your guide: one with information about the client, another containing campaign data, and a third to store information about economics at the time of the campaign.
client = bank_df.iloc[:,:8]
print(client.head())

campaign = bank_df[['client_id','campaign','duration','pdays','previous','poutcome','y','month','day']]
print(campaign.head())

economics = bank_df[['client_id','emp_var_rate','cons_price_idx','euribor3m','nr_employed']]
print(economics.head())

# Rename the column "client_id" to "id" in client (leave as-is in the other subsets); "duration" to "contact_duration", "previous" to "previous_campaign_contacts", "y" to "campaign_outcome", "poutcome" to "previous_outcome", and "campaign" to "number_contacts" in campaign; and "euribor3m" to "euribor_three_months" and "nr_employed" to "number_employed" in economics.
client.rename(columns = {'client_id':'id'},inplace=True)
print(client.info())

campaign.rename(columns ={'duration':'contact_duration','y':'campaign_outcome','previous':'previous_campaign_contract','poutcome':'previous_outcome','campaign':'number_contacts'},inplace=True)
print(campaign.info())

economics.rename(columns = {'euribor3m':'euribor_three_months','nr_employed':'number_employed'},inplace=True)
print(economics.info())

# Clean the "education" column, changing "." to "_" and "unknown" to NumPy's null values.
client['education'] = client['education'].str.replace('.','_') \
                        .replace('unknown',np.nan)
print(client['education'].unique())

# Remove periods from the "job" column.
client['job']=client['job'].str.strip('.')
print(client['job'].unique())

#Convert "success" and "failure" in the "previous_outcome" and "campaign_outcome" columns to binary (1 or 0), along with the changing "nonexistent" to NumPy's null values in "previous_outcome".
campaign['campaign_outcome'] = campaign['campaign_outcome'].map({'yes': 1,'no' : 0})
campaign['previous_outcome'] = campaign['previous_outcome'].replace({'success': 1,'failure': 0 ,'nonexistent':np.nan})
print(campaign['previous_outcome'].unique(),campaign['campaign_outcome'].unique())

#Add a column called campaign_id in campaign, where all rows have a value of 1.
campaign['campaign_id'] = 1
print(campaign.head())

#Create a datetime column called last_contact_date, in the format of "year-month-day", where the year is 2022, and the month and day values are taken from the "month" and "day" columns.
campaign['last_contact_date'] = pd.to_datetime('2022'+campaign['month'] + campaign['day'].astype('str'),format='%Y%b%d')
print(campaign['last_contact_date'].head())

# Remove any redundant data that might have been used to create new columns, ensuring the columns in each subset of the data match the table displayed in the notebook.
campaign.drop(['month','day'],axis=1,inplace=True)
print(campaign.info())

# Remove any redundant data that might have been used to create new columns, ensuring the columns in each subset of the data match the table displayed in the notebook.
client.to_csv('client.csv',index=False)
campaign.to_csv('campaign.csv',index=False)
economics.to_csv('economics.csv',index=False)

# Store and print database_design
client_table = """CREATE TABLE client
(
    id SERIAL PRIMARY KEY,
    age INTEGER,
    job TEXT,
    marital TEXT,
    education TEXT,
    credit_default BOOLEAN,
    housing BOOLEAN,
    loan BOOLEAN
);
\copy client from 'client.csv' DELIMITER ',' CSV HEADER
"""

campaign_table = """CREATE TABLE campaign
(
    campaign_id SERIAL PRIMARY KEY,
    client_id SERIAL references client (id),
    number_contacts INTEGER,
    contact_duration INTEGER,
    pdays INTEGER,
    previous_campaign_contacts INTEGER,
    previous_outcome BOOLEAN,
    campaign_outcome BOOLEAN,
    last_contact_date DATE    
);
\copy campaign from 'campaign.csv' DELIMITER ',' CSV HEADER
"""

economics_table = """CREATE TABLE economics
(
    client_id SERIAL references client (id),
    emp_var_rate FLOAT,
    cons_price_idx FLOAT,
    euribor_three_months FLOAT,
    number_employed FLOAT
);
\copy economics from 'economics.csv' DELIMITER ',' CSV HEADER
"""
Hidden output