kybQvCw4

1DROP TABLE IF EXISTS Professor;
2CREATE TABLE Professor (
3	Persn INT NOT NULL PRIMARY KEY,
4	PName VARCHAR(30) NOT NULL,
5	GebDatum DATE NOT NULL,
6	Raum VARCHAR(10),
7	Rang VARCHAR(10) NOT NULL
8);
9
10DROP TABLE IF EXISTS Assistent;
11CREATE TABLE Assistent (
12	Persn INT NOT NULL PRIMARY KEY,
13	AName VARCHAR(30) NOT NULL,
14	GebDatum DATE NOT NULL,
15	FachGeb VARCHAR(10) NOT NULL,
16	PPersn INT NOT NULL
17);
18
19DROP TABLE IF EXISTS Student;
20CREATE TABLE Student (
21	MatrNR INT NOT NULL PRIMARY KEY,
22	SName VARCHAR(30) NOT NULL,
23	GebDatum DATE NOT NULL,
24	Sem INT NOT NULL
25);
26
27DROP TABLE IF EXISTS Vorlesung;
28CREATE TABLE Vorlesung (
29	VorlNr INT NOT NULL PRIMARY KEY,
30	VName VARCHAR(30) NOT NULL,
31	SWS INT NOT NULL,
32	Persn INT NOT NULL
33);
34
35DROP TABLE IF EXISTS hoeren;
36CREATE TABLE hoeren (
37	VorlNr INT NOT NULL,
38	MatrNR INT NOT NULL
39);
40
41DROP TABLE IF EXISTS pruefen;
42CREATE TABLE pruefen (
43	MatrNR INT NOT NULL,
44	PPersn INT NOT NULL,
45	VorlNr INT NOT NULL,
46	PrDatum DATE NOT NULL,
47	Note INT NOT NULL
48);
49
50DROP TABLE IF EXISTS setztVoraus;
51CREATE TABLE setztVoraus (
52	VorlNr INT NOT NULL,
53	VorausS INT NOT NULL
54);
55
56ALTER TABLE 'assistent'
57ADD CONSTRAINT 'F_ASS_PROF'
58FOREIGN KEY ('PPersn')
59REFERENCES 'professor'('PPersn')
60ON DELETE RESTRICT ON UPDATE CASCADE;
61
62ALTER TABLE 'hoeren'
63ADD CONSTRAINT 'F_HOER_STUD'
64FOREIGN KEY ('MatrNr')
65REFERENCES 'student'('MatrNR')
66ON DELETE CASCADE ON UPDATE CASCADE;
67ALTER TABLE 'hoeren'
68ADD CONSTRAINT 'F_HOER_STUD'
69FOREIGN KEY ('MatrNr')
70REFERENCES 'student'('MatrNR')
71ON DELETE CASCADE ON UPDATE CASCADE;
72
73DELIMITER $$
74DROP TRIGGER IF EXISTS 'after_pruefen_insert'$$
75CREATE TRIGGER 'after_pruefen_insert' AFTER INSERT ON 'pruefen' FOR EACH ROW	
76	IF EXISTS ( SELECT * FROM voraussetzen AS V
77	LEFT JOIN ((SELECT * FROM pruefen WHERE MatrNr=NEW.MatrNr) AS P)
78	ON V.VorausS=P.VorlNr
79	WHERE V.VorlNr=NEW.VorlNr AND P.MatrNr IS NULL
80	) THEN	
81		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Pruefung hat Voraussetzung
82	END IF;
83END;$$
84DELIMITER ;