GBRZudMt

1DROP DATABASE DBWT;
2CREATE DATABASE DBWT;
3 
4USE DBWT;
5 
6DROP TABLE IF EXISTS DBWT.Benutzer;
7CREATE TABLE Benutzer(
8    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
9    `Nutzername` VARCHAR(30) NOT NULL UNIQUE,
10    `Salt` VARCHAR(32) NOT NULL,
11    `Hash` VARCHAR(24) NOT NULL,
12    `Anlegedatum` DATE  NOT NULL DEFAULT CURRENT_DATE,
13    `Aktiv` BOOL DEFAULT FALSE NOT NULL,
14    `Vorname` VARCHAR(30) NOT NULL,
15    `Nachname` VARCHAR(30) NOT NULL,
16    `Email` VARCHAR(40) NOT NULL UNIQUE,
17    `Geburtsdatum` DATE DEFAULT CURRENT_DATE,
18    `Alter` INT DEFAULT ((DATEDIFF(CURRENT_DATE,`Geburtsdatum`))/365),
19    `Letzter Login` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
20    PRIMARY KEY(Nummer)
21   
22);
23
24DROP TABLE IF EXISTS DBWT.Gaeste;
25CREATE TABLE Gaeste(
26    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
27    `Grund` VARCHAR(255) NOT NULL DEFAULT('Keine Angabe'),
28    `Ablaufdatum` DATE DEFAULT  (ADDDATE(CURRENT_DATE, INTERVAL 7 DAY)) NOT NULL,
29   
30    PRIMARY KEY(Nummer),
31    FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
32   
33);
34   
35DROP TABLE IF EXISTS DBWT.FhAngehoerige;
36CREATE TABLE FhAngehoerige(
37    `Nummer` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
38    FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
39);
40 
41 
42DROP TABLE IF EXISTS DBWT.Studenten;
43CREATE TABLE Studenten(
44    `Nummer` INT UNSIGNED  PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE,
45    `Matrikelnummer` INT UNIQUE,
46    `Studiengang` ENUM('ET','INF','ISE','MCD','WI'),
47    CONSTRAINT CheckMaNumber  CHECK (1000000000 > `Matrikelnummer` > 9999999),
48   FOREIGN KEY(Nummer) REFERENCES FhAngehoerige(Nummer) ON DELETE CASCADE
49);
50 
51DROP TABLE IF EXISTS DBWT.Mitarbeiter;
52CREATE TABLE Mitarbeiter(
53    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
54    `Büro` VARCHAR(20),
55    `Telefon` VARCHAR(20),
56    PRIMARY KEY(Nummer),
57    FOREIGN KEY(Nummer) REFERENCES FhAngehoerige(Nummer) ON DELETE CASCADE
58);
59 
60DROP TABLE IF EXISTS DBWT.Fachbereiche;
61CREATE TABLE Fachbereiche(
62    `ID` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
63    `Website` VARCHAR(100) DEFAULT('https://www.fh-aachen.de/fachbereiche/') NOT NULL,
64    `Name` VARCHAR(40) NOT NULL
65);
66
67DROP TABLE IF EXISTS DBWT.Bilder;
68CREATE TABLE Bilder(
69    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
70    `Alt-Text` VARCHAR(50) NOT NULL,
71    `Titel` VARCHAR(50),
72    `Binärdaten` BLOB NOT NULL
73   
74);
75
76DROP TABLE IF EXISTS DBWT.Kategorien;
77CREATE TABLE Kategorien(
78    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
79    `Bezeichnung` VARCHAR(255) NOT NULL,
80    `bilder_ID` INT UNSIGNED NOT NULL,
81    `unterkat_ID` INT UNSIGNED NOT NULL,
82    FOREIGN KEY (ID) REFERENCES Bilder(ID),
83    FOREIGN KEY (unterkat_ID) REFERENCES Kategorien(ID)
84);
85
86DROP TABLE IF EXISTS DBWT.Mahlzeiten;
87CREATE TABLE Mahlzeiten(
88    `ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
89    `Beschreibung` VARCHAR(255) NOT NULL,
90    `Vorrat` INT UNSIGNED NOT NULL DEFAULT 0,
91    `kategorie_id` INT UNSIGNED NOT NULL,
92    `Verfügbar` BOOL NOT NULL DEFAULT (Vorrat > 0),
93    FOREIGN KEY (kategorie_id) REFERENCES Kategorien(ID)
94);
95
96DROP TABLE IF EXISTS DBWT.Kommentare;
97CREATE TABLE Kommentare(
98    `ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
99    `Bemerkung` VARCHAR(150),
100    `Bewertung` VARCHAR(150) NOT NULL DEFAULT('Keine Angabe'),
101    `benutzer_nummer` INT UNSIGNED NOT NULL UNIQUE,
102    `mahlzeiten_id` INT UNSIGNED NOT NULL UNIQUE,
103    FOREIGN KEY (benutzer_nummer) REFERENCES Studenten(Nummer),
104    FOREIGN KEY (mahlzeiten_id) REFERENCES Mahlzeiten(ID)
105);
106 
107DROP TABLE IF EXISTS DBWT.Bestellungen;
108CREATE TABLE Bestellungen(
109    `Nummer` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
110    `Bestellzeitpunkt` TIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
111    `Abholzeitpunkt` TIME DEFAULT Bestellzeitpunkt,
112    `benutzer_nummer` INT UNSIGNED NOT NULL,
113    CONSTRAINT zeit CHECK(`Bestellzeitpunkt` <= `Abholzeitpunkt`),
114    FOREIGN KEY (benutzer_nummer) REFERENCES Benutzer(Nummer)
115);
116
117DROP TABLE IF EXISTS DBWT.Deklaration;
118CREATE TABLE Deklaration(
119    `Zeichen` VARCHAR(2) NOT NULL PRIMARY KEY,
120    `Beschriftung` VARCHAR(32) NOT NULL
121);
122 
123DROP TABLE IF EXISTS DBWT.Zutaten;
124CREATE TABLE Zutaten(
125    `ID` INT UNSIGNED NOT NULL PRIMARY KEY,
126    `Name` VARCHAR(50) NOT NULL,
127    `Bio` BOOL NOT NULL,
128    `Vegetarisch` BOOL NOT NULL,
129    `Vegan` BOOL NOT NULL,
130    `Glutenfrei` BOOL NOT NULL,
131    CONSTRAINT id_zahl CHECK(`ID` BETWEEN 00000 AND 99999)
132);
133
134DROP TABLE IF EXISTS DBWT.Preise;
135CREATE TABLE Preise(
136    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
137    `Jahr` INT UNSIGNED NOT NULL,
138    `Gastpreis` INT UNSIGNED NOT NULL,
139    `Studentpreis` INT UNSIGNED,
140    `MA-Preis` INT UNSIGNED,
141    CONSTRAINT max_preis CHECK(Gastpreis <= 99.99),
142    CONSTRAINT studi_preis CHECK(Studentpreis < `MA-Preis`),
143    FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID) ON DELETE CASCADE
144);
145 
146DROP TABLE IF EXISTS DBWT.befreundet;
147CREATE TABLE befreundet(
148    `benutzer1_id` INT UNSIGNED NOT NULL,
149    `benutzer2_id` INT UNSIGNED NOT NULL,
150    CONSTRAINT PK_befreundet PRIMARY KEY (benutzer1_id,benutzer2_id)
151);
152 
153DROP TABLE IF EXISTS DBWT.gehört_zu;
154CREATE TABLE gehört_zu(
155    `benutzer_id` INT UNSIGNED NOT NULL,
156    `fach_id` INT UNSIGNED NOT NULL,
157    FOREIGN KEY (`benutzer_id`) REFERENCES FhAngehoerige(`Nummer`),
158    FOREIGN KEY (`fach_id`) REFERENCES Fachbereiche(`ID`),
159    CONSTRAINT PK_gehört_zu PRIMARY KEY (`benutzer_id`,`fach_id`)
160);
161 
162DROP TABLE IF EXISTS DBWT.enthält;
163CREATE TABLE enthält(
164    `bestellung_id` INT UNSIGNED NOT NULL,
165    `mahlzeit_id` INT UNSIGNED NOT NULL,
166    `Anzahl` INT UNSIGNED NOT NULL,
167    CONSTRAINT PK_enthält PRIMARY KEY (bestellung_id,mahlzeit_id)
168);
169 
170DROP TABLE IF EXISTS DBWT.braucht;
171CREATE TABLE braucht(
172    `mahlzeiten_id` INT UNSIGNED NOT NULL,
173    `zeichen` VARCHAR(2) NOT NULL,
174    CONSTRAINT PK_braucht PRIMARY KEY (mahlzeiten_id,zeichen)
175);
176 
177DROP TABLE IF EXISTS DBWT.enthält_zutaten;
178CREATE TABLE enthält_zutaten(
179    `mahlzeiten_id` INT UNSIGNED NOT NULL,
180    `zutaten_id` INT UNSIGNED NOT NULL,
181    CONSTRAINT PK_enthält PRIMARY KEY (mahlzeiten_id,zutaten_id)
182);
183 
184DROP TABLE IF EXISTS DBWT.hat;
185CREATE TABLE hat(
186    `mahlzeiten_id` INT UNSIGNED NOT NULL,
187    `bilder_id` INT UNSIGNED NOT NULL,
188    CONSTRAINT PK_hat PRIMARY KEY (mahlzeiten_id,bilder_id)
189);
190
191INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
192VALUES('gregor96','d','wse','Georgios','Nikas','nikasgeorgios7@gmail.com','1996-12-30');
193INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
194VALUES('nico','d','wse','Nico','Gartmann','nico.g@gmail.com','1997-09-08');
195INSERT INTO FhAngehoerige(Nummer) VALUES(2);
196INSERT INTO Mitarbeiter(Nummer, Telefon) VALUES(2,'02461347233');
197
198INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
199VALUES('pascal','d','wse','Pascal','Truglio','pt@gmail.com','1996-11-05');
200INSERT INTO FhAngehoerige(Nummer) VALUES(3);
201INSERT INTO Mitarbeiter(Nummer, Telefon) VALUES(3, '024613299992');
202
203DELETE FROM Benutzer WHERE `Nummer`=2;