alter user aiws1617030
identified by Weeddreams128;


DROP TABLE Dozent
CASCADE CONSTRAINTS;

DROP TABLE Student
CASCADE CONSTRAINTS;

DROP TABLE Veranstaltung
CASCADE CONSTRAINTS;

DROP TABLE Studiengang
CASCADE CONSTRAINTS;

DROP TABLE Halten
CASCADE CONSTRAINTS;

DROP TABLE BESUCHEN
CASCADE CONSTRAINTS;

DROP TABLE Pruefung
CASCADE CONSTRAINTS;

DROP TABLE FACHBEREICH
CASCADE CONSTRAINTS;

DROP SEQUENCE doznr_Seq;
DROP SEQUENCE matnr_Seq;
DROP SEQUENCE vnr_seq;
DROP SEQUENCE stgnr_seq;
DROP SEQUENCE fbnr_seq;

CREATE TABLE Dozent
(
	doznr INTEGER,
	name VARCHAR(20),
	vorname VARCHAR(20),
	gebdat DATE,
	geschlecht CHAR(1),
	buero VARCHAR(10),
	telnr VARCHAR(20),
	email VARCHAR(40),
	einstelldatum DATE,
	fachgebiet VARCHAR(30),
	fachbereich VARCHAR(30)
);

CREATE TABLE Student
(
	matnr INTEGER,
	name VARCHAR(20),
	vorname VARCHAR(20),
	gebdat DATE,
	geschlecht CHAR(1),
	email VARCHAR(40),
	eingeschrieben_seit DATE,
	stgnr INTEGER
);

CREATE TABLE Veranstaltung
(
	vnr INTEGER,
	vtitel VARCHAR(20),
	vtyp VARCHAR(20),
	stgnr INTEGER
);

CREATE TABLE Studiengang
(
	stgnr INTEGER,
	bezeichnung VARCHAR(30),
	fachbereich VARCHAR(30)
);

CREATE TABLE Halten
(
	doznr INTEGER,
	vnr INTEGER,
	semester VARCHAR(10)
);

CREATE TABLE Besuchen
(
	matnr INTEGER,
	vnr INTEGER,
	semester VARCHAR(10)
);

CREATE TABLE Pruefung
(
	doznr INTEGER,
	matnr INTEGER,
	vnr INTEGER,
	datum DATE,
	note DECIMAL(2,1)
);

ALTER TABLE Dozent
ADD CONSTRAINT pk_doz PRIMARY KEY(doznr);

ALTER TABLE Student
ADD CONSTRAINT pk_stud PRIMARY KEY(matnr);

ALTER TABLE Veranstaltung
ADD CONSTRAINT pk_ver PRIMARY KEY(vnr);

ALTER TABLE Studiengang
ADD CONSTRAINT pk_studg PRIMARY KEY(stgnr);

ALTER TABLE Halten
ADD CONSTRAINT pk_hal PRIMARY KEY(doznr,vnr,semester);

ALTER TABLE Besuchen
ADD CONSTRAINT pk_bes PRIMARY KEY(matnr,vnr,semester);

ALTER TABLE Pruefung
ADD CONSTRAINT pk_pruef PRIMARY KEY(doznr,matnr,vnr,datum);


ALTER TABLE Student
ADD CONSTRAINT fk_stud_Stg FOREIGN KEY(stgnr)
REFERENCES Studiengang(stgnr);

ALTER TABLE Veranstaltung
ADD CONSTRAINT fk_vera_stg FOREIGN KEY(stgnr)
REFERENCES Studiengang(stgnr);

ALTER TABLE Halten
ADD CONSTRAINT fk_hal_doz FOREIGN KEY(doznr)
REFERENCES Dozent(doznr);

ALTER TABLE Halten
ADD CONSTRAINT fk_hal_ver FOREIGN KEY(vnr)
REFERENCES Veranstaltung(vnr);

ALTER TABLE Besuchen
ADD CONSTRAINT fk_bes_ver FOREIGN KEY(vnr)
REFERENCES Veranstaltung(vnr);

ALTER TABLE Besuchen
ADD CONSTRAINT fk_bes_matr FOREIGN KEY(matnr)
REFERENCES Student(matnr);

