diff options
| author | Sven Eisenhauer <sven@sven-eisenhauer.net> | 2023-11-10 15:11:48 +0100 |
|---|---|---|
| committer | Sven Eisenhauer <sven@sven-eisenhauer.net> | 2023-11-10 15:11:48 +0100 |
| commit | 33613a85afc4b1481367fbe92a17ee59c240250b (patch) | |
| tree | 670b842326116b376b505ec2263878912fca97e2 /Bachelor/Datenbanken/init_p4_3.sql | |
| download | Studium-33613a85afc4b1481367fbe92a17ee59c240250b.tar.gz Studium-33613a85afc4b1481367fbe92a17ee59c240250b.tar.bz2 | |
Diffstat (limited to 'Bachelor/Datenbanken/init_p4_3.sql')
| -rw-r--r-- | Bachelor/Datenbanken/init_p4_3.sql | 105 |
1 files changed, 105 insertions, 0 deletions
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
+;
|