· 6 years ago · Nov 14, 2019, 09:38 PM
1-- SQL DDL Intro aus der Übung
2-- Ihre Datenbank auswählen, ändern Sie den Namen entsprechend...
3USE `db3169199`;
4-- Tabelle löschen, falls Sie existiert
5
6DROP TABLE IF EXISTS `Enthält_B-M`;
7DROP TABLE IF EXISTS `braucht`;
8DROP TABLE IF EXISTS `Enthält_M-Z`;
9DROP TABLE IF EXISTS `hat`;
10DROP TABLE IF EXISTS `Befreundetmit`;
11DROP TABLE IF EXISTS `Kommentare`;
12
13
14DROP TABLE IF EXISTS `Mitarbeiter`;
15DROP TABLE IF EXISTS `Studenten`;
16DROP TABLE IF EXISTS `Bestellungen`;
17
18DROP TABLE IF EXISTS `FH_Angehörige`;
19DROP TABLE IF EXISTS `Gäste`;
20DROP TABLE IF EXISTS `Benutzer`;
21DROP TABLE IF EXISTS `Preise`;
22
23
24
25DROP TABLE IF EXISTS `Deklarationen`;
26DROP TABLE IF EXISTS `Mahlzeiten`;
27DROP TABLE IF EXISTS `Kategorien`;
28
29
30DROP TABLE IF EXISTS `Fachbereiche`;
31DROP TABLE IF EXISTS `Zutaten`;
32DROP TABLE IF EXISTS `Bilder`;
33
34
35
36-- SET FOREIGN_KEY_CHECKS=0;
37-- Empfohlen ist, zuerst die Attribute der Tabellen anzulegen und die Relationen
38-- anschließend vorzunehmen. dabei werden Sie erkennen, dass nicht jede Lösch-
39-- reihenfolge (DROP) funktioniert.
40
41
42CREATE TABLE `Benutzer`
43(
44 `E-Mail` VARCHAR(200) NOT NULL,
45 CONSTRAINT MailEindeutig UNIQUE (`E-Mail`),
46
47 Bild MEDIUMBLOB NOT NULL,
48 Nummer VARCHAR(50) NOT NULL PRIMARY KEY,
49 Nutzername VARCHAR (50) NOT NULL,
50 CONSTRAINT NutzernameEindeutig UNIQUE (Nutzername),
51 -- Auth
52
53 `Hash` VARCHAR(60) DEFAULT NULL,
54 LetzterLogin DATETIME DEFAULT NULL,
55 --
56 Anlegedatum DATETIME NOT NULL,
57 Aktiv BOOL NOT NULL,
58 -- Name
59
60 Vorname VARCHAR (50) NOT NULL,
61 Nachname VARCHAR (50) NOT NULL,
62
63 Geburtsdatum DATE NOT NULL,
64 `Alter` TINYINT DEFAULT 0
65
66 -- 2019 - Geburtsdatum AS `Alter`,
67 -- isbreundetMit VARCHAR(50) NOT NULL
68);
69
70
71
72CREATE TABLE `Bestellungen`
73(
74 Endpreis BIGINT,
75 Nummer VARCHAR(50) NOT NULL PRIMARY KEY,
76 BestellZeitpunkt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
77 AbholZeitpunkt DATETIME CHECK (BestellZeitpunkt <= AbholZeitpunkt),
78
79 Tätigvon VARCHAR (50) NOT NULL,
80
81 CONSTRAINT `Tätig` FOREIGN KEY (Tätigvon) REFERENCES `Benutzer` (`Nummer`)
82 ON DELETE CASCADE
83
84);
85
86
87
88
89CREATE TABLE `Gäste`
90(
91 ID_Gast VARCHAR(50) NOT NULL,
92 Grund VARCHAR(254) NOT NULL,
93 Ablaufdatum DATE NOT NULL,
94 CONSTRAINT `IsA1` FOREIGN KEY (ID_Gast) REFERENCES `Benutzer` (Nummer)
95 ON DELETE CASCADE
96
97);
98
99CREATE TABLE `FH_Angehörige`
100(
101 ID_FH_Angehörige VARCHAR(50) NOT NULL PRIMARY KEY,
102
103 CONSTRAINT `IsA2` FOREIGN KEY (ID_FH_Angehörige) REFERENCES `Benutzer` (Nummer)
104 ON DELETE CASCADE
105);
106
107
108CREATE TABLE `Deklarationen`
109(
110 Zeichen VARCHAR(2) NOT NULL,
111 Beschriftung VARCHAR(32) NOT NULL,
112 PRIMARY KEY (Zeichen)
113);
114
115
116CREATE TABLE `Bilder`
117(
118 ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
119 `Alt-Text` BOOL NOT NULL,
120 Titel VARCHAR(50) NOT NULL,
121 `Binärdaten` BINARY NOT NULL
122
123);
124
125
126CREATE TABLE `Kategorien`
127(
128 ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
129 Bezeichnung VARCHAR(200) NOT NULL,
130 Kategorie_hat INT UNSIGNED NOT NULL,
131 Kategorie_hatB INT UNSIGNED,
132
133 CONSTRAINT `hat_K-K` FOREIGN KEY (Kategorie_hat) REFERENCES `Kategorien` (ID)
134 -- ON DELETE CASCADE,
135 ON UPDATE CASCADE,
136
137 CONSTRAINT `hat_K-B` FOREIGN KEY (Kategorie_hatB) REFERENCES `Bilder` (ID)
138 ON DELETE CASCADE
139 ON UPDATE CASCADE
140
141);
142
143
144CREATE TABLE `Mahlzeiten`
145(
146 ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
147 Beschreibung TEXT NOT NULL,
148 Verfügbar BOOL,
149 Vorrat TINYINT NOT NULL DEFAULT 0,
150 Mahlzeiten_in INT UNSIGNED NOT NULL
151
152 -- CONSTRAINT `in` FOREIGN KEY (Mahlzeiten_in) REFERENCES `Kategorien` (ID)
153 -- ON DELETE CASCADE
154 -- ON UPDATE CASCADE
155);
156
157
158
159CREATE TABLE `Preise`
160(
161 Mahlzeit_ID INT UNSIGNED NOT NULL,
162 Endpreis DECIMAL(10,2) DEFAULT 0,
163 -- ID INT UNSIGNED AUTO_INCREMENT NOT NULL /*FOREIGN KEY(ID) REFERENCES Malzeiten (ID)*/,
164 `MA-Preis` DECIMAL(4,2) NOT NULL DEFAULT 0,
165 Studentpreis DECIMAL (4,2) NOT NULL DEFAULT 0,
166 CHECK (Studentpreis <= `MA-Preis`),
167 Gastpreis DECIMAL (4,2) DEFAULT 0,
168 Jahr YEAR DEFAULT 0,
169
170 CONSTRAINT `kostet` FOREIGN KEY (Mahlzeit_ID) REFERENCES `Mahlzeiten`(ID)
171 ON DELETE CASCADE
172
173 -- CHECK(YEAR <= 1)
174
175);
176
177
178CREATE TABLE `Mitarbeiter`
179(
180 Büro VARCHAR(10),
181 Telefon BIGINT,
182 Mitarbeiter_ID VARCHAR(50) NOT NULL PRIMARY KEY,
183
184 CONSTRAINT `kannA1` FOREIGN KEY (Mitarbeiter_ID) REFERENCES `FH_Angehörige`(ID_FH_Angehörige)
185 ON DELETE CASCADE
186
187);
188
189
190CREATE TABLE `Studenten`
191(
192 Studiengang ENUM ('ET','INF','MCD','WI') NOT NULL,
193 Matrikelnummer VARCHAR(9) NOT NULL PRIMARY KEY,
194 Studenten_ID VARCHAR(50) NOT NULL,
195
196 CONSTRAINT `kannA2` FOREIGN KEY (Studenten_ID) REFERENCES `FH_Angehörige`(ID_FH_Angehörige)
197 ON DELETE CASCADE
198);
199
200
201CREATE TABLE `Fachbereiche`
202(
203 ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
204 `Name` VARCHAR(50) NOT NULL,
205 Website VARCHAR(200) NOT NULL
206
207);
208
209CREATE TABLE `Kommentare`
210(
211 ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
212 Bemerkung TEXT,
213 Bewertung TINYINT NOT NULL,
214 Kommentare_von VARCHAR(9) NOT NULL,
215 Kommentare_zu INT UNSIGNED,
216
217 CONSTRAINT `schreibt` FOREIGN KEY (Kommentare_von) REFERENCES `Studenten` (Matrikelnummer)
218 ON DELETE CASCADE,
219
220 CONSTRAINT `zu` FOREIGN KEY (Kommentare_zu) REFERENCES `Mahlzeiten` (ID)
221 ON DELETE CASCADE
222
223);
224
225
226CREATE TABLE `Zutaten`
227(
228 ID INT(5) PRIMARY KEY NOT NULL,
229 `Name` VARCHAR(50) NOT NULL,
230 Bio TINYINT(1) NOT NULL DEFAULT 0,
231 Vegetarisch TINYINT(1) NOT NULL DEFAULT 0,
232 Vegan TINYINT(1) NOT NULL DEFAULT 0,
233 Glutenfrei TINYINT(1) NOT NULL DEFAULT 0
234
235);
236
237
238-- Beziehungen
239
240CREATE TABLE `Enthält_B-M`
241(
242 Bestellungen_nummer VARCHAR(50) NOT NULL PRIMARY KEY,
243 Mahlzeiten_ID INT UNSIGNED,
244 Anzahl MEDIUMINT,
245
246 CONSTRAINT `Enthält1` FOREIGN KEY (Bestellungen_nummer) REFERENCES `Bestellungen` (Nummer)
247 ON DELETE CASCADE,
248
249 CONSTRAINT `Enthält2` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
250 ON DELETE CASCADE
251
252);
253
254
255CREATE TABLE `braucht`
256(
257 Deklaration_Zeichen VARCHAR(2) NOT NULL,
258 Mahlzeiten_ID INT UNSIGNED,
259
260 CONSTRAINT `braucht1` FOREIGN KEY (Deklaration_Zeichen) REFERENCES `Deklarationen` (Zeichen)
261 ON DELETE CASCADE,
262
263 CONSTRAINT `braucht2` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
264 ON DELETE CASCADE
265
266);
267
268
269CREATE TABLE `Enthält_M-Z`
270(
271 Mahlzeiten_ID INT UNSIGNED,
272 Zutaten_ID INT(5),
273
274 CONSTRAINT `Enthält_M-Z-1` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
275 ON DELETE CASCADE
276 ON UPDATE CASCADE,
277
278 CONSTRAINT `Enthält_M-Z-2` FOREIGN KEY (Zutaten_ID) REFERENCES `Zutaten` (ID)
279 ON DELETE CASCADE
280 ON UPDATE CASCADE
281
282
283);
284
285CREATE TABLE `Befreundetmit`(
286
287 Benutzer1 VARCHAR(50) NOT NULL,
288 Benutzer2 VARCHAR(50) NOT NULL,
289
290 CONSTRAINT `isbefreundetmit1` FOREIGN KEY (Benutzer1) REFERENCES `Benutzer`(Nummer)
291 ON UPDATE CASCADE
292 ON DELETE CASCADE,
293
294 CONSTRAINT `isbefreundetmit2` FOREIGN KEY (Benutzer2) REFERENCES `Benutzer`(Nummer)
295 ON UPDATE CASCADE
296 ON DELETE CASCADE
297);
298
299
300CREATE TABLE `hat`
301(
302
303 Mahlzeiten_ID INT UNSIGNED,
304 Bilder_ID INT UNSIGNED,
305
306 CONSTRAINT `hat1` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
307 ON DELETE CASCADE,
308
309 CONSTRAINT `hat2` FOREIGN KEY (Bilder_ID) REFERENCES `Bilder` (ID)
310 ON DELETE CASCADE
311
312);
313
314
315-- Benutzer
316ALTER TABLE `Benutzer` DROP CONSTRAINT NutzernameEindeutig;
317ALTER TABLE `Benutzer`
318 ADD CONSTRAINT NutzernameEindeutig UNIQUE (Nutzername);
319
320-- Bestellungen
321
322ALTER TABLE `Bestellungen` DROP CONSTRAINT `Tätig`;
323ALTER TABLE `Bestellungen`
324 ADD CONSTRAINT `Tätig` FOREIGN KEY (Tätigvon) REFERENCES `Benutzer` (`Nummer`)
325 ON UPDATE CASCADE
326 ON DELETE CASCADE;
327
328-- Gäste
329
330ALTER TABLE `Gäste` DROP CONSTRAINT `IsA1`;
331ALTER TABLE `Gäste`
332 ADD CONSTRAINT `IsA1` FOREIGN KEY (ID_Gast) REFERENCES `Benutzer` (Nummer)
333 ON DELETE CASCADE;
334
335-- FH_Angehörige
336ALTER TABLE `FH_Angehörige` DROP CONSTRAINT `IsA2`;
337ALTER TABLE `FH_Angehörige`
338 ADD CONSTRAINT `IsA2` FOREIGN KEY (ID_FH_Angehörige) REFERENCES `Benutzer` (Nummer)
339 ON UPDATE CASCADE
340 ON DELETE CASCADE;
341
342-- Kategorien
343
344ALTER TABLE `Kategorien` DROP CONSTRAINT `hat_K-K`;
345ALTER TABLE `Kategorien` DROP CONSTRAINT `hat_K-B`;
346
347ALTER TABLE `Kategorien`
348 ADD CONSTRAINT `hat_K-K` FOREIGN KEY (Kategorie_hat) REFERENCES `Kategorien` (ID)
349 ON UPDATE CASCADE
350 ON DELETE CASCADE,
351 ADD CONSTRAINT `hat_K-B` FOREIGN KEY (Kategorie_hatB) REFERENCES `Bilder` (ID)
352 ON UPDATE CASCADE
353 ON DELETE CASCADE;
354
355-- Mahlzeiten
356
357-- ALTER TABLE `Mahlzeiten` DROP CONSTRAINT `in`;
358-- ALTER TABLE `Mahlzeiten`
359-- ADD CONSTRAINT `in` FOREIGN KEY (Mahlzeiten_in) REFERENCES `Kategorien` (ID)
360-- ON UPDATE CASCADE
361-- ON DELETE CASCADE;
362
363-- Kommentare
364
365ALTER TABLE `Kommentare` DROP CONSTRAINT `schreibt`;
366ALTER TABLE `Kommentare` DROP CONSTRAINT `zu`;
367
368ALTER TABLE `Kommentare`
369 ADD CONSTRAINT `schreibt` FOREIGN KEY (Kommentare_von) REFERENCES `Studenten` (Matrikelnummer)
370 ON UPDATE CASCADE
371 ON DELETE CASCADE,
372
373 ADD CONSTRAINT `zu` FOREIGN KEY (Kommentare_zu) REFERENCES `Mahlzeiten` (ID)
374 ON UPDATE CASCADE
375 ON DELETE CASCADE;
376
377-- Enthält_B-M
378
379ALTER TABLE `Enthält_B-M` DROP CONSTRAINT `Enthält1`;
380ALTER TABLE `Enthält_B-M` DROP CONSTRAINT `Enthält2`;
381
382ALTER TABLE `Enthält_B-M`
383 ADD CONSTRAINT `Enthält1` FOREIGN KEY (Bestellungen_nummer) REFERENCES `Bestellungen` (Nummer)
384 ON UPDATE CASCADE
385 ON DELETE CASCADE,
386
387 ADD CONSTRAINT `Enthält2` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
388 ON DELETE CASCADE;
389
390-- braucht
391
392ALTER TABLE `braucht` DROP CONSTRAINT `braucht1`;
393ALTER TABLE `braucht` DROP CONSTRAINT `braucht2`;
394
395ALTER TABLE `braucht`
396 ADD CONSTRAINT `braucht1` FOREIGN KEY (Deklaration_Zeichen) REFERENCES `Deklarationen` (Zeichen)
397 ON UPDATE CASCADE
398 ON DELETE CASCADE,
399
400 ADD CONSTRAINT `braucht2` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
401 ON UPDATE CASCADE
402 ON DELETE CASCADE;
403
404-- Enthält_M-Z
405
406ALTER TABLE `Enthält_M-Z` DROP CONSTRAINT `Enthält_M-Z-1`;
407ALTER TABLE `Enthält_M-Z` DROP CONSTRAINT `Enthält_M-Z-2`;
408
409ALTER TABLE `Enthält_M-Z`
410 ADD CONSTRAINT `Enthält_M-Z-1` FOREIGN KEY (Mahlzeiten_ID) REFERENCES `Mahlzeiten` (ID)
411 ON UPDATE CASCADE
412 ON DELETE CASCADE,
413
414 ADD CONSTRAINT `Enthält_M-Z-2` FOREIGN KEY (Zutaten_ID) REFERENCES `Zutaten` (ID)
415 ON UPDATE CASCADE
416 ON DELETE CASCADE;
417
418-- Befreundetmit
419
420ALTER TABLE `Befreundetmit` DROP CONSTRAINT `isbefreundetmit1`;
421ALTER TABLE `Befreundetmit` DROP CONSTRAINT `isbefreundetmit2`;
422
423ALTER TABLE `Befreundetmit`
424 ADD CONSTRAINT `isbefreundetmit1` FOREIGN KEY (Benutzer1) REFERENCES `Benutzer`(Nummer)
425 ON UPDATE CASCADE
426 ON DELETE CASCADE,
427
428 ADD CONSTRAINT `isbefreundetmit2` FOREIGN KEY (Benutzer2) REFERENCES `Benutzer`(Nummer)
429 ON UPDATE CASCADE
430 ON DELETE CASCADE;
431
432-- ------------------------
433
434INSERT INTO Zutaten SELECT * FROM public.zutaten;