R9arcxAx

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema Fog
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema Fog
13-- -----------------------------------------------------
14-- DROP SCHEMA IF EXISTS `Fog`;
15CREATE SCHEMA IF NOT EXISTS `Fog` DEFAULT CHARACTER SET utf8 ;
16USE `Fog` ;
17
18-- -----------------------------------------------------
19-- Table `Fog`.`category`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `Fog`.`category` (
22  `id_category` INT(11) NOT NULL,
23  `category_name` VARCHAR(45) NOT NULL,
24  PRIMARY KEY (`id_category`))
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `Fog`.`materials`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `Fog`.`materials` (
32  `id_material` INT(11) AUTO_INCREMENT,
33  `description` VARCHAR(200) NOT NULL,
34  `height` INT(11) NULL,
35  `width` INT(11) NULL,
36  `length` INT(11) NULL,
37  `price` DOUBLE NOT NULL,
38  `unit` VARCHAR(45) NOT NULL,
39  `id_category` INT(11) NOT NULL,
40  PRIMARY KEY (`id_material`),
41  INDEX `id_category_idx` (`id_category` ASC) ,
42  CONSTRAINT `id_category`
43    FOREIGN KEY (`id_category`)
44    REFERENCES `Fog`.`category` (`id_category`)
45    ON DELETE NO ACTION
46    ON UPDATE NO ACTION)
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `Fog`.`order_details_category`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `Fog`.`order_details_category` (
54  `id_order_details_category` INT(11) NOT NULL,
55  `details_category_name` VARCHAR(45) NOT NULL,
56  PRIMARY KEY (`id_order_details_category`))
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `Fog`.`customers`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `Fog`.`customers` (
64  `id_customer` INT(11) NOT NULL AUTO_INCREMENT,
65  `customer_name` VARCHAR(45) NOT NULL,
66  `phone` VARCHAR(45) NOT NULL,
67  `email` VARCHAR(45) NOT NULL,
68  PRIMARY KEY (`id_customer`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `Fog`.`roles`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `Fog`.`roles` (
76  `id_role` INT(11) NOT NULL,
77  `role` VARCHAR(45) NOT NULL,
78  PRIMARY KEY (`id_role`))
79ENGINE = InnoDB;
80
81-- -----------------------------------------------------
82-- Table `Fog`.`employees`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `Fog`.`employees` (
85  `id_employee` INT(11) NOT NULL AUTO_INCREMENT,
86  `name` VARCHAR(45) NOT NULL,
87  `id_role` INT(11) NOT NULL,
88  PRIMARY KEY (`id_employee`),
89  INDEX `id_role_idx` (`id_role` ASC) ,
90    FOREIGN KEY (`id_role`)
91    REFERENCES `Fog`.`roles` (`id_role`)
92    ON DELETE NO ACTION
93    ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table `Fog`.`orders`
99-- -----------------------------------------------------
100CREATE TABLE IF NOT EXISTS `Fog`.`orders` (
101  `id_order` INT (11) AUTO_INCREMENT,
102  `status` VARCHAR(45) NOT NULL,
103  `order_width` INT(11) NOT NULL,
104  `order_length` INT(11) NOT NULL,
105  `incline` INT(11) NOT NULL,
106  `id_customer` INT(11) NOT NULL,
107  `id_employee` INT(11) NOT NULL,
108  `price` DOUBLE NOT NULL,
109  PRIMARY KEY (`id_order`),
110  INDEX `id_customer_idx` (`id_customer` ASC) ,
111  INDEX `id_employee_idx` (`id_employee` ASC) ,
112  CONSTRAINT `id_customer`
113    FOREIGN KEY (`id_customer`)
114    REFERENCES `Fog`.`customers` (`id_customer`)
115    ON DELETE NO ACTION
116    ON UPDATE NO ACTION,
117  CONSTRAINT `id_employee`
118    FOREIGN KEY (`id_employee`)
119    REFERENCES `Fog`.`employees` (`id_employee`)
120    ON DELETE NO ACTION
121    ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `Fog`.`order_details`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `Fog`.`order_details` (
129  `id_order_detail` INT(11) NOT NULL,
130  `id_material` INT(11) NOT NULL,
131  `id_order` INT(11) NOT NULL,
132  `id_order_detail_category` INT(11) NOT NULL,
133  PRIMARY KEY (`id_order_detail`),
134  INDEX `id_material_idx` (`id_material` ASC) ,
135  INDEX `id_order_idx` (`id_order` ASC) ,
136  INDEX `id_order_detail_category_idx` (`id_order_detail_category` ASC) ,
137  CONSTRAINT `id_material`
138    FOREIGN KEY (`id_material`)
139    REFERENCES `Fog`.`materials` (`id_material`)
140    ON DELETE NO ACTION
141    ON UPDATE NO ACTION,
142  CONSTRAINT `id_order`
143    FOREIGN KEY (`id_order`)
144    REFERENCES `Fog`.`orders` (`id_order`)
145    ON DELETE NO ACTION
146    ON UPDATE NO ACTION,
147  CONSTRAINT `id_order_detail_category`
148    FOREIGN KEY (`id_order_detail_category`)
149    REFERENCES `Fog`.`order_details_category` (`id_order_details_category`)
150    ON DELETE NO ACTION
151    ON UPDATE NO ACTION)
152ENGINE = InnoDB;
153
154INSERT INTO `Fog`.`category` VALUES(1,'Skruer');
155INSERT INTO `Fog`.`category` VALUES(2,'Brædder');
156INSERT INTO `Fog`.`category` VALUES(3,'Beslag');
157INSERT INTO `Fog`.`category` VALUES(4,'Hængsler');
158INSERT INTO `Fog`.`category` VALUES(5,'Tagbeklædning');
159INSERT INTO `Fog`.`category` VALUES(6, 'Misc');
160INSERT INTO customers VALUES(1, 'john', '22548884','simonkruse2@gmail.com');
161INSERT INTO roles VALUES(1, 'admin');
162INSERT INTO roles VALUES(2, 'salesperson');
163INSERT INTO roles VALUES(3, 'picker');
164INSERT INTO employees VALUES(1, 'Carl', 1);
165
166
167INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('plastmo bundskruer 200 stk.', 0, 'Pakke',250,1);
168INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('hulbånd 1x20 mm. 10 mtr.','Rulle',750,1);
169INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('universal 190 mm. højre','Stk',400,1);
170INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('universal 190 mm. venstre', 'Stk', 800,1);
171INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 60 mm. skruer 200 stk.', 'Pakke',1000,1);
172INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,0 x 60 mm. beslagskruer 250 stk.', 0, 'Pakke',300,1);
173INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('bræddebolt 10 x 120 mm.', 'Stk',600,1);
174INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('firkantskiver 40 x 40 x 11 mm', 'Stk',150,1);
175INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 70 mm. skruer 400 stk.', 'Pakke',280,1);
176INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('4,5 x 50 mm. skruer 300 stk.', 'Pakke',350,1);
177
178INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x200 mm. trykimp. Brædt','Stk',300,2);
179INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x200 mm. trykimp. Brædt','Stk',500,2);
180INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x125 mm. trykimp. Brædt','Stk',180,2);
181INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('25x125 mm. trykimp. Brædt','Stk',980,2);
182INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',130,2);
183INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',380,2);
184INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('19x100 mm. trykimp. Brædt','Stk',410,2);
185INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('38x73 mm. Lægte ubh.','Stk',1000,2);
186INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x95 mm. Reglar ubh.','Stk',330,2);
187INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x95 mm. Reglar ubh.','Stk',200,2);
188INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',710,2);
189INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',990,2);
190INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('45x195 mm. spærtræ ubh.','Stk',190,2);
191INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('97x97 mm. trykimp. stolpe','Stk',710,2);
192
193INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Vinkelbeslag 35 mm','Stk',200,3);
194
195INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('stalddørsgreb 50 x 75 mm.','Sæt',150,6);
196INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('t hængsel 390 mm.','Stk',100,4);
197INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Plastmo Exolite blåtonet', 'Stk',905,5);
198INSERT INTO `materials` (`description`,`unit`,`price`,`id_category`)VALUES('Plastmo Exolite blåtonet.', 'Stk', 390,5);
199
200SET SQL_MODE=@OLD_SQL_MODE;
201SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
202SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;