DROP TABLE Pruefung;
DROP TABLE Modul;
DROP TABLE Studierend;

CREATE TABLE Studierend(
  matNr INTEGER,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT PK_Studierend PRIMARY KEY (matNr)
);

CREATE TABLE Modul(
  mid INTEGER,
  titel VARCHAR(32) NOT NULL,
  semester INTEGER NOT NULL,
  cp INTEGER NOT NULL,
  CONSTRAINT PK_Modul PRIMARY KEY (mid),
  CONSTRAINT bachelorsemester CHECK (semester > 0 AND semester < 7),
  CONSTRAINT leistungspunkte CHECK (cp > 0 AND cp < 30)
);

CREATE TABLE Pruefung (
  matNr INTEGER,
  mid INTEGER,
  datum DATE NOT NULL,
  versuch INTEGER,
  note INTEGER NOT NULL,
  CONSTRAINT PK_Pruefung PRIMARY KEY (matNr,mid,versuch),
  CONSTRAINT FK_Pruefung_Studierend FOREIGN KEY (matNr) 
                                 REFERENCES Studierend(matNr),
  CONSTRAINT FK_Pruefung_Modul FOREIGN KEY (mid) 
                                 REFERENCES Modul(mid),
  CONSTRAINT Pruefung_eineProTag UNIQUE(matNr,mid,datum),
  CONSTRAINT Pruefung_Versuch CHECK (versuch in (1,2,3)),
  CONSTRAINT Pruefung_Note 
       CHECK (note in (100,130,170,200,230,270,300,330,370,400,500))
);

INSERT INTO Studierend VALUES (444,'Oleg');
INSERT INTO Studierend VALUES (445,'Helga');
INSERT INTO Studierend VALUES (446,'Olga');
INSERT INTO Studierend VALUES (447,'Abdul');
INSERT INTO Studierend VALUES (448,'Sila');
INSERT INTO Studierend VALUES (449,'Sven');
INSERT INTO Studierend VALUES (450,'Erik');
INSERT INTO Studierend VALUES (451,'Sarah');

INSERT INTO Modul VALUES (11,'Grundlagen Programmierung',1,10);
INSERT INTO Modul VALUES (12,'Grundlagen Mathematik',1,10);
INSERT INTO Modul VALUES (13,'Wissenschaftliches Arbeiten',1,5);
INSERT INTO Modul VALUES (14,'Elektrotechnische Grundlagen',1,5);
INSERT INTO Modul VALUES (21,'Fortgeschrittene Programmierung',2,10);
INSERT INTO Modul VALUES (22,'Mathematik für Informatik',2,5);
INSERT INTO Modul VALUES (23,'Kommunikationsnetze',2,5);
INSERT INTO Modul VALUES (24,'Physikalische Grundlagen',2,5);
INSERT INTO Modul VALUES (25,'Digitaltechnik',2,5);
INSERT INTO Modul VALUES (31,'Englisch',3,5);
INSERT INTO Modul VALUES (32,'OO Analyse und Design',3,5);
INSERT INTO Modul VALUES (33,'Mathematik für TI',3,5);
INSERT INTO Modul VALUES (34,'Algorithmen und Datenstrukturen',3,5);
INSERT INTO Modul VALUES (35,'Datenbanken',3,5);
INSERT INTO Modul VALUES (36,'Rechnerarchitektur',3,5);

