DROP TABLE IF EXISTS attend;
DROP TABLE IF EXISTS require;
DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS Lectures;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Assistants;
DROP TABLE IF EXISTS Professors;

CREATE TABLE Students
       (StudNr         INTEGER PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Semester       INTEGER,
        birthdate      date);

CREATE TABLE Professors
       (PersNr         INTEGER PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Level          CHAR(2) CHECK (Level in ('C2', 'C3', 'C4')),
        Room           INTEGER UNIQUE,
        birthdate      date);

CREATE TABLE Assistants
       (PersNr         INTEGER PRIMARY KEY,
        Name           VARCHAR(30) NOT NULL,
        Area           VARCHAR(30),
        Boss           INTEGER,
        birthdate      date,
        FOREIGN KEY    (Boss) REFERENCES Professors);

CREATE TABLE Lectures
       (LectureNr      INTEGER PRIMARY KEY,
        Title          VARCHAR(30),
        WeeklyHours    INTEGER,
        GivenBy        INTEGER REFERENCES Professors);

CREATE TABLE attend
       (StudNr         INTEGER REFERENCES Students ON DELETE CASCADE,
        LectureNr      INTEGER REFERENCES Lectures ON DELETE CASCADE,
        PRIMARY KEY    (StudNr, LectureNr));

CREATE TABLE require
       (Predecessor    INTEGER REFERENCES Lectures ON DELETE CASCADE,
        Successor      INTEGER REFERENCES Lectures ON DELETE NO ACTION,
        PRIMARY KEY    (Predecessor, Successor));

CREATE TABLE test
       (StudNr         INTEGER REFERENCES Students ON DELETE CASCADE,
        LectureNr      INTEGER REFERENCES Lectures,
        PersNr         INTEGER REFERENCES Professors,
        Grade          NUMERIC(2,1) CHECK (Grade between 0.7 and 5.0),
        PRIMARY KEY    (StudNr, LectureNr));


INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (24002, 'Xenokrates', 18, '1989-03-14'); 
 
INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (25403, 'Jonas', 12, '1994-01-14'); 
 
INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (26120, 'Fichte', 10, '1984-05-12'); 

INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (26830, 'Aristoxenos', 8, '1993-06-25'); 
 
INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (27550, 'Schopenhauer', 6, '1995-11-03'); 

INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (28106, 'Carnap', 3, '1990-08-30'); 
 
INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (29120, 'Theophrastos', 2, '1994-01-14'); 
 
INSERT INTO Students(StudNr, Name, Semester, birthdate) 
VALUES (29555, 'Feuerbach', 2, '1993-07-01'); 
 


INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2125, 'Sokrates', 'C4', 226, '1975-03-17'); 

INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2126, 'Russel', 'C4', 232, '1979-01-27');  
 
INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2127, 'Kopernikus', 'C3', 310, '1982-01-01'); 
 
INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2133, 'Popper', 'C3', 052, '1965-02-20'); 
 
INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2134, 'Augustinus', 'C3', 309, '1977-03-17'); 
 
INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2136, 'Curie', 'C4', 036, '1985-12-24'); 
 
INSERT INTO Professors(PersNr, Name, Level, Room, birthdate) 
VALUES (2137, 'Kant', 'C4', 007, '1969-08-03'); 
 
 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3002, 'Platon', 'Ideenlehre', 2125, '1988-10-23'); 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3003, 'Aristoteles', 'Syllogistik', 2125, '1985-11-28'); 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3004, 'Wittgenstein', 'Sprachtheorie', 2126, '1982-3-25'); 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3005, 'Rhetikus', 'Planetenbewegung', 2127, '1989-9-15'); 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3006, 'Newton', 'Keplersche Gesetze', 2127, '1981-7-30'); 
 
INSERT INTO Assistants(PersNr, Name, Area, Boss, birthdate) 
VALUES (3007, 'Spinoza', 'Gott und Natur', 2134, '1982-4-11'); 

 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5001, 'Grundzuege', 4, 2137);

INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5041, 'Ethik', 4, 2125);
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5043, 'Erkenntnistheorie', 3, 2126);
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5049, 'Maeeutik', 2, 2125);
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (4052, 'Logik', 4, 2125);
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5052, 'Wissenschaftstheorie', 3, 2126); 
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5216, 'Bioethik', 2, 2126); 
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5259, 'Der Wiener Kreis', 2, 2133); 
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (5022, 'Glaube und Wissen', 2, 2134); 
 
INSERT INTO Lectures(LectureNr, Title, WeeklyHours, GivenBy) 
VALUES (4630, 'Die 3 Kritiken', 4, 2137); 


 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (26120, 5001); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (27550, 5001); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (27550, 4052); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (28106, 5041); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (28106, 5052); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (28106, 5216); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (28106, 5259); 
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (29120, 5001); 

INSERT INTO attend(StudNr, LectureNr) 
VALUES (29120, 5041); 

INSERT INTO attend(StudNr, LectureNr) 
VALUES (29120, 5049); 

INSERT INTO attend(StudNr, LectureNr) 
VALUES (29555, 5022); 

INSERT INTO attend(StudNr, LectureNr) 
VALUES (25403, 5022);  
 
INSERT INTO attend(StudNr, LectureNr) 
VALUES (29555, 5001);  
 
 
 
INSERT INTO require(Predecessor, Successor) 
VALUES (5001, 5041); 
 
INSERT INTO require(Predecessor, Successor) 
VALUES (5001, 5043); 
 
INSERT INTO require(Predecessor, Successor) 
VALUES (5001, 5049); 
 
INSERT INTO require(Predecessor, Successor) 
VALUES (5041, 5216);  

INSERT INTO require(Predecessor, Successor) 
VALUES (5043, 5052);  

INSERT INTO require(Predecessor, Successor) 
VALUES (5041, 5052); 
 
INSERT INTO require(Predecessor, Successor) 
VALUES (5052, 5259); 
 


INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (28106, 5001, 2126, 1.0);

INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (25403, 5041, 2125, 2.0);

INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (25403, 5001, 2125, 2.7);

INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (27550, 4630, 2137, 2.0);

INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (27550, 5043, 2137, 1.3);

INSERT INTO test(StudNr, LectureNr, PersNr, Grade) 
VALUES (27550, 5001, 2137, 1.0);
