· 7 years ago · Dec 02, 2018, 09:54 PM
1drop database if exists myDB;
2create database myDB;
3
4use myDB;
5
6create table medewerker (
7 mdwnr integer,
8 naam varchar(255),
9
10 constraint pk_medewerker
11 primary key (mdwnr)
12);
13
14create table verkoopregio (
15 regiocode varchar(5),
16 regioomschrijving varchar(128),
17 pcbegin integer,
18 pceinde integer,
19 regiomgr integer,
20
21 constraint pk_verkoopregio
22 primary key (regiocode),
23 constraint fk_verkoopregio_medewerker
24 foreign key (regiomgr)
25 references medewerker(mdwnr)
26);
27
28create table klant (
29 klantnr integer,
30 naam varchar(255),
31 postcodehuisnr varchar(12),
32 woonplaats varchar (255),
33
34 constraint pk_klant
35 primary key (klantnr)
36);
37
38create table bestelling (
39 bestelnr integer,
40 klantnr integer,
41 verkoper integer,
42 besteldatum date,
43 leverdatum date,
44 betaaldatum date,
45
46 constraint pk_bestelling
47 primary key (bestelnr),
48 constraint fk_bestelling_klant
49 foreign key (klantnr)
50 references klant(klantnr),
51 constraint fk_bestelling_medewerker
52 foreign key (verkoper)
53 references medewerker(mdwnr)
54);
55
56create table bestellingjournal (
57 id integer auto_increment,
58 journaldatum date,
59 dbuser varchar(64),
60 bestelnr integer,
61 verkoper integer,
62 besteldatum date,
63 leverdatum date,
64 betaaldatum date,
65
66 constraint pk_bestellingjournal
67 primary key (id),
68 constraint fk_bestellingjournal_bestelling
69 foreign key (bestelnr)
70 references bestelling(bestelnr)
71);
72
73create table artikelcategorie (
74 catcode varchar(32),
75 catomschrijving varchar(128),
76
77 constraint pk_artikelcategorie
78 primary key (catcode)
79);
80
81create table artikel (
82 artikelnr integer,
83 omschrijving varchar(255) not null,
84 catcode varchar(32),
85
86 constraint pk_artikel
87 primary key (artikelnr),
88 constraint fk_artikel_artikelcategory
89 foreign key (catcode)
90 references artikelcategorie(catcode)
91);
92
93create table artikelprijs (
94 artikelnr integer,
95 prijs decimal(8,2) not null,
96 begindatum date,
97 einddatum date not null default '2099-12-31',
98
99 constraint pk_artikelprijs
100 primary key (artikelnr, begindatum),
101 constraint fk_artikelprijs_artikel
102 foreign key (artikelnr)
103 references artikel(artikelnr)
104);
105
106create table prijsjournal (
107 id integer auto_increment,
108 artikelnr integer,
109 begindatum date,
110 modifier varchar(32),
111 changeDate date,
112
113 constraint pk_prijsjournal
114 primary key (id),
115 constraint fk_prijsjournal_artikelprijs
116 foreign key (artikelnr, begindatum)
117 references artikelprijs(artikelnr, begindatum)
118);
119
120create table bestelregel (
121 bestelnr integer,
122 artikelnr integer,
123 aantal integer,
124
125 constraint pk_bestelregel
126 primary key (bestelnr, artikelnr),
127 constraint fk_bestelregel_bestelling
128 foreign key (bestelnr)
129 references bestelling(bestelnr),
130 constraint fk_bestelregel_artikel
131 foreign key (artikelnr)
132 references artikel(artikelnr)
133);
134
135 create table weer (
136 datum date,
137 temp integer,
138 neerslag integer,
139
140 constraint pk_weer primary key (datum)
141);
142
143
144drop trigger if exists trg_bestelregel_ai;
145delimiter //
146create trigger trg_bestelregel_ai
147after insert on bestelregel
148for each row
149begin
150 declare nr_lines integer;
151 declare amt_order integer;
152 declare city varchar(255);
153
154 select woonplaats, count(*), sum(aantal * prijs)
155 into city, nr_lines, amt_order
156 from klant k, bestelling b, bestelregel r, artikel a, artikelprijs p
157 where k.klantnr = b.klantnr
158 and b.bestelnr = r.bestelnr
159 and r.artikelnr = a.artikelnr
160 and a.artikelnr = p.artikelnr
161 and besteldatum between begindatum and einddatum
162 and b.bestelnr = new.bestelnr
163 group by woonplaats;
164
165 /* max 4 bestelregels per bestelling */
166 if nr_lines > 4 then
167 signal sqlstate '45000' set message_text = 'too many lines';
168 end if;
169
170 /* maximaal 2000 euro per bestelling in Helmond */
171 if city like 'helmond' and amt_order > 2000 then
172 signal sqlstate '45000' set message_text = 'more than 2000 euro order for Helmond inhabitant';
173 end if;
174
175 /* maximaal 3000 euro per bestelling buiten Helmond */
176 if amt_order > 3000 then
177 signal sqlstate '45000' set message_text = 'max 3000 euro per order';
178 end if;
179
180end//
181
182/*
183 * contraint: verkoopregio's mogen elkaar niet overlappen
184 */
185drop trigger if exists trg_verkoopregio_bi//
186create trigger trg_verkoopregio_bi
187before insert on verkoopregio
188for each row
189begin
190 if exists(
191 select 1
192 from verkoopregio
193 where (
194 /* nieuwe regio overlapt aan een van beide uiteinde een andere regio ...*/
195 pcbegin between new.pcbegin and new.pceinde or
196 pceinde between new.pcbegin and new.pceinde or
197 /* ... of nieuwe regio valt binnen een bestaande regio */
198 new.pcbegin between pcbegin and pceinde or
199 new.pceinde between pcbegin and pceinde
200 )
201 ) then
202 signal sqlstate '45000' set message_text = 'chk_no_pc_overlap';
203 end if;
204end//
205
206/*
207 * contraint: luxe producten zijn duurder dan zuivelproducten
208 */
209
210drop trigger if exists trg_artikelprijs_ai//
211create trigger trg_artikelprijs_ai
212after insert on artikelprijs
213for each row
214begin
215 /*
216 * als de nieuwe prijs voor een zuivelartikel is, dan mag de prijs
217 * niet hoger zijn dan de laagste luxe prijs
218 */
219 if 'zuv' = (
220 select catcode
221 from artikel
222 where artikelnr = new.artikelnr
223 ) then if new.prijs > (
224 select min(prijs)
225 from artikelprijs p, artikel a
226 where p.artikelnr = a.artikelnr
227 and a.catcode = 'lux'
228 ) then
229 signal sqlstate '45000' set message_text = 'chk_luxury_more_expensive_than_diary';
230 end if;
231 end if;
232
233 /*
234 * als de nieuwe prijs voor een luxe artikel is, dan mag de prijs
235 * niet lager zijn dan de hoogste zuivel prijs
236 */
237 if 'lux' = (
238 select catcode
239 from artikel
240 where artikelnr = new.artikelnr
241 ) then if new.prijs < (
242 select max(prijs)
243 from artikelprijs p, artikel a
244 where p.artikelnr = a.artikelnr
245 and a.catcode = 'zuv'
246 ) then
247 signal sqlstate '45000' set message_text = 'chk_luxury_more_expensive_than_diary';
248 end if;
249 end if;
250end//
251
252/* log iedere bestelling in een bestel journal */
253drop trigger if exists trg_bestelling_ai//
254create trigger trg_bestelling_ai
255after insert on bestelling
256for each row
257begin
258 insert into bestellingjournal
259 values (
260 null,
261 curdate(),
262 user(),
263 new.bestelnr,
264 new.verkoper,
265 new.besteldatum,
266 new.leverdatum,
267 new.betaaldatum
268 );
269end//
270
271/* wijziging van een bestelling: originele vekoper niet wijzigbaar */
272drop trigger if exists trg_bestelling_bu//
273create trigger trg_bestelling_bu
274before update on bestelling
275for each row
276begin
277 insert into bestellingjournal
278 values (
279 null,
280 curdate(),
281 user(),
282 new.bestelnr,
283 new.verkoper,
284 new.besteldatum,
285 new.leverdatum,
286 new.betaaldatum
287 );
288
289 /* original sales person may not be changed */
290 set new.verkoper = old.verkoper;
291end//
292
293delimiter ;
294
295insert into medewerker values (801, 'Herman');
296insert into medewerker values (802, 'Klaas' );
297insert into medewerker values (803, 'Gerda' );
298insert into medewerker values (804, 'Bertha');
299insert into medewerker values (805, 'Harrie');
300insert into medewerker values (806, 'Karel' );
301
302insert into verkoopregio values ('REHV' , 'Eindhoven en zuidelijke randgemeenten', 5600, 5699, 802);
303insert into verkoopregio values ('RHMD' , 'Groot Helmond' , 5700, 5799, 803);
304insert into verkoopregio values ('GVGH' , 'Veghel en Erp en omgeving' , 5460, 5499, 806);
305
306insert into klant values (11, 'Jansen', '5645KZ1', 'Eindhoven');
307insert into klant values (12, 'Rademakers', '5625BT24', 'Eindhoven');
308insert into klant values (13, 'Heiden, van der','5701HT21', 'Helmond' );
309insert into klant values (14, 'Kraymans', '5472ZK103', 'Erp' );
310insert into klant values (15, 'Boer, de', '5463ZK1003', 'Veghel' );
311insert into klant values (16, 'Velzenmaker', '5625BT24', 'Eindhoven');
312insert into klant values (17, 'Jansen', '5701TT17', 'Helmond' );
313insert into klant values (18, 'Bocht', '5798ZK177', 'Laarbeek' );
314insert into klant values (19, 'Zuur', '5463KK103', 'Veghel' );
315insert into klant values (20, 'Bakermans', '5631PK10', 'Waalre' );
316
317insert into bestelling values (1001, 15, 801, '2011-08-04', '2011-08-09', '2011-08-13');
318insert into bestelling values (1002, 18, 803, '2011-08-07', '2011-08-11', '2011-08-31');
319insert into bestelling values (1003, 11, 802, '2011-08-11', '2011-08-11', NULL );
320insert into bestelling values (1004, 13, 805, '2011-08-15', '2011-08-20', '2011-09-21');
321insert into bestelling values (1005, 15, 801, '2011-08-17', '2011-08-21', '2011-08-31');
322insert into bestelling values (1006, 12, 805, '2011-08-17', '2011-08-18', '2011-08-31');
323insert into bestelling values (1007, 17, 804, '2011-08-25', '2011-08-30', '2011-09-11');
324insert into bestelling values (1008, 18, 804, '2011-08-27', '2011-08-27', '2011-10-10');
325insert into bestelling values (1009, 17, 801, '2011-08-27', '2011-08-28', '2011-09-21');
326insert into bestelling values (1010, 11, 801, '2011-08-27', '2011-09-03', '2011-09-15');
327insert into bestelling values (1011, 18, 805, '2011-08-29', '2011-09-01', '2011-09-05');
328insert into bestelling values (1012, 20, 805, '2011-08-31', '2011-09-06', '2011-09-21');
329insert into bestelling values (1013, 12, 802, '2011-09-03', '2011-09-14', '2011-10-01');
330insert into bestelling values (1014, 12, 801, '2011-09-03', '2011-09-16', NULL );
331insert into bestelling values (1015, 15, 804, '2011-09-09', '2011-09-10', '2011-10-03');
332insert into bestelling values (1016, 17, 801, '2011-09-15', '2011-09-16', '2011-09-30');
333insert into bestelling values (1017, 18, 801, '2011-09-15', NULL , NULL );
334insert into bestelling values (1018, 12, 805, '2011-09-16', '2011-09-16', '2011-09-30');
335insert into bestelling values (1019, 11, 802, '2011-09-16', '2011-09-17', '2011-09-29');
336insert into bestelling values (1020, 18, 803, '2011-09-16', '2011-09-17', '2011-09-25');
337insert into bestelling values (1021, 18, 805, '2011-09-18', NULL , NULL );
338insert into bestelling values (1022, 12, 805, '2011-09-21', '2011-09-30', NULL );
339insert into bestelling values (1023, 13, 804, '2011-09-23', '2011-09-26', '2011-09-30');
340insert into bestelling values (1024, 17, 803, '2011-09-23', '2011-09-30', NULL );
341insert into bestelling values (1025, 15, 801, '2011-09-26', '2011-09-27', '2011-10-11');
342insert into bestelling values (1026, 13, 803, '2011-09-28', '2011-09-30', NULL );
343insert into bestelling values (1027, 18, 801, '2011-09-29', '2011-09-29', '2011-10-11');
344insert into bestelling values (1028, 16, 805, '2011-09-29', '2011-09-29', '2011-09-30');
345
346insert into artikelcategorie values ('lux', 'luxe artikelen van de traitteur');
347insert into artikelcategorie values ('zuv', 'zuivelartikelen' );
348insert into artikelcategorie values ('bio', 'biologische artikelen' );
349
350insert into artikel values (401, 'Kaviaar', 'lux');
351insert into artikel values (402, 'Ganzenlever', 'lux');
352insert into artikel values (403, 'Zwezerik', 'lux');
353insert into artikel values (451, 'Volle Yoghurt', 'zuv');
354insert into artikel values (452, 'Magere Kwark', 'zuv');
355insert into artikel values (453, 'Vruchtenyoghurt Aardbei', 'zuv');
356insert into artikel values (454, 'Vruchtenyoghurt Mango', 'zuv');
357insert into artikel values (471, 'Sojamelk Naturel Biologisch', 'bio');
358insert into artikel values (472, 'Sojamelk Gezoet Biologisch', 'bio');
359insert into artikel values (473, 'Soja Vanillevla Biologisch', 'bio');
360insert into artikel values (474, 'Soja Chocolademelk Biologisch', 'bio');
361
362insert into artikelprijs values (401, 21.50, '2010-01-01', '2099-12-31');
363insert into artikelprijs values (402, 38.95, '2010-01-01', '2099-12-31');
364insert into artikelprijs values (403, 16.25, '2010-01-01', '2099-12-31');
365insert into artikelprijs values (451, 11.50, '2010-01-01', '2099-12-31');
366insert into artikelprijs values (452, 8.95 , '2010-01-01', '2099-12-31');
367insert into artikelprijs values (453, 7.50 , '2010-01-01', '2099-12-31');
368insert into artikelprijs values (454, 12.95, '2010-01-01', '2099-12-31');
369insert into artikelprijs values (471, 16.25, '2010-01-01', '2099-12-31');
370insert into artikelprijs values (472, 16.25, '2010-01-01', '2099-12-31');
371insert into artikelprijs values (473, 21.00, '2010-01-01', '2099-12-31');
372insert into artikelprijs values (474, 19.95, '2010-01-01', '2099-12-31');
373
374insert into bestelregel values (1001, 401, 20);
375insert into bestelregel values (1002, 401, 35);
376insert into bestelregel values (1002, 403, 20);
377insert into bestelregel values (1003, 401, 5 );
378insert into bestelregel values (1003, 403, 20);
379insert into bestelregel values (1003, 451, 35);
380insert into bestelregel values (1004, 453, 20);
381insert into bestelregel values (1005, 471, 5 );
382insert into bestelregel values (1006, 401, 20);
383insert into bestelregel values (1007, 454, 35);
384insert into bestelregel values (1008, 453, 20);
385insert into bestelregel values (1008, 454, 5 );
386insert into bestelregel values (1009, 402, 20);
387insert into bestelregel values (1010, 402, 35);
388insert into bestelregel values (1010, 403, 20);
389insert into bestelregel values (1010, 452, 5 );
390insert into bestelregel values (1010, 474, 20);
391insert into bestelregel values (1011, 402, 35);
392insert into bestelregel values (1012, 403, 20);
393insert into bestelregel values (1013, 472, 5 );
394insert into bestelregel values (1014, 401, 20);
395insert into bestelregel values (1015, 471, 35);
396insert into bestelregel values (1015, 473, 20);
397insert into bestelregel values (1015, 474, 5 );
398insert into bestelregel values (1016, 402, 3 );
399insert into bestelregel values (1017, 403, 20);
400insert into bestelregel values (1018, 452, 5 );
401insert into bestelregel values (1018, 474, 20);
402insert into bestelregel values (1018, 402, 35);
403insert into bestelregel values (1019, 403, 20);
404insert into bestelregel values (1020, 472, 5 );
405insert into bestelregel values (1021, 401, 20);
406insert into bestelregel values (1021, 471, 35);
407insert into bestelregel values (1022, 473, 20);
408insert into bestelregel values (1023, 474, 5 );
409insert into bestelregel values (1023, 402, 3 );
410insert into bestelregel values (1023, 403, 20);
411insert into bestelregel values (1024, 452, 5 );
412insert into bestelregel values (1024, 474, 20);
413insert into bestelregel values (1025, 402, 35);
414insert into bestelregel values (1026, 403, 20);
415insert into bestelregel values (1026, 472, 5 );
416insert into bestelregel values (1026, 401, 20);
417insert into bestelregel values (1026, 471, 35);
418insert into bestelregel values (1027, 403, 20);
419insert into bestelregel values (1028, 471, 15);
420
421insert into weer values ('2011-08-01', 20, 2);
422insert into weer values ('2011-08-02', 21, 3);
423insert into weer values ('2011-08-03', 24, 0);
424insert into weer values ('2011-08-04', 26, 0);
425insert into weer values ('2011-08-05', 26, 0);
426insert into weer values ('2011-08-06', 30, 0);
427insert into weer values ('2011-08-07', 31, 0);
428insert into weer values ('2011-08-08', 29, 0);
429insert into weer values ('2011-08-09', 30, 0);
430insert into weer values ('2011-08-10', 28, 0);
431insert into weer values ('2011-08-11', 28, 0);
432insert into weer values ('2011-08-12', 24, 0);
433insert into weer values ('2011-08-13', 20, 5);
434insert into weer values ('2011-08-14', 19, 7);
435insert into weer values ('2011-08-15', 20, 4);
436insert into weer values ('2011-08-16', 19, 4);
437insert into weer values ('2011-08-17', 18, 5);
438insert into weer values ('2011-08-18', 22, 2);
439insert into weer values ('2011-08-19', 23, 1);
440insert into weer values ('2011-08-20', 24, 0);
441insert into weer values ('2011-08-21', 24, 0);
442insert into weer values ('2011-08-22', 22, 1);
443insert into weer values ('2011-08-23', 21, 2);
444insert into weer values ('2011-08-24', 20, 3);
445insert into weer values ('2011-08-25', 21, 2);
446insert into weer values ('2011-08-26', 25, 0);
447insert into weer values ('2011-08-27', 27, 0);
448insert into weer values ('2011-08-28', 29, 0);
449insert into weer values ('2011-08-29', 31, 0);
450insert into weer values ('2011-08-30', 31, 0);
451insert into weer values ('2011-08-31', 30, 0);
452insert into weer values ('2011-09-01', 30, 7);
453insert into weer values ('2011-09-02', 26, 3);
454insert into weer values ('2011-09-03', 22, 4);
455insert into weer values ('2011-09-04', 20, 2);
456insert into weer values ('2011-09-05', 20, 3);
457insert into weer values ('2011-09-06', 21, 2);
458insert into weer values ('2011-09-07', 18, 4);
459insert into weer values ('2011-09-08', 17, 2);
460insert into weer values ('2011-09-09', 17, 5);
461insert into weer values ('2011-09-10', 19, 2);
462insert into weer values ('2011-09-11', 20, 2);
463insert into weer values ('2011-09-12', 22, 1);
464insert into weer values ('2011-09-13', 21, 2);
465insert into weer values ('2011-09-14', 24, 0);
466insert into weer values ('2011-09-15', 25, 0);
467insert into weer values ('2011-09-16', 27, 0);
468insert into weer values ('2011-09-17', 24, 0);
469insert into weer values ('2011-09-18', 23, 0);
470insert into weer values ('2011-09-19', 22, 0);
471insert into weer values ('2011-09-20', 20, 2);
472insert into weer values ('2011-09-21', 20, 3);
473insert into weer values ('2011-09-22', 18, 4);
474insert into weer values ('2011-09-23', 19, 2);
475insert into weer values ('2011-09-24', 18, 3);
476insert into weer values ('2011-09-25', 20, 1);
477insert into weer values ('2011-09-26', 21, 0);
478insert into weer values ('2011-09-27', 23, 0);
479insert into weer values ('2011-09-28', 20, 2);
480insert into weer values ('2011-09-29', 19, 2);
481insert into weer values ('2011-09-30', 19, 3);
482
483/* log elke wijziging van een artikelprijs */
484drop trigger if exists trg_artikelprijs_au//
485create trigger trg_artikelprijs_au
486after update on artikelprijs
487for each row
488begin
489 insert into prijsjournal values (null, new.artikelnr, new.begindatum, user(), curdate());
490end//
491delimiter ;
492
493/* historische prijsontwikkeling opzoeken */
494select j.*, p.einddatum, p.prijs
495from artikelprijs p left join prijsjournal j
496 on p.artikelnr = j.artikelnr
497 and p.begindatum = j.begindatum
498where artikelnr = :nummer
499;
500
501/*
502 * prijswijziging in artikel aanbrengen: we maken een stored procedure
503 * om het de applicatieontwikkelaar gemakkelijk te maken
504 */
505drop procedure if exists sp_set_new_price;
506delimiter //
507create procedure sp_set_new_price(in artno integer, in newprice decimal(8,2), in startdate date)
508begin
509 declare exit handler for sqlexception rollback;
510
511 /*
512 * stop beide statements in een transactie, zodat ook de
513 * update ge-rollbacked wordt als de insert mislukt
514 * doordat de trigger afgaat
515 * altijd loggen in prijsjournaal: we willen de poging wel terug kunnen zien
516 */
517 insert into prijsjournal values (null, artno, startdate, user(), curdate());
518
519 start transaction;
520 update artikelprijs
521 set einddatum = date_sub(startdate, interval 1 day)
522 where artikelnr = artno
523 and einddatum > startdate;
524
525 insert into artikelprijs values (artno, newprice, startdate, '2099-12-31');
526
527 commit;
528end//
529
530/* test de constraints */
531/* meer dan 2000 Euro voor een Helmondse bestelling */
532insert into bestelregel values (1004, 402, 50);
533
534/* max 4 bestelregels per bestelling */
535insert into bestelregel values (1026, 452, 2);
536
537/* changing prices */
538/* zonder een verandering is het prijsjournal nog leeg */
539select * from prijsjournal;
540
541/*
542 * met een stored procedure en een update trigger, komen er 2 inserts in de journal!
543 * Dat is natuurlijk niet de bedoeling, maar hier bedoeld ter illustratie
544 */
545call sp_set_new_price(474, 19.95, '2012-01-01');
546
547/* door de prijsverandering is er gelogd in de prijsjournal */
548select * from prijsjournal;
549
550/*
551 * verander de prijs van een zuivelproduct zodat het duurder wordt dan het
552 * goedkoopste luxe product. Trigger moet afgaan en transactie in stored procedure
553 * moet terug gedraaid worden
554 */
555call sp_set_new_price(454, 16.50, '2012-01-01');
556
557/* de insert in de artikelprijs tabel is terug gedraaid door de ge-rollbackte transactie */
558select * from artikelprijs;
559
560/* updating and tracking bestelling */
561select * from bestellingjournal;
562
563/* we zetten leverdatum en beataaldatum voor bestelling 1017. Dit wordt gelogd. */
564select *
565from bestelling
566where bestelnr = 1017;
567
568update bestelling
569 set leverdatum = '2011-09-23'
570 where bestelnr = 1017;
571
572update bestelling
573 set betaaldatum = curdate()
574 where bestelnr = 1017;
575
576select *
577from bestellingjournal
578where bestelnr = 1017;
579
580/* poging om de verkopernaam te wijzigen */
581update bestelling
582 set
583 betaaldatum = curdate(),
584 verkoper = 803
585 where bestelnr = 1014;
586
587/* potentieel frauduleuze handelingen opsporen */
588select j1.*
589from bestellingjournal j1, bestellingjournal j2
590where j1.bestelnr = j2.bestelnr
591and j1.verkoper <> j2.verkoper;