/* darf alle Pruefungen machen, fehlt pro Semester mindestens eine */
INSERT INTO Pruefung VALUES (444,11,'2013-10-11',1,500);
INSERT INTO Pruefung VALUES (444,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (444,12,'2014-02-08',2,330);
INSERT INTO Pruefung VALUES (444,13,'2013-10-13',1,230);
INSERT INTO Pruefung VALUES (444,14,'2013-10-14',1,370);
INSERT INTO Pruefung VALUES (444,21,'2014-02-03',1,500);
INSERT INTO Pruefung VALUES (444,22,'2014-02-04',1,400);
INSERT INTO Pruefung VALUES (444,23,'2014-02-05',1,170);
INSERT INTO Pruefung VALUES (444,24,'2014-02-06',1,330);
INSERT INTO Pruefung VALUES (444,25,'2014-02-07',1,270);
INSERT INTO Pruefung VALUES (444,31,'2014-10-11',1,130);
INSERT INTO Pruefung VALUES (444,35,'2014-10-15',1,130);
INSERT INTO Pruefung VALUES (444,36,'2014-10-16',1,230);

/* darf gerade Pruefungen des dritten Semesters machen, ist in
 * einem Drittversuch */
INSERT INTO Pruefung VALUES (445,11,'2013-10-11',1,130);
INSERT INTO Pruefung VALUES (445,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (445,12,'2014-02-08',2,500);
INSERT INTO Pruefung VALUES (445,13,'2013-10-13',1,230);
INSERT INTO Pruefung VALUES (445,14,'2013-10-14',1,370);
INSERT INTO Pruefung VALUES (445,22,'2014-02-04',1,200);
INSERT INTO Pruefung VALUES (445,23,'2014-02-05',1,170);
INSERT INTO Pruefung VALUES (445,24,'2014-02-06',1,130);
INSERT INTO Pruefung VALUES (445,25,'2014-02-07',1,370);


/* in zwei Modulen im dritten Versuch, zu wenig CP für das
 * dritte Semester */
INSERT INTO Pruefung VALUES (446,11,'2013-10-11',1,500);
INSERT INTO Pruefung VALUES (446,11,'2014-02-09',2,500);
INSERT INTO Pruefung VALUES (446,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (446,12,'2014-02-08',2,500);
INSERT INTO Pruefung VALUES (446,13,'2013-10-13',1,300);
INSERT INTO Pruefung VALUES (446,21,'2014-02-03',1,400);
INSERT INTO Pruefung VALUES (446,22,'2014-02-04',1,500);
INSERT INTO Pruefung VALUES (446,23,'2014-02-05',1,270);

/* darf gerade nicht ins dritte Semester, da 5 CP fehlen
 * ist im dritten Versuch bei 12 */
INSERT INTO Pruefung VALUES (447,11,'2013-10-11',1,130);
INSERT INTO Pruefung VALUES (447,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (447,12,'2014-02-08',2,500);
INSERT INTO Pruefung VALUES (447,13,'2013-10-13',1,100);
INSERT INTO Pruefung VALUES (447,14,'2013-10-14',1,200);
INSERT INTO Pruefung VALUES (447,21,'2014-02-03',1,270);
INSERT INTO Pruefung VALUES (447,23,'2014-02-05',1,170);
INSERT INTO Pruefung VALUES (447,24,'2014-02-06',1,230);

/* alle Pruefungen gemacht und bestanden*/
INSERT INTO Pruefung VALUES (448,11,'2013-10-11',1,130);
INSERT INTO Pruefung VALUES (448,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (448,12,'2014-02-08',2,330);
INSERT INTO Pruefung VALUES (448,13,'2013-10-13',1,230);
INSERT INTO Pruefung VALUES (448,14,'2013-10-14',1,370);
INSERT INTO Pruefung VALUES (448,21,'2014-02-03',1,100);
INSERT INTO Pruefung VALUES (448,22,'2014-02-04',1,200);
INSERT INTO Pruefung VALUES (448,23,'2013-02-05',1,170);
INSERT INTO Pruefung VALUES (448,24,'2014-02-06',1,330);
INSERT INTO Pruefung VALUES (448,25,'2014-02-07',1,270);
INSERT INTO Pruefung VALUES (448,31,'2014-10-11',1,130);
INSERT INTO Pruefung VALUES (448,32,'2014-10-12',1,100);
INSERT INTO Pruefung VALUES (448,33,'2014-10-13',1,130);
INSERT INTO Pruefung VALUES (448,34,'2014-10-14',1,230);
INSERT INTO Pruefung VALUES (448,35,'2014-10-15',1,130);
INSERT INTO Pruefung VALUES (448,36,'2014-10-16',1,230);

/* nur wenige Pruefungen des ersten Semesters */
INSERT INTO Pruefung VALUES (449,11,'2013-10-11',1,500);
INSERT INTO Pruefung VALUES (449,11,'2014-02-09',2,500);
INSERT INTO Pruefung VALUES (449,11,'2014-10-11',3,400);
INSERT INTO Pruefung VALUES (449,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (449,12,'2014-02-08',2,500);
INSERT INTO Pruefung VALUES (449,13,'2013-10-13',1,230);

/* endgueltig in 11 durchgefallen */
INSERT INTO Pruefung VALUES (450,12,'2013-10-12',1,500);
INSERT INTO Pruefung VALUES (450,12,'2014-02-08',2,400);
INSERT INTO Pruefung VALUES (450,13,'2013-10-13',1,330);
INSERT INTO Pruefung VALUES (450,11,'2013-10-11',1,500);
INSERT INTO Pruefung VALUES (450,11,'2014-02-09',2,500);
INSERT INTO Pruefung VALUES (450,11,'2014-10-11',3,500);

COMMIT;