-- Datenbanksysteme I
-- Author: Manuel Mayr
-- Sommersemester 2009

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

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

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

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

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

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

CREATE TABLE pruefen
       (MatrNr         INTEGER REFERENCES Studenten NOT NULL,
        VorlNr         INTEGER REFERENCES Vorlesungen NOT NULL,
        PersNr         INTEGER REFERENCES Professoren NOT NULL,
        Note           NUMERIC(2,1) CHECK (Note BETWEEN 0.7 AND 5.0),
        PRIMARY KEY    (MatrNr, VorlNr));