· 7 years ago · Nov 26, 2018, 11:02 AM
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 mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`factuur-status`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`factuur-status` (
21 `status` VARCHAR(45) NOT NULL,
22 PRIMARY KEY (`status`))
23ENGINE = InnoDB;
24
25
26-- -----------------------------------------------------
27-- Table `mydb`.`factuur`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `mydb`.`factuur` (
30 `idfactuur` INT NOT NULL AUTO_INCREMENT,
31 `status` VARCHAR(45) NOT NULL,
32 `datum` DATETIME NULL,
33 PRIMARY KEY (`idfactuur`),
34 INDEX `fk_factuur_factuur-status1_idx` (`status` ASC),
35 CONSTRAINT `fk_factuur_factuur-status1`
36 FOREIGN KEY (`status`)
37 REFERENCES `mydb`.`factuur-status` (`status`)
38 ON DELETE NO ACTION
39 ON UPDATE NO ACTION)
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `mydb`.`order-status`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `mydb`.`order-status` (
47 `status` VARCHAR(45) NOT NULL,
48 PRIMARY KEY (`status`))
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `mydb`.`functie`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mydb`.`functie` (
56 `naam` VARCHAR(20) NOT NULL,
57 `omschrijving` VARCHAR(75) NOT NULL,
58 PRIMARY KEY (`naam`))
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `mydb`.`medewerker`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `mydb`.`medewerker` (
66 `idmedewerker` INT NOT NULL AUTO_INCREMENT,
67 `voornaam` VARCHAR(45) NOT NULL,
68 `tussenvoegsel` VARCHAR(10) NULL,
69 `achternaam` VARCHAR(45) NOT NULL,
70 `functie_naam` VARCHAR(20) NOT NULL,
71 PRIMARY KEY (`idmedewerker`),
72 INDEX `fk_medewerker_functie1_idx` (`functie_naam` ASC),
73 CONSTRAINT `fk_medewerker_functie1`
74 FOREIGN KEY (`functie_naam`)
75 REFERENCES `mydb`.`functie` (`naam`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION)
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `mydb`.`klant`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `mydb`.`klant` (
85 `idklant` INT NOT NULL AUTO_INCREMENT,
86 `postcode` VARCHAR(6) NOT NULL,
87 `huisnummer` INT NOT NULL,
88 `jaaromzet` INT NOT NULL,
89 `contactpersoon` VARCHAR(45) NOT NULL,
90 `email` VARCHAR(45) NOT NULL,
91 `telefoonnummer` INT NOT NULL,
92 `bedrijfsnaam` VARCHAR(45) NOT NULL,
93 `klantinfo` VARCHAR(100) NULL,
94 `idmedewerker` INT NOT NULL,
95 PRIMARY KEY (`idklant`),
96 INDEX `fk_klant_medewerker1_idx` (`idmedewerker` ASC),
97 CONSTRAINT `fk_klant_medewerker1`
98 FOREIGN KEY (`idmedewerker`)
99 REFERENCES `mydb`.`medewerker` (`idmedewerker`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `mydb`.`bestelling`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `mydb`.`bestelling` (
109 `idbestelling` INT NOT NULL AUTO_INCREMENT,
110 `status` VARCHAR(45) NOT NULL,
111 `idfactuur` INT NOT NULL,
112 `idklant` INT NOT NULL,
113 `totaalprijs` INT NOT NULL,
114 `datum` DATETIME NOT NULL,
115 PRIMARY KEY (`idbestelling`),
116 INDEX `fk_bestelling_factuur1_idx` (`idfactuur` ASC),
117 INDEX `fk_bestelling_order-status1_idx` (`status` ASC),
118 INDEX `fk_bestelling_klant1_idx` (`idklant` ASC),
119 CONSTRAINT `fk_bestelling_factuur1`
120 FOREIGN KEY (`idfactuur`)
121 REFERENCES `mydb`.`factuur` (`idfactuur`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION,
124 CONSTRAINT `fk_bestelling_order-status1`
125 FOREIGN KEY (`status`)
126 REFERENCES `mydb`.`order-status` (`status`)
127 ON DELETE NO ACTION
128 ON UPDATE NO ACTION,
129 CONSTRAINT `fk_bestelling_klant1`
130 FOREIGN KEY (`idklant`)
131 REFERENCES `mydb`.`klant` (`idklant`)
132 ON DELETE NO ACTION
133 ON UPDATE NO ACTION)
134ENGINE = InnoDB;
135
136
137-- -----------------------------------------------------
138-- Table `mydb`.`verpakkingsoort`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `mydb`.`verpakkingsoort` (
141 `verpakkingsoort` VARCHAR(45) NOT NULL,
142 PRIMARY KEY (`verpakkingsoort`))
143ENGINE = InnoDB;
144
145
146-- -----------------------------------------------------
147-- Table `mydb`.`medicijn`
148-- -----------------------------------------------------
149CREATE TABLE IF NOT EXISTS `mydb`.`medicijn` (
150 `idmedicijn` INT NOT NULL AUTO_INCREMENT,
151 `verpakkingsoort` VARCHAR(45) NOT NULL,
152 `naam` VARCHAR(45) NOT NULL,
153 `omschrijving` VARCHAR(45) NOT NULL,
154 `prijs` DECIMAL NOT NULL,
155 `grootverpakking_inhoud` INT NULL,
156 `hoeveelheid_werkzame_stof` INT NOT NULL,
157 `minimale_bestelhoeveelheid` INT NOT NULL,
158 `gang` CHAR NOT NULL,
159 `schap` INT NOT NULL,
160 `minimale_voorraad` INT NOT NULL,
161 `huidige_voorraad` INT NOT NULL,
162 PRIMARY KEY (`idmedicijn`),
163 INDEX `fk_medicijn_verpakkingsoort1_idx` (`verpakkingsoort` ASC),
164 CONSTRAINT `fk_medicijn_verpakkingsoort1`
165 FOREIGN KEY (`verpakkingsoort`)
166 REFERENCES `mydb`.`verpakkingsoort` (`verpakkingsoort`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB;
170
171
172-- -----------------------------------------------------
173-- Table `mydb`.`robot`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `mydb`.`robot` (
176 `gang` CHAR NOT NULL,
177 `bezig` TINYINT NOT NULL,
178 PRIMARY KEY (`gang`))
179ENGINE = InnoDB;
180
181
182-- -----------------------------------------------------
183-- Table `mydb`.`deelorder`
184-- -----------------------------------------------------
185CREATE TABLE IF NOT EXISTS `mydb`.`deelorder` (
186 `iddeelorder` INT NOT NULL,
187 `gang` CHAR NOT NULL,
188 INDEX `fk_deelorder_robot1_idx` (`gang` ASC),
189 PRIMARY KEY (`iddeelorder`),
190 CONSTRAINT `fk_deelorder_robot1`
191 FOREIGN KEY (`gang`)
192 REFERENCES `mydb`.`robot` (`gang`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197
198-- -----------------------------------------------------
199-- Table `mydb`.`bestelregel`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `mydb`.`bestelregel` (
202 `idbestelling` INT NOT NULL,
203 `idmedicijn` INT NOT NULL,
204 `aantal` INT NOT NULL,
205 `huidige_prijs` DECIMAL NOT NULL,
206 `iddeelorder` INT NOT NULL,
207 PRIMARY KEY (`idbestelling`, `idmedicijn`),
208 INDEX `fk_bestelregel_bestelling_idx` (`idbestelling` ASC),
209 INDEX `fk_bestelregel_medicijn1_idx` (`idmedicijn` ASC),
210 INDEX `fk_bestelregel_deelorder1_idx` (`iddeelorder` ASC),
211 CONSTRAINT `fk_bestelregel_bestelling`
212 FOREIGN KEY (`idbestelling`)
213 REFERENCES `mydb`.`bestelling` (`idbestelling`)
214 ON DELETE NO ACTION
215 ON UPDATE NO ACTION,
216 CONSTRAINT `fk_bestelregel_medicijn1`
217 FOREIGN KEY (`idmedicijn`)
218 REFERENCES `mydb`.`medicijn` (`idmedicijn`)
219 ON DELETE NO ACTION
220 ON UPDATE NO ACTION,
221 CONSTRAINT `fk_bestelregel_deelorder1`
222 FOREIGN KEY (`iddeelorder`)
223 REFERENCES `mydb`.`deelorder` (`iddeelorder`)
224 ON DELETE NO ACTION
225 ON UPDATE NO ACTION)
226ENGINE = InnoDB;
227
228
229-- -----------------------------------------------------
230-- Table `mydb`.`fabrikant`
231-- -----------------------------------------------------
232CREATE TABLE IF NOT EXISTS `mydb`.`fabrikant` (
233 `idfabrikant` INT NOT NULL AUTO_INCREMENT,
234 `naam` VARCHAR(45) NOT NULL,
235 `postcode` VARCHAR(6) NOT NULL,
236 `huisnummer` INT NOT NULL,
237 `contactpersoon` VARCHAR(45) NOT NULL,
238 `internetadres` VARCHAR(45) NOT NULL,
239 `telefoonnummer` INT NOT NULL,
240 PRIMARY KEY (`idfabrikant`))
241ENGINE = InnoDB;
242
243
244-- -----------------------------------------------------
245-- Table `mydb`.`goedkoopste-fabrikant`
246-- -----------------------------------------------------
247CREATE TABLE IF NOT EXISTS `mydb`.`goedkoopste-fabrikant` (
248 `idmedicijn` INT NOT NULL,
249 `idfabrikant` INT NOT NULL,
250 `minimum-hoeveelheid` INT NOT NULL,
251 PRIMARY KEY (`idmedicijn`, `idfabrikant`),
252 INDEX `fk_goedkoopste-fabrikant_medicijn1_idx` (`idmedicijn` ASC),
253 INDEX `fk_goedkoopste-fabrikant_fabrikant1_idx` (`idfabrikant` ASC),
254 CONSTRAINT `fk_goedkoopste-fabrikant_medicijn1`
255 FOREIGN KEY (`idmedicijn`)
256 REFERENCES `mydb`.`medicijn` (`idmedicijn`)
257 ON DELETE NO ACTION
258 ON UPDATE NO ACTION,
259 CONSTRAINT `fk_goedkoopste-fabrikant_fabrikant1`
260 FOREIGN KEY (`idfabrikant`)
261 REFERENCES `mydb`.`fabrikant` (`idfabrikant`)
262 ON DELETE NO ACTION
263 ON UPDATE NO ACTION)
264ENGINE = InnoDB;
265
266
267-- -----------------------------------------------------
268-- Table `mydb`.`inkoop`
269-- -----------------------------------------------------
270CREATE TABLE IF NOT EXISTS `mydb`.`inkoop` (
271 `idinkoop` INT NOT NULL AUTO_INCREMENT,
272 `bestel_datum` DATETIME NOT NULL,
273 `ontvangst_datum` DATETIME NULL,
274 `idmedewerker` INT NOT NULL,
275 PRIMARY KEY (`idinkoop`),
276 INDEX `fk_inkoop_medewerker1_idx` (`idmedewerker` ASC),
277 CONSTRAINT `fk_inkoop_medewerker1`
278 FOREIGN KEY (`idmedewerker`)
279 REFERENCES `mydb`.`medewerker` (`idmedewerker`)
280 ON DELETE NO ACTION
281 ON UPDATE NO ACTION)
282ENGINE = InnoDB;
283
284
285-- -----------------------------------------------------
286-- Table `mydb`.`inkoopregel`
287-- -----------------------------------------------------
288CREATE TABLE IF NOT EXISTS `mydb`.`inkoopregel` (
289 `idmedicijn` INT NOT NULL,
290 `idfabrikant` INT NOT NULL,
291 `idinkoop` INT NOT NULL,
292 `prijs` DECIMAL NOT NULL,
293 `aantal` INT NOT NULL,
294 PRIMARY KEY (`idmedicijn`, `idfabrikant`, `idinkoop`),
295 INDEX `fk_inkoopregel_goedkoopste-fabrikant2_idx` (`idmedicijn` ASC, `idfabrikant` ASC),
296 INDEX `fk_inkoopregel_inkoop1_idx` (`idinkoop` ASC),
297 CONSTRAINT `fk_inkoopregel_goedkoopste-fabrikant2`
298 FOREIGN KEY (`idmedicijn` , `idfabrikant`)
299 REFERENCES `mydb`.`goedkoopste-fabrikant` (`idmedicijn` , `idfabrikant`)
300 ON DELETE NO ACTION
301 ON UPDATE NO ACTION,
302 CONSTRAINT `fk_inkoopregel_inkoop1`
303 FOREIGN KEY (`idinkoop`)
304 REFERENCES `mydb`.`inkoop` (`idinkoop`)
305 ON DELETE NO ACTION
306 ON UPDATE NO ACTION)
307ENGINE = InnoDB;
308
309
310-- -----------------------------------------------------
311-- Table `mydb`.`bak`
312-- -----------------------------------------------------
313CREATE TABLE IF NOT EXISTS `mydb`.`bak` (
314 `code` INT NOT NULL,
315 `leeg` TINYINT NOT NULL,
316 `iddeelorder` INT NULL,
317 PRIMARY KEY (`code`),
318 INDEX `fk_bak_deelorder1_idx` (`iddeelorder` ASC),
319 CONSTRAINT `fk_bak_deelorder1`
320 FOREIGN KEY (`iddeelorder`)
321 REFERENCES `mydb`.`deelorder` (`iddeelorder`)
322 ON DELETE NO ACTION
323 ON UPDATE NO ACTION)
324ENGINE = InnoDB;
325
326
327SET SQL_MODE=@OLD_SQL_MODE;
328SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
329SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;