From 33613a85afc4b1481367fbe92a17ee59c240250b Mon Sep 17 00:00:00 2001 From: Sven Eisenhauer Date: Fri, 10 Nov 2023 15:11:48 +0100 Subject: add new repo --- Bachelor/Datenbanken/init_p4_3.sql | 105 +++++++++++++++++++++++++++++++++++++ 1 file changed, 105 insertions(+) create mode 100644 Bachelor/Datenbanken/init_p4_3.sql (limited to 'Bachelor/Datenbanken/init_p4_3.sql') diff --git a/Bachelor/Datenbanken/init_p4_3.sql b/Bachelor/Datenbanken/init_p4_3.sql new file mode 100644 index 0000000..c3a6b7b --- /dev/null +++ b/Bachelor/Datenbanken/init_p4_3.sql @@ -0,0 +1,105 @@ +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 +; -- cgit v1.2.3