ALTER TABLE Pruefung
ADD CONSTRAINT fk_pruef_doz FOREIGN KEY(doznr)
REFERENCES Dozent(doznr);

ALTER TABLE Pruefung
ADD CONSTRAINT fk_pruef_stud FOREIGN KEY(matnr)
REFERENCES Student(matnr);

ALTER TABLE Pruefung
ADD CONSTRAINT fk_pruef_ver FOREIGN KEY(vnr)
REFERENCES Veranstaltung(vnr);

ALTER TABLE Dozent
ADD CONSTRAINT c_dozges CHECK (UPPER(geschlecht)in ('M','W'));

ALTER TABLE Student
ADD CONSTRAINT c_studges CHECK (UPPER(geschlecht) in ('M','W'));

ALTER TABLE Pruefung
ADD CONSTRAINT c_preufnote CHECK(note in (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0));

ALTER TABLE Veranstaltung
ADD CONSTRAINT c_vertyp CHECK (vtyp='Vorlesung'OR vtyp='Uebung'OR vtyp='Praktikum'OR vtyp='Projekt');

ALTER Table Halten
ADD CONSTRAINT c_halsem CHECK(semester LIKE 'SS____' OR semester LIKE 'WS____/__');

ALTER Table BESUCHEN
ADD CONSTRAINT c_bessem CHECK(semester LIKE 'SS____' OR semester LIKE 'WS____/__');

CREATE SEQUENCE doznr_seq
START WITH 1
INCREMENT BY 1;


CREATE SEQUENCE matnr_seq
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE vnr_seq
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE stgnr_seq
START WITH 1
INCREMENT BY 1;

CREATE TABLE Fachbereich
(
	fbnr INTEGER,
	fbname VARCHAR(50)
);

ALTER TABLE Fachbereich
ADD CONSTRAINT pk_fachb PRIMARY KEY(fbnr);

CREATE SEQUENCE fbnr_seq
START WITH 1
INCREMENT BY 1;

ALTER TABLE Dozent
ADD fbnr INTEGER;

ALTER TABLE Dozent
ADD CONSTRAINT fk_dozfb FOREIGN KEY(fbnr)
REFERENCES Fachbereich(fbnr);

ALTER TABLE Studiengang
ADD fbnr INTEGER;

ALTER TABLE Studiengang
ADD CONSTRAINT fk_studfb FOREIGN KEY(fbnr)
REFERENCES Fachbereich(fbnr);

ALTER TABLE Dozent
DROP COLUMN fachbereich;

ALTER TABLE Studiengang
DROP COLUMN fachbereich;


