1.)
1.1)
CREATE DEFAULT wohndef AS "Dresden"
sp_bindefault wohndef, "Mitarbeiter.Ort"

1.2)
CREATE DEFAULT berufdef AS "Dipl.-Ing."
sp_bindefault berufdef, "Mitarbeiter.Beruf"

2.)
2.1)
CREATE RULE alterrule AS

DATEDIFF(YY,@age,GETDATE()) BETWEEN 18 AND 60

sp_bindrule alterrule, "Mitarbeiter.Gebdat"

2.2)
a.)
CREATE RULE pronrrule AS CONVERT(INT,@pronr) BETWEEN 30 AND 50
sp_bindrule pronrrule, "MiPro.Pronr"

b.)
CREATE RULE istvzaerule AS @istvzae <=1.0
sp_bindrule istvzrule, "MiPro.Istvzae"

3.)
ALTER TABLE Mitarbeiter  ADD CONSTRAINT mitconstr
CHECK (Mitarbeiter.Mitnr
LIKE '[0-9][0-9] [0-9] [0-9] [0-9]')



4.)
4.1)
ALTER TABLE MiPro ADD CONSTRAINT foreign_key
FOREIGN KEY(Mitnr) REFERENCES Mitarbeiter(Mitnr)

Test:
a.)
INSERT INTO Mitarbeiter VALUES('200','Fate','Twisted','Dresden','11.11.1964',DEFAULT,'7777',NULL)
INSERT INTO MiPro VALUES('200','32',0.7,0.7)


UPDATE Mitarbeiter SET Mitnr='201' WHERE Mitnr='200'
DELETE FROM Mitarbeiter WHERE Mitnr='200'
Selects und UPDATE geht nicht: dependend KEY constriant violation…
b.)
UPDATE MiPro SET Mitnr='202' WHERE Mitnr='200'
UPDATE:  FOREIGN KEY CONSTRAINT violation…

UPDATE Mitarbeiter SET Mitnr='201' WHERE Mitnr='200'
DELETE FROM Mitarbeiter WHERE Mitnr='200'
c.)
INSERT INTO MiPro VALUES('300','31',0.4,0.5)
UPDATE:  FOREIGN KEY CONSTRAINT violation…


4.2)
ALTER TABLE Projekt ADD CONSTRAINT co_Leiter
FOREIGN KEY(Projleiternr) REFERENCES Mitarbeiter(Mitnr)

gleiches verhalten wie oben




TRIGGER

5.)
5.1)
CREATE TRIGGER refint_mipro
ON MiPro FOR INSERT,UPDATE
AS
DECLARE @anz INT
SELECT @anz=@@rowcount

IF(SELECT COUNT(*) FROM Projekt P,inserted
WHERE P.Pronr=inserted.Pronr)!=@anz
BEGIN
PRINT 'Pronr nicht in Projekt vorhanden'
PRINT 'Anzahl Datensätze:%1!',@anz
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'Einfügen war erfolgreich'
PRINT 'Anzahl Datensätze:%1!',@anz
END
--test— insert into MiPro values('200','40',0.4,0.5)
5.2)
INSERT INTO MiPro VALUES('103','37',0.1,0.1)
INSERT INTO MiPro VALUES('104','49',0.2,0.4)—nicht einfügbar weil Projekt 49 nicht vorhanden
INSERT INTO MiPro VALUES('112','38',0.3,0.4)


5.3)
INSERT INTO MiPro VALUES('99999','38',0.3,0.4)
FOREIGN KEY CONSTRAINT violation occurred

5.4)
INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='188'
Mitnr nicht IN Mitarbeiter vorhanden

--select M.Mitnr from Mitarbeiter M,depotN..quelleaumi2 D where M.Mitnr=D.Mitnr

INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='145'
konflikt mit pronrrule: Pronr muss zwischen 30 und 50 sein
INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='103'
Primärschlüsselverletzung-> abbruch
INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='113'
INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='108'
erfolgreich
INSERT INTO MiPro SELECT * FROM depotN..quelleaumi2 WHERE Mitnr='104'
Pronr nicht IN Projekt vorhanden

