-
Notifications
You must be signed in to change notification settings - Fork 6
/
Northwind Data vault.sql
389 lines (346 loc) · 11 KB
/
Northwind Data vault.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
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
--
-- DWH Northwind, Data vault
--
DROP TABLE IF EXISTS s_categories;
DROP TABLE IF EXISTS s_products;
DROP TABLE IF EXISTS s_suppliers;
DROP TABLE IF EXISTS s_shippers;
DROP TABLE IF EXISTS s_orders;
DROP TABLE IF EXISTS s_customers;
DROP TABLE IF EXISTS s_customer_demographics;
DROP TABLE IF EXISTS s_employees;
DROP TABLE IF EXISTS s_territories;
DROP TABLE IF EXISTS s_region;
DROP TABLE IF EXISTS l_s_products_orders;
DROP TABLE IF EXISTS l_categories_products;
DROP TABLE IF EXISTS l_suppliers_products;
DROP TABLE IF EXISTS l_products_orders;
DROP TABLE IF EXISTS l_shippers_orders;
DROP TABLE IF EXISTS l_customers_orders;
DROP TABLE IF EXISTS l_customer_demographics_customers;
DROP TABLE IF EXISTS l_employees_orders;
DROP TABLE IF EXISTS l_employees_territories;
DROP TABLE IF EXISTS l_employees_employees;
DROP TABLE IF EXISTS l_territories_region;
DROP TABLE IF EXISTS h_categories;
DROP TABLE IF EXISTS h_products;
DROP TABLE IF EXISTS h_suppliers;
DROP TABLE IF EXISTS h_shippers;
DROP TABLE IF EXISTS h_orders;
DROP TABLE IF EXISTS h_customers;
DROP TABLE IF EXISTS h_customer_demographics;
DROP TABLE IF EXISTS h_employees;
DROP TABLE IF EXISTS h_territories;
DROP TABLE IF EXISTS h_region;
--
-- Hubs
--
CREATE TABLE h_categories (
h_categories_rk bigint PRIMARY KEY,
category_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_products (
h_products_rk bigint PRIMARY KEY,
product_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_suppliers (
h_suppliers_rk bigint PRIMARY KEY,
supplier_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_shippers (
h_shippers_id bigint PRIMARY KEY,
shipper_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_orders (
h_orders_rk bigint PRIMARY KEY,
order_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_customers (
h_customers_rk bigint PRIMARY KEY,
customer_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_customer_demographics (
h_customer_demographics_rk bigint PRIMARY KEY,
customer_type_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_employees (
h_employees_rk bigint PRIMARY KEY,
employee_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_territories (
h_territories_rk bigint PRIMARY KEY,
territory_id smallint,
source_system character varying(255),
processed_dttm date
);
CREATE TABLE h_region (
h_region_rk bigint PRIMARY KEY,
region_id smallint,
source_system character varying(255),
processed_dttm date
);
--
-- Links
--
CREATE TABLE l_categories_products (
l_categories_products_rk bigint PRIMARY KEY,
h_categories_rk bigint,
h_products_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_categories_rk) REFERENCES h_categories,
FOREIGN KEY (h_products_rk) REFERENCES h_products
);
CREATE TABLE l_suppliers_products (
l_suppliers_products_rk bigint PRIMARY KEY,
h_suppliers_rk bigint,
h_products_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_suppliers_rk) REFERENCES h_suppliers,
FOREIGN KEY (h_products_rk) REFERENCES h_products
);
CREATE TABLE l_products_orders (
l_products_orders_rk bigint PRIMARY KEY,
h_orders_rk bigint,
h_products_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_orders_rk) REFERENCES h_orders,
FOREIGN KEY (h_products_rk) REFERENCES h_products
);
CREATE TABLE l_shippers_orders (
l_shippers_orders_rk bigint PRIMARY KEY,
h_shippers_rk bigint,
h_orders_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_shippers_rk) REFERENCES h_shippers,
FOREIGN KEY (h_orders_rk) REFERENCES h_orders
);
CREATE TABLE l_customers_orders (
l_customers_orders_rk bigint PRIMARY KEY,
h_customers_rk bigint,
h_orders_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_customers_rk) REFERENCES h_customers,
FOREIGN KEY (h_orders_rk) REFERENCES h_orders
);
CREATE TABLE l_customer_demographics_customers (
l_customer_demographics_customers_rk bigint PRIMARY KEY,
h_customer_demographics_rk bigint,
h_customers_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_customer_demographics_rk) REFERENCES h_customer_demographics,
FOREIGN KEY (h_customers_rk) REFERENCES h_customers
);
CREATE TABLE l_employees_orders (
l_employees_orders_rk bigint PRIMARY KEY,
h_employees_rk bigint,
h_orders_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_employees_rk) REFERENCES h_employees,
FOREIGN KEY (h_orders_rk) REFERENCES h_orders
);
CREATE TABLE l_employees_territories (
l_employees_territories_rk bigint PRIMARY KEY,
h_employees_rk bigint,
h_territories_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_employees_rk) REFERENCES h_employees,
FOREIGN KEY (h_territories_rk) REFERENCES h_territories
);
CREATE TABLE l_employees_employees (
l_employees_employees_rk bigint PRIMARY KEY,
h_employees_rk bigint,
h_employees_rk_reports_to bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_employees_rk) REFERENCES h_employees,
FOREIGN KEY (h_employees_rk_reports_to) REFERENCES h_employees (h_employees_rk)
);
CREATE TABLE l_territories_region (
l_territories_region_rk bigint PRIMARY KEY,
h_territories_rk bigint,
h_region_rk bigint,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_territories_rk) REFERENCES h_territories,
FOREIGN KEY (h_region_rk) REFERENCES h_region
);
--
-- Satellites
--
CREATE TABLE s_categories (
h_categories_rk bigint,
category_name character varying(15) NOT NULL,
description text,
picture bytea,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_categories_rk) REFERENCES h_categories
);
CREATE TABLE s_customer_demographics (
h_customer_demographics_rk bigint,
customer_desc text,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_customer_demographics_rk) REFERENCES h_customer_demographics
);
CREATE TABLE s_customers (
h_customers_rk bigint,
company_name character varying(40) NOT NULL,
contact_name character varying(30),
contact_title character varying(30),
address character varying(60),
city character varying(15),
region character varying(15),
postal_code character varying(10),
country character varying(15),
phone character varying(24),
fax character varying(24),
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_customers_rk) REFERENCES h_customers
);
CREATE TABLE s_employees (
h_employees_rk bigint,
last_name character varying(20) NOT NULL,
first_name character varying(10) NOT NULL,
title character varying(30),
title_of_courtesy character varying(25),
birth_date date,
hire_date date,
address character varying(60),
city character varying(15),
region character varying(15),
postal_code character varying(10),
country character varying(15),
home_phone character varying(24),
extension character varying(4),
photo bytea,
notes text,
photo_path character varying(255),
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_employees_rk) REFERENCES h_employees
);
CREATE TABLE s_suppliers (
h_suppliers_rk bigint,
company_name character varying(40) NOT NULL,
contact_name character varying(30),
contact_title character varying(30),
address character varying(60),
city character varying(15),
region character varying(15),
postal_code character varying(10),
country character varying(15),
phone character varying(24),
fax character varying(24),
homepage text,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_suppliers_rk) REFERENCES h_suppliers
);
CREATE TABLE s_products (
h_products_rk bigint,
product_name character varying(40) NOT NULL,
quantity_per_unit character varying(20),
unit_price real,
units_in_stock smallint,
units_on_order smallint,
reorder_level smallint,
discontinued integer NOT NULL,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_products_rk) REFERENCES h_products
);
CREATE TABLE s_region (
h_region_rk bigint,
region_description bpchar NOT NULL,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_region_rk) REFERENCES h_region
);
CREATE TABLE s_shippers (
h_shippers_rk bigint,
company_name character varying(40) NOT NULL,
phone character varying(24),
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_shippers_rk) REFERENCES h_shippers
);
CREATE TABLE s_orders (
h_orders_rk bigint,
order_date date,
required_date date,
shipped_date date,
freight real,
ship_name character varying(40),
ship_address character varying(60),
ship_city character varying(15),
ship_region character varying(15),
ship_postal_code character varying(10),
ship_country character varying(15),
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_orders_rk) REFERENCES h_orders
);
CREATE TABLE s_territories (
h_territories_rk bigint,
territory_description bpchar NOT NULL,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (h_territories_rk) REFERENCES h_territories
);
CREATE TABLE l_s_products_orders (
l_products_orders_rk bigint,
unit_price real NOT NULL,
quantity smallint NOT NULL,
discount real NOT NULL,
valid_from_dttm date,
valid_to_dttm date,
source_system character varying(255),
processed_dttm date,
FOREIGN KEY (l_products_orders_rk) REFERENCES l_products_orders
);