The Credit History Database is a simple star schema that represents customer credit history.
Table Name
|
Default Number of Rows
|
 CreditHistory_Fact
Each tuple in the fact table represents a credit transaction done by an individual.
Field Name
|
Data Type
|
Description
|
Date_Key
|
Integer
|
Foreign Key reference Date Table
|
Customer_Key
|
Integer
|
Foreign Key reference Customer table
|
Institution_Key
|
Integer
|
Foreign Key reference Institution table
|
Account_Key
|
Integer
|
Foreign Key reference AccountType Table
|
Mortgage_Key
|
Integer
|
Foreign Key reference MortgageType Table
|
Days_Overdue
|
Integer
|
This field represents the number of days credit is overdue. 99999 Represents Bad Debt.
|
Outstanding_Amount
|
Float
|
Outstanding amount for a credit transaction
|
|
5000000
|
Customer_Dimension
This table describes details of customers whose credit history is maintained by the company.
Field Name
|
Data Type
|
Description/Example
|
Customer_Key
|
Integer
|
Primary Key
|
Customer_FirstName
|
Varchar
|
Customer First Name
|
Customer_LastName
|
Varchar
|
Customer Last Name
|
Current_Employer
|
Varchar
|
Current Employer
|
SSN
|
Varchar
|
Social Security Number
|
HomePhone
|
Varchar
|
Home phone
|
Age
|
Varchar
|
Customer Age
|
Sex
|
Varchar
|
Customer Sex
|
City
|
Varchar
|
Customer City
|
State
|
Integer
|
Customer Sate
|
Zip
|
Varchar
|
Zip code
|
|
5000
|
Institution_Dimension
This table describes all the banking and financial institutions in the country.
Field Name
|
Data Type
|
Description/Example
|
Institution_Key
|
Integer
|
Primary key
|
Institution_Name
|
Varchar
|
Bank/Credit lending institutions
|
Address
|
Varchar
|
Address of institution
|
City
|
Varchar
|
City of institution
|
State
|
Varchar
|
State of institution
|
Zip
|
Varchar
|
Zip code
|
|
100
|
AccountType_Dimension
This table describes the type of accounts that can be offered by financial institutions
Field Name
|
Data Type
|
Description/Example
|
Account_Key
|
Integer
|
Primary Key
|
Account_Type
|
Varchar
|
Type of account Checking/Current/Loan
|
Account_Desc
|
Varchar
|
Brief Description of Account Type
|
Account_Limit
|
Integer
|
If loan account then sanctioned credit limit
|
|
50
|
MortgageType_Dimension
This table describes types of mortgages.
Field Name
|
Data Type
|
Description/Example
|
Mortgage_Key
|
Integer
|
Primary Key
|
Mortgage_Type
|
Varchar
|
Car/Home/Personal mortgage
|
Mortgage_Amount
|
Integer
|
Mortgage Amount, Like $1000, $10000 etc.
|
Mortgage_Tenure
|
Integer
|
Mortgage Tenure in Months like 12, 24, 36 etc
|
Mortgage_Interest
|
Double
|
Applicable Interest Rate.
|
Mortgage_EMI
|
Double
|
Amount payable monthly as installments.
|
|
1000
|
