tQrQViEC

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,
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,
18	`Letzter Login` Timestamp,
19	PRIMARY KEY(Nummer)
20	
21);
22
23	
24INSERT INTO Benutzer(`Nummer`, `Nutzername`, `Salt`, `Hash`, `Vorname`, `Nachname`, `Email`)
25VALUES(1, 'Georgios5', 'Nummer', 'Nummer', 'Georgios', 'Nikas', 'nikasgeorgios7@gmail.com');
26
27DROP TABLE IF EXISTS DBWT.Gaeste;
28CREATE TABLE Gaeste(
29	`Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
30	`Grund` VARCHAR(255) NOT NULL,
31	`Ablaufdatum` DATE NOT NULL,
32	
33	PRIMARY KEY(Nummer),
34	FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer)
35	
36);benutzer
37	
38DROP TABLE IF EXISTS DBWT.FhAngehoerige;
39CREATE TABLE FhAngehoerige(
40	`Nummer` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
41	FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer)
42);
43
44DROP TABLE IF EXISTS DBWT.Studenten;
45CREATE TABLE Studenten(
46	`Nummer` INT UNSIGNED  PRIMARY KEY NOT NULL AUTO_INCREMENT,
47	`Matrikelnummer` INT,
48	`Studiengang` ENUM('ET','INF','ISE','MCD','WI'),
49	CONSTRAINT CheckMaNumber  CHECK (1000000000 > `Matrikelnummer` > 9999999)
50	
51);
52
53DROP TABLE IF EXISTS DBWT.Mitarbeiter;
54CREATE TABLE Mitarbeiter(
55	`Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
56	`Büro` VARCHAR(20),
57	`Telefon` Varchar(20),
58	PRIMARY KEY(`Nummer`)
59);
60
61DROP TABLE IF EXISTS DBWT.Fachbereiche;
62CREATE TABLE Fachbereiche(
63	`ID` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
64	`Website` VARCHAR(100) NOT NULL,
65	`Name` VARCHAR(40) NOT NULL
66);
67
68DROP TABLE IF EXISTS DBWT.Kommentare;
69CREATE TABLE Kommentare(
70	`ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
71	`Bemerkung` VARCHAR(150),
72	`Bewertung` VARCHAR(150) NOT NULL,
73	`benutzer_nummer` INT UNSIGNED NOT NULL,
74	`mahlzeiten_id` INT UNSIGNED NOT NULL,
75	FOREIGN KEY (benutzer_nummer) REFERENCES Studenten(Nummer),
76	FOREIGN KEY (mahlzeiten_id) REFERENCES Mahlzeiten(ID)
77);
78
79DROP TABLE IF EXISTS DBWT.Bestellungen;
80CREATE TABLE Bestellungen(
81	`Nummer` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
82	`Bestellzeitpunkt` TIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
83	`Abholzeitpunkt` TIME,
84	`benutzer_nummer` INT UNSIGNED NOT NULL,
85	CONSTRAINT zeit CHECK(`Bestellzeitpunkt` < `Abholzeitpunkt`),
86	FOREIGN KEY (benutzer_nummer) REFERENCES Benutzer(Nummer)
87);
88
89DROP TABLE IF EXISTS DBWT.Mahlzeiten;
90CREATE TABLE Mahlzeiten(
91	`ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
92	`Beschreibung` VARCHAR(255) NOT NULL,
93	`Vorrat` INT UNSIGNED NOT NULL DEFAULT 0,
94	`kategorie_id` INT UNSIGNED NOT NULL,
95	`Verfügbar` BOOL NOT NULL DEFAULT TRUE,
96	FOREIGN KEY (kategorie_id) REFERENCES Kategorien(ID)
97);
98
99DROP TABLE IF EXISTS DBWT.Deklaration;
100CREATE TABLE Deklaration(
101	`Zeichen` VARCHAR(2) NOT NULL PRIMARY KEY,
102	`Beschriftung` VARCHAR(32) NOT NULL
103);
104
105DROP TABLE IF EXISTS DBWT.Zutaten;
106CREATE TABLE Zutaten(
107	`ID` INT UNSIGNED NOT NULL PRIMARY KEY,
108	`Name` VARCHAR(50) NOT NULL,
109	`Bio` BOOL NOT NULL,
110	`Vegetarisch` BOOL NOT NULL,
111	`Vegan` BOOL NOT NULL,
112	`Glutenfrei` BOOL NOT NULL,
113	CONSTRAINT id_zahl CHECK(`ID` BETWEEN 00000 AND 99999)
114);
115
116DROP TABLE IF EXISTS DBWT.Kategorien;
117CREATE TABLE Kategorien(
118	`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
119	`Bezeichnung` VARCHAR(255) NOT NULL,
120	`bilder_ID` INT UNSIGNED NOT NULL,
121	`unterkat_ID` INT UNSIGNED NOT NULL,
122	FOREIGN KEY (ID) REFERENCES Bilder(ID),
123	FOREIGN KEY (unterkat_ID) REFERENCES Kategorien(ID)
124);
125
126DROP TABLE IF EXISTS DBWT.Bilder;
127CREATE TABLE Bilder(
128	`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
129	`Alt-Text` VARCHAR(50) NOT NULL,
130	`Titel` VARCHAR(50),
131	`Binärdaten` BLOB NOT NULL
132	
133);
134
135DROP TABLE IF EXISTS DBWT.Preise;
136CREATE TABLE Preise(
137	`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
138	`Jahr` INT UNSIGNED NOT NULL,
139	`Gastpreis` INT UNSIGNED NOT NULL,
140	`Studentpreis` INT UNSIGNED,
141	`MA-Preis` INT UNSIGNED,
142	CONSTRAINT max_preis CHECK(Gastpreis BETWEEN 0 AND 99.99),
143	CONSTRAINT studi_preis CHECK(Studentpreis < `MA-Preis`),
144	FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID)
145);
146
147DROP TABLE IF EXISTS DBWT.befreundet;
148CREATE TABLE befreundet(
149	`benutzer1_id` INT UNSIGNED NOT NULL,
150	`benutzer2_id` INT UNSIGNED NOT NULL,
151	CONSTRAINT PK_befreundet PRIMARY KEY (benutzer1_id,benutzer2_id)
152);
153
154DROP TABLE IF EXISTS DBWT.gehört_zu;
155CREATE TABLE gehört_zu(
156	`benutzer_id` INT UNSIGNED NOT NULL,
157	`fach_id` INT UNSIGNED NOT NULL,
158	FOREIGN KEY (`benutzer_id`) REFERENCES FhAngehoerige(`Nummer`),
159	FOREIGN KEY (`fach_id`) REFERENCES Fachbereiche(`ID`),
160	CONSTRAINT PK_gehört_zu PRIMARY KEY (`benutzer_id`,`fach_id`)
161);
162
163DROP TABLE IF EXISTS DBWT.enthält;
164CREATE TABLE enthält(
165	`bestellung_id` INT UNSIGNED NOT NULL,
166	`mahlzeit_id` INT UNSIGNED NOT NULL,
167	`Anzahl` INT UNSIGNED NOT NULL,
168	CONSTRAINT PK_enthält PRIMARY KEY (bestellung_id,mahlzeit_id)
169);
170
171DROP TABLE IF EXISTS DBWT.braucht;
172CREATE TABLE braucht(
173	`mahlzeiten_id` INT UNSIGNED NOT NULL,
174	`zeichen` VARCHAR(2) NOT NULL,
175	CONSTRAINT PK_braucht PRIMARY KEY (mahlzeiten_id,zeichen)
176);
177
178DROP TABLE IF EXISTS DBWT.enthält_zutaten;
179CREATE TABLE enthält_zutaten(
180	`mahlzeiten_id` INT UNSIGNED NOT NULL,
181	`zutaten_id` INT UNSIGNED NOT NULL,
182	CONSTRAINT PK_enthält PRIMARY KEY (mahlzeiten_id,zutaten_id)
183);
184
185DROP TABLE IF EXISTS DBWT.hat;
186CREATE TABLE hat(
187	`mahlzeiten_id` INT UNSIGNED NOT NULL,
188	`bilder_id` INT UNSIGNED NOT NULL,
189	CONSTRAINT PK_hat PRIMARY KEY (mahlzeiten_id,bilder_id)
190);