· 6 years ago · May 17, 2019, 09:34 AM
1--Datenbank und Logins löschen
2USE master
3DROP DATABASE IF EXISTS Videothek
4
5
6
7DROP LOGIN Boss
8DROP LOGIN Kasse
9DROP LOGIN Verwaltung
10
11
12
13--Datenbank erstellen
14
15 CREATE DATABASE [Videothek]
16 GO
17
18 USE Videothek
19
20 --Logins erstellen
21
22CREATE LOGIN [Boss] WITH PASSWORD ='9000_chefieren!', DEFAULT_DATABASE= [Videothek], DEFAULT_LANGUAGE=[Deutsch]
23CREATE LOGIN [Kasse] WITH PASSWORD ='Kassieren0000.', DEFAULT_DATABASE= [Videothek], DEFAULT_LANGUAGE=[Deutsch]
24CREATE LOGIN [Verwaltung] WITH PASSWORD ='verwaltieren11elf.', DEFAULT_DATABASE= [Videothek], DEFAULT_LANGUAGE=[Deutsch]
25
26
27--Universaluser erstellen
28CREATE USER [Boss] FOR LOGIN [Boss]
29
30
31--Schemata erstellen
32GO
33CREATE SCHEMA [Verwaltung] AUTHORIZATION [Boss]
34
35GO
36CREATE SCHEMA [Log] AUTHORIZATION [Boss]
37
38GO
39CREATE SCHEMA [Referenz] AUTHORIZATION [Boss]
40GO
41
42
43--Benutzer erstellen
44CREATE USER [Kasse] FOR LOGIN [Kasse] WITH DEFAULT_SCHEMA=[Log]
45CREATE USER [Verwaltung] FOR LOGIN [Verwaltung] WITH DEFAULT_SCHEMA=[Verwaltung]
46
47--Rollen erstellen
48CREATE ROLE [administrieren] AUTHORIZATION [Boss]
49CREATE ROLE [verwalten] AUTHORIZATION [Verwaltung]
50CREATE ROLE [kassieren] AUTHORIZATION [Kasse]
51
52
53--Berechtigungen festlegen
54GRANT INSERT, DELETE, SELECT ON SCHEMA :: [Referenz] TO [verwalten]
55GRANT INSERT, DELETE, SELECT ON SCHEMA :: [Verwaltung] TO [verwalten]
56GRANT SELECT ON SCHEMA :: [Log] TO [verwalten]
57
58
59GRANT INSERT, DELETE, SELECT ON SCHEMA :: [Referenz] TO [administrieren]
60GRANT INSERT, DELETE, SELECT ON SCHEMA :: [Verwaltung] TO [administrieren]
61GRANT INSERT, DELETE, SELECT ON SCHEMA :: [Log] TO [administrieren]
62
63
64GRANT SELECT ON SCHEMA :: [Referenz] TO [kassieren]
65GRANT SELECT ON SCHEMA :: [Verwaltung] TO [kassieren]
66GRANT INSERT, SELECT ON SCHEMA :: [Log] TO [kassieren]
67
68--Rollen den Usern zuweisen
69exec sp_addrolemember 'kassieren','Kasse'
70exec sp_addrolemember 'administrieren','Boss'
71exec sp_addrolemember 'verwalten','Verwaltung'
72
73
74
75
76--Tabellen erstellen
77CREATE TABLE [Referenz].[Film](
78ID INT PRIMARY KEY IDENTITY(1,1),
79Name NVARCHAR(200),
80Menge INT,
81GenreID INT,
82Erscheinungsjahr int,
83RegisseurID INT,
84AltersfreigabeID INT,
85Filmdauer INT)
86
87CREATE TABLE [Referenz].[Regisseur](
88ID INT PRIMARY KEY IDENTITY(1,1),
89Vorname NVARCHAR(50),
90Nachname NVARCHAR(50),
91Geburtsjahr INT)
92
93CREATE TABLE [Verwaltung].[Mitarbeiter](
94ID INT PRIMARY KEY IDENTITY(1,1),
95Vorname NVARCHAR(50),
96Nachname NVARCHAR(50))
97
98CREATE TABLE [Log].Verleih(
99ID INT PRIMARY KEY IDENTITY(1,1),
100FilmID INT,
101KundenID INT,
102MitarbeiterID INT,
103AktionID INT,
104Datum DATE,
105Dauer INT)
106
107CREATE TABLE [Referenz].Aktion(
108ID INT PRIMARY KEY IDENTITY(1,1),
109Name VARCHAR(10))
110
111CREATE TABLE [Referenz].Ort(
112ID INT PRIMARY KEY IDENTITY(1,1),
113Postleitzahl CHAR(5),
114Name NVARCHAR(30))
115
116CREATE TABLE [Verwaltung].Kunde(
117ID INT PRIMARY KEY IDENTITY(1,1),
118Vorname NVARCHAR(50),
119Nachname NVARCHAR(100),
120AnschriftID INT,
121Hausnummer VARCHAR(10))
122
123CREATE TABLE [Referenz].[Anschrift](
124ID INT PRIMARY KEY IDENTITY(1,1),
125Straße NVARCHAR(50),
126OrtID INT)
127
128CREATE TABLE [Referenz].[Altersfreigabe](
129ID INT PRIMARY KEY IDENTITY(1,1),
130Name VARCHAR(10))
131
132CREATE TABLE [Referenz].[Genre] (
133ID INT PRIMARY KEY IDENTITY(1,1),
134Name VARCHAR(50)
135)
136
137--Foreign Keys hinzufügen
138ALTER TABLE Referenz.Film
139ADD FOREIGN KEY (GenreID) REFERENCES [Referenz].Genre(ID),
140FOREIGN KEY (RegisseurID) REFERENCES [Referenz].Regisseur(ID),
141FOREIGN KEY (AltersfreigabeID) REFERENCES [Referenz].Altersfreigabe(ID)
142
143ALTER TABLE Verwaltung.Kunde
144ADD FOREIGN KEY (AnschriftID) REFERENCES Referenz.Anschrift(ID)
145
146ALTER TABLE Log.Verleih
147ADD FOREIGN KEY (FilmID) REFERENCES Referenz.Film(ID),
148FOREIGN KEY (KundenID) REFERENCES Verwaltung.Kunde(ID) ON DELETE CASCADE,
149FOREIGN KEY (MitarbeiterID) REFERENCES Verwaltung.Mitarbeiter(ID) ON DELETE SET NULL,
150FOREIGN KEY (AktionID) REFERENCES Referenz.Aktion(ID)
151
152ALTER TABLE Referenz.Anschrift
153ADD FOREIGN KEY (OrtID) REFERENCES Referenz.Ort(ID)
154
155
156--Tabellen füllen
157 INSERT INTO Referenz.Aktion(Name)
158VALUES('ausleihen'),
159 ('rückgabe')
160
161INSERT INTO Referenz.Regisseur(Vorname, Nachname, Geburtsjahr)
162VALUES('Wes', 'Craven', 1939),
163 ('Brett', 'Ratner', 1969),
164 ('David','Yates',1963)
165
166INSERT INTO Referenz.Ort(Name, Postleitzahl)
167VALUES ('Schauenburg','34270'),
168 ('Kassel','34134'),
169 ('Altenritte','34225')
170
171
172INSERT INTO Referenz.Anschrift(Straße, OrtId)
173VALUES ('Korbacherstraße',1),
174 ('Gartenstraße', 1),
175 ('Heiligenbergstraße', 2),
176 ('Fliederweg',3)
177
178Insert into Referenz.genre (Name)
179Values ('Horror'), ('Action'), ('Drama')
180
181
182Insert Into [Referenz].[Altersfreigabe] (Name)
183VALUES ('FSK16'), ('FSK12')
184
185INSERT INTO Referenz.Film(Name, Menge, GenreId, Erscheinungsjahr, RegisseurId, AltersfreigabeID, Filmdauer)
186VALUES ('Scream - Schrei!',1, 1, 1996, 1, 1, 111),
187 ('Hercules: The Thracian Wars',3, 2, 2014, 2, 2, 101),
188 ('Legend of Tarzan',1, 3, 2016, 3, 2, 110),
189 ('Rush Hour',2, 3, 1998, 2, 2, 98)
190
191INSERT INTO Verwaltung.Kunde(Vorname, Nachname, AnschriftId, Hausnummer)
192VALUES ('Adam', 'Wehrbus', 1, 351),
193 ('Mike', 'Kosicke', 2,7),
194 ('Thomas', 'Müller', 3,2),
195 ('Sabrina', 'Miller', 4,7)
196
197INSERT INTO Verwaltung.Mitarbeiter(Vorname, Nachname)
198VALUES ('Alex', 'Schmidt'),
199 ('Jana', 'Pearce')
200
201INSERT INTO Log.Verleih(FilmId, KundenId, MitarbeiterId, AktionID,Datum, Dauer)
202VALUES (1, 1, 1, 1, convert(date,'2018.03.24'), 3),
203 (2, 2, 2, 1,convert(date,'2018.04.24'), 14),
204 (2, 1, 2, 1,convert(date,'2018.06.24'), 4),
205 (4, 3, 1, 1,convert(date,'2018.08.24'), 12),
206 (4, 4, 1, 1,convert(date,'2018.09.24'), 12)
207
208GO
209
210--Views erstellen
211CREATE VIEW log.VerleihView
212AS
213SELECT Verleih.ID as VerleihID
214 ,FilmID
215 ,Film.Name as Filmtitel
216 ,KundenID
217 ,Kunde.Vorname +' '+ Kunde.Nachname as Kundenname
218 ,Mitarb.Vorname+' '+Mitarb.Nachname as Mitarbeitername
219 ,Akt.Name as Aktion
220 ,Verleih.Datum
221 ,Verleih.Dauer
222
223
224FROM Log.Verleih Verleih
225
226 INNER JOIN Referenz.Film Film
227 ON Film.ID=Verleih.FilmID
228
229 INNER JOIN Verwaltung.Kunde Kunde
230 ON Kunde.ID=Verleih.KundenID
231
232 INNER JOIN Verwaltung.Mitarbeiter Mitarb
233 ON Mitarb.ID=Verleih.MitarbeiterID
234
235 INNER JOIN Referenz.Aktion Akt
236 ON Akt.ID=Verleih.AktionID
237GO
238
239CREATE VIEW [Log].[VerfuegbareFilme]
240AS
241
242SELECT *
243
244FROM (
245 SELECT Film.ID
246 ,Film.Name
247 ,(CASE WHEN Verleih_Differenz.FilmID IS NULL THEN Menge ELSE Menge-Verleih_Differenz.Differenz END) as [verfügbare Anzahl]
248
249 FROM Referenz.Film Film
250
251 LEFT JOIN (
252 SELECT Verleih_A.FilmID
253 ,COUNT(Verleih_A.ID)-(CASE WHEN Verleih_B.ID IS NULL THEN 0 ELSE COUNT(DISTINCT Verleih_B.ID) END) as Differenz
254
255 FROM Log.Verleih Verleih_A
256
257 LEFT JOIN
258 (SELECT ID, FilmID
259 FROM Log.Verleih
260 WHERE AktionID=2
261 ) Verleih_B ON Verleih_A.FilmID=Verleih_B.FilmID
262
263 WHERE Verleih_A.AktionID=1
264 GROUP BY Verleih_A.FilmID, Verleih_B.ID
265 ) Verleih_Differenz
266 ON Film.ID=Verleih_Differenz.FilmID
267
268 ) Ergebnis_inkl_nicht_verfuegbaren
269
270WHERE [verfügbare Anzahl]>0
271GO
272
273--Prozeduren erstellen
274
275
276CREATE PROCEDURE [Verwaltung].[MitarbeiterAnlegen]
277(
278@Vorname nvarchar(50),
279@Nachname nvarchar(100)
280)
281AS
282
283INSERT INTO Verwaltung.Mitarbeiter (Vorname, Nachname)
284VALUES(@Vorname, @Nachname)
285
286SELECT MAX(ID) as [neue MitarbeiterID] FROM Verwaltung.Mitarbeiter
287
288GO
289---------------------------------------------------------------------------------------------
290CREATE PROCEDURE [Verwaltung].[KundeEntfernen]
291(
292@KundenId int
293)
294AS
295
296IF ((SELECT COUNT(KundenID) FROM Log.Verleih WHERE KundenId = @KundenId AND AktionId = 1) =
297 (SELECT COUNT(KundenId) FROM Log.Verleih WHERE KundenId = @KundenId AND AktionId = 2))
298 BEGIN
299 DELETE FROM Verwaltung.Kunde
300 WHERE Id = @kundenId
301 SELECT CONCAT('Kunde ', @KundenId,' erfolgreich entfernt') [Erfolg]
302 END
303ELSE SELECT 'Der Kunde kann nicht gelöscht werden, bis alle ausgeliehenen Filme zurückgegeben wurden.' as [Fehler]
304GO
305-------------------------------------------------------------------------------------------------
306CREATE PROCEDURE [Verwaltung].[KundeAnlegen](
307@Vorname nvarchar(50),
308@Nachname nvarchar(100),
309@Postleitzahl char(5),
310@Straße varchar(100),
311@Hausnummer varchar(10),
312@Ort varchar(100))
313AS
314
315DECLARE @OrtsId INT
316SET @OrtsId = ( SELECT TOP 1 Id
317 FROM Referenz.Ort
318 WHERE Ort.Name = @Ort
319 AND Postleitzahl = @Postleitzahl
320 )
321IF (@OrtsId IS NULL)
322BEGIN
323 INSERT INTO Referenz.Ort(Name,Postleitzahl)
324 VALUES(@Ort, @Postleitzahl)
325 SET @OrtsId = ( SELECT Id
326 FROM Referenz.Ort
327 WHERE Ort.Name = @Ort
328 AND Postleitzahl = @Postleitzahl
329 )
330END
331
332DECLARE @AnschriftsId INT
333SET @AnschriftsId = (SELECT Id
334 FROM Referenz.Anschrift
335 WHERE Straße = @Straße
336 AND OrtId = @OrtsId)
337
338IF (@AnschriftsId IS NULL)
339BEGIN
340 INSERT INTO Referenz.Anschrift(Straße, OrtId)
341 VALUES(@Straße, @OrtsId)
342 SET @AnschriftsId = (SELECT Id
343 FROM Referenz.Anschrift
344 WHERE Straße = @Straße
345 AND OrtId = @OrtsId)
346END
347
348INSERT INTO Verwaltung.Kunde(Vorname, Nachname, AnschriftId, Hausnummer)
349VALUES(@Vorname, @Nachname, @AnschriftsId, @Hausnummer)
350
351SELECT MAX(ID) FROM Verwaltung.Kunde as [neue KundenID]
352
353GO
354-------------------------------------------------------------------------------------------------
355
356
357CREATE PROCEDURE [Verwaltung].[FilmAnlegen](
358@Titel nvarchar(200),
359@FSK_ID nvarchar(50),
360@RegisseurID int,
361@GenreId varchar(10),
362@Menge int,
363@Erscheinungsjahr int,
364@Filmdauer int)
365AS
366
367DECLARE @FilmID int = (SELECT TOP(1) ID
368 FROM Referenz.Film
369 WHERE Name=@Titel
370 AND Erscheinungsjahr=@Erscheinungsjahr
371 AND RegisseurId=@RegisseurID
372 AND Filmdauer=@Filmdauer)
373
374IF (@FilmID IS NULL)
375BEGIN
376
377INSERT INTO Referenz.Film(Name,Erscheinungsjahr,GenreId,Menge,RegisseurId,AltersfreigabeId,Filmdauer)
378VALUES(@Titel, @Erscheinungsjahr, @GenreId, @Menge, @RegisseurID, @FSK_ID, @Filmdauer)
379
380END
381ELSE
382BEGIN
383 UPDATE Referenz.Film
384 SET Menge+=@Menge
385 WHERE ID=@FilmID
386SELECT 'Dieser Film existiert bereits. Die Anzahl wurde um die übergebene Menge erhöht.' as [Warnung]
387END
388
389GO
390-------------------------------------------------------------------------------------------------
391
392CREATE PROCEDURE [Referenz].[FilmBearbeiten](
393@FilmID int,
394@Titel nvarchar(200),
395@FSK_ID nvarchar(50),
396@RegisseurID int,
397@GenreId varchar(10),
398@Menge int,
399@Erscheinungsjahr int,
400@Filmdauer int)
401AS
402
403UPDATE Referenz.Film
404SET Name=@Titel,
405 AltersfreigabeID=@FSK_ID,
406 RegisseurID=@RegisseurID,
407 GenreID=@GenreId,
408 Menge=@Menge,
409 Erscheinungsjahr=@Erscheinungsjahr,
410 Filmdauer=@Filmdauer
411WHERE ID=@FilmID
412
413GO
414-------------------------------------------------------------------------------------------------
415
416CREATE PROCEDURE [Verwaltung].[MitarbeiterBearbeiten](
417@MitarbeiterID int,
418@Vorname nvarchar(50),
419@Nachname nvarchar(50))
420AS
421
422UPDATE Verwaltung.Mitarbeiter
423SET Vorname=@Vorname,
424 Nachname=@Nachname
425WHERE Id=@MitarbeiterID
426
427GO
428-------------------------------------------------------------------------------------------------
429CREATE PROCEDURE [Log].[FilmVerleihen](
430@FilmID int,
431@KundenID int,
432@Dauer int,
433@MitarbeiterID int
434)
435AS
436
437IF (@FilmID IN(SELECT ID FROM [Log].[VerfuegbareFilme]))
438BEGIN
439 INSERT INTO Log.Verleih ([FilmID],[KundenID],[MitarbeiterID],[AktionID],[Datum],[Dauer])
440 VALUES (@FilmID, @KundenID, @MitarbeiterID, 1, GETDATE(), @Dauer)
441
442END
443ELSE
444SELECT 'Film ist nicht mehr verfügbar.' AS [Fehlermeldung]
445
446GO
447-------------------------------------------------------------------------------------------------
448
449CREATE PROCEDURE [Log].[FilmZurueckgeben](
450@FilmID int,
451@KundenID int,
452@MitarbeiterID int
453)
454AS
455
456IF EXISTS (SELECT ID FROM Log.Verleih WHERE ID=(SELECT MAX(ID) FROM Log.Verleih WHERE KundenID=@KundenID AND FilmID=@FilmID) AND AktionID=1)
457 INSERT INTO Log.Verleih ([FilmID],[KundenID],[MitarbeiterID],[AktionID],[Datum],[Dauer])
458 VALUES (@FilmID, @KundenID, @MitarbeiterID, 2, GETDATE(), NULL)
459
460ELSE SELECT 'Zurückgabe nicht möglich, bitte prüfen Sie Ihre Eingabe.' as [Fehler]
461
462
463
464GO
465-------------------------------------------------------------------------------------------------
466
467CREATE TRIGGER [Log].[AusleihdauerBerechnen]
468ON Log.Verleih
469AFTER
470INSERT
471AS
472
473DECLARE @letzteID int = (SELECT MAX(ID) FROM [Log].[Verleih])
474
475IF ((SELECT AktionID FROM [Log].[Verleih] WHERE id=@letzteID)=2)
476BEGIN
477
478 DECLARE @FilmID int = ( SELECT FilmID
479 FROM [Log].[Verleih]
480 WHERE id=@letzteID),
481 @KundenID int = (SELECT KundenID
482 FROM [Log].[Verleih]
483 WHERE id=@letzteID)
484
485
486 DECLARE @start_date DATE = (SELECT Datum
487 FROM [Log].[Verleih]
488 WHERE ID = (SELECT MAX(ID)
489 FROM [Log].[Verleih]
490 WHERE FilmID=@FilmID AND KundenID=@KundenID AND AktionID=1)),
491
492 @end_date DATE= (SELECT Datum FROM [Log].[Verleih] WHERE id=@letzteID)
493
494 UPDATE [Log].[Verleih]
495 SET Dauer = DATEDIFF(day, @start_date, @end_date)
496 WHERE ID=@letzteID
497
498END
499
500GO
501-------------------------------------------------------------------------------------------------
502CREATE PROCEDURE [Verwaltung].[MitarbeiterEntfernen](
503@MitarbeiterID int
504)
505AS
506
507
508
509DELETE FROM Verwaltung.Mitarbeiter
510WHERE ID=@MitarbeiterID
511
512GO
513
514-------------------------------------------------------------------------------------------------
515
516CREATE PROCEDURE [Referenz].[AltersfreigabeAnlegen](
517@Bezeichnung varchar(10)
518)
519AS
520
521
522BEGIN TRANSACTION
523
524INSERT INTO Referenz.Altersfreigabe (Name)
525VALUES (@Bezeichnung)
526
527IF (SELECT COUNT(ID) FROM Referenz.Altersfreigabe WHERE Name=@Bezeichnung GROUP BY Name)>1
528ROLLBACK
529ELSE
530COMMIT
531
532GO
533
534-------------------------------------------------------------------------------------------------
535
536CREATE PROCEDURE [Referenz].[GenreAnlegen](
537@Bezeichnung varchar(50)
538)
539AS
540
541
542BEGIN TRANSACTION
543
544INSERT INTO Referenz.Genre(Name)
545VALUES (@Bezeichnung)
546
547IF (SELECT COUNT(ID) FROM Referenz.Genre WHERE Name=@Bezeichnung GROUP BY Name)>1
548ROLLBACK
549ELSE
550COMMIT
551
552GO
553
554-------------------------------------------------------------------------------------------------
555CREATE PROCEDURE [Verwaltung].[KundenBearbeiten](
556@KundenID int,
557@Vorname nvarchar(50),
558@Nachname nvarchar(100),
559@Postleitzahl char(5),
560@Straße varchar(100),
561@Hausnummer varchar(10),
562@Ort varchar(100))
563AS
564
565DECLARE @alteAnschriftsID int = (SELECT AnschriftID FROM Verwaltung.Kunde WHERE ID=@KundenID)
566
567DECLARE @OrtsId INT
568SET @OrtsId = ( SELECT TOP 1 Id
569 FROM Referenz.Ort
570 WHERE Ort.Name = @Ort
571 AND Postleitzahl = @Postleitzahl
572 )
573IF (@OrtsId IS NULL)
574BEGIN
575 INSERT INTO Referenz.Ort(Name,Postleitzahl)
576 VALUES(@Ort, @Postleitzahl)
577 SET @OrtsId = ( SELECT Id
578 FROM Referenz.Ort
579 WHERE Ort.Name = @Ort
580 AND Postleitzahl = @Postleitzahl
581 )
582END
583
584DECLARE @AnschriftsId INT
585SET @AnschriftsId = (SELECT Id
586 FROM Referenz.Anschrift
587 WHERE Straße = @Straße
588 AND OrtId = @OrtsId)
589
590IF (@AnschriftsId IS NULL)
591BEGIN
592 INSERT INTO Referenz.Anschrift(Straße, OrtId)
593 VALUES(@Straße, @OrtsId)
594 SET @AnschriftsId = (SELECT Id
595 FROM Referenz.Anschrift
596 WHERE Straße = @Straße
597 AND OrtId = @OrtsId)
598END
599
600UPDATE Verwaltung.Kunde
601SET Vorname=@Vorname,
602 Nachname=@Nachname,
603 AnschriftId=@AnschriftsId,
604 Hausnummer=@Hausnummer
605WHERE ID=@KundenID
606
607IF NOT EXISTS (SELECT ID FROM Verwaltung.Kunde WHERE AnschriftID=@alteAnschriftsID)
608 DELETE FROM Referenz.Anschrift
609 WHERE ID=@alteAnschriftsID
610
611
612GO
613
614
615
616-------------------------------------------------------------------------------------------------
617
618CREATE PROCEDURE [Referenz].[FilmEntfernen](
619@FilmID int
620)
621AS
622
623IF ((SELECT Menge FROM Referenz.Film WHERE ID=@FilmID)>0)
624 UPDATE Referenz.Film
625 SET Menge -=1
626 WHERE ID=@FilmID
627ELSE SELECT 'Die Anzahl der Filme ist schon bei 0.'
628
629GO