# MySQL 5.7: Das umfassende Handbuch (Rheinwerk Verlag)
# Diese Datei enthält das Beispiel-Schema der Flughafen -Datenbank
# Falls eine Datenbank mit dem Namen FlughafenDB bereits in Ihrer
# MySQL Installation vorhanden ist, wir diese automatisch gelöscht
# und neu angelegt. Ebenfalls wird vor dem Anlegen einer Tabelle
# geprüft, ob diese bereits vorhanden ist und gegebenenfalls gelöscht.


# Legt die Datenbank FlughafenDB an 
DROP DATABASE IF EXISTS FlughafenDB;
CREATE DATABASE IF NOT EXISTS FlughafenDB;

# Setzt die Datenbank FlughafenDB als aktiv.
USE FlughafenDB;

# Erzeugt die Tabelle für die Flughäfen
DROP TABLE IF EXISTS flughafen;
CREATE TABLE IF NOT EXISTS flughafen (
    flughafen_id SMALLINT NOT NULL auto_increment,
    iata CHAR(3) DEFAULT NULL,
    icao CHAR(4) NOT NULL,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (flughafen_id),
    INDEX name_idx (name),
    INDEX iata_idx (iata),
    UNIQUE icao_unq (icao)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Fluglinien
# IATA Codes sind UNIQUE
DROP TABLE IF EXISTS fluglinie;
CREATE TABLE IF NOT EXISTS fluglinie (
    fluglinie_id SMALLINT NOT NULL auto_increment,
    iata CHAR(2) NOT NULL, 
    firmenname VARCHAR(30) DEFAULT NULL, 
    heimat_flughafen SMALLINT NOT NULL,
    PRIMARY KEY (fluglinie_id),
    UNIQUE iata_unq (iata),
    INDEX heimat_idx (heimat_flughafen)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


# Erzeugt die Tabelle für Flugzeugtypen inkl. Volltextindex
DROP TABLE IF EXISTS flugzeug_typ;
CREATE TABLE IF NOT EXISTS flugzeug_typ (
  typ_id INT(11) NOT NULL AUTO_INCREMENT,
  bezeichnung VARCHAR(50) DEFAULT NULL,
  beschreibung TEXT,
  PRIMARY KEY (typ_id),
  FULLTEXT KEY bezeichnung_full (bezeichnung, beschreibung)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


# Erzeugt die Tabelle für die Flugzeuge
DROP TABLE IF EXISTS flugzeug;
CREATE TABLE IF NOT EXISTS flugzeug (
    flugzeug_id INT NOT NULL auto_increment,
    kapazitaet MEDIUMINT unsigned NOT NULL,
    typ_id INT NOT NULL,    
    fluglinie_id INT NOT NULL,
    PRIMARY KEY (flugzeug_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für den Flugplan
DROP TABLE IF EXISTS flugplan;
CREATE TABLE IF NOT EXISTS flugplan (
    flugnr CHAR(8) NOT NULL,
    von SMALLINT NOT NULL,
    nach SMALLINT NOT NULL,
    abflug TIME NOT NULL,
    ankunft TIME NOT NULL,
    fluglinie_id SMALLINT NOT NULL,
    montag TINYINT(1) DEFAULT 0,
    dienstag TINYINT(1) DEFAULT 0,
    mittwoch TINYINT(1) DEFAULT 0,
    donnerstag TINYINT(1) DEFAULT 0,
    freitag TINYINT(1) DEFAULT 0,
    samstag TINYINT(1) DEFAULT 0, 
    sonntag TINYINT(1) DEFAULT 0,
    PRIMARY KEY (flugnr),
    INDEX von_idx (von),
    INDEX nach_idx (nach),
    INDEX fluglinie_idx (fluglinie_id)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für die Flüge
DROP TABLE IF EXISTS flug;
CREATE TABLE IF NOT EXISTS flug (
    flug_id INT NOT NULL auto_increment,
    flugnr CHAR(8) NOT NULL,
    von SMALLINT NOT NULL,
    nach SMALLINT NOT NULL,
    abflug DATETIME NOT NULL,
    ankunft DATETIME NOT NULL,
    fluglinie_id SMALLINT NOT NULL,
    flugzeug_id INT NOT NULL,
    PRIMARY KEY (flug_id),
    INDEX von_idx (von),
    INDEX nach_idx (nach),
    INDEX abflug_idx (abflug),
    INDEX ankunft_idx (ankunft),
    INDEX fluglinie_idx (fluglinie_id),
    INDEX flugzeug_idx (flugzeug_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für Passagierdaten
# Die Passnummer ist UNIQUE
DROP TABLE IF EXISTS passagier;
CREATE TABLE IF NOT EXISTS passagier (
    passagier_id INT NOT NULL auto_increment,
    passnummer CHAR(9) NOT NULL,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    PRIMARY KEY (passagier_id),
    UNIQUE pass_unq (passnummer)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für die Details der Passagiere
DROP TABLE IF EXISTS passagierdetails;
CREATE TABLE IF NOT EXISTS passagierdetails (
    passagier_id INT NOT NULL,
    geburtsdatum DATE NOT NULL,
    geschlecht CHAR(1) DEFAULT NULL,
    strasse VARCHAR(100) NOT NULL,
    ort VARCHAR(100) NOT NULL,
    plz SMALLINT NOT NULL,
    land VARCHAR(100) NOT NULL,
    emailadresse VARCHAR(120) DEFAULT NULL,
    telefonnummer VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (passagier_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für die Buchungen
DROP TABLE IF EXISTS buchung;
CREATE TABLE IF NOT EXISTS buchung (
    buchung_id INT NOT NULL auto_increment,
    flug_id INT NOT NULL,
    sitzplatz CHAR(4) DEFAULT NULL,
    passagier_id INT NOT NULL,
    preis DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (buchung_id),
    INDEX flug_idx (flug_id),
    INDEX passagier_idx (passagier_id),
    UNIQUE sitzplan_unq(flug_id, sitzplatz)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für Mitarbeiterdaten
DROP TABLE IF EXISTS mitarbeiter;
CREATE TABLE IF NOT EXISTS mitarbeiter (
    mitarbeiter_id int(11) NOT NULL AUTO_INCREMENT,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    geburtsdatum DATE NOT NULL,
    geschlecht CHAR(1) DEFAULT NULL,
    strasse VARCHAR(100) NOT NULL,
    ort VARCHAR(100) NOT NULL,
    plz SMALLINT NOT NULL,
    land VARCHAR(100) NOT NULL,
    emailadresse VARCHAR(120) DEFAULT NULL,
    telefonnummer VARCHAR(30) DEFAULT NULL,
    gehalt DECIMAL(8,2) DEFAULT NULL,
    abteilung ENUM('Marketing','Buchhaltung','Management','Logistik','Flugfeld') DEFAULT NULL,
    benutzername varchar(20) DEFAULT NULL,
    passwort char(32) DEFAULT NULL,
    PRIMARY KEY (mitarbeiter_id),
    UNIQUE benutzer_unq (benutzername)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für GIS-Daten der Flughäfen
DROP TABLE IF EXISTS flughafen_geo;
CREATE TABLE IF NOT EXISTS flughafen_geo (
    flughafen_id SMALLINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    stadt VARCHAR(50) DEFAULT NULL,
    land VARCHAR(50) DEFAULT NULL,
    breite DECIMAL(11,8) NOT NULL,
    laenge DECIMAL(11,8) NOT NULL,
    geolokation POINT NOT NULL,
    INDEX (flughafen_id),
    SPATIAL KEY geolokation_idx (geolokation)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für Wetterdaten
DROP TABLE IF EXISTS  wetterdaten;
CREATE TABLE IF NOT EXISTS wetterdaten (
  datum DATE NOT NULL,
  zeit TIME NOT NULL,
  station INT(11) NOT NULL,
  temp DECIMAL(3,1) NOT NULL,
  feuchtigkeit DECIMAL(4,1) NOT NULL,
  luftdruck DECIMAL(10,2) NOT NULL,
  wind DECIMAL(5,2) NOT NULL,
  wetter enum('Nebel-Schneefall','Schneefall','Regen','Regen-Schneefall','Nebel-Regen','Nebel-Regen-Gewitter','Gewitter','Nebel','Regen-Gewitter') DEFAULT NULL,
  windrichtung SMALLINT NOT NULL,
  PRIMARY KEY (datum, zeit, station)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# Erzeugt die Tabelle für Log-Daten
DROP TABLE IF EXISTS flug_log;
CREATE TABLE IF NOT EXISTS flug_log (
    datum DATETIME NOT NULL,
    benutzer VARCHAR(100) NOT NULL,
    flug_id INT NOT NULL,
    flugnr_alt CHAR(8) NOT NULL,
    flugnr_neu CHAR(8) NOT NULL,
    von_alt SMALLINT NOT NULL,
    nach_alt SMALLINT NOT NULL,
    von_neu SMALLINT NOT NULL,
    nach_neu SMALLINT NOT NULL,
    abflug_alt DATETIME NOT NULL,
    ankunft_alt DATETIME NOT NULL,
    abflug_neu DATETIME NOT NULL,
    ankunft_neu DATETIME NOT NULL,
    flugzeug_id_alt INT NOT NULL,
    flugzeug_id_neu INT NOT NULL,
    fluglinie_id_alt SMALLINT NOT NULL,
    fluglinie_id_neu SMALLINT NOT NULL,
    kommentar VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Legt Fremdschlüsselbeziehungen an.
ALTER TABLE buchung ADD FOREIGN KEY flug_flug (flug_id) REFERENCES flug(flug_id) ;
ALTER TABLE buchung ADD FOREIGN KEY passagier_passagier (passagier_id) REFERENCES passagier(passagier_id);

ALTER TABLE flug ADD FOREIGN KEY von_flughafen (von) REFERENCES flughafen(flughafen_id);
ALTER TABLE flug ADD FOREIGN KEY nach_flughafen (nach) REFERENCES flughafen(flughafen_id);
ALTER TABLE flug ADD FOREIGN KEY fluglinie_fluglinie (fluglinie_id) REFERENCES fluglinie(fluglinie_id);
ALTER TABLE flug ADD FOREIGN KEY flugzeug_flugzeug (flugzeug_id) REFERENCES flugzeug(flugzeug_id);

ALTER TABLE flughafen_geo ADD FOREIGN KEY flughafen_flughafen (flughafen_id) REFERENCES flughafen(flughafen_id);

ALTER TABLE flugplan ADD FOREIGN KEY von_flughafen (von) REFERENCES flughafen(flughafen_id);
ALTER TABLE flugplan ADD FOREIGN KEY nach_flughafen (nach) REFERENCES flughafen(flughafen_id);
ALTER TABLE flugplan ADD FOREIGN KEY fluglinie_fluglinie (fluglinie_id) REFERENCES fluglinie(fluglinie_id);

ALTER TABLE fluglinie ADD FOREIGN KEY heimat_flughafen (heimat_flughafen) REFERENCES flughafen(flughafen_id);

ALTER TABLE passagierdetails ADD FOREIGN KEY passagier_passagier (passagier_id) REFERENCES passagier(passagier_id) ON DELETE CASCADE; 

