jg9dSHke

1BEGIN TRANSACTION;
2CREATE TABLE IF NOT EXISTS `Werknemer` (
3	`Voornaam`	varchar ( 255 ) NOT NULL,
4	`WerknemerID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
5	`Achternaam`	varchar ( 255 ) NOT NULL,
6	`Leeftijd`	integer ( 10 )
7);
8INSERT INTO `Werknemer` (Voornaam,WerknemerID,Achternaam,Leeftijd) VALUES ('Teri',2,'Grant',31),
9 ('Lloyd',3,'Ware',26),
10 ('Jane',4,'Otero',21),
11 ('Joanne',5,'Meadows',19),
12 ('Pauline',6,'Stein',32),
13 ('Nathaniel',7,'Hall',18),
14 ('Mary',8,'Chambers',34),
15 ('David',9,'Smith',25),
16 ('Linda',10,'Martin',35),
17 ('Martha',11,'Prater',24),
18 ('Joseph',12,'Tarbutton',25),
19 ('Lindsay',13,'Her',25),
20 ('Olivia',14,'Ellerbee',34),
21 ('Glen',15,'Crass',25),
22 ('Wanda',16,'Peters',32),
23 ('Kristin',17,'Harris',35),
24 ('Molly',18,'Goodenow',32),
25 ('Merry',19,'Buchanan',30),
26 ('Lynne',20,'Fenelus',32),
27 ('Alice',21,'Mcmenamy',32);
28CREATE TABLE IF NOT EXISTS `Reviews` (
29	`GebruikerID`	integer ( 10 ) NOT NULL,
30	`ProductID`	integer ( 10 ) NOT NULL,
31	`Reviewtekst`	varchar ( 255 ) NOT NULL,
32	`Rating`	INTEGER NOT NULL,
33	FOREIGN KEY(`GebruikerID`) REFERENCES `Gebruiker`(`GebruikerID`),
34	FOREIGN KEY(`ProductID`) REFERENCES `Producten`(`ProductID`),
35	PRIMARY KEY(`GebruikerID`,`ProductID`)
36);
37INSERT INTO `Reviews` (GebruikerID,ProductID,Reviewtekst,Rating) VALUES (1,10,'dummy review user: 1 Product: 10',1),
38 (1,15,'dummy review user: 1 Product: 15',5),
39 (3,4,'dummy review user: 3 Product: 4',2),
40 (4,16,'dummy review user: 4 Product: 16',4),
41 (4,19,'dummy review user: 4 Product: 19',4),
42 (4,20,'dummy review user: 4 Product: 20',3),
43 (5,10,'dummy review user: 5 Product: 10',1),
44 (5,16,'dummy review user: 5 Product: 16',4),
45 (5,24,'dummy review user: 5 Product: 24',4),
46 (6,2,'dummy review user: 6 Product: 2',5),
47 (6,6,'dummy review user: 6 Product: 6',1),
48 (7,15,'dummy review user: 7 Product: 15',2),
49 (8,5,'dummy review user: 8 Product: 5',2),
50 (8,10,'dummy review user: 8 Product: 10',5),
51 (8,12,'dummy review user: 8 Product: 12',3),
52 (8,15,'dummy review user: 8 Product: 15',5),
53 (8,24,'dummy review user: 8 Product: 24',3),
54 (9,9,'dummy review user: 9 Product: 9',1),
55 (9,16,'dummy review user: 9 Product: 16',3),
56 (10,14,'dummy review user: 10 Product: 14',5),
57 (10,17,'dummy review user: 10 Product: 17',3),
58 (2,1,'dummy review user: 2 Product: 1',1),
59 (2,2,'dummy review user: 2 Product: 2',2),
60 (2,3,'dummy review user: 2 Product: 3',1),
61 (2,4,'dummy review user: 2 Product: 4',3),
62 (2,5,'dummy review user: 2 Product: 5',4),
63 (2,6,'dummy review user: 2 Product: 6',4),
64 (2,7,'dummy review user: 2 Product: 7',3),
65 (2,8,'dummy review user: 2 Product: 8',1),
66 (2,9,'dummy review user: 2 Product: 9',5),
67 (2,10,'dummy review user: 2 Product: 10',2),
68 (2,11,'dummy review user: 2 Product: 11',1),
69 (2,12,'dummy review user: 2 Product: 12',3),
70 (2,13,'dummy review user: 2 Product: 13',3),
71 (2,14,'dummy review user: 2 Product: 14',3),
72 (2,15,'dummy review user: 2 Product: 15',1),
73 (2,16,'dummy review user: 2 Product: 16',3),
74 (2,17,'dummy review user: 2 Product: 17',1),
75 (2,18,'dummy review user: 2 Product: 18',4),
76 (2,19,'dummy review user: 2 Product: 19',2),
77 (2,20,'dummy review user: 2 Product: 20',4),
78 (2,21,'dummy review user: 2 Product: 21',4),
79 (2,22,'dummy review user: 2 Product: 22',5),
80 (2,23,'dummy review user: 2 Product: 23',5),
81 (2,24,'dummy review user: 2 Product: 24',2);
82CREATE TABLE IF NOT EXISTS `Retourneren` (
83	`GebruikerID`	INTEGER NOT NULL,
84	`BestellingID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
85	FOREIGN KEY(`GebruikerID`) REFERENCES `Gebruiker`(`GebruikerID`)
86);
87INSERT INTO `Retourneren` (GebruikerID,BestellingID) VALUES (3,30),
88 (6,31),
89 (8,32),
90 (10,33);
91CREATE TABLE IF NOT EXISTS `Productopslag` (
92	`Distributiecentra-Naam`	varchar ( 255 ) NOT NULL,
93	`ProductID`	integer ( 10 ) NOT NULL,
94	`Stock`	integer ( 10 ) NOT NULL,
95	FOREIGN KEY(`ProductID`) REFERENCES `Producten`(`ProductID`),
96	FOREIGN KEY(`Distributiecentra-Naam`) REFERENCES `Distributecentra`(`Naam`),
97	PRIMARY KEY(`Distributiecentra-Naam`,`ProductID`)
98);
99INSERT INTO `Productopslag` (Distributiecentra-Naam,ProductID,Stock) VALUES ('Kerstman',1,85),
100 ('Kerstman',2,79),
101 ('Kerstman',3,64),
102 ('Kerstman',4,17),
103 ('Kerstman',5,54),
104 ('Kerstman',6,92),
105 ('Kerstman',7,3),
106 ('Kerstman',8,36),
107 ('Kerstman',9,11),
108 ('Kerstman',10,58),
109 ('Kerstman',11,20),
110 ('Kerstman',12,66),
111 ('Kerstman',13,15),
112 ('Kerstman',14,30),
113 ('Kerstman',15,91),
114 ('Kerstman',16,33),
115 ('Kerstman',17,90),
116 ('Kerstman',18,76),
117 ('Kerstman',19,52),
118 ('Kerstman',20,91),
119 ('Kerstman',21,28),
120 ('Kerstman',22,92),
121 ('Kerstman',23,13),
122 ('Kerstman',24,96),
123 ('Paashaas',1,60),
124 ('Paashaas',2,60),
125 ('Paashaas',3,100),
126 ('Paashaas',4,34),
127 ('Paashaas',5,5),
128 ('Paashaas',6,64),
129 ('Paashaas',7,48),
130 ('Paashaas',8,35),
131 ('Paashaas',9,97),
132 ('Paashaas',10,11),
133 ('Paashaas',11,47),
134 ('Paashaas',12,59),
135 ('Paashaas',13,8),
136 ('Paashaas',14,83),
137 ('Paashaas',15,74),
138 ('Paashaas',16,39),
139 ('Paashaas',17,43),
140 ('Paashaas',18,69),
141 ('Paashaas',19,18),
142 ('Paashaas',20,70),
143 ('Paashaas',21,17),
144 ('Paashaas',22,46),
145 ('Paashaas',23,61),
146 ('Paashaas',24,9),
147 ('Sinterklaas',1,27),
148 ('Sinterklaas',2,78),
149 ('Sinterklaas',3,88),
150 ('Sinterklaas',4,20),
151 ('Sinterklaas',5,53),
152 ('Sinterklaas',6,76),
153 ('Sinterklaas',7,17),
154 ('Sinterklaas',8,0),
155 ('Sinterklaas',9,44),
156 ('Sinterklaas',10,77),
157 ('Sinterklaas',11,80),
158 ('Sinterklaas',12,16),
159 ('Sinterklaas',13,43),
160 ('Sinterklaas',14,33),
161 ('Sinterklaas',15,45),
162 ('Sinterklaas',16,8),
163 ('Sinterklaas',17,46),
164 ('Sinterklaas',18,95),
165 ('Sinterklaas',19,13),
166 ('Sinterklaas',20,82),
167 ('Sinterklaas',21,82),
168 ('Sinterklaas',22,45),
169 ('Sinterklaas',23,6),
170 ('Sinterklaas',24,65);
171CREATE TABLE IF NOT EXISTS `Producten` (
172	`ProductID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
173	`Naam`	varchar ( 255 ) NOT NULL
174);
175INSERT INTO `Producten` (ProductID,Naam) VALUES (1,'Microsoft Word'),
176 (2,'Microsoft Excel'),
177 (3,'Microsoft Outlook'),
178 (4,'Microsoft Powerpoint'),
179 (5,'Microsoft OneNote'),
180 (6,'Microsoft Windows 10'),
181 (7,'RTX 2080TI'),
182 (8,'RTX 2080'),
183 (9,'RTX 2070'),
184 (10,'RTX 2060'),
185 (11,'RTX 1080TI'),
186 (12,'Half-Life 3'),
187 (13,'Dauntless'),
188 (14,'Borderlands 3'),
189 (15,'Minecraft'),
190 (16,'Lego'),
191 (17,'Barbie'),
192 (18,'Duplo'),
193 (19,'Kapla'),
194 (20,'Roos'),
195 (21,'Tulp'),
196 (22,'Cactus'),
197 (23,'Moss'),
198 (24,'Zonnebloem');
199CREATE TABLE IF NOT EXISTS `Productbestelling` (
200	`ProductID`	integer ( 10 ) NOT NULL,
201	`BestellingID`	integer ( 10 ) NOT NULL,
202	`Aantal`	integer ( 10 ) NOT NULL,
203	FOREIGN KEY(`ProductID`) REFERENCES `Producten`(`ProductID`),
204	FOREIGN KEY(`BestellingID`) REFERENCES `Retour-Bestelling`(`BestellingID`),
205	FOREIGN KEY(`BestellingID`) REFERENCES `Bestellingen`(`BestellingID`),
206	PRIMARY KEY(`ProductID`,`BestellingID`)
207);
208INSERT INTO `Productbestelling` (ProductID,BestellingID,Aantal) VALUES (5,1,3),
209 (8,2,3),
210 (13,3,2),
211 (13,4,2),
212 (12,5,1),
213 (19,5,1),
214 (8,7,2),
215 (16,11,2),
216 (12,12,1),
217 (5,13,2),
218 (6,14,2),
219 (13,14,1),
220 (12,15,1),
221 (7,17,3),
222 (1,18,3),
223 (11,18,2),
224 (18,18,1),
225 (22,18,3),
226 (8,19,2),
227 (18,19,2),
228 (11,22,1),
229 (14,23,2),
230 (22,24,2),
231 (7,25,3),
232 (10,25,2),
233 (17,25,2),
234 (19,25,3),
235 (12,26,3),
236 (5,27,2),
237 (21,27,1),
238 (22,27,1),
239 (20,28,1),
240 (21,28,3),
241 (8,29,3),
242 (13,9,2),
243 (15,10,2),
244 (14,16,3),
245 (12,20,3),
246 (20,20,2),
247 (15,21,2),
248 (14,8,2),
249 (12,6,3),
250 (19,6,3);
251CREATE TABLE IF NOT EXISTS `Leverancier-Locaties` (
252	`WerknemerID`	integer ( 10 ) NOT NULL,
253	`Distributiecentra-Naam`	varchar ( 255 ) NOT NULL,
254	FOREIGN KEY(`Distributiecentra-Naam`) REFERENCES `Distributecentra`(`Naam`),
255	FOREIGN KEY(`WerknemerID`) REFERENCES `Leverancier`(`WerknemerID`)
256);
257INSERT INTO `Leverancier-Locaties` (WerknemerID,Distributiecentra-Naam) VALUES (3,'Kerstman'),
258 (4,'Sinterklaas'),
259 (6,'Sinterklaas'),
260 (7,'Sinterklaas'),
261 (9,'Paashaas'),
262 (10,'Sinterklaas'),
263 (12,'Paashaas'),
264 (13,'Sinterklaas'),
265 (15,'Sinterklaas'),
266 (16,'Sinterklaas'),
267 (18,'Sinterklaas'),
268 (19,'Sinterklaas'),
269 (21,'Kerstman');
270CREATE TABLE IF NOT EXISTS `Leverancier` (
271	`WerknemerID`	integer ( 10 ) NOT NULL,
272	FOREIGN KEY(`WerknemerID`) REFERENCES `Werknemer`(`WerknemerID`),
273	PRIMARY KEY(`WerknemerID`)
274);
275INSERT INTO `Leverancier` (WerknemerID) VALUES (3),
276 (4),
277 (6),
278 (7),
279 (9),
280 (10),
281 (12),
282 (13),
283 (15),
284 (16),
285 (18),
286 (19),
287 (21);
288CREATE TABLE IF NOT EXISTS `Gebruiker` (
289	`Voornaam`	varchar ( 255 ) NOT NULL,
290	`GebruikerID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
291	`Achternaam`	varchar ( 255 ) NOT NULL,
292	`Leeftijd`	integer ( 10 )
293);
294INSERT INTO `Gebruiker` (Voornaam,GebruikerID,Achternaam,Leeftijd) VALUES ('Siem',1,'van den Tweel',18),
295 ('Maik',2,'van Esseveld',40),
296 ('Merijn',3,'Stiekema',12),
297 ('Sytse',4,'Backx',22),
298 ('Jerome',5,'Mohammed',25),
299 ('Robert',6,'Vleugels',35),
300 ('Wietse',7,'Yeetze',25),
301 ('Elon',8,'Musk',47),
302 ('Jelte',9,'van den Akker',25),
303 ('Alice',10,'van Velzen',88);
304CREATE TABLE IF NOT EXISTS `Distributeur` (
305	`WerknemerID`	integer ( 10 ) NOT NULL,
306	`Distributecentra-Naam`	varchar ( 255 ) NOT NULL,
307	FOREIGN KEY(`WerknemerID`) REFERENCES `Werknemer`(`WerknemerID`),
308	PRIMARY KEY(`WerknemerID`),
309	FOREIGN KEY(`Distributecentra-Naam`) REFERENCES `Distributecentra`(`Naam`)
310);
311INSERT INTO `Distributeur` (WerknemerID,Distributecentra-Naam) VALUES (2,'Sinterklaas'),
312 (5,'Sinterklaas'),
313 (8,'Paashaas'),
314 (11,'Paashaas'),
315 (14,'Kerstman'),
316 (17,'Sinterklaas'),
317 (20,'Kerstman');
318CREATE TABLE IF NOT EXISTS `Distributecentra` (
319	`Naam`	varchar ( 255 ) NOT NULL,
320	`Locatie`	varchar ( 255 ) NOT NULL,
321	PRIMARY KEY(`Naam`)
322);
323INSERT INTO `Distributecentra` (Naam,Locatie) VALUES ('Sinterklaas','Spanje'),
324 ('Kerstman','Noordpool'),
325 ('Paashaas','Het bos');
326CREATE TABLE IF NOT EXISTS `Categorie-Producten` (
327	`CategorieNaam`	varchar ( 255 ) NOT NULL,
328	`ProductID`	integer ( 10 ) NOT NULL,
329	PRIMARY KEY(`CategorieNaam`,`ProductID`),
330	FOREIGN KEY(`ProductID`) REFERENCES `Producten`(`ProductID`),
331	FOREIGN KEY(`CategorieNaam`) REFERENCES `Categorie`(`Naam`)
332);
333INSERT INTO `Categorie-Producten` (CategorieNaam,ProductID) VALUES ('Software',1),
334 ('Software',2),
335 ('Software',3),
336 ('Software',4),
337 ('Software',5),
338 ('Software',6),
339 ('Hardware',7),
340 ('Hardware',8),
341 ('Hardware',9),
342 ('Hardware',10),
343 ('Hardware',11),
344 ('Games',12),
345 ('Games',13),
346 ('Games',14),
347 ('Games',15),
348 ('Speelgoed',16),
349 ('Speelgoed',17),
350 ('Speelgoed',18),
351 ('Speelgoed',19),
352 ('Planten',20),
353 ('Planten',21),
354 ('Planten',22),
355 ('Planten',23),
356 ('Planten',24);
357CREATE TABLE IF NOT EXISTS `Categorie` (
358	`Naam`	varchar ( 255 ) NOT NULL,
359	PRIMARY KEY(`Naam`)
360);
361INSERT INTO `Categorie` (Naam) VALUES ('Software'),
362 ('Hardware'),
363 ('Games'),
364 ('Speelgoed'),
365 ('Planten');
366CREATE TABLE IF NOT EXISTS `Bestellingen` (
367	`BestellingID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
368	`GebruikerID`	integer ( 10 ) NOT NULL,
369	FOREIGN KEY(`GebruikerID`) REFERENCES `Gebruiker`(`GebruikerID`)
370);
371INSERT INTO `Bestellingen` (BestellingID,GebruikerID) VALUES (1,3),
372 (2,3),
373 (3,5),
374 (4,5),
375 (5,6),
376 (6,6),
377 (7,6),
378 (8,8),
379 (9,8),
380 (10,8),
381 (11,9),
382 (12,9),
383 (13,10),
384 (14,10),
385 (15,10),
386 (16,10),
387 (17,1),
388 (18,1),
389 (19,1),
390 (20,3),
391 (21,3),
392 (22,3),
393 (23,4),
394 (24,4),
395 (25,4),
396 (26,5),
397 (27,5),
398 (28,5),
399 (29,9);
400CREATE TABLE IF NOT EXISTS `Aanbiedingen` (
401	`CategorieID`	varchar ( 255 ) NOT NULL,
402	`Percentage`	integer ( 10 ) NOT NULL,
403	`Begindatum`	timestamp NOT NULL,
404	`Einddatum`	timestamp NOT NULL,
405	FOREIGN KEY(`CategorieID`) REFERENCES `Categorie`(`Naam`),
406	PRIMARY KEY(`CategorieID`)
407);
408INSERT INTO `Aanbiedingen` (CategorieID,Percentage,Begindatum,Einddatum) VALUES ('Games',10,'2019-04-01 00:00:00.000','2019-04-027 00:00:00.000'),
409 ('Speelgoed',10,'2019-03-01 00:00:00.000','2019-04-15 00:00:00.000'),
410 ('Software',25,'2019-01-01 00:00:00.000','2019-03-01 00:00:00.000');
411COMMIT;