1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
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
;
|
