· 6 years ago · Jun 17, 2019, 11:52 AM
1/*_________________________________________________*/
2/*===============| Create Database |===============*/
3/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
4
5USE master
6GO
7
8DROP DATABASE Eenmaalandermaal
9GO
10
11CREATE DATABASE Eenmaalandermaal
12GO
13
14USE Eenmaalandermaal
15
16/*_________________________________________________*/
17/*================| Create Script |================*/
18/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
19
20go
21
22
23--Tabellen creëren:
24CREATE TABLE Vraag (
25 vraagnummer INT NOT NULL,
26 vraagtekst VARCHAR(255) NOT NULL,
27 CONSTRAINT PK_Vraag PRIMARY KEY (vraagnummer)
28)
29go
30
31CREATE TABLE Gebruiker (
32 gebruikersnaam VARCHAR(50) NOT NULL,
33 voornaam VARCHAR(35) NOT NULL,
34 achternaam VARCHAR(35) NOT NULL,
35 adresregel1 VARCHAR(95) NOT NULL,
36 adresregel2 VARCHAR(95) NULL,
37 postcode VARCHAR(12) NOT NULL,
38 plaatsnaam VARCHAR(100) NOT NULL,
39 land VARCHAR(100) NOT NULL,
40 geboortedag DATE NOT NULL,
41 mailbox VARCHAR(255) NOT NULL,
42 wachtwoord CHAR(64) NOT NULL,
43 vraagnummer INT NOT NULL,
44 antwoordtekst VARCHAR(255) NOT NULL,
45 registratiedatum DATE DEFAULT GETDATE(),
46 land2 VARCHAR(100) NULL,
47 plaatsnaam2 VARCHAR(100) NULL,
48 postcode2 VARCHAR(12) NULL,
49 geslacht VARCHAR(5) NOT NULL DEFAULT 'Man',
50 CONSTRAINT PK_Gebruiker PRIMARY KEY (gebruikersnaam),
51 CONSTRAINT AK_Mailbox UNIQUE (mailbox),
52 CONSTRAINT FK_Vraag FOREIGN KEY (vraagnummer) REFERENCES Vraag(vraagnummer),
53 CONSTRAINT CHK_geslacht CHECK (geslacht = 'Man' OR geslacht = 'Vrouw'),
54 CONSTRAINT CHK_GebruikersnaamLengte CHECK (LEN(gebruikersnaam) <= 50) ,
55 CONSTRAINT CHK_MailboxLengte CHECK (LEN(mailbox) <= 50)
56)
57go
58
59CREATE TABLE Verkoper (
60 gebruikersnaam VARCHAR(50) NOT NULL,
61 banknaam VARCHAR(50) NULL,
62 rekeningnummer VARCHAR(34) NULL,
63 controleoptie VARCHAR(20) NOT NULL,
64 creditcardnummer DECIMAL(18,0) NULL
65 CONSTRAINT PK_Verkoper PRIMARY KEY (gebruikersnaam),
66 CONSTRAINT FK_Gebruiker_3 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
67 CONSTRAINT CHK_Controleoptie CHECK (controleoptie IN ('Creditcard', 'Post')),
68)
69go
70CREATE TABLE Voorwerp (
71 voorwerpnummer INT IDENTITY(1,1) NOT NULL,
72 titel TEXT NOT NULL,
73 beschrijving TEXT NOT NULL,
74 startprijs NUMERIC(10,2) NOT NULL,
75 betalingswijze VARCHAR(100) NOT NULL,
76 betalingsinstructie VARCHAR(255) NULL,
77 plaatsnaam VARCHAR(200) NOT NULL,
78 landnaam VARCHAR(200) NOT NULL,
79 looptijd TINYINT NOT NULL DEFAULT 7,
80 looptijdbegindag DATE DEFAULT GETDATE(),
81 looptijdbegintijdstip TIME DEFAULT CONVERT(time, CURRENT_TIMESTAMP),
82 verzendkosten NUMERIC(10,2) NULL,
83 verzendinstructies VARCHAR(255) NULL,
84 verkoper VARCHAR(50) NOT NULL,
85 koper VARCHAR(50) NULL,
86 looptijdeindedag AS DATEADD(day, looptijd, looptijdbegindag),
87 looptijdeindetijdstip TIME DEFAULT CONVERT(time, CURRENT_TIMESTAMP),
88 veiliggesloten BIT NOT NULL,
89 verkoopprijs NUMERIC(10,2) NULL,
90 CONSTRAINT PK_Voorwerp PRIMARY KEY (voorwerpnummer),
91 CONSTRAINT CHK_looptijd CHECK (looptijd IN (1,3,5,7,10)),
92 CONSTRAINT FK_Gebruiker_1 FOREIGN KEY (verkoper) REFERENCES Verkoper(gebruikersnaam),
93 CONSTRAINT FK_Gebruiker_2 FOREIGN KEY (koper) REFERENCES Gebruiker(gebruikersnaam),
94 CONSTRAINT CHK_startprijs CHECK (startprijs >=0)
95)
96go
97
98CREATE FUNCTION Maximaalvier ()
99RETURNS BIT
100AS
101BEGIN
102 IF EXISTS ( SELECT voorwerpnummer, COUNT(voorwerpnummer)
103 FROM Bestand
104 GROUP BY voorwerpnummer
105 HAVING COUNT(voorwerpnummer) > 4 )
106 RETURN 1
107 RETURN 0
108END
109go
110
111CREATE TABLE Bestand (
112 bestandsnaam VARCHAR(500) NOT NULL,
113 voorwerpnummer INT NOT NULL,
114 CONSTRAINT PK_Bestand PRIMARY KEY (bestandsnaam, voorwerpnummer),
115 CONSTRAINT CHK_Bestand CHECK (dbo.Maximaalvier() = 0),
116 CONSTRAINT FK_Voorwerp_1 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer)
117)
118go
119
120--Functie om ervoor te zorgen dat er geen kleiner of gelijk bod kan worden gemaakt dan de startprijs:
121CREATE FUNCTION Startprijs (@voorwerpnummer INT)
122RETURNS NUMERIC(10,2)
123AS
124BEGIN
125 DECLARE @startprijs NUMERIC(10,2)
126 SELECT @startprijs = startprijs
127 FROM Voorwerp
128 WHERE voorwerpnummer = @voorwerpnummer
129 RETURN @startprijs
130END
131go
132
133CREATE TABLE Bod (
134 voorwerpnummer INT NOT NULL,
135 bodbedrag NUMERIC(10,2) NOT NULL,
136 gebruikersnaam VARCHAR(50) NOT NULL,
137 boddag DATE NOT NULL,
138 bodtijdstip TIME NOT NULL,
139 CONSTRAINT PK_Bod PRIMARY KEY (voorwerpnummer, bodbedrag),
140 CONSTRAINT AK_Bod UNIQUE (gebruikersnaam, boddag, bodtijdstip),
141 CONSTRAINT FK_Voorwerp_2 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
142 CONSTRAINT FK_Gebruiker_5 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
143 --CONSTRAINT CHK_bodbedrag CHECK (dbo.Geenbod(voorwerpnummer) = 1),
144 CONSTRAINT CHK_hogerdanstart CHECK (bodbedrag > dbo.Startprijs(voorwerpnummer))
145)
146go
147
148CREATE TABLE Feedback (
149 voorwerpnummer INT NOT NULL,
150 gebruikersnaam VARCHAR(50) NOT NULL,
151 gebruikersoort VARCHAR(8) NOT NULL,
152 feedbacksoort CHAR(8) NOT NULL,
153 feedbackdag DATE NOT NULL,
154 feedbacktijd TIME NOT NULL,
155 feedback TEXT NULL,
156 CONSTRAINT PK_Feedback PRIMARY KEY (voorwerpnummer),
157 CONSTRAINT FK_Voorwerp_3 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
158 CONSTRAINT FK_Gebruiker_8 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
159 CONSTRAINT CHK_Gebruikersoort CHECK (gebruikersoort IN ('Verkoper', 'Koper')),
160 CONSTRAINT CHK_Feedbacksoort CHECK (feedbacksoort IN ('Negatief','Neutraal','Positief'))
161)
162go
163
164CREATE TABLE Gebruikertelefoon (
165 volgnr DECIMAL(2,0) NOT NULL,
166 gebruikersnaam VARCHAR(50) NOT NULL,
167 telefoonnummer NUMERIC(13,0) NOT NULL,
168 CONSTRAINT PK_Gebruikertelefoon PRIMARY KEY (volgnr,gebruikersnaam),
169 CONSTRAINT FK_Gebruiker_4 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
170)
171go
172
173CREATE TABLE Rubriek (
174 rubrieknummer INT NOT NULL,
175 rubrieknaam VARCHAR(50) NOT NULL,
176 subrubriekvan INT NULL,
177 voorwerpCount INT NULL,
178 CONSTRAINT PK_Rubriek PRIMARY KEY (rubrieknummer),
179 CONSTRAINT AK_Rubriek UNIQUE (rubrieknaam, subrubriekvan),
180 CONSTRAINT FK_Rubriek_1 FOREIGN KEY (subrubriekvan) REFERENCES Rubriek(rubrieknummer)
181)
182go
183
184CREATE TABLE Voorwerprubriek (
185 voorwerpnummer INT NOT NULL,
186 rubrieknummer INT NOT NULL,
187 CONSTRAINT PK_Voorwerprubriek PRIMARY KEY (voorwerpnummer, rubrieknummer),
188 CONSTRAINT FK_Voorwerp_4 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
189 CONSTRAINT FK_Rubriek_2 FOREIGN KEY (rubrieknummer) REFERENCES Rubriek(rubrieknummer)
190)
191
192CREATE TABLE PrecalculatedInfo (
193 gebruikersHoeveelheid INT NULL,
194 activeVoorwerpHoeveelheid INT NULL,
195 veilingVoltooid INT NULL,
196 aantalAfgelopen INT NULL,
197 Gemiddeldeprijsveiling NUMERIC(10,2) NULL
198)
199
200GO
201INSERT INTO PrecalculatedInfo VALUES (
202 NULL,NULL,NULL,NULL,NULL
203)
204
205GO
206
207CREATE TRIGGER CHECKVOORGEBRUIKERS ON Gebruiker
208FOR INSERT,UPDATE,DELETE
209AS
210BEGIN
211 UPDATE PrecalculatedInfo
212 SET gebruikersHoeveelheid = (SELECT COUNT(gebruikersnaam) FROM Gebruiker)
213END
214
215GO
216
217CREATE TRIGGER CHECKVOORVOORWERPEN ON Voorwerp
218FOR INSERT,UPDATE,DELETE
219AS
220BEGIN
221 UPDATE PrecalculatedInfo
222 SET activeVoorwerpHoeveelheid = (SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 0)
223END
224
225GO
226
227CREATE TRIGGER CHECKVOORDONEVEILINGEN ON Voorwerp
228FOR INSERT,UPDATE,DELETE
229AS
230BEGIN
231 UPDATE PrecalculatedInfo
232 SET veilingVoltooid = (
233 SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 1
234 )
235END
236
237GO
238
239UPDATE PrecalculatedInfo
240 SET gebruikersHoeveelheid = (SELECT COUNT(gebruikersnaam) FROM Gebruiker)
241
242UPDATE PrecalculatedInfo
243 SET activeVoorwerpHoeveelheid = (SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 0)
244
245UPDATE PrecalculatedInfo
246 SET veilingVoltooid = (
247 SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 1
248 )
249
250 UPDATE PrecalculatedInfo
251 SET aantalAfgelopen = (
252 SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE DATEDIFF(hour, (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME)), GETDATE()) < 10 AND veiliggesloten = 0
253 )
254
255GO
256
257CREATE TRIGGER CHECKVOORVEILINGENDONE ON Voorwerp
258FOR INSERT,UPDATE,DELETE
259AS
260BEGIN
261 IF EXISTS (
262 SELECT voorwerpnummer FROM Voorwerp WHERE CURRENT_TIMESTAMP - (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME)) < 10
263 )
264 BEGIN
265 UPDATE PrecalculatedInfo
266 SET aantalAfgelopen = (
267 SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE DATEDIFF(DAY, CURRENT_TIMESTAMP , (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME))) < 10 AND veiliggesloten = 1
268 )
269 END
270END
271
272GO
273
274CREATE TABLE RegistratieCode (
275 gebruikersnaam VARCHAR(50) NOT NULL,
276 code VARCHAR(8) NOT NULL,
277
278 CONSTRAINT PK_GebruikersNaam_1 PRIMARY KEY (gebruikersnaam),
279 CONSTRAINT FK_GebruikersNaam_2 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
280)
281
282CREATE TABLE VergetenWWCode (
283 gebruikersnaam VARCHAR(50) NOT NULL,
284 code VARCHAR(8) NOT NULL,
285
286 CONSTRAINT PK_GebruikersNaam_2 PRIMARY KEY (gebruikersnaam),
287 CONSTRAINT FK_GebruikersNaam_3 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
288)
289
290CREATE TABLE VerBannenGebruikers (
291 gebruikersnaam VARCHAR(50) NOT NULL,
292 VerbannenTot DATETIME NOT NULL,
293 VerbannenDatum DATETIME DEFAULT GETDATE(),
294
295 CONSTRAINT PK_GebruikersNaam_3 PRIMARY KEY (gebruikersnaam),
296 CONSTRAINT FK_GebruikersNaam_4 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
297)
298
299CREATE TABLE GedeactiveerdeGebruikers (
300 gebruikersnaam VARCHAR(50) NOT NULL,
301 Deactivatiedatum DATETIME DEFAULT GETDATE(),
302
303 CONSTRAINT PK_GebruikersNaam_4 PRIMARY KEY (gebruikersnaam),
304 CONSTRAINT FK_GebruikersNaam_5 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
305)
306
307CREATE TABLE Beheerders (
308 gebruikersnaam VARCHAR(50) NOT NULL,
309 beheerdersinds DATE DEFAULT GETDATE(),
310
311 CONSTRAINT PK_Beheerders PRIMARY KEY (gebruikersnaam),
312 CONSTRAINT FK_Gebruiker_6 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
313)
314
315GO
316
317CREATE TRIGGER GEMIDDELDEPRIJSVOLTOOID ON Voorwerp
318FOR INSERT, UPDATE
319AS
320BEGIN
321 DECLARE @aantalvoltooid INT
322 SELECT @aantalvoltooid = veilingVoltooid FROM PrecalculatedInfo
323 DECLARE @totaalprijs NUMERIC(38,2)
324 SELECT @totaalprijs = SUM(verkoopprijs) FROM Voorwerp WHERE veiliggesloten = 1
325 DECLARE @resultaat NUMERIC(10,2)
326 SET @resultaat = (@totaalprijs/@aantalvoltooid)
327
328 UPDATE PrecalculatedInfo
329 SET Gemiddeldeprijsveiling = @resultaat
330END
331go
332
333CREATE TABLE FeedbackCode (
334 mailbox VARCHAR(255) NOT NULL,
335 voorwerpnummer INT NOT NULL,
336 code CHAR(6) NOT NULL,
337 CONSTRAINT PK_FeedbackCode PRIMARY KEY (mailbox, voorwerpnummer),
338 CONSTRAINT FK_Gebruiker_7 FOREIGN KEY (mailbox) REFERENCES Gebruiker(mailbox),
339 CONSTRAINT FK_Voorwerp_5 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer)
340)
341go
342
343ALTER TABLE Gebruiker ADD CONSTRAINT CHK_Lengtegebruikersnaam CHECK (LEN(gebruikersnaam) <= 50)
344ALTER TABLE Gebruiker ADD CONSTRAINT CHK_Minimumgebruiekrsnaam CHECK (LEN(gebruikersnaam) >= 3)
345
346
347
348/*_________________________________________________*/
349/*==============| Stored Procedures |==============*/
350/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
351
352GO
353
354CREATE PROCEDURE spVoegUserToe
355 --Userinfo
356 @gebruikersnaam VARCHAR(100),
357 @voornaam VARCHAR(35),
358 @achternaam VARCHAR(35),
359 @geboortedag DATE,
360 @geslacht VARCHAR(5),
361 @telefoonnummer NUMERIC(13,0),
362
363 --adresinfo
364 @adresregel1 VARCHAR(95),
365 @postcode VARCHAR(12),
366 @plaatsnaam VARCHAR(100),
367 @land VARCHAR(100),
368
369 --Overige securityinfo
370 @mailbox VARCHAR(64),
371 @wachtwoord CHAR(64),
372 @vraag VARCHAR(255),
373 @antwoordtekst VARCHAR(255),
374 @RegistratieCode VARCHAR(8),
375
376 --Optioneel
377 @adresregel2 VARCHAR(95) = NULL,
378 @plaatsnaam2 VARCHAR(100) = NULL,
379 @postcode2 VARCHAR(12) = NULL,
380 @land2 VARCHAR(100) = NULL,
381 @telefoonnummer2 NUMERIC(13,0) = NULL
382AS
383BEGIN
384 DECLARE @vraagnummer DECIMAL(3,0)
385 IF EXISTS (
386 SELECT vraagtekst FROM Vraag WHERE vraagtekst = @Vraag
387 )
388 BEGIN
389 SET @vraagnummer = (SELECT vraagnummer FROM Vraag WHERE vraagtekst = @Vraag)
390 END
391 ELSE
392 BEGIN
393 SET @vraagnummer = (SELECT COUNT(vraagnummer) FROM Vraag) + 1
394
395 INSERT INTO Vraag VALUES
396 (@vraagnummer, @vraag)
397 END
398
399 IF EXISTS (
400 SELECT mailbox FROM Gebruiker WHERE mailbox = @mailbox
401 )
402 BEGIN
403 RAISERROR('Email is al geregistreerd!', 5,1)
404 RETURN
405 END
406
407 INSERT INTO Gebruiker(gebruikersnaam, voornaam, achternaam, adresregel1, adresregel2, postcode, plaatsnaam, land, geboortedag, mailbox, wachtwoord, vraagnummer, antwoordtekst, registratiedatum, plaatsnaam2, postcode2, geslacht, land2) VALUES
408 (@gebruikersnaam, @voornaam, @achternaam, @adresregel1, @adresregel2, @postcode, @plaatsnaam, @land, @geboortedag, @mailbox, @wachtwoord, @vraagnummer, @antwoordtekst, DEFAULT, @plaatsnaam2, @postcode2, @geslacht, @land2)
409
410 INSERT INTO RegistratieCode VALUES (
411 @gebruikersnaam, @RegistratieCode
412 )
413
414 INSERT INTO Gebruikertelefoon VALUES (
415 1, @gebruikersnaam, @telefoonnummer
416 )
417
418 IF @telefoonnummer2 IS NOT NULL
419 BEGIN
420 INSERT INTO Gebruikertelefoon VALUES (
421 2, @gebruikersnaam, @telefoonnummer2
422 )
423 END
424
425END
426
427GO
428
429CREATE PROCEDURE spVoegCategorieToe
430 --alle benodigde info
431 @rubriekNaam VARCHAR(50),
432
433 --Optioneel
434 @parentRubriekNr INT = NULL,
435 @parentRubriek VARCHAR(50) = NULL
436AS
437BEGIN
438 DECLARE @ParentNummer INT
439 DECLARE @RubriekNUMMER INT
440 IF @parentRubriekNr IS NULL
441 BEGIN
442 SET @RubriekNUMMER = (SELECT COUNT(rubrieknummer) FROM Rubriek) + 1
443 IF @parentRubriek IS NOT NULL
444 BEGIN
445 SET @ParentNummer = (SELECT rubrieknummer FROM Rubriek WHERE rubrieknaam = @rubriekNaam)
446 END
447 END
448
449 INSERT INTO Rubriek VALUES
450 (@RubriekNUMMER, @rubriekNaam, @parentRubriek, NULL)
451END
452
453GO
454
455CREATE PROCEDURE spVoegVoorwerpToe
456 --Benodigde info
457 @productnaam TEXT,
458 @beschrijving TEXT,
459 @startprijs VARCHAR(12),
460 @betalingswijze VARCHAR(100),
461 @rubrieknummer INT,
462
463 @verkoper VARCHAR(100),
464
465 --Optioneel
466 @looptijd TINYINT = 7,
467 @verzendkosten VARCHAR(12) = NULL,
468 @verzendinstructies VARCHAR(255) = NULL,
469 @betalingsinstructie VARCHAR(255) = NULL,
470 @looptijdbegintijdstip TIME = DEFAULT,
471 @begindag DATE = DEFAULT,
472 @gesloten BIT = 0
473
474AS
475BEGIN
476 DECLARE @voorwerpnummer INT
477
478 DECLARE @plaatsnaam VARCHAR(100)
479 DECLARE @landnaam VARCHAR(100)
480
481 SET @plaatsnaam = (
482 SELECT plaatsnaam FROM Gebruiker WHERE gebruikersnaam = @verkoper
483 )
484
485 SET @landnaam = (
486 SELECT land FROM Gebruiker WHERE gebruikersnaam = @verkoper
487 )
488
489 INSERT INTO Voorwerp (titel, beschrijving, startprijs, betalingswijze, betalingsinstructie, plaatsnaam, landnaam, verkoper, veiliggesloten, looptijdbegintijdstip, looptijd, verzendkosten, verzendinstructies)
490 VALUES (
491 @productnaam, @beschrijving, CAST(@startprijs AS NUMERIC(10,2)), @betalingswijze, @betalingsinstructie, @plaatsnaam, @landnaam, @verkoper, @gesloten, CONVERT(time, CURRENT_TIMESTAMP), @looptijd, CAST(@verzendkosten AS NUMERIC(10,2)), @verzendinstructies
492 )
493
494 SET @voorwerpnummer = (SELECT TOP 1 voorwerpnummer FROM Voorwerp ORDER BY voorwerpnummer DESC)
495
496 INSERT INTO Voorwerprubriek
497 VALUES (
498 @voorwerpnummer, @rubrieknummer
499 )
500END
501
502GO
503
504CREATE PROCEDURE spVoegBestandToe
505--benodigde info
506 @voorwerpnummer INT,
507
508--Optioneel
509 @Bestand1 VARCHAR(500) = NULL,
510 @Bestand2 VARCHAR(500) = NULL,
511 @Bestand3 VARCHAR(500) = NULL,
512 @Bestand4 VARCHAR(500) = NULL
513
514AS
515BEGIN
516 IF @Bestand1 IS NOT NULL
517 BEGIN
518 IF NOT EXISTS (
519 SELECT @Bestand1 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand1
520 )
521 BEGIN
522 INSERT INTO Bestand
523 VALUES (@Bestand1, @voorwerpnummer)
524 END
525 END
526
527 IF @Bestand2 IS NOT NULL
528 BEGIN
529 IF NOT EXISTS (
530 SELECT @Bestand2 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand2
531 )
532 BEGIN
533 INSERT INTO Bestand
534 VALUES (@Bestand2, @voorwerpnummer)
535 END
536 END
537
538 IF @Bestand3 IS NOT NULL
539 BEGIN
540 IF NOT EXISTS (
541 SELECT @Bestand3 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand3
542 )
543 BEGIN
544 INSERT INTO Bestand
545 VALUES (@Bestand3, @voorwerpnummer)
546 END
547 END
548
549 IF @Bestand4 IS NOT NULL
550 BEGIN
551 IF NOT EXISTS (
552 SELECT @Bestand4 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand4
553 )
554 BEGIN
555 INSERT INTO Bestand
556 VALUES (@Bestand4, @voorwerpnummer)
557 END
558 END
559
560END
561
562GO
563
564CREATE PROCEDURE spNieuwBod
565 @voorwerpnummer INT,
566 @bodbedrag VARCHAR(12),
567 @gebruikersnaam VARCHAR(100),
568
569 --Optioneel
570 @boddag DATE = NULL,
571 @bodtijdstip TIME = NULL
572AS
573BEGIN
574 IF @boddag IS NULL
575 BEGIN
576 SET @boddag = GETDATE()
577 END
578
579 IF @bodtijdstip IS NULL
580 BEGIN
581 SET @bodtijdstip = CONVERT(time, CURRENT_TIMESTAMP)
582 END
583
584 IF NOT EXISTS (
585 SELECT voorwerpnummer FROM Bod WHERE Bod.bodbedrag > @bodbedrag AND Bod.voorwerpnummer = @voorwerpnummer
586 )
587 BEGIN
588 INSERT INTO Bod VALUES(
589 @voorwerpnummer, CAST(@bodbedrag AS NUMERIC(10,2)), @gebruikersnaam, @boddag, @bodtijdstip
590 )
591 END
592END
593
594GO
595
596CREATE PROCEDURE spNieuweVerkoper
597 @gebruikersnaam VARCHAR(20),
598 @controleoptie VARCHAR(20),
599
600 --Optioneel, rekeningnummer of creditcardnummer moet != NULL.
601 @banknaam VARCHAR(50) = NULL,
602 @rekeningnummer VARCHAR(34) = NULL,
603 @creditcardnummer DECIMAL(18,0) = NULL
604AS
605BEGIN
606 IF NOT EXISTS (
607 SELECT gebruikersnaam FROM Verkoper WHERE Verkoper.gebruikersnaam = @gebruikersnaam
608 )
609 BEGIN
610 INSERT INTO Verkoper VALUES (
611 @gebruikersnaam, @banknaam, @rekeningnummer, @controleoptie, @creditcardnummer
612 )
613 END
614END
615
616GO
617
618CREATE PROCEDURE spNieuweFeedback
619 @voorwerpnummer INT,
620 @gebruikersnaam VARCHAR(100),
621 @feedbacksoort CHAR(8),
622 @feedback TEXT,
623
624 --Automatisch bepaald
625 @soortgebruiker VARCHAR(8) = NULL,
626 @feedbackdag DATE = NULL,
627 @feedbacktijd TIME = NULL
628AS
629BEGIN
630 IF @feedbackdag IS NULL
631 BEGIN
632 SET @feedbackdag = CONVERT(DATE, GETDATE())
633 END
634
635 IF @feedbacktijd IS NULL
636 BEGIN
637 SET @feedbacktijd = CONVERT(TIME, CURRENT_TIMESTAMP)
638 END
639
640 IF @soortgebruiker IS NULL
641 BEGIN
642 IF @gebruikersnaam IN (SELECT verkoper FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer)
643 BEGIN
644 SET @soortgebruiker = 'Verkoper'
645 END
646 IF @gebruikersnaam IN (SELECT koper FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer)
647 BEGIN
648 SET @soortgebruiker = 'Koper'
649 END
650 END
651
652 INSERT INTO Feedback(
653 voorwerpnummer, gebruikersnaam, gebruikersoort, feedbacksoort, feedbackdag, feedbacktijd, feedback
654 ) VALUES (
655 @voorwerpnummer, @gebruikersnaam, @soortgebruiker, @feedbacksoort, @feedbackdag, @feedbacktijd, @Feedback
656 )
657END
658GO
659
660CREATE PROCEDURE spUpdateThings
661AS
662BEGIN
663 UPDATE Voorwerp
664 SET veiliggesloten = 1
665 WHERE Voorwerp.looptijdeindedag < GETDATE() AND Voorwerp.looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) OR Voorwerp.looptijdeindedag < DATEADD(day, -1, GETDATE())
666END
667
668GO
669
670CREATE PROCEDURE spUpdateAllVoorwerpen
671AS
672BEGIN
673 UPDATE Voorwerp
674 SET looptijdbegindag = GETDATE()
675 WHERE looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) AND looptijdeindedag < GETDATE()
676
677 UPDATE Voorwerp
678 SET Veiliggesloten = 0
679 WHERE Veiliggesloten = 1
680
681 ALTER TABLE Voorwerp
682 DROP COLUMN looptijdeindedag
683
684 ALTER TABLE Voorwerp
685 ADD looptijdeindedag AS DATEADD(day, looptijd, looptijdbegindag)
686
687END
688GO
689
690CREATE PROCEDURE spDeleteVeiling
691 @voorwerpnummer INT
692AS
693BEGIN
694 IF EXISTS (
695 SELECT voorwerpnummer FROM Bestand WHERE voorwerpnummer = @voorwerpnummer
696 )
697 BEGIN
698 DELETE FROM Bestand WHERE voorwerpnummer = @voorwerpnummer
699 END
700
701 IF EXISTS (
702 SELECT voorwerpnummer FROM Bod WHERE voorwerpnummer = @voorwerpnummer
703 )
704 BEGIN
705 DELETE FROM Bod WHERE voorwerpnummer = @voorwerpnummer
706 END
707
708 IF EXISTS (
709 SELECT voorwerpnummer FROM Voorwerprubriek WHERE voorwerpnummer = @voorwerpnummer
710 )
711 BEGIN
712 DELETE FROM Voorwerprubriek WHERE voorwerpnummer = @voorwerpnummer
713 END
714
715 IF EXISTS (
716 SELECT voorwerpnummer FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
717 )
718 BEGIN
719 DELETE FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
720 END
721END
722
723GO
724
725CREATE PROCEDURE spVoegBeheerderToe
726 @gebruikersnaam VARCHAR(100)
727AS
728BEGIN
729 INSERT INTO Beheerders VALUES(
730 @gebruikersnaam, GETDATE()
731 )
732END
733
734GO
735
736CREATE PROCEDURE spEditGebruikersNaam
737 @gebruikersnaam VARCHAR(100),
738 @nieuwegebruikersnaam VARCHAR(100)
739AS
740BEGIN
741 UPDATE Gebruiker
742 SET gebruikersnaam = @nieuwegebruikersnaam
743 WHERE gebruikersnaam = @gebruikersnaam
744
745 UPDATE Voorwerp
746 SET verkoper = @nieuwegebruikersnaam
747 WHERE verkoper = @gebruikersnaam
748
749 UPDATE Voorwerp
750 SET koper = @nieuwegebruikersnaam
751 WHERE koper = @gebruikersnaam
752
753 UPDATE Bod
754 SET gebruikersnaam = @nieuwegebruikersnaam
755 WHERE gebruikersnaam = @gebruikersnaam
756END
757
758GO
759
760CREATE PROCEDURE spDeleteBod
761 @voorwerpnummer INT,
762 @bodbedrag NUMERIC(10,2)
763AS
764BEGIN
765 DELETE FROM Bod
766 WHERE voorwerpnummer = @voorwerpnummer AND bodbedrag = @bodbedrag
767END
768
769GO
770
771CREATE PROCEDURE spDeleteUser
772 @gebruikersnaam VARCHAR(20)
773AS
774BEGIN
775
776 IF EXISTS (
777 SELECT code FROM RegistratieCode WHERE gebruikersnaam = @gebruikersnaam
778 )
779 BEGIN
780 DELETE FROM RegistratieCode WHERE gebruikersnaam = @gebruikersnaam
781 END
782
783 DELETE FROM Gebruikertelefoon WHERE gebruikersnaam = @gebruikersnaam
784
785 IF EXISTS (
786 SELECT gebruikersnaam FROM Verkoper WHERE gebruikersnaam = @gebruikersnaam
787 )
788 BEGIN
789 DELETE FROM Verkoper WHERE gebruikersnaam = @gebruikersnaam
790 END
791
792 IF EXISTS (
793 SELECT verkoper FROM Voorwerp WHERE verkoper = @gebruikersnaam
794 )
795 BEGIN
796 DELETE FROM Voorwerp WHERE verkoper = @gebruikersnaam
797 END
798
799 DELETE FROM Gebruiker WHERE gebruikersnaam = @gebruikersnaam
800END
801
802/*__________________________________________________*/
803/*===================| Triggers |===================*/
804/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
805
806GO
807
808CREATE TRIGGER CHECKVERHOGING ON Bod
809FOR INSERT
810AS
811BEGIN
812 DECLARE @verkoopprijs NUMERIC(10,2)
813 DECLARE @bodbedrag NUMERIC(10,2)
814 DECLARE @voorwerpnummer INT
815
816 IF (SELECT bodbedrag FROM DELETED) != (SELECT bodbedrag FROM INSERTED)
817 BEGIN
818
819 SET @voorwerpnummer = (
820 SELECT TOP 1 voorwerpnummer FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
821 )
822 SET @verkoopprijs = (
823 SELECT TOP 1 verkoopprijs FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
824 )
825 SET @bodbedrag = (
826 SELECT TOP 1 bodbedrag FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
827 )
828
829 IF (@verkoopprijs >= 1 AND @verkoopprijs < 49.98 AND @bodbedrag < (@verkoopprijs + 0.5))
830 BEGIN
831 RAISERROR ('Uw bieding moet minimaal �0,50 meer zijn dat de huidige hoogste bieding.', 5, 1)
832 ROLLBACK
833 END
834 IF (@verkoopprijs >= 49.99 AND @verkoopprijs <=500 AND @bodbedrag < (@verkoopprijs + 1))
835 BEGIN
836 RAISERROR ('Uw bieding moet minimaal �1,- meer zijn dat de huidige hoogste bieding.', 5, 1)
837 ROLLBACK
838 END
839 IF (@verkoopprijs >= 500 AND @verkoopprijs <1000 AND @bodbedrag < (@verkoopprijs + 5))
840 BEGIN
841 RAISERROR ('Uw bieding moet minimaal �5,- meer zijn dat de huidige hoogste bieding.', 5, 1)
842 ROLLBACK
843 END
844 IF (@verkoopprijs >= 1000 AND @verkoopprijs < 5000 AND @bodbedrag < (@verkoopprijs + 10))
845 BEGIN
846 RAISERROR ('Uw bieding moet minimaal �10,- meer zijn dat de huidige hoogste bieding.', 5, 1)
847 ROLLBACK
848 END
849 IF (@verkoopprijs >= 5000 AND @bodbedrag < (@verkoopprijs + 50))
850 BEGIN
851 RAISERROR ('Uw bieding moet minimaal �1,- meer zijn dat de huidige hoogste bieding.', 5, 1)
852 ROLLBACK
853 END
854 END
855END
856
857GO
858
859CREATE TRIGGER CHECKFORWINNER ON Bod
860FOR UPDATE, DELETE
861AS
862BEGIN
863 UPDATE Voorwerp
864 SET koper = (
865 SELECT TOP 1 gebruikersnaam FROM Bod WHERE Bod.voorwerpnummer = Voorwerp.voorwerpnummer ORDER BY bodbedrag DESC
866 )
867END
868
869GO
870
871CREATE TRIGGER CHECKFORTIMEROVER ON Bod
872FOR INSERT
873AS
874BEGIN
875 IF EXISTS ((SELECT voorwerpnummer FROM Voorwerp WHERE (Voorwerp.looptijdeindedag < GETDATE() AND Voorwerp.looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) OR Voorwerp.looptijdeindedag < DATEADD(day, -1, GETDATE())) AND voorwerpnummer = (
876 SELECT TOP 1 voorwerpnummer FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
877 )))
878 BEGIN
879 RAISERROR ('KAN NIET BIEDEN OP AFGELOPEN VEILING!', 5, 1)
880 ROLLBACK
881 END
882END
883
884GO
885
886CREATE TRIGGER GETVOORWERPNUMMERRUBRIEK ON Voorwerprubriek
887FOR INSERT,DELETE
888AS
889BEGIN
890 UPDATE Rubriek
891 SET voorwerpCount = (
892 SELECT COUNT(voorwerpnummer) FROM Voorwerprubriek WHERE rubrieknummer = Rubriek.rubrieknummer
893 )
894END
895
896GO
897
898CREATE TRIGGER CHECKFORCREDITORBANK ON Verkoper
899FOR INSERT,UPDATE
900AS
901BEGIN
902 IF EXISTS (
903 SELECT rekeningnummer FROM Verkoper WHERE rekeningnummer IS NULL AND creditcardnummer IS NULL)
904 BEGIN
905 RAISERROR ('Rekening of creditcardnummer must contain data!',5,1)
906 ROLLBACK
907 END
908END
909
910
911GO
912
913CREATE TRIGGER CHECKFORLOOPTIJDEINDETIJDSTIP ON Voorwerp
914AFTER INSERT,UPDATE
915AS
916BEGIN
917 IF EXISTS (
918 SELECT looptijdeindetijdstip FROM Voorwerp WHERE looptijdeindetijdstip = looptijdbegintijdstip
919 )
920 BEGIN
921 UPDATE Voorwerp
922 SET looptijdeindetijdstip = looptijdbegintijdstip
923 END
924END
925
926GO
927
928CREATE TRIGGER CHECKFORBODINTEGRITEIT ON Bod
929FOR INSERT,UPDATE
930AS
931BEGIN
932 IF EXISTS (
933 SELECT gebruikersnaam, voorwerpnummer, bodbedrag FROM Bod WHERE voorwerpnummer IN (SELECT Bod.voorwerpnummer FROM Voorwerp, Bod WHERE Bod.gebruikersnaam = Voorwerp.verkoper AND Voorwerp.voorwerpnummer = Bod.voorwerpnummer)
934 )
935 BEGIN
936 RAISERROR ('Cannot bid on your own Auction', 5,1)
937 ROLLBACK
938 END
939END
940
941GO
942
943CREATE TRIGGER UPDATEVERKOOPPRIJS ON Bod
944FOR INSERT,UPDATE,DELETE
945AS
946BEGIN
947 UPDATE Voorwerp
948 SET verkoopprijs = (
949 SELECT MAX(bodbedrag) FROM Bod WHERE Bod.voorwerpnummer = Voorwerp.voorwerpnummer
950 )
951
952 UPDATE Voorwerp
953 SET verkoopprijs = startprijs
954 WHERE verkoopprijs IS NULL AND veiliggesloten = 1
955END
956
957GO
958
959CREATE TRIGGER UPDATESTARTPRIJS ON Voorwerp
960FOR INSERT
961AS
962BEGIN
963 UPDATE Voorwerp
964 SET verkoopprijs = startprijs
965 WHERE verkoopprijs IS NULL
966
967END
968
969/*__________________________________________________*/
970/*===================| Cascades |===================*/
971/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
972
973ALTER TABLE Beheerders
974drop CONSTRAINT FK_Gebruiker_6;
975
976ALTER TABLE Beheerders
977ADD CONSTRAINT FK_Gebruiker_6
978 FOREIGN KEY (gebruikersnaam)
979 REFERENCES Gebruiker(gebruikersnaam)
980 ON DELETE CASCADE ON UPDATE CASCADE;
981
982
983ALTER TABLE Verkoper
984drop CONSTRAINT FK_Gebruiker_3;
985
986ALTER TABLE Verkoper
987ADD CONSTRAINT FK_Gebruiker_3
988 FOREIGN KEY (gebruikersnaam)
989 REFERENCES Gebruiker(gebruikersnaam)
990 ON DELETE CASCADE ON UPDATE CASCADE;
991
992
993
994ALTER TABLE Bod
995drop CONSTRAINT FK_Gebruiker_5;
996
997ALTER TABLE Bod
998ADD CONSTRAINT FK_Gebruiker_5
999 FOREIGN KEY (gebruikersnaam)
1000 REFERENCES Gebruiker(gebruikersnaam)
1001 ON DELETE CASCADE ON UPDATE CASCADE;
1002
1003ALTER TABLE Gebruikertelefoon
1004drop CONSTRAINT FK_Gebruiker_4;
1005
1006ALTER TABLE Gebruikertelefoon
1007ADD CONSTRAINT FK_Gebruiker_4
1008 FOREIGN KEY (gebruikersnaam)
1009 REFERENCES Gebruiker(gebruikersnaam)
1010 ON DELETE CASCADE ON UPDATE CASCADE;
1011
1012ALTER TABLE Verkoper
1013drop CONSTRAINT FK_Gebruiker_3;
1014
1015ALTER TABLE Verkoper
1016ADD CONSTRAINT FK_Gebruiker_3
1017 FOREIGN KEY (gebruikersnaam)
1018 REFERENCES Gebruiker(gebruikersnaam)
1019
1020ALTER TABLE FeedbackCode
1021drop CONSTRAINT FK_Voorwerp_5;
1022
1023ALTER TABLE FeedbackCode
1024ADD CONSTRAINT FK_Voorwerp_5
1025 FOREIGN KEY (voorwerpnummer)
1026 REFERENCES Voorwerp(voorwerpnummer)
1027 ON DELETE CASCADE ON UPDATE CASCADE;
1028
1029ALTER TABLE FeedbackCode
1030drop CONSTRAINT FK_Gebruiker_7;
1031
1032ALTER TABLE FeedbackCode
1033ADD CONSTRAINT FK_Gebruiker_7
1034 FOREIGN KEY (mailbox)
1035 REFERENCES Gebruiker(mailbox)
1036 ON DELETE CASCADE ON UPDATE CASCADE;
1037
1038ALTER TABLE Feedback
1039DROP CONSTRAINT FK_Gebruiker_8
1040
1041ALTER TABLE Feedback
1042ADD CONSTRAINT FK_Gebruiker_8
1043 FOREIGN KEY (gebruikersnaam)
1044 REFERENCES Gebruiker(gebruikersnaam)
1045 ON DELETE CASCADE ON UPDATE CASCADE;
1046
1047
1048GO
1049
1050CREATE FUNCTION dbo.Biedenopeigenveiling (@gebruikersnaam VARCHAR(100), @voorwerpnummer INT)
1051RETURNS BIT
1052AS
1053BEGIN
1054 IF NOT EXISTS (SELECT voorwerpnummer, verkoper
1055 FROM Voorwerp
1056 WHERE voorwerpnummer = @voorwerpnummer
1057 AND verkoper = @gebruikersnaam)
1058 RETURN 1
1059 RETURN 0
1060END
1061go
1062
1063ALTER TABLE Bod ADD CONSTRAINT CHK_Biedenopeigenveiling CHECK (dbo.Biedenopeigenveiling(gebruikersnaam, voorwerpnummer) = 1)
1064
1065
1066/*_________________________________________________*/
1067/*==========| Meegeleverd adminaccount |===========*/
1068/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/
1069
1070/*=================| Login Creds |==================/
1071Gebruikersnaam: TestAdmin
1072Email: TestAdmin@han.nl
1073Wachtwoord: Wachtwoord123
1074/==================================================*/
1075
1076EXEC spVoegUserToe 'TestAdmin', 'Admin', 'Admin', '1990-01-01', 'Vrouw', 0612345678, 'Adminstraat', '1337HX', 'Straat', 'Land', 'TestAdmin@Admin.Admin', '88232fd34468c0bede2f46e03c3a5754010552b474e05a3b6f1ec72d4f1ab1aa', 'Wie is je favoriete pokemon?', 'Pikachu', '12345678'
1077
1078DELETE FROM RegistratieCode WHERE code = '12345678'
1079
1080EXEC spVoegBeheerderToe 'TestAdmin'