139 lines
4.3 KiB
SQL
139 lines
4.3 KiB
SQL
USE db_vorlesung;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
DROP TABLE IF EXISTS Saal;
|
|
DROP TABLE IF EXISTS Sitzplatz;
|
|
DROP TABLE IF EXISTS Reservierung;
|
|
DROP TABLE IF EXISTS Reservierung_Log;
|
|
DROP TABLE IF EXISTS Vorstellung;
|
|
DROP TABLE IF EXISTS Film;
|
|
DROP TABLE IF EXISTS Schauspieler;
|
|
DROP TABLE IF EXISTS nm_Reservierung_Sitzplatz;
|
|
DROP TABLE IF EXISTS nm_Film_Schauspieler;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
CREATE TABLE IF NOT EXISTS Saal (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Name VARCHAR(50) NOT NULL,
|
|
Beschreibung MEDIUMTEXT,
|
|
UNIQUE u_saal_name (Name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS Sitzplatz (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Reihe INTEGER UNSIGNED NOT NULL,
|
|
Platz INTEGER UNSIGNED NOT NULL,
|
|
Preis FLOAT UNSIGNED NOT NULL,
|
|
fk_Saal_ID INTEGER UNSIGNED NOT NULL,
|
|
UNIQUE u_sitzplatz (Reihe, Platz, fk_Saal_ID),
|
|
CONSTRAINT fk_sitzplatz_saal_id
|
|
FOREIGN KEY (fk_Saal_ID)
|
|
REFERENCES Saal (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS Schauspieler (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Vorname VARCHAR(255) NOT NULL,
|
|
Nachname VARCHAR(255) NOT NULL,
|
|
Geburtstag DATE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS Film (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Name VARCHAR(255) NOT NULL,
|
|
Spieldauer INTEGER UNSIGNED NOT NULL,
|
|
Bewertung FLOAT UNSIGNED NOT NULL,
|
|
Beschreibung MEDIUMTEXT,
|
|
INDEX i_film_name (Name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS Vorstellung (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Beginn DATETIME NOT NULL,
|
|
fk_Film_ID INTEGER UNSIGNED NOT NULL,
|
|
fk_Saal_ID INTEGER UNSIGNED NOT NULL,
|
|
CONSTRAINT fk_vorstellung_film_id
|
|
FOREIGN KEY (fk_Film_ID)
|
|
REFERENCES Film (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE RESTRICT,
|
|
CONSTRAINT fk_vorstellung_saal_id
|
|
FOREIGN KEY (fk_Saal_ID)
|
|
REFERENCES Saal (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS Reservierung (
|
|
ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Vorname VARCHAR(255) NOT NULL,
|
|
Nachname VARCHAR(255) NOT NULL,
|
|
Mail VARCHAR(255) NOT NULL,
|
|
fk_Vorstellung_ID INTEGER UNSIGNED NOT NULL,
|
|
INDEX i_reservierung_name (Vorname, Nachname),
|
|
INDEX i_reservierung_name2 (Nachname),
|
|
CONSTRAINT fk_reservierung_vorstellung_id
|
|
FOREIGN KEY (fk_Vorstellung_ID)
|
|
REFERENCES Vorstellung (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE Reservierung_Log LIKE Reservierung;
|
|
select
|
|
concat('ALTER TABLE Reservierung_Log', GROUP_CONCAT(' MODIFY COLUMN ', c.COLUMN_NAME, ' ', c.COLUMN_TYPE)) as alter_statement INTO @prep_statement
|
|
from information_schema.COLUMNS c
|
|
where
|
|
c.IS_NULLABLE = 'NO'
|
|
and c.TABLE_SCHEMA = 'db_vorlesung'
|
|
and c.TABLE_NAME = 'Reservierung_Log'
|
|
group by c.TABLE_NAME;
|
|
PREPARE query_nullable FROM @prep_statement;
|
|
EXECUTE query_nullable;
|
|
DEALLOCATE PREPARE query_nullable;
|
|
|
|
ALTER TABLE Reservierung_Log DROP PRIMARY KEY;
|
|
ALTER TABLE Reservierung_Log ADD `LogID` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
|
|
ALTER TABLE Reservierung_Log ADD `VorheigeID` INTEGER UNSIGNED;
|
|
ALTER TABLE Reservierung_Log ADD `Zeitstempel` TIMESTAMP DEFAULT CURRENT_TIMESTAMP() NOT NULL;
|
|
ALTER TABLE Reservierung_Log ADD `is_Erfolgreich` BIT NOT NULL;
|
|
ALTER TABLE Reservierung_Log ADD `Methode` ENUM ('insert', 'update', 'delete') NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS nm_Film_Schauspieler (
|
|
fk_Film_ID INTEGER UNSIGNED NOT NULL,
|
|
fk_Schauspieler_ID INTEGER UNSIGNED NOT NULL,
|
|
PRIMARY KEY (fk_Film_ID, fk_Schauspieler_ID),
|
|
CONSTRAINT fk_nm_film_schauspieler_film_id
|
|
FOREIGN KEY (fk_Film_ID)
|
|
REFERENCES Film (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_nm_film_schauspieler_schauspieler_id
|
|
FOREIGN KEY (fk_Schauspieler_ID)
|
|
REFERENCES Schauspieler (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS nm_Reservierung_Sitzplatz (
|
|
fk_Reservierung_ID INTEGER UNSIGNED NOT NULL,
|
|
fk_Sitzplatz_ID INTEGER UNSIGNED NOT NULL,
|
|
PRIMARY KEY pk_nm_reservierung_sitzplatz_reservierung (fk_Reservierung_ID, fk_Sitzplatz_ID),
|
|
CONSTRAINT fk_nm_reservierung_sitzplatz_reservierung_id
|
|
FOREIGN KEY (fk_Reservierung_ID)
|
|
REFERENCES Reservierung (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_nm_reservierung_sitzplatz_sitzplatz_id
|
|
FOREIGN KEY (fk_Sitzplatz_ID)
|
|
REFERENCES Sitzplatz (ID)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
|