fWcVWWEC

1create database fantasyshop;
2use fantasyshop;
3
4CREATE TABLE IF NOT EXISTS `vendor` (
5  `VEN_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
6  `VEN_NAME` 			varchar(32) NOT NULL DEFAULT ' ',
7  `VEN_REALM` 			varchar(64) NOT NULL DEFAULT ' ',
8  `VEN_CONTACT` 		varchar(64) NULL
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10
11
12CREATE TABLE IF NOT EXISTS `material` (
13  `MAT_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
14  `MAT_NAME`			varchar(32) NOT NULL DEFAULT ' ',
15  `MAT_QOH` 			int(3) 		NULL,
16  `VEN_ID` 				int(5) 		NOT NULL
17) ENGINE=InnoDB DEFAULT CHARSET=latin1;
18
19
20CREATE TABLE IF NOT EXISTS `recipe` (
21  `PROD_ID` 			int(5) 		PRIMARY KEY AUTO_INCREMENT,
22  `MAT_ID` 				int(5) 		NOT NULL,
23  `REC_QUANTITY` 		int(3) 		NULL
24) ENGINE=InnoDB DEFAULT CHARSET=latin1;
25
26
27CREATE TABLE IF NOT EXISTS `product` (
28  `PROD_ID` 			int(5) 		PRIMARY KEY AUTO_INCREMENT,
29  `PROD_NAME` 			varchar(32) NOT NULL DEFAULT ' ',
30  `PROD_DESCRIPT` 		varchar(64) NOT NULL DEFAULT ' ',
31  `PROD_PRICE` 			float(6) 	NULL,
32  `ENCH_ID` 			int(5) 		NULL
33) ENGINE=INNODB DEFAULT CHARSET=latin1;
34
35
36CREATE TABLE IF NOT EXISTS `enchantment` (
37  `ENCH_ID` 			int(5) 		PRIMARY KEY AUTO_INCREMENT,
38  `ENCH_TYPE` 			varchar(32) NOT NULL,
39  `ENCH_POWER` 			varchar(32) NULL
40) ENGINE=INNODB DEFAULT CHARSET=latin1;
41
42
43CREATE TABLE IF NOT EXISTS `orders` (
44  `ORD_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
45  `CUS_ID` 				int(5) 		NOT NULL,
46  `ORD_DEST` 			varchar(64) NULL DEFAULT ' ',
47  `ORD_ORDER_DATE` 		date 		NOT NULL,
48  `ORD_DELIVERY_DATE` 	date 		NOT NULL
49) ENGINE=INNODB DEFAULT CHARSET=latin1;
50
51
52CREATE TABLE IF NOT EXISTS `customer` (
53  `CUS_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
54  `CUS_RACE` 			varchar(32) NOT NULL DEFAULT ' ',
55  `CUS_BALANCE` 		float(9) 	NULL
56) ENGINE=INNODB DEFAULT CHARSET=latin1;
57
58
59CREATE TABLE IF NOT EXISTS `line` (
60  `LINE_ID` 			int(5) 		PRIMARY KEY AUTO_INCREMENT,
61  `ORD_ID` 				int(5) 		NULL,
62  `PROD_ID` 			int(5) 		NULL,
63  `EMP_ID` 				int(5) 		NULL,
64  `LINE_PRICE` 			float(6) 	NULL,
65  `LINE_QUANTITY` 		int(3) 		NULL
66) ENGINE=INNODB DEFAULT CHARSET=latin1;
67
68
69CREATE TABLE IF NOT EXISTS `employee` (
70  `EMP_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
71  `EMP_FNAME` 			varchar(32) NOT NULL DEFAULT ' ',
72  `EMP_LNAME` 			varchar(32) NOT NULL DEFAULT ' ',
73  `EMP_RACE` 			varchar(32) NOT NULL DEFAULT 'Troll',
74  `EMP_CONTACT` 		varchar(64) NOT NULL DEFAULT ' ',
75  `EMP_HIRE_DATE` 		date 		NULL,
76  `JOB_ID` 				int(5) 		NOT NULL
77) ENGINE=INNODB DEFAULT CHARSET=latin1;
78
79
80CREATE TABLE IF NOT EXISTS `jobtype` (
81  `JOB_ID` 				int(5) 		PRIMARY KEY AUTO_INCREMENT,
82  `JOB_NAME` 			varchar(64) NOT NULL DEFAULT 'Jerb',
83  `JOB_DESCRIPT` 		varchar(64) NULL
84) ENGINE=INNODB DEFAULT CHARSET=latin1;
85
86
87#CREATE TABLE IF NOT EXISTS `baileydurkin` (
88#  `V_ID` int(5) NOT NULL DEFAULT '0',
89#  PRIMARY KEY (`V_ID`),
90#  FOREIGN KEY (`V_ID`) REFERENCES `vendor` (`V_ID`)
91#) ENGINE=InnoDB DEFAULT CHARSET=latin1;
92
93
94ALTER TABLE `vendor` AUTO_INCREMENT = 		10001;
95ALTER TABLE `material` AUTO_INCREMENT = 	11001;
96ALTER TABLE `recipe` AUTO_INCREMENT = 		12001;
97ALTER TABLE `product` AUTO_INCREMENT = 		13001;
98ALTER TABLE `enchantment` AUTO_INCREMENT =  14001;
99ALTER TABLE `orders` AUTO_INCREMENT = 		15001;
100ALTER TABLE `customer` AUTO_INCREMENT = 	16001;
101ALTER TABLE `line` AUTO_INCREMENT = 		17001;
102ALTER TABLE `employee` AUTO_INCREMENT = 	18001;
103ALTER TABLE `jobtype` AUTO_INCREMENT = 		19001;
104
105#ALTER TABLE vendor ADD PRIMARY KEY (VEN_ID);
106#ALTER TABLE material ADD PRIMARY KEY (MAT_ID);
107ALTER TABLE material ADD FOREIGN KEY (VEN_ID) REFERENCES vendor(VEN_ID);
108###ALTER TABLE material ADD FOREIGN KEY (MAT_ID) REFERENCES recipe(MAT_ID);
109##ALTER TABLE recipe ADD PRIMARY KEY (PROD_ID, MAT_ID);
110ALTER TABLE recipe ADD FOREIGN KEY (PROD_ID) REFERENCES product(PROD_ID);
111ALTER TABLE recipe ADD FOREIGN KEY (MAT_ID) REFERENCES material(MAT_ID);
112#ALTER TABLE product ADD PRIMARY KEY (PROD_ID);
113ALTER TABLE product ADD FOREIGN KEY (ENCH_ID) REFERENCES enchantment(ENCH_ID);
114###ALTER TABLE product ADD FOREIGN KEY (PROD_ID) REFERENCES recipe(PROD_ID);
115#ALTER TABLE `orders` ADD PRIMARY KEY (ORD_ID);
116ALTER TABLE `orders` ADD FOREIGN KEY (CUS_ID) REFERENCES customer(CUS_ID);
117#ALTER TABLE line ADD PRIMARY KEY (LINE_ID);
118ALTER TABLE line ADD FOREIGN KEY (ORD_ID) REFERENCES `orders`(ORD_ID);
119ALTER TABLE line ADD FOREIGN KEY (PROD_ID) REFERENCES product(PROD_ID);
120ALTER TABLE line ADD FOREIGN KEY (EMP_ID) REFERENCES employee(EMP_ID);
121#ALTER TABLE employee ADD PRIMARY KEY (EMP_ID);
122ALTER TABLE employee ADD FOREIGN KEY (JOB_ID) REFERENCES jobtype(JOB_ID);
123#ALTER TABLE jobtype ADD PRIMARY KEY (JOB_ID);
124#ALTER TABLE customer ADD PRIMARY KEY (CUS_ID);