DROP TABLE Student;
DROP TABLE Professor;
DROP TABLE Mitarbeiter;
DROP TABLE Veranstaltung;



CREATE TABLE Student (
  matNr				integer NOT NULL,
  name				varchar(30),
  adresse			varchar(50),
  studienfach	varchar(30),
  PRIMARY KEY (matNr),
  CHECK (matNr > 0),
  CHECK (studienfach IN
    ('Informatik', 'Maschinenbau', 'Informationswirtschaft'))
);

CREATE TABLE Professor (
  name				varchar(30) NOT NULL,
  fakultät		varchar(30) NOT NULL,
  fachgebiet	varchar(50),
  PRIMARY KEY (name),
  CHECK (fachgebiet IN
    ('Informatik', 'Maschinenbau', 'Informationswirtschaft'))
);

CREATE TABLE Mitarbeiter (
  name				varchar(30) NOT NULL,
  chef				varchar(30),
  zimmerNr		integer,
  PRIMARY KEY (name),
  FOREIGN KEY (chef)
    REFERENCES Professor
    ON DELETE SET NULL
);

CREATE TABLE Veranstaltung (
  nummer			integer NOT NULL,
  typ					varchar(20),
  name	 			varchar(30),
  dozent			varchar(30),
  hoersaal		varchar(20),
  PRIMARY KEY (nummer),
  FOREIGN KEY (dozent)
    REFERENCES Professor
    ON DELETE SET NULL,
  CHECK (typ IN ('Vorlesung', 'Uebung', 'Seminar')),
  CHECK (nummer > 0)
);

INSERT INTO Student VALUES
(1043232, 'Hans Schmidt', 'Baumweg 3, 76131 Karlsruhe', 'Informatik');

INSERT INTO Student VALUES
(1000542, 'Tim Mueller', null,
'Maschinenbau');

INSERT INTO Student
(name, matNr, studienfach) VALUES
('Fred Sachs', 1009345, 'Informatik');

DELETE FROM Student
WHERE adresse IS NULL;

DELETE FROM Student
WHERE matNr IN (
	SELECT matNr FROM Student WHERE adresse IS NULL
);

DELETE FROM Student;

UPDATE Student
SET name = 'Willi Winzig'
WHERE matNr = 1000542;

CREATE INDEX hs_index ON
Veranstaltung(hoersaal);

CREATE INDEX ma_index ON
Mitarbeiter(chef);

CREATE UNIQUE INDEX matNr_index ON
Student(matNr);

CREATE DOMAIN Fachrichtung AS varchar(50)
CHECK (value IN ('Informatik', 'Maschinenbau', 'Informationswirtschaft'));