6.)
CREATE TRIGGER pro_del
ON Projekt FOR DELETE
AS
DECLARE @anz INT
SELECT @anz=@@rowcount

IF(SELECT COUNT(*) FROM MiPro MP,deleted
WHERE MP.Pronr=deleted.Pronr)>0
BEGIN
PRINT 'Dem Projekt sind in MiPro noch Mitarbeiter zugewiesen'
PRINT 'Anzahl Datensätze:%1!',@anz
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'Löschen war erfolgreich'
PRINT 'Anzahl Datensätze:%1!',@anz
END

DELETE FROM Projekt  WHERE Pronr='35' OR Pronr='37' OR Pronr='38' OR Pronr='39'
DELETE FROM Projekt  WHERE Pronr='31' OR Pronr='33' OR Pronr='43'

bei beiden noch Einträge IN MiPro
7.)
7.1)
CREATE TRIGGER planmax
ON MiPro
FOR INSERT,UPDATE
AS
DECLARE @anz INT
SELECT @anz=@@rowcount

IF(EXISTS(SELECT SUM(Planvzae) FROM MiPro GROUP BY Mitnr HAVING SUM(Planvzae)>1))
BEGIN
PRINT 'Der/Die Mitarbeiter  darf/dürfen nicht überlastet werden(Summe Planvzae pro Mitarbieter darf 1,0 nicht übersteigen)'
SELECT M.* FROM Mitarbeiter M WHERE Mitnr IN
(SELECT Mitnr FROM MiPro GROUP BY Mitnr HAVING SUM(Planvzae)>1)
 PRINT 'Anzahl Datensätze:%1!',@anz
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'Datensätze erfolgreich eingefügt/geändert'
PRINT 'Anzahl Datensätze:%1!',@anz
END

7.2)

CREATE TRIGGER del_mipro_proj
ON MiPro
FOR INSERT,UPDATE
AS
DECLARE @anz INT
DECLARE @str VARCHAR(30)
SELECT @anz=@@rowcount
SELECT @str='Geänderte Datensätze ' + CAST(@anz AS VARCHAR)

BEGIN
PRINT @str
IF(EXISTS(SELECT SUM(Planvzae)-SUM(Istvzae) FROM MiPro GROUP BY Pronr HAVING SUM(Planvzae)-SUM(Istvzae) <=0))
--Projektleiter
SELECT M.* FROM Mitarbeiter M, Projekt P  WHERE M.Mitnr=Projleiternr AND Pronr  IN
 (SELECT Pronr FROM MiPro MP GROUP BY Pronr HAVING SUM(MP.Planvzae)-SUM(MP.Istvzae) <=0)
--Projektleiter=NULL
UPDATE Projekt SET Projleiternr=NULL WHERE Pronr IN
(SELECT Pronr FROM MiPro GROUP BY Pronr HAVING SUM(Planvzae)-SUM(Istvzae) <=0)
--Delete in MiPro
DELETE FROM MiPro WHERE Pronr IN (SELECT Pronr FROM MiPro MP
GROUP BY Pronr HAVING SUM(MP.Planvzae)-SUM(MP.Istvzae)<=0)
END
UPDATE MiPro SET Istvzae=Planvzae WHERE Pronr='31'

8.1.)
CREATE TABLE Bprotokoll
(
Mitnr CHAR(5) PRIMARY KEY,
Nutzer CHAR(16),
Zeit datetime,
Beruf_alt CHAR(15),
Beruf_neu CHAR(15)
)

8.2)
CREATE TRIGGER Mitprotokoll
ON Mitarbeiter
FOR UPDATE
AS
DECLARE @anz INT
DECLARE @str VARCHAR(30)
SELECT @anz=@@rowcount
SELECT @str='Geänderte Datensätze ' + CAST(@anz AS VARCHAR)

BEGIN
PRINT @str
IF(EXISTS (SELECT * FROM Mitarbeiter WHERE Beruf  NOT IN (SELECT Beruf FROM inserted)))
-->beruf wurde geupdated
BEGIN
SELECT I.Beruf INTO Bprotokoll.Beruf_neu FROM inserted I
SELECT I.Mitnr, user_name(), getdate(),I.Beruf

END