CREATE TABLE Studenten
        (MatrNr         INTEGER PRIMARY KEY,
         Name           VARCHAR2(20) NOT NULL,
         Semester       INTEGER,
         GebDatum       DATE);

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

CREATE TABLE Assistenten
        (PersNr         INTEGER PRIMARY KEY,
         Name           VARCHAR2(20) NOT NULL,
         Fachgebiet     VARCHAR2(20),
         Boss           INTEGER,
         FOREIGN KEY    (Boss) REFERENCES Professoren,
         GebDatum       DATE);
 
CREATE TABLE Vorlesungen
        (VorlNr         INTEGER PRIMARY KEY,
         Titel          VARCHAR2(20),
         SWS            INTEGER,
         gelesenVon     INTEGER REFERENCES Professoren);

CREATE TABLE hoeren
        (MatrNr         INTEGER REFERENCES Studenten ON DELETE CASCADE,
         VorlNr         INTEGER REFERENCES Vorlesungen ON DELETE CASCADE,
         PRIMARY KEY    (MatrNr, VorlNr));
 
CREATE TABLE voraussetzen
        (Vorgaenger     INTEGER REFERENCES Vorlesungen ON DELETE CASCADE,
         Nachfolger     INTEGER REFERENCES Vorlesungen ON DELETE CASCADE,
         PRIMARY KEY    (Vorgaenger, Nachfolger));

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