-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathcreate_master_schema.sql
324 lines (304 loc) · 10.9 KB
/
create_master_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
drop schema if exists master cascade;
create schema master authorization postgres;
drop table if exists master.tradetype;
create table master.tradetype(
tt_id char(3) not null,
tt_name char(12) not null,
tt_is_sell numeric(1) not null check(tt_is_sell >= 0),
tt_is_mrkt numeric(1) not null check(tt_is_mrkt >= 0)
);
drop table if exists master.statustype;
create table master.statustype(
st_id char(4) not null,
st_name char(10) not null
);
drop table if exists master.taxrate;
create table master.taxrate(
tx_id char(4) not null,
tx_name char(50) not null,
tx_rate numeric(6,5) not null check(tx_rate >= 0)
);
drop table if exists master.industry;
create table master.industry(
in_id char(2) not null,
in_name char(50) not null,
in_sc_id char(4) not null
);
drop table if exists master.dimdate;
create table master.dimdate(
sk_dateid numeric(11) not null check(sk_dateid >= 0),
datevalue date not null,
datedesc char(20) not null,
calendaryearid numeric(4) not null check(calendaryearid >= 0),
calendaryeardesc char(20) not null,
calendarqtrid numeric(5) not null check(calendarqtrid >= 0),
calendarqtrdesc char(20) not null,
calendarmonthid numeric(6) not null check(calendarmonthid >= 0),
calendarmonthdesc char(20) not null,
calendarweekid numeric(6) not null check(calendarweekid >= 0),
calendarweekdesc char(20) not null,
dayofweeknum numeric(1) not null check(dayofweeknum >= 0),
dayofweekdesc char(10) not null,
fiscalyearid numeric(4) not null check(fiscalyearid >= 0),
fiscalyeardesc char(20) not null,
fiscalqtrid numeric(5) not null check(fiscalqtrid >= 0),
fiscalqtrdesc char(20) not null,
holidayflag boolean
);
drop table if exists master.dimtime;
create table master.dimtime(
sk_timeid numeric(11) not null check(sk_timeid >= 0),
timevalue time not null,
hourid numeric(2) not null check(hourid >= 0),
hourdesc char(20) not null,
minuteid numeric(2) not null check(minuteid >= 0),
minutedesc char(20) not null,
secondid numeric(2) not null check(secondid >= 0),
seconddesc char(20) not null,
markethoursflag boolean,
officehoursflag boolean
);
drop table if exists master.dimcompany;
create table master.dimcompany(
sk_companyid numeric(11) not null check(sk_companyid >= 0),
companyid numeric(11) not null check(companyid >= 0),
status char(10) not null,
name char(60) not null,
industry char(50) not null,
sprating char(4),
islowgrade boolean,
ceo char(100) not null,
addressline1 char(80),
addressline2 char(80),
postalcode char(12) not null,
city char(25) not null,
stateprov char(20) not null,
country char(24),
description char(150) not null,
foundingdate date,
iscurrent boolean not null,
batchid numeric(5) not null check(batchid >= 0),
effectivedate date not null,
enddate date not null
);
drop table if exists master.dimbroker;
create table master.dimbroker(
sk_brokerid numeric(11) not null check(sk_brokerid >= 0),
brokerid numeric(11) not null check(brokerid >= 0),
managerid numeric(11) check(managerid >= 0),
firstname char(50) not null,
lastname char(50) not null,
middleinitial char(1),
branch char(50),
office char(50),
phone char(14),
iscurrent boolean not null,
batchid numeric(5) not null check(batchid >= 0),
effectivedate date not null,
enddate date not null
);
drop table if exists master.dimcustomer;
create table master.dimcustomer(
sk_customerid numeric(11) not null check(sk_customerid >= 0),
customerid numeric(11) not null check(customerid >= 0),
taxid char(20) not null,
status char(10) not null,
lastname char(30) not null,
firstname char(20) not null,
middleinitial char(1),
gender char(1),
tier numeric(1) check(tier >= 0),
dob date not null,
addressline1 char(80) not null,
addressline2 char(80),
postalcode char(12) not null,
city char(25) not null,
stateprov char(20) not null,
country char(24),
phone1 char(30),
phone2 char(30),
phone3 char(30),
email1 char(50),
email2 char(50),
nationaltaxratedesc char(50),
nationaltaxrate numeric(6,5) check(nationaltaxrate >= 0),
localtaxratedesc char(50),
localtaxrate numeric(6,5) check(localtaxrate >= 0),
agencyid char(30),
creditrating numeric(5) check(creditrating >= 0),
networth numeric(10),
marketingnameplate char(100),
iscurrent boolean not null,
batchid numeric(5) not null check(batchid >= 0),
effectivedate date not null,
enddate date not null
);
drop table if exists master.dimaccount;
create table master.dimaccount(
sk_accountid numeric(11) not null check(sk_accountid >= 0),
accountid numeric(11) not null check(accountid >= 0),
sk_brokerid numeric(11) not null check(sk_brokerid >= 0),
sk_customerid numeric(11) not null check(sk_customerid >= 0),
status char(10) not null,
accountdesc char(50),
taxstatus numeric(1) check(taxstatus in(0, 1, 2)),
iscurrent boolean not null,
batchid numeric(5) not null check(batchid >= 0),
effectivedate date not null,
enddate date not null
);
drop table if exists master.dimsecurity;
create table master.dimsecurity(
sk_securityid numeric(11) not null check(sk_securityid >= 0),
symbol char(15) not null,
issue char(6) not null,
status char(10) not null,
name char(70) not null,
exchangeid char(6) not null,
sk_companyid numeric(11) not null check(sk_companyid >= 0),
sharesoutstanding numeric(12) not null check(sharesoutstanding >= 0),
firsttrade date not null,
firsttradeonexchange date not null,
dividend numeric(10,2) not null,
iscurrent boolean not null,
batchid numeric(5) not null check(batchid >= 0),
effectivedate date not null,
enddate date not null
);
drop table if exists master.dimtrade;
create table master.dimtrade(
tradeid numeric(11) not null check(tradeid >= 0),
sk_brokerid numeric(11) check(sk_brokerid >= 0),
sk_createdateid numeric(11) not null check(sk_createdateid >= 0),
sk_createtimeid numeric(11) not null check(sk_createtimeid >= 0),
sk_closedateid numeric(11) check(sk_closedateid >= 0),
sk_closetimeid numeric(11) check(sk_closetimeid >= 0),
status char(10) not null,
type char(12) not null,
cashflag boolean not null,
sk_securityid numeric(11) not null check(sk_securityid >= 0),
sk_companyid numeric(11) not null check(sk_companyid >= 0),
quantity numeric(6, 0) not null check(quantity >= 0),
bidprice numeric(8, 2) not null check(bidprice >= 0),
sk_customerid numeric(11) not null check(sk_customerid >= 0),
sk_accountid numeric(11) not null check(sk_accountid >= 0),
executedby char(64) not null,
tradeprice numeric(8,2) check(tradeprice >= 0),
fee numeric(10,2) check(fee >= 0),
commission numeric(10,2) check(commission >= 0),
tax numeric(10,2) check(tax >= 0),
batchid numeric(5) not null check(batchid >= 0)
);
drop table if exists master.financial;
create table master.financial(
sk_companyid numeric(11) not null check(sk_companyid >= 0),
fi_year numeric(4) not null check(fi_year >= 0),
fi_qtr numeric(1) not null check(fi_qtr >= 0),
fi_qtr_start_date date not null,
fi_revenue numeric(15, 2) not null,
fi_net_earn numeric(15, 2) not null,
fi_basic_eps numeric(10, 2) not null,
fi_dilut_eps numeric(10, 2) not null,
fi_margin numeric(10, 2) not null,
fi_inventory numeric(15, 2) not null,
fi_assets numeric(15, 2) not null,
fi_liability numeric(15, 2) not null,
fi_out_basic numeric(12) not null,
fi_out_dilut numeric(12) not null
);
drop table if exists master.factcashbalances;
create table master.factcashbalances(
sk_customerid numeric(11) not null check(sk_customerid >= 0),
sk_accountid numeric(11) not null check(sk_accountid >= 0),
sk_dateid numeric(11) not null check(sk_dateid >= 0),
cash numeric(15, 2) not null,
batchid numeric(5) not null check(batchid >= 0)
);
drop table if exists master.factholdings;
create table master.factholdings(
tradeid numeric(11) not null check(tradeid >= 0),
currenttradeid numeric(11) not null check(currenttradeid >= 0),
sk_customerid numeric(11) not null check(sk_customerid >= 0),
sk_accountid numeric(11) not null check(sk_accountid >= 0),
sk_securityid numeric(11) not null check(sk_securityid >= 0),
sk_companyid numeric(11) not null check(sk_companyid >= 0),
sk_dateid numeric(11) not null check(sk_dateid >= 0),
sk_timeid numeric(11) not null check(sk_timeid >= 0),
currentprice numeric(8, 2) not null check(currentprice >= 0),
currentholding numeric(6) not null,
batchid numeric(5) not null check(batchid >= 0)
);
drop table if exists master.factmarkethistory;
create table master.factmarkethistory(
sk_securityid numeric(11) not null check(sk_securityid >= 0),
sk_companyid numeric(11) not null check(sk_companyid >= 0),
sk_dateid numeric(11) not null check(sk_dateid >= 0),
peratio numeric(10, 2) check(peratio >= 0),
yield numeric(5, 2) not null check(yield >= 0),
fiftytwoweekhigh numeric(8, 2) not null check(fiftytwoweekhigh >= 0),
sk_fiftytwoweekhighdate numeric(11) not null check(sk_fiftytwoweekhighdate >= 0),
fiftytwoweeklow numeric(8, 2) not null check(fiftytwoweeklow >= 0),
sk_fiftytwoweeklowdate numeric(11) not null check(sk_fiftytwoweeklowdate >= 0),
closeprice numeric(8, 2) not null check(closeprice >= 0),
dayhigh numeric(8, 2) not null check(dayhigh >= 0),
daylow numeric(8, 2) not null check(daylow >= 0),
volume numeric(12) not null check(volume >= 0),
batchid numeric(5) not null check(batchid >= 0)
);
drop table if exists master.factwatches;
create table master.factwatches(
sk_customerid numeric(11) not null check(sk_customerid >= 0),
sk_securityid numeric(11) not null check(sk_securityid >= 0),
sk_dateid_dateplaced numeric(11) not null check(sk_dateid_dateplaced >= 0),
sk_dateid_dateremoved numeric(11) check(sk_dateid_dateremoved >= 0),
batchid numeric(5) not null check(batchid >= 0)
);
drop table if exists master.prospect;
create table master.prospect(
agencyid char(30) not null,
sk_recorddateid numeric(11) not null check(sk_recorddateid >= 0),
sk_updatedateid numeric(11) not null check(sk_updatedateid >= 0),
batchid numeric(5) not null check(batchid >= 0),
iscustomer boolean not null,
lastname char(30) not null,
firstname char(30) not null,
middleinitial char(1),
gender char(1),
addressline1 char(80),
addressline2 char(80),
postalcode char(12),
city char(25) not null,
state char(20) not null,
country char(24),
phone char(30),
income numeric(9) check(income >= 0),
numbercars numeric(2) check(numbercars >= 0),
numberchildren numeric(2) check(numbercars >= 0),
maritalstatus char(1),
age numeric(3) check(age >= 0),
creditrating numeric(4) check(creditrating >= 0),
ownorrentflag char(1),
employer char(30),
numbercreditcards numeric(2) check(numbercreditcards >= 0),
networth numeric(12) check(networth >= 0),
marketingnameplate char(100)
);
-- operational tables
drop table if exists master.audit;
create table master.audit(
dataset char(20) not null,
batchid numeric(5) check(batchid >= 0),
date date,
attribute char(50) not null,
value numeric(15),
dvalue numeric(15, 5)
);
drop table if exists master.dimessages;
create table master.dimessages(
messagedateandtime timestamp not null,
batchid numeric(5) not null check(batchid >= 0),
messagesource char(30),
messagetext char(50) not null,
messagetype char(12) not null,
messagedata char(100)
);