DROP TABLE hoeren;
DROP TABLE voraussetzen;
DROP TABLE pruefen;
DROP TABLE Vorlesungen;
DROP TABLE Studenten;
DROP TABLE Assistenten;
DROP TABLE Professoren;

CREATE TABLE Studenten
       (MatrNr         INTEGER NOT NULL PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Semester       INTEGER);

CREATE TABLE Professoren
       (PersNr         INTEGER NOT NULL PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Rang           CHAR(2) CHECK (Rang in ('C2', 'C3', 'C4')),
        Raum           INTEGER NOT NULL UNIQUE);

CREATE TABLE Assistenten
       (PersNr         INTEGER NOT NULL PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Fachgebiet     VARCHAR(30),
        Boss           INTEGER,
        FOREIGN KEY    (Boss) REFERENCES Professoren);

CREATE TABLE Vorlesungen
       (VorlNr         INTEGER NOT NULL PRIMARY KEY,
        Titel          VARCHAR(30),
        SWS            INTEGER,
        gelesenVon     INTEGER REFERENCES Professoren);

CREATE TABLE hoeren
       (MatrNr         INTEGER NOT NULL REFERENCES Studenten ON DELETE CASCADE,
        VorlNr         INTEGER NOT NULL REFERENCES Vorlesungen ON DELETE CASCADE,
        PRIMARY KEY    (MatrNr, VorlNr));

CREATE TABLE voraussetzen
       (Vorgaenger     INTEGER NOT NULL REFERENCES Vorlesungen,
        Nachfolger     INTEGER NOT NULL REFERENCES Vorlesungen,
        PRIMARY KEY    (Vorgaenger, Nachfolger));

CREATE TABLE pruefen
       (MatrNr         INTEGER NOT NULL REFERENCES Studenten ON DELETE CASCADE,
        VorlNr         INTEGER NOT NULL REFERENCES Vorlesungen,
        PersNr         INTEGER REFERENCES Professoren,
        Note           NUMERIC(2,1) CHECK (Note between 0.7 and 5.0),
        PRIMARY KEY    (MatrNr, VorlNr));
