DROP DATABASE IF EXISTS firma;

CREATE DATABASE firma
  COLLATE latin1_german1_ci;
USE firma;
# --------------------------------------------------------

CREATE TABLE  mitarbeiter (

  nachname            varchar(50) NOT NULL,

  vorname             varchar(50) NOT NULL,

  pnr                 int NOT NULL,

  gebdatum            date,

  adresse             varchar(150),

  geschlecht          ENUM('M','W') NOT NULL,

  gehalt              int NOT NULL CHECK (gehalt >0),

  vpnr                int,

  abtnr               int,
  PRIMARY KEY (pnr)

) ENGINE = InnoDB;


LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/mitarbeiter.csv" INTO TABLE mitarbeiter LINES TERMINATED BY '\r\n';

# --------------------------------------------------------
CREATE TABLE abteilung (

  abtname             varchar(50) NOT NULL,

  anr                 int,

  mgrpnr              int,

  mgrstartdat         date,
  PRIMARY KEY (anr),
  UNIQUE (abtname)

) ENGINE = InnoDB;



LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/abteilung.csv" INTO TABLE abteilung LINES TERMINATED BY '\r\n';

# --------------------------------------------------------

CREATE TABLE standort (

  abtnummer           int,

  stadt               varchar(50),
  PRIMARY KEY (abtnummer,stadt)

) ENGINE = InnoDB;



LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/standort.csv" INTO TABLE standort LINES TERMINATED BY '\r\n';

# --------------------------------------------------------

CREATE TABLE arbeitet_in (

  mapnr               int,

  projectnr           int,

  stunden             int NOT NULL CHECK (stunden >=0 AND stunden <= 40),
  PRIMARY KEY (mapnr,projectnr)

) ENGINE = InnoDB;



LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/arbeitet_in.csv" INTO TABLE arbeitet_in LINES TERMINATED BY '\r\n';

# --------------------------------------------------------

CREATE TABLE projekt (

  projname            varchar(50),

  pnummer             int,

  port                varchar(50),

  abtnummer           int,
  PRIMARY KEY (pnummer)

) ENGINE = InnoDB;



LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/projekt.csv" INTO TABLE projekt LINES TERMINATED BY '\r\n';

# --------------------------------------------------------

CREATE TABLE angehoeriger (

  pnr                 int,

  vorname             varchar(50) NOT NULL,

  geschlecht          ENUM('W','M'),

  gebdatum            date,

  beziehung            ENUM('W','M') NOT NULL,
  PRIMARY KEY (pnr,vorname,beziehung) 

) ENGINE = InnoDB;



LOAD DATA LOCAL INFILE "/home/sven/Studium/Datenbanken/P4/MySQL/firma/angehoeriger.csv" INTO TABLE angehoeriger LINES TERMINATED BY '\r\n';

# --------------------------------------------------------

ALTER TABLE  mitarbeiter 
  ADD FOREIGN KEY (abtnr) REFERENCES abteilung(anr)
    ON UPDATE CASCADE 
    ON DELETE SET NULL,
  ADD FOREIGN KEY (vpnr) REFERENCES mitarbeiter(pnr)
    ON UPDATE CASCADE
    ON DELETE SET NULL

;
ALTER TABLE abteilung 
  ADD FOREIGN KEY (mgrpnr) REFERENCES mitarbeiter(pnr)
    ON UPDATE CASCADE
    ON DELETE SET NULL

;
ALTER TABLE projekt 

  ADD FOREIGN KEY (abtnummer) REFERENCES abteilung(anr)
    ON DELETE SET NULL
    ON UPDATE CASCADE

;
ALTER TABLE standort 

  ADD FOREIGN KEY (abtnummer) REFERENCES abteilung(anr)
    ON DELETE CASCADE
    ON UPDATE CASCADE

;
ALTER TABLE arbeitet_in 
  ADD FOREIGN KEY (mapnr) REFERENCES mitarbeiter(pnr)
    ON DELETE CASCADE
    ON UPDATE CASCADE
  ADD FOREIGN KEY (projectnr) REFERENCES projekt(pnummer)
    ON DELETE CASCADE
    ON UPDATE CASCADE

;

ALTER TABLE angehoeriger

  ADD FOREIGN KEY (pnr) REFERENCES mitarbeiter(pnr)
    ON UPDATE CASCADE
    ON DELETE CASCADE

;