#CREATE DATABASE Hochschule
DROP TABLE IF EXISTS Lehrveranstaltung_Studiengang;
DROP TABLE IF EXISTS Studiengang_Dozent;
DROP TABLE IF EXISTS Student_Lehrveranstaltung;
DROP TABLE IF EXISTS Assistent_Dozent;
DROP TABLE IF EXISTS Dozent_Lehrveranstaltung;
DROP TABLE IF EXISTS Raum_Lehrveranstaltung;
DROP TABLE IF EXISTS Lehrveranstaltung;
DROP TABLE IF EXISTS Raum;
DROP TABLE IF EXISTS Assistent;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Studiengang;
DROP TABLE IF EXISTS Fachbereich;
DROP TABLE IF EXISTS Dozent;
CREATE TABLE Dozent
(
id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,
Adresse VARCHAR(100) NOT NULL,
Tel INT NOT NULL,
Photo BLOB NOT NULL,
PRIMARY KEY (id),
CHECK (Name!='')
) ENGINE=INNODB;
CREATE TABLE Fachbereich
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
dekan VARCHAR(100) NOT NULL,
gebaeude VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE Studiengang
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
fachbereich INT NOT NULL,
PRIMARY KEY (id),
FOREIGN Key (fachbereich) REFERENCES Fachbereich(id)
) ENGINE=INNODB;
CREATE TABLE Student
(
id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,
Adresse VARCHAR(100) NOT NULL,
Studiengang INT NOT NULL,
PRIMARY KEY (id),
FOREIGN Key (Studiengang) REFERENCES Studiengang(id)
) ENGINE=INNODB;
CREATE TABLE Assistent
(
id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,
Adresse VARCHAR(100) NOT NULL,
Tel INT NOT NULL,
Studiengang INT NOT NULL,
PRIMARY KEY (id),
FOREIGN Key (Studiengang) REFERENCES Studiengang(id)
) ENGINE=INNODB;
CREATE TABLE Raum
(
id INT NOT NULL AUTO_INCREMENT,
Plaetze INT NOT NULL,
Ausruestung VARCHAR(100),
PRIMARY KEY (id),
CHECK (Plaetze>0)
) ENGINE=INNODB;
CREATE TABLE Lehrveranstaltung
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
themen VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE(themen)
) ENGINE=INNODB;
# Relationstabellen
CREATE TABLE Raum_Lehrveranstaltung
(
Raum_id INT NOT NULL,
Lehrveranstaltung_id INT NOT NULL,
FOREIGN Key (Raum_id) REFERENCES Raum(id),
FOREIGN Key (Lehrveranstaltung_id) REFERENCES Lehrveranstaltung(id)
) ENGINE=INNODB;
CREATE TABLE Dozent_Lehrveranstaltung
(
Dozent_id INT NOT NULL,
Lehrveranstaltung_id INT NOT NULL,
FOREIGN Key (Dozent_id) REFERENCES Dozent(id),
FOREIGN Key (Lehrveranstaltung_id) REFERENCES Lehrveranstaltung(id)
) ENGINE=INNODB;
CREATE TABLE Assistent_Dozent
(
Assistent_id INT NOT NULL,
Dozent_id INT NOT NULL,
FOREIGN Key (Assistent_id) REFERENCES Assistent(id),
FOREIGN Key (Dozent_id) REFERENCES Dozent(id)
) ENGINE=INNODB;
CREATE TABLE Student_Lehrveranstaltung
(
Student_id INT NOT NULL,
Lehrveranstaltung_id INT NOT NULL,
FOREIGN Key (Student_id) REFERENCES Student(id),
FOREIGN Key (Lehrveranstaltung_id) REFERENCES Lehrveranstaltung(id)
) ENGINE=INNODB;
CREATE TABLE Studiengang_Dozent
(
Studiengang_id INT NOT NULL,
Dozent_id INT NOT NULL,
FOREIGN Key (Studiengang_id) REFERENCES Studiengang(id),
FOREIGN Key (Dozent_id) REFERENCES Dozent(id)
) ENGINE=INNODB;
CREATE TABLE Lehrveranstaltung_Studiengang
(
Lehrveranstaltung_id INT NOT NULL,
Studiengang_id INT NOT NULL,
FOREIGN Key (Lehrveranstaltung_id) REFERENCES Lehrveranstaltung(id),
FOREIGN Key (Studiengang_id) REFERENCES Studiengang(id)
) ENGINE=INNODB;