· 8 years ago · Apr 21, 2018, 09:54 PM
1if exists (select * from sysobjects where name='Rawbank_bazaar' and xtype='U')
2Drop table Rawbank_bazaar
3GO
4
5CREATE TABLE [dbo].[Rawbank_bazaar](
6 [bank_id] [int] NOT NULL PRIMARY KEY,
7 [NameOfBank] [nvarchar](50) NOT NULL,
8 [Interest_Rate] [nvarchar](50) NOT NULL,
9 [Loan_Amount] [nvarchar](50) NOT NULL,
10 [Tenure_Range] [nvarchar](50) NOT NULL,
11 [Type] [nvarchar](50) NOT NULL
12)
13
14GO
15if exists (select * from sysobjects where name='Rawcredit_score' and xtype='U')
16Drop table Rawcredit_score
17GO
18
19CREATE TABLE [dbo].[Rawcredit_score](
20 [customer_id] [int] NOT NULL PRIMARY KEY,
21 [CreditScore] [int] NOT NULL,
22 [country] [nvarchar](50) NOT NULL,
23 [Gender] [nvarchar](50) NOT NULL,
24 [Age] [int] NOT NULL,
25 [Tenure] [int] NOT NULL,
26 [Balance_Loan] [float] NOT NULL,
27 [HasCrCard] [int] NOT NULL,
28 [EstimatedSalary] [float] NOT NULL,
29 [Repaid] [nvarchar](50) NOT NULL
30)
31
32GO
33if exists (select * from sysobjects where name='Rawperson' and xtype='U')
34Drop table Rawperson
35GO
36CREATE TABLE [dbo].[Rawperson](
37 [person_id] [int] NOT NULL,
38 [duration] [int] NOT NULL,
39 [credit_history] [nvarchar](50) NOT NULL,
40 [purpose] [nvarchar](50) NOT NULL,
41 [credit_amount] [int] NOT NULL,
42 [savings_status] [nvarchar](50) NOT NULL,
43 [employment] [nvarchar](50) NOT NULL,
44 [personal_status] [nvarchar](50) NOT NULL,
45 [residence_since] [int] NOT NULL,
46 [property_magnitude] [nvarchar](50) NOT NULL,
47 [age] [int] NOT NULL,
48 [other_payment_plans] [nvarchar](50) NOT NULL,
49 [housing] [nvarchar](50) NOT NULL,
50 [existing_credits] [int] NOT NULL,
51 [Education_Loan] [nvarchar](50) NOT NULL,
52 [num_dependents] [int] NOT NULL,
53 [foreign_worker] [nvarchar](50) NOT NULL
54)
55
56GO
57if exists (select * from sysobjects where name='Rawpast_loans' and xtype='U')
58Drop table Rawpast_loans
59GO
60
61CREATE TABLE [dbo].[Rawpast_loans](
62 [person_id] [int] NOT NULL PRIMARY KEY,
63 [age] [int] NOT NULL,
64 [job] [nvarchar](50) NOT NULL,
65 [marital_status] [nvarchar](50) NOT NULL,
66 [education] [nvarchar](50) NOT NULL,
67 [defaulter] [nvarchar](50) NOT NULL,
68 [housing] [nvarchar](50) NOT NULL,
69 [loan] [nvarchar](50) NOT NULL,
70 [duration] [int] NOT NULL,
71 [campaign] [int] NOT NULL,
72 [previous_loan] [int] NOT NULL
73)
74
75@@@----DIMENSION TEABLE
76
77if exists (select * from sysobjects where name='Dimbank_bazaar' and xtype='U')
78Drop table Dimbank_bazaar
79GO
80CREATE TABLE [dbo].[Dimbank_bazaar](
81 [bank_id] [int] NOT NULL PRIMARY KEY,
82 [NameOfBank] [nvarchar](50) NOT NULL,
83 [Interest_Rate] [nvarchar](50) NOT NULL,
84 [Loan_Amount] [nvarchar](50) NOT NULL,
85 [Tenure_Range] [nvarchar](50) NOT NULL,
86 [Type] [nvarchar](50) NOT NULL
87)
88
89
90GO
91if exists (select * from sysobjects where name='Dimcredit_score' and xtype='U')
92Drop table Dimcredit_score
93GO
94CREATE TABLE [dbo].[Dimcredit_score](
95 [customer_id] [int] NOT NULL PRIMARY KEY,
96 [CreditScore] [int] NOT NULL,
97 [country] [nvarchar](50) NOT NULL,
98 [Gender] [nvarchar](50) NOT NULL,
99 [Age] [int] NOT NULL,
100 [Tenure] [int] NOT NULL,
101 [Balance_Loan] [float] NOT NULL,
102 [HasCrCard] [int] NOT NULL,
103 [EstimatedSalary] [float] NOT NULL,
104 [Repaid] [nvarchar](50) NOT NULL
105)
106
107
108GO
109if exists (select * from sysobjects where name='Dimperson' and xtype='U')
110Drop table Dimperson
111GO
112
113CREATE TABLE [dbo].[Dimperson](
114 [person_id] [int] NOT NULL,
115 [duration] [int] NOT NULL,
116 [credit_history] [nvarchar](50) NOT NULL,
117 [purpose] [nvarchar](50) NOT NULL,
118 [credit_amount] [int] NOT NULL,
119 [savings_status] [nvarchar](50) NOT NULL,
120 [employment] [nvarchar](50) NOT NULL,
121 [personal_status] [nvarchar](50) NOT NULL,
122 [residence_since] [int] NOT NULL,
123 [property_magnitude] [nvarchar](50) NOT NULL,
124 [age] [int] NOT NULL,
125 [other_payment_plans] [nvarchar](50) NOT NULL,
126 [housing] [nvarchar](50) NOT NULL,
127 [existing_credits] [int] NOT NULL,
128 [Education_Loan] [nvarchar](50) NOT NULL,
129 [num_dependents] [int] NOT NULL,
130 [foreign_worker] [nvarchar](50) NOT NULL
131)
132
133
134@@@@@@@@@@@@@@ Insert Dim Table @@@@@@@@@@@@@@@
135TRUNCATE TABLE [dbo].[Dimperson]
136GO
137INSERT INTO [dbo].[Dimperson]
138 ([person_id]
139 ,[duration]
140 ,[credit_history]
141 ,[purpose]
142 ,[credit_amount]
143 ,[savings_status]
144 ,[employment]
145 ,[personal_status]
146 ,[residence_since]
147 ,[property_magnitude]
148 ,[age]
149 ,[other_payment_plans]
150 ,[housing]
151 ,[existing_credits]
152 ,[Education_Loan]
153 ,[num_dependents]
154 ,[foreign_worker]
155)
156SELECT [person_id]
157 ,[duration]
158 ,[credit_history]
159 ,[purpose]
160 ,[credit_amount]
161 ,[savings_status]
162 ,[employment]
163 ,[personal_status]
164 ,[residence_since]
165 ,[property_magnitude]
166 ,[age]
167 ,[other_payment_plans]
168 ,[housing]
169 ,[existing_credits]
170 ,[Education_Loan]
171 ,[num_dependents]
172 ,[foreign_worker]
173 FROM [dbo].[Rawperson]
174GO
175
176
177
178TRUNCATE TABLE [dbo].[Dimcredit_score]
179GO
180INSERT INTO [dbo].[Dimcredit_score]
181 ([customer_id]
182 ,[CreditScore]
183 ,[country]
184 ,[Gender]
185 ,[Age]
186 ,[Tenure]
187 ,[Balance_Loan]
188 ,[HasCrCard]
189 ,[EstimatedSalary]
190 ,[Repaid]
191)
192SELECT [customer_id]
193 ,[CreditScore]
194 ,[country]
195 ,[Gender]
196 ,[Age]
197 ,[Tenure]
198 ,[Balance_Loan]
199 ,[HasCrCard]
200 ,[EstimatedSalary]
201 ,[Repaid]
202 FROM [dbo].[Rawcredit_score]
203GO
204
205
206TRUNCATE TABLE [dbo].[Dimbank_bazaar]
207GO
208INSERT INTO [dbo].[Dimbank_bazaar]
209 ([bank_id]
210 ,[NameOfBank]
211 ,[Interest_Rate]
212 ,[Loan_Amount]
213 ,[Tenure_Range]
214 ,[Type]
215)
216SELECT [bank_id]
217 ,[NameOfBank]
218 ,[Interest_Rate]
219 ,[Loan_Amount]
220 ,[Tenure_Range]
221 ,[Type]
222 FROM [dbo].[Rawbank_bazaar]
223GO
224
225@@@@@@@@@ CREATE Factatble @@@@@@@@@@@@@@2