select * from Dozent;
INSERT into Fachbereich values (fbnr_seq.nextval,'Informatik');
INSERT into Dozent values (doznr_seq.nextval,'Paul','Guenther','01.01.1980','M','A125','01234/5678','paulguenther@gmail.com','02.04.2011','Systemarchitektur',fbnr_seq.currval);
INSERT into Studiengang values(stgnr_seq.nextval,'Medieninformatik',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Adrian','Phelix','03.05.1993','M','adrianwinchester@gmail.com','04.03.2015',stgnr_seq.currval);
INSERT into Veranstaltung values(vnr_seq.nextval,'GDI','Vorlesung',stgnr_seq.currval);

INSERT into Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT into HALTEN values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT into Pruefung values (doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'13.01.11',3.7);

INSERT INTO dozent values(doznr_seq.nextval,'Brill','Manfred','05.08.1984','M','A134','424242/2424','mafredbrill@gmail.com','02.04.05','Computergrafik',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Kowollik','Dennith','07.03.91','M','dennithwinchester@gmail.com','02.03.15',stgnr_seq.currval);
INSERT INTO Veranstaltung values(vnr_seq.nextval,'MAT1','Vorlesung',stgnr_seq.currval);
INSERT INTO Halten values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Pruefung values(doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'11.01.11',3.3);

INSERT INTO Veranstaltung values(vnr_seq.nextval,'GDI-Uebung','Uebung',stgnr_seq.currval);
INSERT INTO Halten values(3,vnr_seq.currval,'WS2010/11');
INSERT INTO BESUCHEN values(3,vnr_seq.currval,'WS2010/11');

INSERT INTO Veranstaltung values(vnr_seq.nextval,'GDI-Uebung','Uebung',stgnr_seq.currval);
INSERT INTO Halten values(3,vnr_seq.currval,'WS2010/11');
INSERT INTO BESUCHEN values(2,vnr_seq.currval,'WS2010/11');


INSERT INTO dozent values(doznr_seq.nextval,'Hettel','Edgar','05.08.1970','M','A117','98765/5783','hetteledgar@gmail.com','09.02.03','Programmiertechniken',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Sander','Marthel','13.11.89','M','sandermarthel@gemail.com','02.03.15',stgnr_seq.currval);
INSERT INTO Veranstaltung values(vnr_seq.nextval,'Rechenarchitektur','Vorlesung',stgnr_seq.currval);
INSERT INTO Halten values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Pruefung values(doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'27.01.11',2.3);

INSERT INTO dozent values(doznr_seq.nextval,'Steffens','Hans','27.01.1976','M','A008','987644578/456','steffenshans@gmail.com','17.12.00','Computerdesign',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Bonny','Andreas','07.03.96','M','bonnyAndreas@gmail.com','02.03.14',stgnr_seq.currval);
INSERT INTO Veranstaltung values(vnr_seq.nextval,'Prog2','Vorlesung',stgnr_seq.currval);
INSERT INTO Halten values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Pruefung values(doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'20.01.11',1.0);

INSERT into Fachbereich values (fbnr_seq.nextval,'Mathe');
INSERT into Studiengang values(stgnr_seq.nextval,'Mathematik',fbnr_seq.currval);

INSERT INTO dozent values(doznr_seq.nextval,'Mueller','Joerg','30.10.1990','M','A100','31431/2617','muellerjoerg@gmail.com','18.07.98','Logik',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Kos','Amina','18.06.97','W','kosamina@gmail.com','02.03.15',stgnr_seq.currval);
INSERT INTO Veranstaltung values(vnr_seq.nextval,'Logik','Vorlesung',stgnr_seq.currval);
INSERT INTO Halten values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Pruefung values(doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'01.02.11',2.0);


INSERT INTO dozent values(doznr_seq.nextval,'Mustermann','Max','01.01.2000','M','A001','9999/999','mustermannmax@gmail.com','01.01.01','Musterbereich',fbnr_seq.currval);
INSERT INTO Student values(matnr_seq.nextval,'Luhan','Klaus','07.03.95','M','luhanklaus@gmail.com','02.03.15',stgnr_seq.currval);
INSERT INTO Veranstaltung values(vnr_seq.nextval,'Mat2','Vorlesung',stgnr_seq.currval);
INSERT INTO Halten values(doznr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Besuchen values(matnr_seq.currval,vnr_seq.currval,'WS2010/11');
INSERT INTO Pruefung values(doznr_seq.currval,matnr_seq.currval,vnr_seq.currval,'01.01.11',1.0);

UPDATE Dozent set buero ='H246' where doznr = 3;
UPDATE Student set name = 'Trophobie' where matnr = 7;
UPDATE Student set vorname= 'Klaus' where matnr = 7;
UPDATE Pruefung set note = 2.3 where matnr = 7;

DELETE from besuchen where matnr= 5;
DELETE from pruefung where matnr= 5;
DELETE from Student where matnr = 5;

commit;


rollback;
drop trigger t_doznr;
drop trigger t_vnr;
drop trigger t_stgnr;
drop trigger t_fbnr;
drop trigger t_matnr;
Drop Table Pruefungsprotokoll
CASCADE Constraints;
Drop SEQUENCE protnr_seq;

-- Aufgabe 1)
-- a)

Create Trigger t_doznr
before insert on dozent
for each row
begin
	select doznr_seq.nextval into :new.doznr from dual;
End;
/

Create Trigger t_vnr
before insert on veranstaltung
for each row
begin
  select vnr_seq.nextval into :new.vnr from dual;
End;
/

Create Trigger t_stgnr
before insert on Studiengang
for each row
begin
  select stgnr_seq.nextval into :new.stgnr from dual;
End;
/

Create Trigger t_fbnr
before insert on Fachbereich
for each row
begin
  select fbnr_seq.nextval into :new.fbnr from dual;
End;
/

Create Trigger t_matnr
before insert on Student
for each row
begin
  select matnr_seq.nextval into :new.matnr from dual;
End;
/

-- b)
insert into Dozent values (null,'Cullmann','petra','04.04.1980','M','A222','6666/6666','cullmannpetra@gmx.com','01.01.2016','Deutsch',(select fbnr from Fachbereich where fbname='Informatik'));
insert into Veranstaltung values(null,'was ist feuer','Vorlesung',(select stgnr from studiengang where bezeichnung = 'Mathematik'));
insert into Studiengang values(null,'Philosophie',(select fbnr from Fachbereich where fbname ='Informatik'));
insert into Fachbereich values(null,'fachsippeln');
insert into Student values(null,'Lindenau','Hans','20.4.1904','M','420snoopdog@gmx.de','20.4.2004',(select stgnr from Studiengang where bezeichnung ='420'));


--Aufgabe 2
-- a)

Alter Table Dozent
add einnahmen decimal(10,2) DEFAULT(0.0);

-- b)
Update dozent d
set einnahmen = (select count(Distinct vnr) from Pruefung p where d.doznr=p.doznr)*35;

--c)
drop Trigger t_doz_einnahmen;

