-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDW_schema_SQLserverVersion2.sql
More file actions
163 lines (140 loc) · 7.05 KB
/
DW_schema_SQLserverVersion2.sql
File metadata and controls
163 lines (140 loc) · 7.05 KB
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
/*drop database OLAP_DW1;
go
*/
create database OLAP_DW1;
go
use OLAP_DW1;
go
DROP TABLE DWDAYSALESFACT;
DROP TABLE DWCUSTOMER;
DROP TABLE DWREGION;
DROP TABLE DWPRODUCT;
DROP TABLE DWVENDOR;
DROP TABLE DWTIME;
CREATE TABLE DWVENDOR (
V_CODE INTEGER PRIMARY KEY,
V_NAME VARCHAR(35),
V_AREACODE CHAR(4),
V_COUNTRY CHAR(2));
CREATE TABLE DWTIME (
TM_ID INTEGER PRIMARY KEY,
TM_YEAR INTEGER,
TM_MONTH INTEGER,
TM_DAY INTEGER,
TM_QTR INTEGER);
CREATE TABLE DWREGION (
REG_ID INTEGER PRIMARY KEY,
REG_NAME VARCHAR(10));
CREATE TABLE DWPRODUCT (
P_CODE VARCHAR(10) PRIMARY KEY,
P_DESCRIPT VARCHAR(35),
P_CATEGORY VARCHAR(5),
V_CODE INTEGER REFERENCES DWVENDOR);
CREATE TABLE DWCUSTOMER (
CUS_CODE INTEGER PRIMARY KEY,
CUS_LNAME VARCHAR(15),
CUS_FNAME VARCHAR(15),
CUS_INITIAL CHAR(1),
CUS_COUNTRY CHAR(2),
REG_ID INTEGER REFERENCES DWREGION);
CREATE TABLE DWDAYSALESFACT (
TM_ID INTEGER,
CUS_CODE INTEGER,
P_CODE VARCHAR(10),
SALE_UNITS INTEGER,
SALE_PRICE FLOAT,
PRIMARY KEY (TM_ID, CUS_CODE, P_CODE));
/* Loading data rows */
/* Turn Escape character on */
/* Default escape character "\" */
/* Used to enter special characters (&) */
/*SET ESCAPE ON; */
/* DWVENDORS rows */
INSERT INTO DWVENDOR VALUES(21225,'Bryson, Inc.' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(21226,'SuperLoo, Inc.' ,'0113','SA');
INSERT INTO DWVENDOR VALUES(21231,'D\&E Supply' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(21344,'Gomez Bros.' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(22567,'Dome Supply' ,'7253','FR');
INSERT INTO DWVENDOR VALUES(23119,'Randsets Ltd.' ,'7253','FR');
INSERT INTO DWVENDOR VALUES(24004,'Brackman Bros.' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(24288,'ORDVA, Inc.' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(25443,'B\&K, Inc.' ,'0113','SA');
INSERT INTO DWVENDOR VALUES(25501,'Damal Supplies' ,'0181','UK');
INSERT INTO DWVENDOR VALUES(25595,'Rubicon Systems' ,'0113','SA');
/* DWPRODUCT rows */
INSERT INTO DWPRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'CAT1',25595);
INSERT INTO DWPRODUCT VALUES('13-Q2/P2','7.25-cm. pwr. saw blade' ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('14-Q1/L3','9.00-cm. pwr. saw blade' ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-cm., 2x50' ,'CAT2',23119);
INSERT INTO DWPRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-cm., 3x50' ,'CAT2',23119);
INSERT INTO DWPRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-cm. blade' ,'CAT2',24288);
INSERT INTO DWPRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-cm. blade' ,'CAT3',24288);
INSERT INTO DWPRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-cm.' ,'CAT3',25595);
INSERT INTO DWPRODUCT VALUES('23109-HB','Claw hammer' ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('23114-AA','Sledge hammer, 6kg.' ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('54778-2T','Rat-tail file, 1/8-cm. fine' ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 cm.' ,'CAT2',24288);
INSERT INTO DWPRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-cm., 4-m' ,'CAT3',21225);
INSERT INTO DWPRODUCT VALUES('SM-18277','1.25-cm. metal screw, 25' ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('SW-23116','2.5-cm. wd. screw, 50' ,'CAT2',21231);
INSERT INTO DWPRODUCT VALUES('WR3/TT3' ,'Steel matting, 4x8x1/6cm, .5m mesh','CAT3',25595);
/* DWREGION rows */
INSERT INTO DWREGION VALUES(1,'NE');
INSERT INTO DWREGION VALUES(2,'NW');
INSERT INTO DWREGION VALUES(3,'SE');
INSERT INTO DWREGION VALUES(4,'SW');
/* DWCUSTOMER rows */
INSERT INTO DWCUSTOMER VALUES(10010,'Ramas' ,'Alfred','A' ,'TN',3);
INSERT INTO DWCUSTOMER VALUES(10011,'Dunne' ,'Leona' ,'K' ,'GA',3);
INSERT INTO DWCUSTOMER VALUES(10012,'Smith' ,'Kathy' ,'W' ,'NY',1);
INSERT INTO DWCUSTOMER VALUES(10013,'Olowski' ,'Paul' ,'F' ,'NJ',1);
INSERT INTO DWCUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'CO',2);
INSERT INTO DWCUSTOMER VALUES(10015,'O''Brian','Amy' ,'B' ,'TN',3);
INSERT INTO DWCUSTOMER VALUES(10016,'Brown' ,'James' ,'G' ,'GA',3);
INSERT INTO DWCUSTOMER VALUES(10017,'Williams','George',NULL,'CA',4);
INSERT INTO DWCUSTOMER VALUES(10018,'Farriss' ,'Anne' ,'G' ,'CA',4);
INSERT INTO DWCUSTOMER VALUES(10019,'Smith' ,'Olette','K' ,'CO',2);
/* DWTIME rows */
INSERT INTO DWTIME VALUES(201,2005,09,29,3);
INSERT INTO DWTIME VALUES(202,2005,09,30,3);
INSERT INTO DWTIME VALUES(203,2005,09,31,3);
INSERT INTO DWTIME VALUES(206,2005,10,03,4);
INSERT INTO DWTIME VALUES(207,2005,10,04,4);
/* DWDAYSALESFACT rows */
INSERT INTO DWDAYSALESFACT VALUES(201,10014,'13-Q2/P2',1,14.99);
INSERT INTO DWDAYSALESFACT VALUES(201,10014,'23109-HB',1,9.95);
INSERT INTO DWDAYSALESFACT VALUES(201,10015,'54778-2T',2,4.99);
INSERT INTO DWDAYSALESFACT VALUES(201,10015,'2238/QPD',1,38.95);
INSERT INTO DWDAYSALESFACT VALUES(202,10016,'1546-QQ2',1,39.95);
INSERT INTO DWDAYSALESFACT VALUES(202,10016,'13-Q2/P2',5,14.99);
INSERT INTO DWDAYSALESFACT VALUES(202,10017,'54778-2T',3,4.99);
INSERT INTO DWDAYSALESFACT VALUES(202,10017,'23109-HB',2,9.95);
INSERT INTO DWDAYSALESFACT VALUES(202,10018,'PVC23DRT',12,5.87);
INSERT INTO DWDAYSALESFACT VALUES(203,10012,'SM-18277',3,6.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10014,'2232/QTY',1,109.92);
INSERT INTO DWDAYSALESFACT VALUES(203,10015,'23109-HB',1,9.95);
INSERT INTO DWDAYSALESFACT VALUES(203,10015,'89-WRE-Q',1,256.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10016,'13-Q2/P2',2,14.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10016,'54778-2T',1,4.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10016,'PVC23DRT',5,5.87);
INSERT INTO DWDAYSALESFACT VALUES(203,10017,'WR3/TT3',3,119.95);
INSERT INTO DWDAYSALESFACT VALUES(203,10017,'23109-HB',1,9.95);
INSERT INTO DWDAYSALESFACT VALUES(203,10017,'13-Q2/P2',1,14.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10018,'23109-HB',1,9.95);
INSERT INTO DWDAYSALESFACT VALUES(203,10018,'54778-2T',2,4.99);
INSERT INTO DWDAYSALESFACT VALUES(203,10018,'2238/QPD',1,38.95);
INSERT INTO DWDAYSALESFACT VALUES(203,10019,'1546-QQ2',1,39.95);
INSERT INTO DWDAYSALESFACT VALUES(206,10010,'13-Q2/P2',5,14.99);
INSERT INTO DWDAYSALESFACT VALUES(206,10010,'54778-2T',3,4.99);
INSERT INTO DWDAYSALESFACT VALUES(206,10010,'23109-HB',2,9.95);
INSERT INTO DWDAYSALESFACT VALUES(206,10010,'PVC23DRT',12,5.87);
INSERT INTO DWDAYSALESFACT VALUES(206,10011,'SM-18277',3,6.99);
INSERT INTO DWDAYSALESFACT VALUES(206,10011,'2232/QTY',1,109.92);
INSERT INTO DWDAYSALESFACT VALUES(206,10012,'23109-HB',1,9.95);
INSERT INTO DWDAYSALESFACT VALUES(206,10012,'89-WRE-Q',1,256.99);
INSERT INTO DWDAYSALESFACT VALUES(207,10013,'13-Q2/P2',2,14.99);
INSERT INTO DWDAYSALESFACT VALUES(207,10013,'54778-2T',1,4.99);
INSERT INTO DWDAYSALESFACT VALUES(207,10013,'PVC23DRT',5,5.87);
INSERT INTO DWDAYSALESFACT VALUES(207,10014,'WR3/TT3',3,119.95);
INSERT INTO DWDAYSALESFACT VALUES(207,10015,'23109-HB',1,9.95);
COMMIT;