· 8 years ago · Apr 20, 2018, 05:42 PM
13. Aufgabenkomplex SQL-Praktikum - Referentielle und Semantische Integrität
2
3Teil 1: Sicherung der semantischen Integität - Deklarative Lösung
4
51. DEFAULTS
6
71.1.
8CREATE DEFAULT dOrt_DD AS 'Dresden'
9sp_bindefault dOrt_DD, 'Mitarbeiter.Ort'
10
111.2.
12CREATE DEFAULT dBeruf_Ing AS 'Dipl.-Ing.'
13sp_bindefault dBeruf_Ing, 'Mitarbeiter.Beruf'
14
15Probe:
16INSERT INTO Mitarbeiter (Mitnr,Name, Vorname, Gebdat, Telnr)
17 VALUES ('001', 'Mustermann', 'Max','1990/01/01', '110')
18
19Ergebnis:
20Mitnr,Name,Vorname,Ort,Gebdat,Beruf,Telnr,Abtnr
21'001 ','Mustermann','Max ','Dresden ',1990-01-01,'Dipl.-Ing. ','110 ',
22
23DELETE FROM Mitarbeiter WHERE Mitnr='001'
24
252. RULES
26
272.1.
28CREATE RULE rAlter AS (YEAR(GETDATE())-YEAR(@Gebdat)-1+
29 case
30 when MONTH(GETDATE()) > MONTH(@Gebdat)
31 then 1
32 when (MONTH(GETDATE()) = MONTH(@Gebdat)) AND (DAY(GETDATE()) >= DAY(@Gebdat))
33 then 1
34 when (MONTH(GETDATE()) = MONTH(@Gebdat)) AND (DAY(GETDATE()) < DAY(@Gebdat))
35 then 0
36 when MONTH(GETDATE()) < MONTH(@Gebdat)
37 then 0
38 end) BETWEEN 17 AND 61
39sp_bindrule rAlter, 'Mitarbeiter.Gebdat'
40
41Probe:
42
43a) Alter ist zu niedrig (17)
44
45Could not execute statement.
46A column insert or update conflicts with a rule bound to the column. The
47command is aborted. The conflict occured in database 'im10s64725', table
48'Mitarbeiter', rule 'rAlter', column 'Gebdat'.
49
50INSERT INTO Mitarbeiter (Mitnr,Name, Vorname, Gebdat, Telnr)
51 VALUES ('002', 'Wittke', 'Martin','1994/01/01', '110')
52
53b) Alter ist zu hoch (61)
54
55Could not execute statement.
56A column insert or update conflicts with a rule bound to the column. The
57command is aborted. The conflict occured in database 'im10s64725', table
58'Mitarbeiter', rule 'rAlter', column 'Gebdat'.
59
60INSERT INTO Mitarbeiter (Mitnr,Name, Vorname, Gebdat, Telnr)
61 VALUES ('002', 'Wittke', 'Martin','1950/01/01', '110')
62
63c) Alter in der Zeitspanne
64
65INSERT INTO Mitarbeiter (Mitnr,Name, Vorname, Gebdat, Telnr)
66 VALUES ('002', 'Wittke', 'Martin','1962/01/01', '110')
67
68Ergebnis:
69Mitnr,Name,Vorname,Ort,Gebdat,Beruf,Telnr,Abtnr
70'002 ','Wittke ','Martin ','Dresden ',1962-01-01,'Dipl.-Ing. ','110 ',
71
72DELETE FROM MiPro WHERE Mitnr='002'
73
742.2.
75
76a)
77CREATE RULE rPronr AS CONVERT(INT,@pronr) BETWEEN 30 AND 50
78
79CREATE RULE rPronr
80AS CONVERT(INT,@prnr) >= 30
81AND CONVERT (INT,@prnr) <= 50
82
83sp_bindrule rPronr, 'MiPro.Pronr'
84
85a) Pronr zu niedrig (29)
86
87Could not execute statement.
88A column insert or update conflicts with a rule bound to the column. The
89command is aborted. The conflict occured in database 'im10s64725', table
90'MiPro', rule 'rPronr', column 'Pronr'.
91
92INSERT INTO MiPro (Mitnr,Pronr, Istvzae, Planvzae)
93 VALUES ('001', '29', 0.0, 0.0)
94
95b) Pronr zu hoch (51)
96
97Could not execute statement.
98A column insert or update conflicts with a rule bound to the column. The
99command is aborted. The conflict occured in database 'im10s64725', table
100'MiPro', rule 'rPronr', column 'Pronr'.
101
102INSERT INTO MiPro (Mitnr,Pronr, Istvzae, Planvzae)
103 VALUES ('001', '51', 0.0, 0.0)
104
105c) theoretisch gültige Pronr
106
107INSERT INTO MiPro (Mitnr,Pronr, Istvzae, Planvzae)
108 VALUES ('001', '50', 0.0, 0.0)
109
110Ergebnis:
111Mitnr,Pronr,Istvzae,Planvzae
112'001 ','50',0.0,0.0
113
114DELETE FROM MiPro WHERE Mitnr='001'
115
116
117b)
118CREATE RULE rIst AS @ist <= 1.0
119sp_bindrule rIst, 'MiPro.Istvzae'
120
121--> Fehler bei Istvzae = 1.1
122Could not execute statement.
123A column insert or update conflicts with a rule bound to the column. The
124command is aborted. The conflict occured in database 'im10s64725', table
125'MiPro', rule 'rIst', column 'Istvzae'.
126
127INSERT INTO MiPro (Mitnr,Pronr, Istvzae, Planvzae)
128 VALUES ('002', '50', 1.1, 0.0)
129
130--> Regel eingehalten:
131INSERT INTO MiPro (Mitnr,Pronr, Istvzae, Planvzae)
132 VALUES ('002', '50', 1.0, 0.0)
133
134Ergebnis:
135Mitnr,Pronr,Istvzae,Planvzae
136'002 ','50',1.0,0.0
137
1383. CHECK-Klausel
139
140ALTER TABLE Mitarbeiter ADD CONSTRAINT chkMitnr CHECK (char_length(Mitnr) = 5)
141ALTER TABLE Mitarbeiter DROP CONSTRAINT chkMitnr
142ALTER TABLE Mitarbeiter ADD CONSTRAINT chkMitnr CHECK (Mitnr= '[0-9][0-9][0-9][0-9][0-9]')
143
144
145Teil 2: Sicherung der referentiellen Integrität - Deklarative Lösung
146
1474. Aufgaben zur Sicherung der referentiellen Integrität auf deklarativem Wege
148
1494.1. Mitarbeiter - Mipro
150
151ALTER TABLE MiPro ADD CONSTRAINT co_forkey
152FOREIGN KEY(Mitnr) REFERENCES Mitarbeiter(Mitnr)
153
1544.2. Mitarbeiter - Projekt (nur gültige LeiterNr)
155
156ALTER TABLE Projekt ADD CONSTRAINT co_Leiter
157FOREIGN KEY(Lnr) REFERENCES Mitarbeiter(Mitnr)
158
159Probe:
160
1611. Einfügen von Testdatensätzen in Mitarbeiter, Projekt und MiPro mit Einhaltung der referentiellen Integrität
162
163INSERT INTO Mitarbeiter
164 VALUES('251','Mueller','Jakob', default, '1981-11-11', default, '3377', NULL)
165
166INSERT INTO Projekt VALUES('45', 'Projekt 45', 'Ein Testprojekt', 4, null)
167
168INSERT INTO MiPro VALUES('251', '45', 0.6, 0.7)
169
1702. Tests
171
172--- Versuch die aktuelle Lnr durch eine ungültige (nicht in Mitarbeiter vorhanden) zu ersetzen
173Could not execute statement.
174Foreign key constraint violation occurred, dbname = 'im10s64725', table
175name = 'Projekt', constraint name = 'co_Leiter'.
176
177UPDATE Projekt SET Lnr='230'
178WHERE Pronr='45'
179
180--- Versuch eine Mitnr in MiPro durch eine ungültige zu ersetzen
181Could not execute statement.
182Foreign key constraint violation occurred, dbname = 'im10s64725', table
183name = 'MiPro', constraint name = 'co_forkey'.
184
185UPDATE MiPro SET Mitnr = '333'
186WHERE Mitnr = '251'
187
188--- Versuch den Mitarbeiter '251' aus Mitarbeiter zu löschen, ohne ihn in MiPro gelöscht zu haben
189Could not execute statement.
190Dependent foreign key constraint violation in a referential integrity
191constraint. dbname = 'im10s64725', table name = 'Mitarbeiter',
192constraint name = 'co_forkey'.
193
194DELETE Mitarbeiter WHERE Mitnr='251'
195
196--- Versuch des Einfügens eines Datensatzes mit ungültiger Mitnr
197Could not execute statement.
198Foreign key constraint violation occurred, dbname = 'im10s64725', table
199name = 'MiPro', constraint name = 'co_forkey'.
200
201INSERT INTO MiPro VALUES('333', '45', 0.6, 0.7)
202
2033. Löschen der fiktiven Datensätze
204
205DELETE MiPro WHERE Mitnr = '251'
206DELETE Mitarbeiter WHERE Mitnr = '251'
207DELETE Projekt WHERE Pronr='45'
208
209
210Teil 3: Sicherung der referentiellen bzw. semantischen Integrität - Prozedurale Lösung
211
2125. Trigger zur Sicherung der referentiellen Integrität beim Einfügen oder Ändern von Datensätzen
213
2145.1. In MiPro dürfen nur gültige Pronr eingefügt werden
215
216CREATE TRIGGER MiPro_Eintrag ON MiPro
217 FOR INSERT, UPDATE AS
218 BEGIN
219 DECLARE @anz INT
220
221 SELECT @anz=@@rowcount
222 IF (SELECT COUNT(*) FROM Projekt P JOIN inserted i ON P.Pronr=i.Pronr)!=@anz
223 BEGIN
224 PRINT 'Einfügen/ Ändern der Datensätze fehlgeschlagen, Pronr ungültig'
225 ROLLBACK TRANSACTION
226 END
227 END
228
2295.2. Einfügen von Testdatensätzen
230
231INSERT INTO MiPro VALUES('103','37',0.1,0.1)
232
2331 row(s) inserted
2341 row(s) inserted
235
236DELETE FROM MiPro WHERE Mitnr='103' AND Pronr='37'
237
238
239INSERT INTO MiPro VALUES('104','49',0.2,0.4)
240
2411 row(s) inserted
242Einfügen fehlgeschlagen, falsche Projektnummer
243
244
245INSERT INTO MiPro VALUES('112','38',0.3,0.4)
246
2471 row(s) inserted
2481 row(s) inserted
249
250DELETE FROM MiPro WHERE Mitnr='112' AND Pronr='38'
251
2525.3.
253
254INSERT INTO MiPro VALUES('99999','38',0.3,0.4)
255
256Could not execute statement.
257Foreign key constraint violation occurred, dbname = 'im10s64706', table
258name = 'MiPro', constraint name = 'co_forkey'.
259
260--> Fremdschlüsselprüfung wird zuerst durchgeführt --> Löschen erforderlich, um Trigger zu testen
261ALTER TABLE MiPro DROP CONSTRAINT co_forkey
262
263Ergebnis:
264
2651 row(s) inserted
2660 row(s) inserted
2671 row(s) inserted
268
269Mitnr,Pronr,Istvzae,Planvzae
270'99999','38',0.3,0.4
271
272--> Einfügen war aufgrund der ungültigen Mitnr nicht möglich, für Trigger lediglich gültige Pronr relevant
273
274DELETE FROM MiPro WHERE Mitnr='99999'
275
2765.4. Einfügen von 2 Datentupeln aus depotN..quelleaumi2
277
278INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE (Mitnr='188' AND Aufgnr='33') OR (Mitnr='188' AND Aufgnr='34')
279
280Ergebnis:
281
2821 row(s) inserted
2831 row(s) inserted
2841 row(s) inserted
2851 row(s) inserted
2860 row(s) inserted
2872 row(s) inserted
288
289Mitnr,Pronr,Istvzae,Planvzae
290'188 ','33',0.5,0.5
291'188 ','34',0.5,0.5
292
293DELETE FROM MiPro WHERE Mitnr='188'
294
295--> FAZIT: Trigger fängt lediglich ungültige Pronr ab, ungültige Mitnr werden ignoriert
296--> erneutes Einfügen des Fremdschlüssels notwendig zum Abfangen dieser
297
298ALTER TABLE MiPro ADD CONSTRAINT co_forkey
299FOREIGN KEY(Mitnr) REFERENCES Mitarbeiter(Mitnr)
300
3016. Trigger zur Sicherung der referentiellen Integrität beim Löschen von Datensätzen
302
303CREATE TRIGGER Projekt_Loesch ON Projekt
304 FOR DELETE AS
305 BEGIN
306 IF EXISTS (SELECT MP.Pronr FROM MiPro MP JOIN deleted d ON MP.Pronr=d.Pronr)
307 BEGIN
308 PRINT 'Das Projekt befindet sich noch in Bearbeitung'
309 ROLLBACK TRANSACTION
310 END
311 END
312
313DELETE FROM Projekt WHERE Pronr IN ('35', '37', '38', '39')
314
315DELETE FROM Projekt WHERE Pronr = '35'
316DELETE FROM Projekt WHERE Pronr = '37'
317DELETE FROM Projekt WHERE Pronr = '38'
318DELETE FROM Projekt WHERE Pronr = '39'
319
320--> Das Projekt befindet sich gerade in Bearbeitung
321
322DELETE FROM Projekt WHERE Pronr IN ('31', '33', '34')
323
324DELETE FROM Projekt WHERE Pronr = '31'
325DELETE FROM Projekt WHERE Pronr = '33'
326DELETE FROM Projekt WHERE Pronr = '34'
327
328--> Das Projekt befindet sich gerade in Bearbeitung
329
3307. Trigger zur Sicherung der semantischen Integrität
331
3327.1. Planvzae eines Mitarbeiters darf nicht 0.1 überschreiten
333
334CREATE TRIGGER MiPro_Plan ON MiPro
335 FOR INSERT, UPDATE AS
336 BEGIN
337
338 SELECT Mitnr, SUM(Planvzae) AS Plansumme
339 FROM MiPro
340 WHERE Mitnr IN (SELECT Mitnr FROM inserted)
341 GROUP BY Mitnr
342 HAVING SUM(Planvzae) > 1.0
343
344
345 IF @@rowcount > 0
346 BEGIN
347 PRINT 'ERROR: Bei folgenden Mitarbeitern überschreitet die Plansumme den zugelassenen Wert:'
348 ROLLBACK TRANSACTION
349 END
350 END
351
352Test:
353
354Mitnr,Pronr,Istvzae,Planvzae
355'106 ','36',0.1,0.1
356'106 ','39',0.3,0.1
357'106 ','43',0.7,0.8
358
359--> Mitnr 106 hat bereits Planvzae = 1.0 --> Trigger muss es abbrechen
360
361Ergebnis:
362
363Could not execute statement.
364Attempt to use a cursor 'mit' which is not open. Use the system stored
365procedure sp_cursorinfo for more information.
366
367INSERT INTO MiPro VALUES('106','31',0.5,0.4)
368
3691 row(s) inserted
370ERROR: Die Summe der geplanten Projekttätigkeiten eines Mitarbeiter überschreitet den zugelassenen Wert
371
3727.2. Aktuell nicht bearbeitete Projekte dürfen gelöscht werden
373
374CREATE TRIGGER MiPro_Loesch ON MiPro
375 FOR DELETE AS
376 BEGIN
377
378 SELECT DISTINCT Pronr, SUM(Planvzae) AS SUM_Plan, SUM(Istvzae) AS SUM_Ist
379 FROM deleted
380 GROUP BY Pronr
381 HAVING SUM(Planvzae) > SUM(Istvzae)
382
383 IF @@rowcount > 0
384 BEGIN
385 PRINT 'ERROR: Das Löschen folgender Projekte ist nicht möglich, da diese sich noch in Bearbeitung befinden'
386 ROLLBACK TRANSACTION
387 END
388 ELSE
389 BEGIN
390 SELECT Lnr FROM Projekt WHERE Pronr IN (SELECT Pronr FROM deleted)
391 UPDATE Projekt SET Lnr = NULL WHERE Pronr IN (SELECT Pronr FROM deleted)
392 PRINT 'Löschen war erfolgreich'
393 END
394 RETURN
395 END
396
397/* Test der Abfrage im Trigger mit äquivalenter Tabelle zu MiPro:
398
399SELECT DISTINCT Pronr, (SUM(DISTINCT d.Planvzae) + SUM(DISTINCT MP.Planvzae)), (SUM(DISTINCT d.Istvzae) + SUM(DISTINCT MP.Istvzae))
400FROM MiPro MP JOIN depotN..quelleaumi2 d ON Pronr=Aufgnr
401GROUP BY Pronr, Aufgnr
402HAVING (SUM(DISTINCT d.Planvzae) + SUM(DISTINCT MP.Planvzae)) > (SUM(DISTINCT d.Istvzae) + SUM(DISTINCT MP.Istvzae))
403
404SELECT DISTINCT Pronr, (SUM(DISTINCT d.Planvzae) + SUM(DISTINCT MP.Planvzae)), (SUM(DISTINCT d.Istvzae) + SUM(DISTINCT MP.Istvzae))
405FROM MiPro MP JOIN depotN..quelleaumi2 d ON Pronr=Aufgnr
406GROUP BY Pronr, Aufgnr
407
408SELECT DISTINCT Pronr, SUM(DISTINCT d.Planvzae) AS dPlan, SUM(DISTINCT d.Istvzae) AS dIst , SUM(DISTINCT MP.Planvzae) AS MPPlan, SUM(DISTINCT MP.Istvzae) AS MPIst
409FROM MiPro MP JOIN depotN..quelleaumi2 d ON Pronr=Aufgnr
410GROUP BY Pronr, Aufgnr
411
412SELECT DISTINCT Aufgnr, SUM(DISTINCT d.Planvzae) AS dPlan, SUM(DISTINCT d.Istvzae) AS dIst
413FROM depotN..quelleaumi2 d
414GROUP BY Aufgnr
415ORDER BY Aufgnr
416
417SELECT DISTINCT Pronr, SUM(DISTINCT MP.Planvzae) AS MPPlan, SUM(DISTINCT MP.Istvzae) AS MPIst
418FROM MiPro MP
419GROUP BY Pronr
420ORDER BY Pronr
421
422*/
423
424
425Probe:
426
427SELECT Pronr, SUM(Planvzae), SUM(Istvzae)
428FROM MiPro GROUP BY Pronr
429ORDER BY Pronr
430
431Pronr,SPlan,SIst
432'31',2.1,2.6
433'32',0.5,0.5
434'33',0.6,0.3
435'34',0.8,0.8
436'35',0.5,0.5
437'36',2.1,1.4
438'37',1.0,1.0
439'38',0.5,0.5
440'39',0.8,0.8
441'41',0.6,0.7
442'42',0.9,0.9
443'43',0.8,0.7
444'44',0.6,0.6
445
446--> Projekt 31 erfüllt Bedingung für Löschen
447
448SELECT* FROM MiPro WHERE Pronr='31'
449
450Mitnr,Pronr,Istvzae,Planvzae
451'107 ','31',0.5,0.4
452'108 ','31',0.7,0.6
453'109 ','31',0.5,0.4
454'112 ','31',0.9,0.7
455
456
457INSERT INTO MiPro VALUES('107','31',0.5,0.4)
458INSERT INTO MiPro VALUES('108','31',0.7,0.6)
459INSERT INTO MiPro VALUES('109','31',0.5,0.4)
460INSERT INTO MiPro VALUES('112','31',0.9,0.7)
461
462SELECT * FROM Projekt WHERE Pronr='31'
463
464Pronr,Proname,Beschreibung,Aufwand,Lnr
465'31','Reportgenerator',,3,'112 '
466
467UPDATE Projekt SET Lnr = '112' WHERE Pronr ='31'
468
469Test:
470DELETE FROM MiPro WHERE Pronr='31'
471
472Ergebnis:
473
474Löschen war erfolgreich
4754 row(s) deleted
476
477Lnr
478'112 '
479
480Pronr,Proname,Beschreibung,Aufwand,Lnr
481'31','Reportgenerator',,3,
482
483
484--> Projekt 33 erfüllt die Bedingung für Abbruch
485
486Mitnr,Pronr,Istvzae,Planvzae
487'112 ','33',0.2,0.3
488'145 ','33',0.1,0.3
489
490INSERT INTO MiPro VALUES('112 ','33',0.2,0.3)
491INSERT INTO MiPro VALUES('145 ','33',0.1,0.3)
492
493UPDATE Projekt SET Lnr='145' WHERE Pronr='33'
494
495DELETE FROM MiPro WHERE Pronr='33'
496
497Ergebnis:
498ERROR: Das Löschen folgender Projekte ist nicht möglich, da diese sich noch in Bearbeitung befinden
499Pronr,SUM_Plan,SUM_Ist
500'33',0.6,0.3
501
5028. Trigger zur Protokollierung von Datenänderungen
503
5048.1. Protokolltabelle
505
506CREATE TABLE Bprotokoll
507(Mitnr CHAR(5) NOT NULL,
508Nutzer CHAR(16),
509Zeit DATETIME,
510Beruf_alt CHAR(15),
511Beruf_neu CHAR(15)
512)
513
5148.2. Trigger zum Protokollieren der Änderungen des Berufes
515
516CREATE TRIGGER Beruf_Update ON Mitarbeiter
517 FOR UPDATE AS
518 BEGIN
519 IF UPDATE(Beruf)
520 BEGIN
521 INSERT INTO Bprotokoll
522 SELECT i.Mitnr, user_name(), getdate(), d.Beruf, i.Beruf
523 FROM deleted d JOIN inserted i ON d.Mitnr=i.Mitnr
524 END
525 END
526
5278.3. Test
528
5298.4. zusammengesetzter Primärschlüssel: Mitnr, Nutzer, Zeit
530
5318.5. Datensatzzähler
532
533ALTER TABLE Bprotokoll ADD ID INT
534
535CREATE PROCEDURE init
536AS
537 DECLARE @id INT
538 DECLARE @mitnr CHAR(5)
539 DECLARE @user CHAR(16)
540 DECLARE @time datetime
541
542 DECLARE table CURSOR FOR
543 SELECT Mitnr, Nutzer, Zeit FROM Bprotokoll
544
545 SELECT @id=0
546 OPEN table
547
548 FETCH table INTO @mitnr, @user, @time
549
550 WHILE @@sqlstatus=0
551 BEGIN
552 UPDATE @table SET ID = @id WHERE Mitnr=@mitnr AND Nutzer=@user AND Zeit=@time
553 SELECT @id = @id + 1
554 FETCH table INTO @mitnr, @user, @time
555 END
556RETURN 0
557
558ALTER TABLE Bprotokoll ADD PRIMARY KEY(ID)
559
5608.6. Test/ Anpassung des Triggers aus 8.1.
561
562--> Tabelle Bprotokoll enthält nun einen Primärschlüssel, d.h. dieser muss beim Einfügen eines neuen Tupels berücksichtigt werden
563
564CREATE TRIGGER Beruf_Update ON Mitarbeiter
565 FOR UPDATE AS
566 BEGIN
567 IF UPDATE(Beruf)
568 BEGIN
569 DECLARE @id INT
570
571 SELECT @id=(SELECT MAX(ID) FROM Bprotokoll) + 1
572 INSERT INTO Bprotokoll
573 SELECT @id, i.Mitnr, user_name(), getdate(), d.Beruf, i.Beruf
574 FROM deleted d JOIN inserted i ON d.Mitnr=i.Mitnr
575 END
576 END
577
5788.7. Bprotokoll2
579
580CREATE TABLE Bprotokoll2
581(DSNo INT PRIMARY KEY,
582Mitnr CHAR(5) NOT NULL,
583Nutzer CHAR(16),
584Zeit DATETIME,
585Beruf_alt CHAR(15),
586Beruf_neu CHAR(15)
587)