create Trigger t_doz_einnahmen
After insert on Pruefung
referencing new as pruef
for each row
begin
  Update dozent set einnahmen = einnahmen+35  where doznr = :pruef.doznr;
End;
/

--d)
insert into Pruefung values(2,3,2,'10.3.2010',2.3);
insert into Pruefung values(2,2,3,'10.3.2010',2.0);

-- Aufgabe 3

--a)
Alter Table Student
ADD avg_note decimal(2,1);

-- b)
Update Student s
set avg_note=(select avg(note) from Pruefung p where s.matnr = p.matnr);

-- c)
Drop Trigger t_stud_avgnote;

create Trigger t_stud_avgnote
After insert on pruefung
begin
  Update Student s set avg_note=(select avg(note) from Pruefung p where  s.matnr = p.matnr );
End;
/

-- d)

insert into Pruefung values(2,6,4,'10.10.2010',1.0);
insert into Pruefung values(2,7,4,'10.10.2010',5.0);
insert into Pruefung values(2,7,2,'10.11.2010',1.0);

--Aufgabe 4

--a)
Create Table Pruefungsprotokoll
(
  protnr          INTEGER,
  datum           date,
  benutzer        Varchar(20),
  ereignis        Varchar(50),
  preuf_datum_alt date,
  pruef_datum_neu date,
  pruef_note_alt  Decimal(2,1),
  preuf_note_neu  Decimal(2,1)
);

Alter Table Pruefungsprotokoll
ADD CONSTRAINT pk_preufprot PRIMARY KEY(protnr);

Create Sequence protnr_seq
Start WITH 1
INCREMENT BY 1;

-- b)
Drop Trigger t_watchdog_pruefung;

CREATE Trigger t_watchdog_pruefung
AFTER INSERT OR DELETE OR  UPDATE  on Pruefung
for each row --GEHT EH NIT 80% SAVE CALL

BEGIN
  if inserting then
  INSERT into Pruefungsprotokoll values( protnr_seq.nextval,(select sysdate from dual),user,'INSERTING',NULL,:new.datum,NULL,:new.note);
end if;

if deleting then
   INSERT into Pruefungsprotokoll values(protnr_seq.nextval,(select sysdate from dual),user,'DELETING',:old.datum,NULL,:old.note,NULL);
end if;

if updating then
   INSERT into Pruefungsprotokoll values(protnr_seq.nextval,(select sysdate from dual),user,'UPDATING',:old.datum,:new.datum,:old.note,:new.note);
end if;
End;
/

--c)
insert into Pruefung values(3,2,9,'10.11.2010',2.0);
Update Pruefung set note = 3.0 where matnr=2 AND vnr = 9;
DELETE from Pruefung where matnr = 2 AND vnr = 9;