-- Dieses Skript erstellt und fuellt die Universitaetsdatenbank, wie sie im
-- Skript verwendet wird. 
-- Ist die Datenbank und/oder einzelne Tabellen bereits vorhanden, werden
-- sie geloescht und neu angelegt.

-- --------------------------------------------------------

DROP DATABASE IF EXISTS `UniWeb`;

CREATE DATABASE `UniWeb`;


USE `UniWeb`;


DROP TABLE IF EXISTS Studenten;

CREATE TABLE Studenten(
  MatrNr         INTEGER PRIMARY KEY,
  Name           VARCHAR(20) NOT NULL,
  Semester       INTEGER,
  GebDatum       DATE
) ENGINE=InnoDB;



DROP TABLE IF EXISTS Professoren;

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



DROP TABLE IF EXISTS Assistenten;
CREATE TABLE Assistenten (
  PersNr         INTEGER PRIMARY KEY,
  Name           VARCHAR(20) NOT NULL,
  Fachgebiet     VARCHAR(20),
  Boss           INTEGER,
  GebDatum       DATE,
  FOREIGN KEY (Boss) REFERENCES Professoren (PersNr)
) ENGINE=InnoDB;



DROP TABLE IF EXISTS Vorlesungen;

CREATE TABLE Vorlesungen (
  VorlNr         INTEGER PRIMARY KEY,
  Titel          VARCHAR(20),
  SWS            INTEGER,
  gelesenVon     INTEGER,
  FOREIGN KEY (gelesenVon) REFERENCES Professoren (PersNr)
) ENGINE=InnoDB;



DROP TABLE IF EXISTS hoeren;

CREATE TABLE hoeren (
  MatrNr         INTEGER,
  VorlNr         INTEGER,
  PRIMARY KEY (MatrNr, VorlNr),
  FOREIGN KEY (MatrNr) REFERENCES Studenten (MatrNr)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
  FOREIGN KEY (VorlNr) REFERENCES Vorlesungen (VorlNr)
      ON UPDATE CASCADE
) ENGINE=InnoDB;



DROP TABLE IF EXISTS voraussetzen;

CREATE TABLE voraussetzen (
  Vorgaenger     INTEGER,
  Nachfolger     INTEGER,
  PRIMARY KEY (Vorgaenger, Nachfolger),
  FOREIGN KEY (Vorgaenger) REFERENCES Vorlesungen (VorlNr)
      ON UPDATE CASCADE,
  FOREIGN KEY (Nachfolger) REFERENCES Vorlesungen (VorlNr)
      ON UPDATE CASCADE
) ENGINE=InnoDB;




DROP TABLE IF EXISTS pruefen;

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



-- -------------------- mit Daten fuellen --------------------------

INSERT INTO Studenten (MatrNr, Name, Semester, gebDatum) VALUES
(24002, 'Xenokrates',  19, '1975-10-23'),
(25403, 'Jonas',       12, '1973-09-18'),
(26120, 'Fichte',      10, '1967-12-04'),
(26830, 'Aristoxenos',  8, '1943-08-05'),
(27550, 'Schopenhauer', 6, '1954-06-22'),
(28106, 'Carnap',       3, '1979-10-02'),
(29120, 'Theophrastos', 2, '1948-04-19'),
(29555, 'Feuerbach',    2, '1961-02-12');


INSERT INTO Professoren (PersNr, anzVorlesungen, Name, Rang, Raum, gebDatum) VALUES
(2125, 0, 'Sokrates',   'C4', 226, '1923-08-23'),
(2126, 0, 'Russel',     'C4', 232, '1934-07-10'),
(2127, 0, 'Kopernikus', 'C3', 310, '1962-03-12'),
(2133, 0, 'Popper',     'C3',  52, '1949-09-03'),
(2134, 0, 'Augustinus', 'C3', 309, '1939-04-19'),
(2136, 0, 'Curie',      'C4',  36, '1929-05-10'),
(2137, 0, 'Kant',       'C4',   7, '1950-04-04'),
(4711, 0, 'Willi',      'C4', 339, '1951-03-24'),
(4712, 0, 'Erika',      'C3', 222, '1962-09-18');



INSERT INTO Assistenten (PersNr, Name, Fachgebiet, Boss, gebDatum) VALUES
(3002, 'Platon',       'Ideenlehre',         2125, '1966-08-14'),
(3003, 'Aristoteles',  'Syllogistik',        2125, '1970-12-23'),
(3004, 'Wittgenstein', 'Sprachtheorie',      2126, '1968-08-02'),
(3005, 'Rhetikus',     'Planetenbewegung',   2127, '1962-12-20'),
(3006, 'Newton',       'Keplersche Gesetze', 2127, '1961-11-10'),
(3007, 'Spinoza',      'Gott und Natur',     2134, '1963-02-08');


INSERT INTO Vorlesungen (VorlNr, Titel, SWS, gelesenVon) VALUES
(4052, 'Logik',                4, 2125),
(4630, 'Die 3 Kritiken',       4, 2137),
(5001, 'Grundzuege',           4, 2137),
(5022, 'Glaube und Wissen',    2, 2134),
(5041, 'Ethik',                4, 2125),
(5043, 'Erkenntnistheorie',    3, 2126),
(5049, 'MÃ¤eutik',             2, 2125),
(5052, 'Wissenschaftstheorie', 3, 2126),
(5216, 'Bioethik',             2, 2126),
(5259, 'Der Wiener Kreis',     2, 2133);


INSERT INTO hoeren (MatrNr, VorlNr) VALUES
(25403, 5022),
(26120, 5001),
(27550, 4052),
(27550, 4630),
(27550, 5001),
(27550, 5041),
(27550, 5259),
(28106, 4052),
(28106, 5216),
(28106, 5259),
(29120, 5041),
(29120, 5049),
(29555, 5001),
(29555, 5022);


INSERT INTO voraussetzen (Vorgaenger, Nachfolger) VALUES
(5001, 5041),
(5001, 5043),
(5001, 5049),
(5041, 5052),
(5041, 5216),
(5043, 5052),
(5052, 5259);


INSERT INTO pruefen (MatrNr, VorlNr, PersNr, Note) VALUES
(25403, 5041, 2125, 2.0),
(27550, 4630, 2137, 2.0),
(27550, 5001, 2126, 2.3),
(28106, 5001, 2126, 1.0);





