CPwEXTj0

1-- SQL DDL Intro aus der Übung
2
3-- Ihre Datenbank auswählen, ändern Sie den Namen entsprechend...
4USE `db3156505`;
5
6-- Tabelle löschen, falls Sie existiert
7DROP TABLE IF EXISTS BenthältM;
8DROP TABLE IF EXISTS braucht;
9DROP TABLE IF EXISTS MenthältZ;
10DROP TABLE IF EXISTS MhatB;
11DROP TABLE IF EXISTS gehörtZu;
12DROP TABLE IF EXISTS befreundetMit;
13DROP TABLE IF EXISTS Deklarationen;
14DROP TABLE IF EXISTS Kommentare;
15DROP TABLE IF EXISTS Zutaten;
16DROP TABLE IF EXISTS Preise;
17DROP TABLE IF EXISTS Mahlzeiten;
18DROP TABLE IF EXISTS Kategorien;
19DROP TABLE IF EXISTS Bilder;
20DROP TABLE IF EXISTS Bestellungen;
21DROP TABLE IF EXISTS Fachbereiche;
22DROP TABLE IF EXISTS Studenten;
23DROP TABLE IF EXISTS Mitarbeiter;
24DROP TABLE IF EXISTS `FH Angehörige`;
25DROP TABLE IF EXISTS Gäste;
26DROP TABLE IF EXISTS Benutzer;
27
28
29-- Empfohlen ist, zuerst die Attribute der Tabellen anzulegen und die Relationen 
30-- anschließend vorzunehmen. dabei werden Sie erkennen, dass nicht jede Lösch-
31-- reihenfolge (DROP) funktioniert.
32
33CREATE TABLE Benutzer (
34	Nummer INT UNSIGNED AUTO_INCREMENT,
35	Nutzername VARCHAR(25) NOT NULL UNIQUE, -- NOT NULL weil nicht optional
36	Vorname VARCHAR(50) NOT NULL,
37	Nachname VARCHAR(50) NOT NULL,
38	Geburtsdatum DATE,
39	`Alter` INT DEFAULT DATEDIFF(CURDATE(), Geburtsdatum),
40	`E-Mail` VARCHAR(50) NOT NULL UNIQUE, -- Backticks wegen Minus im namen
41	Bild VARBINARY(1000), -- verbessern Sie die Datentypen, wenn nötig
42	Anlegedatum DATETIME DEFAULT NOW(),
43	Aktiv BOOL NOT NULL,
44	`Hash` CHAR(60) NOT NULL,
45	LetzterLogin DATETIME DEFAULT NULL,
46	PRIMARY KEY (Nummer)
47);
48
49-- Benutzer is A (Gast/FH-Angehöriger) funktioniert nicht mit den Tools die uns zur Verfügung stehen
50CREATE TABLE Gäste (
51	Grund VARCHAR(254) NOT NULL,
52	Ablaufdatum DATETIME DEFAULT DATE_ADD(NOW(), INTERVAL 1 WEEK),
53	Benutzernummer INT UNSIGNED NOT NULL,
54	PRIMARY KEY (Benutzernummer),
55	FOREIGN KEY (Benutzernummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
56);
57
58CREATE TABLE `FH Angehörige` (
59	Benutzernummer INT UNSIGNED NOT NULL,
60	PRIMARY KEY (Benutzernummer),
61	FOREIGN KEY (Benutzernummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
62);
63
64CREATE TABLE Mitarbeiter (
65	Büro CHAR(4),
66	Telefon VARCHAR(15),
67	FHAngehörigenNummer INT UNSIGNED NOT NULL,
68	PRIMARY KEY (FHAngehörigenNummer),
69	FOREIGN KEY (FHAngehörigenNummer) REFERENCES `FH Angehörige`(Benutzernummer) ON DELETE CASCADE
70);
71
72CREATE TABLE Studenten (
73	Studiengang ENUM('ET','INF','ISE','MCD','WI') NOT NULL,
74	Matrikelnummer INT NOT NULL CHECK (Matrikelnummer BETWEEN 10000000 AND 999999999),
75	FHAngehörigenNummer INT UNSIGNED NOT NULL,
76	PRIMARY KEY (FHAngehörigenNummer),
77	FOREIGN KEY (FHAngehörigenNummer) REFERENCES `FH Angehörige`(Benutzernummer) ON DELETE CASCADE
78);
79
80CREATE TABLE Fachbereiche (
81	ID INT UNSIGNED AUTO_INCREMENT,
82	Website VARCHAR(100) NOT NULL,
83	`Name` VARCHAR(100) NOT NULL,
84	PRIMARY KEY (ID)
85);
86
87CREATE TABLE Bestellungen (
88	Nummer INT UNSIGNED AUTO_INCREMENT,
89	`Bestell-Zeitpunkt` DATETIME DEFAULT NOW(),
90	`Abhol-Zeitpunkt` DATETIME DEFAULT DATE_ADD(NOW(), INTERVAL 1 HOUR),
91	`Endpreis` DECIMAL(5,2) UNSIGNED NOT NULL,
92	Benutzernummer INT UNSIGNED NOT NULL,
93	PRIMARY KEY (Nummer),
94	FOREIGN KEY (Benutzernummer) REFERENCES Benutzer(Nummer)
95);
96
97CREATE TABLE Bilder (
98	ID INT UNSIGNED AUTO_INCREMENT,
99	Titel VARCHAR(50),
100	`Alt-Text` VARCHAR(50) DEFAULT 'IMAGE', -- denken Sie auch hier an Backticks
101	`Binärdaten` BLOB,
102	PRIMARY KEY (ID)
103);
104
105CREATE TABLE Kategorien (
106	ID INT UNSIGNED AUTO_INCREMENT,
107	Bezeichnung VARCHAR(50) NOT NULL,
108	KategorienID INT UNSIGNED NOT NULL,
109	BilderID INT UNSIGNED NOT NULL,
110	PRIMARY KEY (ID),
111	FOREIGN KEY (KategorienID) REFERENCES Kategorien(ID),
112	FOREIGN KEY (BilderID) REFERENCES Bilder(ID)
113);
114
115CREATE TABLE Mahlzeiten (
116	ID INT UNSIGNED AUTO_INCREMENT,
117	Beschreibung VARCHAR(200) NOT NULL,
118	Vorrat INT UNSIGNED DEFAULT 0,
119	Verfügbar BOOL,
120	KategorienID INT UNSIGNED NOT NULL,
121	PRIMARY KEY (ID),
122	FOREIGN KEY (KategorienID) REFERENCES Kategorien(ID)
123);
124
125CREATE TABLE Preise (
126	Gastpreis DECIMAL(4,2) UNSIGNED NOT NULL,
127	`Student-Preis` DECIMAL(4,2) UNSIGNED,
128	`MA-Preis` DECIMAL(4,2) UNSIGNED,
129	Jahr INT UNSIGNED NOT NULL,
130	MahlzeitID INT UNSIGNED NOT NULL,
131	PRIMARY KEY (MahlzeitID, Jahr),
132	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID) ON DELETE CASCADE
133);
134
135CREATE TABLE Zutaten (
136	ID INT UNSIGNED NOT NULL CHECK(ID BETWEEN 10000 AND 99999),
137	`Name` VARCHAR(50) NOT NULL,
138	Bio BOOL NOT NULL,
139	Vegetarisch BOOL NOT NULL,
140	Vegan BOOL NOT NULL,
141	Glutenfrei BOOL NOT NULL,
142	PRIMARY KEY (ID)
143);
144
145CREATE TABLE Kommentare (
146	ID INT UNSIGNED AUTO_INCREMENT,
147	Bewertung INT UNSIGNED NOT NULL CHECK (Bewertung BETWEEN 1 AND 5),
148	Bemerkung TEXT,
149	MahlzeitID INT UNSIGNED NOT NULL,
150	Studentennummer INT UNSIGNED NOT NULL,
151	PRIMARY KEY (ID),
152	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID),
153	FOREIGN KEY (Studentennummer) REFERENCES Studenten(FHAngehörigenNummer)
154);
155
156CREATE TABLE Deklarationen (
157	Zeichen VARCHAR(2) NOT NULL CHECK (LENGTH(Zeichen) > 0),
158	Beschriftung VARCHAR(32) NOT NULL,
159	PRIMARY KEY (Zeichen)
160);
161
162CREATE TABLE befreundetMit (
163	Benutzernummer INT UNSIGNED NOT NULL,
164	Freundnummer INT UNSIGNED NOT NULL,
165	PRIMARY KEY (Benutzernummer, Freundnummer),
166	FOREIGN KEY (Benutzernummer) REFERENCES Benutzer(Nummer),
167	FOREIGN KEY (Freundnummer) REFERENCES Benutzer(Nummer)
168);
169
170CREATE TABLE BenthältM (
171	Anzahl INT UNSIGNED NOT NULL,
172	Bestellnummer INT UNSIGNED NOT NULL,
173	MahlzeitID INT UNSIGNED NOT NULL,
174	PRIMARY KEY (Bestellnummer, MahlzeitID),
175	FOREIGN KEY (Bestellnummer) REFERENCES Bestellungen(Nummer),
176	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID)
177);
178
179CREATE TABLE MenthältZ (
180	MahlzeitID INT UNSIGNED NOT NULL,
181	ZutatenID INT UNSIGNED NOT NULL,
182	PRIMARY KEY (MahlzeitID, ZutatenID),
183	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID),
184	FOREIGN KEY (ZutatenID) REFERENCES Zutaten(ID)
185);
186
187CREATE TABLE MhatB (
188	MahlzeitID INT UNSIGNED NOT NULL,
189	BilderID INT UNSIGNED NOT NULL,
190	PRIMARY KEY (MahlzeitID, BilderID),
191	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID),
192	FOREIGN KEY (BilderID) REFERENCES Bilder(ID)
193);
194
195CREATE TABLE braucht (
196	MahlzeitID INT UNSIGNED NOT NULL,
197	DeklarationZeichen VARCHAR(2) NOT NULL,
198	PRIMARY KEY (MahlzeitID, DeklarationZeichen),
199	FOREIGN KEY (MahlzeitID) REFERENCES Mahlzeiten(ID),
200	FOREIGN KEY (DeklarationZeichen) REFERENCES Deklarationen(Zeichen)
201);
202
203CREATE TABLE gehörtZu (
204	FHAngehörigenNummer INT UNSIGNED NOT NULL,
205	FachbereichID INT UNSIGNED NOT NULL,
206	PRIMARY KEY (FHAngehörigennummer, FachbereichID),
207	FOREIGN KEY (FHAngehörigennummer) REFERENCES `FH Angehörige`(Benutzernummer),
208	FOREIGN KEY (FachbereichID) REFERENCES Fachbereiche(ID)
209);
210
211INSERT INTO Benutzer(Nutzername, Vorname, Nachname, `E-Mail`, Aktiv, `Hash`)
212VALUES ('xXmax1Xx', 'max1', 'mustermann1', 'max1@mustermann.de', TRUE, '000000000000000000000000000000000000000000000000000000000000'),
213('xXmax2Xx', 'max2', 'mustermann2', 'max2@mustermann.de', FALSE, '000000000000000000000000000000000000000000000000000000000000'),
214('xXmax3Xx', 'max3', 'mustermann3', 'max3@mustermann.de', TRUE, '000000000000000000000000000000000000000000000000000000000000'),
215('xXmax4Xx', 'max4', 'mustermann4', 'max4@mustermann.de', TRUE, '000000000000000000000000000000000000000000000000000000000000');