· 6 years ago · Nov 08, 2019, 06:38 PM
1-- SQL DDL Intro aus der Übung
2
3-- Ihre Datenbank auswählen, ändern Sie den Namen entsprechend...
4USE `db3201476`;
5
6
7
8
9-- Tabelle löschen, falls Sie existiert
10DROP TABLE IF EXISTS hat;
11DROP TABLE IF EXISTS EnthältZutaten;
12DROP TABLE IF EXISTS Braucht;
13DROP TABLE IF EXISTS Enthält;
14DROP TABLE IF EXISTS gehörtZu;
15DROP TABLE IF EXISTS Zusammensetzung;
16DROP TABLE IF EXISTS `Preise`;
17DROP TABLE IF EXISTS `Kommentare`;
18DROP TABLE IF EXISTS `Zutaten`;
19DROP TABLE IF EXISTS `Deklarationen`;
20DROP TABLE IF EXISTS `Mahlzeiten`;
21DROP TABLE IF EXISTS `Kategorien`;
22DROP TABLE IF EXISTS `Bilder`;
23DROP TABLE IF EXISTS `Bestellungen`;
24DROP TABLE IF EXISTS `Mitarbeiter`;
25DROP TABLE IF EXISTS `Studenten`;
26DROP TABLE IF EXISTS `Gäste`;
27DROP TABLE IF EXISTS `Fachbereiche`;
28DROP TABLE IF EXISTS `FHAngehörige`;
29DROP TABLE IF EXISTS `Benutzer`;
30
31
32-- Empfohlen ist, zuerst die Attribute der Tabellen anzulegen und die Relationen
33-- anschließend vorzunehmen. dabei werden Sie erkennen, dass nicht jede Lösch-
34-- reihenfolge (DROP) funktioniert.
35
36CREATE TABLE Benutzer (
37 Nummer INT UNSIGNED NOT NULL AUTO_INCREMENT ,
38 `E-Mail` VARCHAR(255) NOT NULL, -- Backticks wegen Minus im namen
39 CONSTRAINT `E-MailEindeutig` UNIQUE (`E-Mail`),
40 Bild VARBINARY(1000), -- verbessern Sie die Datentypen, wenn nötig
41 Aktiv BOOL NOT NULL,
42 Anlegedatum DATETIME NOT NULL,
43 LetzterLogin TIMESTAMP NOT NULL,
44 Vorname VARCHAR(60) NOT NULL,
45 Nachname VARCHAR(69) NOT NULL,
46 Geburtsdatum DATE,
47 `Alter` INT UNSIGNED,
48 Nutzername VARCHAR(59) NOT NULL,
49 CONSTRAINT `NutzernameEindeutig` UNIQUE (Nutzername),
50 `Hash` VARCHAR(60) NOT NULL,
51 PRIMARY KEY (Nummer)
52);
53
54CREATE TABLE `FhAngehörige`(
55 Nummer INT UNSIGNED PRIMARY KEY ,
56 FOREIGN KEY (Nummer) REFERENCES Benutzer (Nummer)
57);
58
59CREATE TABLE Fachbereiche (
60 Website VARCHAR(50) NOT NULL,
61 `Name` VARCHAR(50) NOT NULL,
62 ID TINYINT PRIMARY KEY AUTO_INCREMENT
63);
64
65CREATE TABLE Gäste (
66 Grund VARCHAR(254) NOT NULL,
67 Ablaufdatum DATETIME DEFAULT (CURRENT_DATE + 7),
68 Nummer INT UNSIGNED PRIMARY KEY ,
69 FOREIGN KEY(Nummer) REFERENCES Benutzer(Nummer)
70);
71
72CREATE TABLE Studenten (
73 Studiengang TINYINT NOT NULL,
74 Martrikelnummer INT (9) NOT NULL ,
75 Nummer INT UNSIGNED PRIMARY KEY,
76 FOREIGN KEY (Nummer) REFERENCES `Benutzer`(Nummer),
77 CONSTRAINT StudentenEindeutig UNIQUE (Martrikelnummer),
78 CHECK (Studiengang = 'ET' OR Studiengang = 'INF' OR Studiengang = 'MCD' OR Studiengang = 'WI')
79);
80
81CREATE TABLE Mitarbeiter (
82 Büro VARCHAR(50),
83 Telefon INT,
84 Nummer INT UNSIGNED PRIMARY KEY
85);
86
87
88CREATE TABLE Bestellungen (
89 Nummer INT UNSIGNED NOT NULL AUTO_INCREMENT,
90 Bestellzeitpunkt DATETIME NOT NULL DEFAULT (CURRENT_DATE) ,
91 Abholzeitpunkt DATETIME,
92 Endpreis Float NOT NULL,
93 bestelltVon INT UNSIGNED NOT NULL,
94 FOREIGN KEY (bestelltVon) REFERENCES Benutzer(Nummer),
95 PRIMARY KEY (Nummer),
96 CHECK (Bestellzeitpunkt < Abholzeitpunkt)
97);
98
99CREATE TABLE Bilder (
100 ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
101 Titel INT(10) UNSIGNED NOT NULL DEFAULT 0,
102 `Binärdaten` INT(10) UNSIGNED NOT NULL DEFAULT 0,
103 `Alt-Text` TINYINT(1) NULL DEFAULT NULL,
104 PRIMARY KEY (`ID`)
105);
106
107CREATE TABLE Kategorien (
108 Bezeichnung VARCHAR(50) NOT NULL ,
109 ID INT NOT NULL AUTO_INCREMENT ,
110 hatBilder INT(10) UNSIGNED NOT NULL,
111 hatKategorien INT NOT NULL ,
112 FOREIGN KEY (hatBilder) REFERENCES Bilder(ID),
113 FOREIGN KEY (hatKategorien) REFERENCES Kategorien(ID),
114 PRIMARY KEY (ID)
115);
116--
117CREATE TABLE Mahlzeiten (
118 Beschreibung VARCHAR(50),
119 ID INT PRIMARY KEY AUTO_INCREMENT,
120 Verfügbar BOOL,
121 Vorrat INT DEFAULT 0,
122 `in` INT NOT NULL ,
123 FOREIGN KEY (`in`) REFERENCES Kategorien(ID)
124);
125
126CREATE TABLE Deklarationen (
127 Zeichen CHAR (2) NOT NULL,
128 Beschriftung VARCHAR (32) NOT NULL,
129 PRIMARY KEY (Zeichen)
130);
131--
132
133
134CREATE TABLE Zutaten (
135 ID INT (5) NOT NULL ,
136 `Name` VARCHAR (50) NOT NULL ,
137 Bio BOOL NOT NULL ,
138 Vegetarisch BOOL NOT NULL ,
139 Vegan BOOL NOT NULL ,
140 Glutenfrei BOOL NOT NULL
141);
142
143
144--
145CREATE TABLE Kommentare (
146 Bemerkung VARCHAR (50),
147 Bewertung VARCHAR (50) NOT NULL,
148 ID INT NOT NULL AUTO_INCREMENT ,
149 zu INT,
150 geschriebenVon INT UNSIGNED,
151 FOREIGN KEY (geschriebenVon) REFERENCES Studenten(Nummer),
152 FOREIGN KEY (zu) REFERENCES Mahlzeiten(ID),
153 PRIMARY KEY (ID)
154);
155--
156
157--
158CREATE TABLE Preise (
159 Jahr YEAR NOT NULL ,
160 Gastpreis FLOAT NOT NULL,
161 Studentpreis FLOAT ,
162 `MA-Preis` FLOAT,
163 ID INT,
164 PRIMARY KEY(Jahr, ID) ,
165 FOREIGN KEY (ID) REFERENCES Mahlzeiten (ID),
166 CHECK (Gastpreis < 100 AND Gastpreis > 0),
167 CHECK (Studentpreis < `Ma-Preis`) ,
168 CHECK (`MA-Preis` <= 99.99 AND `MA-Preis`> 0)
169);
170
171
172CREATE TABLE gehörtZu(
173 ID TINYINT ,
174 Nummer INT UNSIGNED NOT NULL,
175 FOREIGN KEY (ID) REFERENCES Fachbereiche(ID),
176 FOREIGN KEY (Nummer) REFERENCES `FhAngehörige`(Nummer)
177);
178
179
180CREATE TABLE Enthält(
181 Nummer INT UNSIGNED NOT NULL ,
182 ID INT ,
183 Anzahl INT UNSIGNED,
184 FOREIGN KEY (Nummer) REFERENCES Bestellungen(Nummer),
185 FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID)
186
187);
188
189
190
191CREATE TABLE Braucht(
192 Zeichen CHAR (2) NOT NULL ,
193 ID INT ,
194 FOREIGN KEY (Zeichen) REFERENCES Deklarationen (Zeichen),
195 FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID)
196);
197
198
199
200
201CREATE TABLE EnthältZutaten(
202 IDz INT (5) NOT NULL,
203 ID INT
204 -- FOREIGN KEY (IDz) REFERENCES Zutaten(ID),
205 -- FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID)
206);
207
208
209
210CREATE TABLE hat(
211 IDb INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
212 ID INT ,
213 FOREIGN KEY (IDb) REFERENCES Bilder(ID),
214 FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID)
215);
216
217
218
219CREATE TABLE Zusammensetzung(
220 Nummer INT UNSIGNED NOT NULL ,
221 befreundetMit INT UNSIGNED NOT NULL PRIMARY KEY ,
222 FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer),
223 FOREIGN KEY (befreundetMit) REFERENCES Benutzer (Nummer)
224);
225
226INSERT INTO Benutzer (Vorname, Nachname, Geburtsdatum, `E-Mail`, Aktiv, Anlegedatum, `Hash`, Bild, Nutzername )
227VALUES ('Max', 'Mustermann', '1997-09-12', 'maxmuster@gmx.de', '1', '2018-09-12', ' ', ' ', 'Maxi');
228INSERT INTO Benutzer (Vorname, Nachname, Geburtsdatum, `E-Mail`, Aktiv, Anlegedatum, `Hash`, Bild, Nutzername )
229VALUES('Maria', 'Meier', '1999-08-17', 'mariameier@gmx.de', '0', '2014-12-13', ' ', ' ', 'Mari');
230INSERT INTO Benutzer (Vorname, Nachname, Geburtsdatum, `E-Mail`, Aktiv, Anlegedatum, `Hash`, Bild, Nutzername )
231VALUES ('Kai', 'Schulze', '1995-09-30', 'kaischule@gmx.de', '1', '2010-06-20', ' ', ' ', 'Kaii');
232INSERT INTO Benutzer (Vorname, Nachname, Geburtsdatum, `E-Mail`, Aktiv, Anlegedatum, `Hash`, Bild, Nutzername )
233VALUES ('Jan', 'Fuchs', '1999-05-24', 'janfuchs@gmx.de', '1', '2009-07-19', ' ', ' ', 'Jani');
234
235INSERT INTO Mitarbeiter (Nummer)
236SELECT Nummer FROM Benutzer
237WHERE Nummer = 1;
238
239INSERT INTO Studenten (Nummer,Studiengang, Matrikelnummer)
240SELECT Nummer, Studiengang, Matrikelnummer FROM Benutzer
241WHERE Nummer = 2;
242
243
244INSERT INTO Studenten (Nummer, Studiengang, Matrikelnummer)
245SELECT Nummer, Studiengang, Matrikelnummer FROM Benutzer
246WHERE Nummer = 3;
247
248DELETE FROM Benutzer WHERE Nummer = 4;