/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL */

CREATE TABLE DWVENDOR (
V_CODE 		INTEGER PRIMARY KEY,
V_NAME		VARCHAR(35),
V_AREACODE 	CHAR(3),
V_STATE 	CHAR(2));

CREATE TABLE DWPRODUCT (
P_CODE 		VARCHAR(10) PRIMARY KEY,
P_DESCRIPT 	VARCHAR(35),
P_CATEGORY	VARCHAR(5),
V_CODE		INTEGER REFERENCES DWVENDOR);

CREATE TABLE DWREGION (
REG_ID		INTEGER PRIMARY KEY,
REG_NAME	VARCHAR(10));

CREATE TABLE DWCUSTOMER (
CUS_CODE	INTEGER PRIMARY KEY,
CUS_LNAME	VARCHAR(15),
CUS_FNAME	VARCHAR(15),
CUS_INITIAL	CHAR(1),
CUS_STATE 	CHAR(2),
REG_ID		INTEGER REFERENCES DWREGION);

CREATE TABLE DWTIME (
TM_ID		INTEGER PRIMARY KEY,
TM_YEAR		INTEGER,
TM_MONTH	INTEGER,
TM_DAY		INTEGER,
TM_QTR 		INTEGER);

CREATE TABLE DWDAYSALESFACT (
TM_ID  		INTEGER,
CUS_CODE	INTEGER,
P_CODE		VARCHAR(10),
SALE_UNITS	INTEGER,
SALE_PRICE 	DECIMAL(10,2),
PRIMARY KEY (TM_ID, CUS_CODE, P_CODE));


/* DWVENDORS rows						*/
INSERT INTO DWVENDOR VALUES(21225,'Bryson, Inc.'    ,'615','TN');
INSERT INTO DWVENDOR VALUES(21226,'SuperLoo, Inc.'  ,'904','FL');
INSERT INTO DWVENDOR VALUES(21231,'D\&E Supply'     ,'615','TN');
INSERT INTO DWVENDOR VALUES(21344,'Gomez Bros.'     ,'615','KY');
INSERT INTO DWVENDOR VALUES(22567,'Dome Supply'     ,'901','GA');
INSERT INTO DWVENDOR VALUES(23119,'Randsets Ltd.'   ,'901','GA');
INSERT INTO DWVENDOR VALUES(24004,'Brackman Bros.'  ,'615','TN');
INSERT INTO DWVENDOR VALUES(24288,'ORDVA, Inc.'     ,'615','TN');
INSERT INTO DWVENDOR VALUES(25443,'B\&K, Inc.'      ,'904','FL');
INSERT INTO DWVENDOR VALUES(25501,'Damal Supplies'  ,'615','TN');
INSERT INTO DWVENDOR VALUES(25595,'Rubicon Systems' ,'904','FL');

/* 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-in. pwr. saw blade'              ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade'              ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50'            ,'CAT2',23119);
INSERT INTO DWPRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50'            ,'CAT2',23119);
INSERT INTO DWPRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-in. blade'            ,'CAT2',24288);
INSERT INTO DWPRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-in. blade'             ,'CAT3',24288);
INSERT INTO DWPRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-in.'         ,'CAT3',25595);
INSERT INTO DWPRODUCT VALUES('23109-HB','Claw hammer'                          ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.'                ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine'          ,'CAT1',21344);
INSERT INTO DWPRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.'              ,'CAT2',24288);
INSERT INTO DWPRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft'              ,'CAT3',21225);
INSERT INTO DWPRODUCT VALUES('SM-18277','1.25-in. metal screw, 25'             ,'CAT4',21225);
INSERT INTO DWPRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50'                ,'CAT2',21231);
INSERT INTO DWPRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" 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,2015,09,29,3);
INSERT INTO DWTIME VALUES(202,2015,09,30,3);
INSERT INTO DWTIME VALUES(203,2015,09,31,3);
INSERT INTO DWTIME VALUES(206,2015,10,03,4);
INSERT INTO DWTIME VALUES(207,2015,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);

