b0Tb9ve9

1-- MySQL Script generated by MySQL Workbench
2-- 12/07/16 14:51:02
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Table `klant`
16-- -----------------------------------------------------
17DROP TABLE IF EXISTS `klant` ;
18
19CREATE TABLE IF NOT EXISTS `klant` (
20  `klantID` INT NOT NULL,
21  `naam` VARCHAR(45) NOT NULL,
22  `telefoonnummer` VARCHAR(45) NOT NULL,
23  `postadres` VARCHAR(45) NOT NULL,
24  `emailadres` VARCHAR(45) NULL,
25  PRIMARY KEY (`klantID`))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `factuur`
31-- -----------------------------------------------------
32DROP TABLE IF EXISTS `factuur` ;
33
34CREATE TABLE IF NOT EXISTS `factuur` (
35  `factuurID` INT NOT NULL,
36  `totale_prijs` DOUBLE NOT NULL,
37  `datum` DATE NOT NULL,
38  `klantID` INT NOT NULL,
39  PRIMARY KEY (`factuurID`),
40  INDEX `fk_factuur_klant_idx` (`klantID` ASC),
41  CONSTRAINT `fk_factuur_klant`
42    FOREIGN KEY (`klantID`)
43    REFERENCES `klant` (`klantID`)
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION)
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `order`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `order` ;
53
54CREATE TABLE IF NOT EXISTS `order` (
55  `orderID` INT NOT NULL,
56  `orderstatus` VARCHAR(45) NOT NULL,
57  `datum` DATE NOT NULL,
58  `klantID` INT NOT NULL,
59  PRIMARY KEY (`orderID`),
60  INDEX `fk_order_klant1_idx` (`klantID` ASC),
61  CONSTRAINT `fk_order_klant1`
62    FOREIGN KEY (`klantID`)
63    REFERENCES `klant` (`klantID`)
64    ON DELETE NO ACTION
65    ON UPDATE NO ACTION)
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `robot`
71-- -----------------------------------------------------
72DROP TABLE IF EXISTS `robot` ;
73
74CREATE TABLE IF NOT EXISTS `robot` (
75  `robotID` INT NOT NULL,
76  `gang` INT NOT NULL,
77  PRIMARY KEY (`robotID`))
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `verzamelen`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `verzamelen` ;
85
86CREATE TABLE IF NOT EXISTS `verzamelen` (
87  `orderID` INT NOT NULL,
88  `robotID` INT NOT NULL,
89  PRIMARY KEY (`orderID`, `robotID`),
90  INDEX `fk_order_has_robot_robot1_idx` (`robotID` ASC),
91  INDEX `fk_order_has_robot_order1_idx` (`orderID` ASC),
92  CONSTRAINT `fk_order_has_robot_order1`
93    FOREIGN KEY (`orderID`)
94    REFERENCES `order` (`orderID`)
95    ON DELETE NO ACTION
96    ON UPDATE NO ACTION,
97  CONSTRAINT `fk_order_has_robot_robot1`
98    FOREIGN KEY (`robotID`)
99    REFERENCES `robot` (`robotID`)
100    ON DELETE NO ACTION
101    ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `product`
107-- -----------------------------------------------------
108DROP TABLE IF EXISTS `product` ;
109
110CREATE TABLE IF NOT EXISTS `product` (
111  `productID` INT NOT NULL,
112  `naam` VARCHAR(45) NOT NULL,
113  `prijs` DOUBLE NOT NULL,
114  PRIMARY KEY (`productID`))
115ENGINE = InnoDB;
116
117
118-- -----------------------------------------------------
119-- Table `bestelling`
120-- -----------------------------------------------------
121DROP TABLE IF EXISTS `bestelling` ;
122
123CREATE TABLE IF NOT EXISTS `bestelling` (
124  `orderID` INT NULL,
125  `productID` INT NULL,
126  PRIMARY KEY (`orderID`, `productID`),
127  INDEX `fk_order_has_product_product1_idx` (`productID` ASC),
128  INDEX `fk_order_has_product_order1_idx` (`orderID` ASC),
129  CONSTRAINT `fk_order_has_product_order1`
130    FOREIGN KEY (`orderID`)
131    REFERENCES `order` (`orderID`)
132    ON DELETE NO ACTION
133    ON UPDATE NO ACTION,
134  CONSTRAINT `fk_order_has_product_product1`
135    FOREIGN KEY (`productID`)
136    REFERENCES `product` (`productID`)
137    ON DELETE NO ACTION
138    ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `leverancier`
144-- -----------------------------------------------------
145DROP TABLE IF EXISTS `leverancier` ;
146
147CREATE TABLE IF NOT EXISTS `leverancier` (
148  `kvknummer` INT NOT NULL,
149  `emailadres` VARCHAR(45) NULL,
150  `adres` VARCHAR(45) NOT NULL,
151  `contactpersoon` VARCHAR(45) NOT NULL,
152  `telefoonnummer` VARCHAR(30) NOT NULL,
153  PRIMARY KEY (`kvknummer`))
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `gekocht_bij`
159-- -----------------------------------------------------
160DROP TABLE IF EXISTS `gekocht_bij` ;
161
162CREATE TABLE IF NOT EXISTS `gekocht_bij` (
163  `productID` INT NULL,
164  `kvknummer` INT NULL,
165  PRIMARY KEY (`productID`, `kvknummer`),
166  INDEX `fk_product_has_leverancier_leverancier1_idx` (`kvknummer` ASC),
167  INDEX `fk_product_has_leverancier_product1_idx` (`productID` ASC),
168  CONSTRAINT `fk_product_has_leverancier_product1`
169    FOREIGN KEY (`productID`)
170    REFERENCES `product` (`productID`)
171    ON DELETE NO ACTION
172    ON UPDATE NO ACTION,
173  CONSTRAINT `fk_product_has_leverancier_leverancier1`
174    FOREIGN KEY (`kvknummer`)
175    REFERENCES `leverancier` (`kvknummer`)
176    ON DELETE NO ACTION
177    ON UPDATE NO ACTION)
178ENGINE = InnoDB;
179
180
181-- -----------------------------------------------------
182-- Table `leveren`
183-- -----------------------------------------------------
184DROP TABLE IF EXISTS `leveren` ;
185
186CREATE TABLE IF NOT EXISTS `leveren` (
187  `kvknummer` INT NOT NULL,
188  `productID` INT NOT NULL,
189  `prijs` DOUBLE NOT NULL,
190  PRIMARY KEY (`kvknummer`, `productID`),
191  INDEX `fk_leverancier_has_product_product1_idx` (`productID` ASC),
192  INDEX `fk_leverancier_has_product_leverancier1_idx` (`kvknummer` ASC),
193  CONSTRAINT `fk_leverancier_has_product_leverancier1`
194    FOREIGN KEY (`kvknummer`)
195    REFERENCES `leverancier` (`kvknummer`)
196    ON DELETE NO ACTION
197    ON UPDATE NO ACTION,
198  CONSTRAINT `fk_leverancier_has_product_product1`
199    FOREIGN KEY (`productID`)
200    REFERENCES `product` (`productID`)
201    ON DELETE NO ACTION
202    ON UPDATE NO ACTION)
203ENGINE = InnoDB;
204
205
206SET SQL_MODE=@OLD_SQL_MODE;
207SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
208SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
209
210-- -----------------------------------------------------
211-- Data for table `klant`
212-- -----------------------------------------------------
213START TRANSACTION;
214INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES (01, 'Pieter Pater', '06-78945232', 'Paterstraat 69', 'pieterpater@msn.com');
215INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES (02, 'Peter Pan', '06-45786213', 'Panterstraat 20', 'peterpannetje@gmail.com');
216INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES (03, 'Robin van Persie', '06-12367996', 'Tijgerlaan 11', 'rvp4life@live.nl');
217INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES (04, 'Arjen Robben', '06-14782235', 'Olifantendorp 87', NULL);
218INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES (05, 'Klaas Taart', '06-47851236', 'Taartenstraat 23', 'klaasappeltaart@hotmail.com');
219
220COMMIT;
221
222
223-- -----------------------------------------------------
224-- Data for table `factuur`
225-- -----------------------------------------------------
226START TRANSACTION;
227INSERT INTO `factuur` (`factuurID`, `totale_prijs`, `datum`, `klantID`) VALUES (00000001, 569.69, '2016-12-07', 01);
228INSERT INTO `factuur` (`factuurID`, `totale_prijs`, `datum`, `klantID`) VALUES (00000002, 210.00, '2015-01-01', 02);
229INSERT INTO `factuur` (`factuurID`, `totale_prijs`, `datum`, `klantID`) VALUES (00000003, 177.00, '2016-05-08', 03);
230INSERT INTO `factuur` (`factuurID`, `totale_prijs`, `datum`, `klantID`) VALUES (00000004, 99.10, '2016-12-02', 04);
231INSERT INTO `factuur` (`factuurID`, `totale_prijs`, `datum`, `klantID`) VALUES (00000005, 20.20, '2016-01-09', 05);
232
233COMMIT;
234
235
236-- -----------------------------------------------------
237-- Data for table `order`
238-- -----------------------------------------------------
239START TRANSACTION;
240INSERT INTO `order` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES (000001, 'verzonden', '2016-11-15', 01);
241INSERT INTO `order` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES (000002, 'geleverd', '2016-11-04', 02);
242INSERT INTO `order` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES (000003, 'uitverkocht', '2016-12-04', 03);
243INSERT INTO `order` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES (000004, 'verzonden', '2016-12-05', 04);
244INSERT INTO `order` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES (000005, 'uitverkocht', '2016-11-29', 05);
245
246COMMIT;
247
248
249-- -----------------------------------------------------
250-- Data for table `robot`
251-- -----------------------------------------------------
252START TRANSACTION;
253INSERT INTO `robot` (`robotID`, `gang`) VALUES (001, 1);
254INSERT INTO `robot` (`robotID`, `gang`) VALUES (002, 2);
255INSERT INTO `robot` (`robotID`, `gang`) VALUES (003, 3);
256INSERT INTO `robot` (`robotID`, `gang`) VALUES (004, 4);
257INSERT INTO `robot` (`robotID`, `gang`) VALUES (005, 5);
258
259COMMIT;
260
261
262-- -----------------------------------------------------
263-- Data for table `verzamelen`
264-- -----------------------------------------------------
265START TRANSACTION;
266INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES (000001, 001);
267INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES (000002, 002);
268INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES (000003, 003);
269INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES (000004, 004);
270INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES (000005, 005);
271
272COMMIT;
273
274
275-- -----------------------------------------------------
276-- Data for table `product`
277-- -----------------------------------------------------
278START TRANSACTION;
279INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES (100058, 'paracetamol', 15.00);
280INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES (100049, 'vitamine c', 5.00);
281INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES (100064, 'melatonine', 45.00);
282INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES (100095, 'ritalin', 20.00);
283INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES (110005, 'rennie', 2.00);
284
285COMMIT;
286
287
288-- -----------------------------------------------------
289-- Data for table `bestelling`
290-- -----------------------------------------------------
291START TRANSACTION;
292INSERT INTO `bestelling` (`orderID`, `productID`) VALUES (000001, 100058);
293INSERT INTO `bestelling` (`orderID`, `productID`) VALUES (000002, 100049);
294INSERT INTO `bestelling` (`orderID`, `productID`) VALUES (000003, 100064);
295INSERT INTO `bestelling` (`orderID`, `productID`) VALUES (000004, 100095);
296INSERT INTO `bestelling` (`orderID`, `productID`) VALUES (000005, 110005);
297
298COMMIT;
299
300
301-- -----------------------------------------------------
302-- Data for table `leverancier`
303-- -----------------------------------------------------
304START TRANSACTION;
305INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES (54826351, 'dylanschaafstra@gmail.com', 'fransstraat 10', 'Dylan Schaafstra', '0318-654879');
306INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES (54826332, 'michielbuyserd@gmail.com', 'klaasstraat 10', 'Michiel Buyserd', '073-987456');
307INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES (54826400, 'wesleynoorlander@gmail.com', 'pjotrstraat 12', 'Wesley Noorlander', '042-587963');
308INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES (54826650, NULL, 'vincentstraat 15', 'Patrick Jansen', '020-4669876');
309INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES (54826701, 'klaasappeltaartje@gmail.com', 'klaaslaan 22', 'Klaas Taart', '010-4578642');
310
311COMMIT;
312
313
314-- -----------------------------------------------------
315-- Data for table `gekocht_bij`
316-- -----------------------------------------------------
317START TRANSACTION;
318INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES (100058, 54826351);
319INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES (100049, 54826332);
320INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES (100064, 54826400);
321INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES (100095, 54826650);
322INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES (110005, 54826701);
323
324COMMIT;
325
326
327-- -----------------------------------------------------
328-- Data for table `leveren`
329-- -----------------------------------------------------
330START TRANSACTION;
331INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES (54826351, 100058, 1.50);
332INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES (54826332, 100049, 3.00);
333INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES (54826400, 100064, 10.00);
334INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES (54826650, 100095, 12.50);
335INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES (54826701, 110005, 0.50);
336
337COMMIT;