From 33613a85afc4b1481367fbe92a17ee59c240250b Mon Sep 17 00:00:00 2001
From: Sven Eisenhauer <sven@sven-eisenhauer.net>
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

