· 6 years ago · Nov 06, 2019, 03:28 PM
1-- SQL DDL Intro aus der Übung
2
3-- Ihre Datenbank auswählen, ändern Sie den Namen entsprechend...
4USE `db3196863`;
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
49CREATE TABLE Gäste ( -- Man kann diese [1,1] mit den zu Verfügung stehenden Tools nicht umsetzen
50 Grund VARCHAR(254) NOT NULL,
51 Ablaufdatum DATETIME DEFAULT DATE_ADD(NOW(), INTERVAL 1 WEEK),
52 BenutzerNummer INT UNSIGNED NOT NULL,
53 PRIMARY KEY (BenutzerNummer),
54 FOREIGN KEY (BenutzerNummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
55);
56
57CREATE TABLE `FH Angehörige` ( -- Man kann diese [1,1] mit den zu Verfügung stehenden Tools nicht umsetzen
58 BenutzerNummer INT UNSIGNED NOT NULL,
59 PRIMARY KEY (BenutzerNummer),
60 FOREIGN KEY (BenutzerNummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
61);
62
63CREATE TABLE Mitarbeiter (
64 Büro CHAR(4),
65 Telefon VARCHAR(15),
66 FHAngehörigenNummer INT UNSIGNED NOT NULL,
67 PRIMARY KEY (FHAngehörigenNummer),
68 FOREIGN KEY (FHAngehörigenNummer) REFERENCES `FH Angehörige`(Benutzernummer) ON DELETE CASCADE
69);
70
71CREATE TABLE Studenten (
72 Studiengang ENUM('ET','INF','ISE','MCD','WI') NOT NULL,
73 Matrikelnummer INT NOT NULL CHECK (Matrikelnummer BETWEEN 10000000 AND 999999999),
74 FHAngehörigenNummer INT UNSIGNED NOT NULL,
75 PRIMARY KEY (FHAngehörigenNummer),
76 FOREIGN KEY (FHAngehörigenNummer) REFERENCES `FH Angehörige`(Benutzernummer) ON DELETE CASCADE
77);
78
79CREATE TABLE Fachbereiche (
80 ID INT UNSIGNED AUTO_INCREMENT,
81 Website VARCHAR(100) NOT NULL,
82 `Name` VARCHAR(100) NOT NULL,
83 PRIMARY KEY (ID)
84);
85
86CREATE TABLE Bestellungen (
87 Nummer INT UNSIGNED AUTO_INCREMENT,
88 `Bestell-Zeitpunkt` DATETIME DEFAULT NOW(),
89 `Abhol-Zeitpunkt` DATETIME DEFAULT DATE_ADD(NOW(), INTERVAL 1 HOUR),
90 `Endpreis` DECIMAL(5,2) UNSIGNED NOT NULL,
91 Benutzernummer INT UNSIGNED NOT NULL,
92 PRIMARY KEY (Nummer),
93 FOREIGN KEY (Benutzernummer) REFERENCES Benutzer(Nummer)
94);
95
96CREATE TABLE Bilder (
97 ID INT UNSIGNED AUTO_INCREMENT,
98 Titel VARCHAR(50),
99 `Alt-Text` VARCHAR(50) DEFAULT 'IMAGE', -- denken Sie auch hier an Backticks
100 `Binärdaten` BLOB,
101 PRIMARY KEY (ID)
102);
103
104CREATE TABLE Kategorien (
105 ID INT UNSIGNED AUTO_INCREMENT,
106 Bezeichnung VARCHAR(50) NOT NULL,
107 KategorienID INT UNSIGNED NOT NULL,
108 BilderID INT UNSIGNED NOT NULL,
109 PRIMARY KEY (ID),
110 FOREIGN KEY (KategorienID) REFERENCES Kategorien(ID),
111 FOREIGN KEY (BilderID) REFERENCES Bilder(ID)
112);
113
114CREATE TABLE Mahlzeiten (
115 ID INT UNSIGNED AUTO_INCREMENT,
116 Beschreibung VARCHAR(200) NOT NULL,
117 Vorrat INT UNSIGNED NOT NULL DEFAULT 0,
118 Verfügbar BOOL,
119 KategorienID INT UNSIGNED NOT NULL,
120 PRIMARY KEY (ID),
121 FOREIGN KEY (KategorienID) REFERENCES Kategorien(ID)
122);
123
124CREATE TABLE Preise (
125 Gastpreis DECIMAL(4,2) UNSIGNED NOT NULL,
126 `Student-Preis` DECIMAL(4,2) UNSIGNED,
127 `MA-Preis` DECIMAL(4,2) UNSIGNED,
128 Jahr INT UNSIGNED NOT NULL,
129 MahlzeitenID INT UNSIGNED NOT NULL,
130 PRIMARY KEY (MahlzeitenID, Jahr),
131 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID) ON DELETE CASCADE
132);
133
134CREATE TABLE Zutaten (
135 ID INT UNSIGNED NOT NULL CHECK(ID BETWEEN 10000 AND 99999),
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 PRIMARY KEY (ID)
142);
143
144CREATE TABLE Kommentare (
145 ID INT UNSIGNED AUTO_INCREMENT,
146 Bewertung INT UNSIGNED NOT NULL CHECK (Bewertung BETWEEN 1 AND 5),
147 Bemerkung TEXT,
148 MahlzeitenID INT UNSIGNED NOT NULL,
149 StudentenNummer INT UNSIGNED NOT NULL,
150 PRIMARY KEY (ID),
151 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID),
152 FOREIGN KEY (StudentenNummer) REFERENCES Studenten(FHAngehörigenNummer)
153);
154
155CREATE TABLE Deklarationen (
156 Zeichen VARCHAR(2) NOT NULL CHECK(LENGTH(Zeichen) > 0),
157 Beschriftung VARCHAR(32) NOT NULL,
158 PRIMARY KEY (Zeichen)
159);
160
161CREATE TABLE befreundetMit (
162 Benutzernummer INT UNSIGNED NOT NULL,
163 Freundnummer INT UNSIGNED NOT NULL,
164 PRIMARY KEY (Benutzernummer, Freundnummer),
165 FOREIGN KEY(Benutzernummer) REFERENCES Benutzer(Nummer),
166 FOREIGN KEY(Freundnummer) REFERENCES Benutzer(Nummer)
167);
168
169CREATE TABLE BenthältM (
170 Anzahl INT UNSIGNED NOT NULL,
171 Bestellnummer INT UNSIGNED NOT NULL,
172 MahlzeitenID INT UNSIGNED NOT NULL,
173 PRIMARY KEY (Bestellnummer, MahlzeitenID),
174 FOREIGN KEY (Bestellnummer) REFERENCES Bestellungen(Nummer),
175 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID)
176);
177
178CREATE TABLE MenthältZ (
179 MahlzeitenID INT UNSIGNED NOT NULL,
180 ZutatenID INT UNSIGNED NOT NULL,
181 PRIMARY KEY(MahlzeitenID,ZutatenID),
182 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID),
183 FOREIGN KEY (ZutatenID) REFERENCES Zutaten(ID)
184);
185
186CREATE TABLE MhatB (
187 MahlzeitenID INT UNSIGNED NOT NULL,
188 BilderID INT UNSIGNED NOT NULL,
189 PRIMARY KEY (MahlzeitenID, BilderID),
190 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID),
191 FOREIGN KEY (BilderID) REFERENCES Bilder(ID)
192);
193
194CREATE TABLE braucht (
195 MahlzeitenID INT UNSIGNED NOT NULL,
196 DeklarationenZEICHEN VARCHAR(2) NOT NULL,
197 PRIMARY KEY(MahlzeitenID,DeklarationenZEICHEN),
198 FOREIGN KEY (MahlzeitenID) REFERENCES Mahlzeiten(ID);
199 FOREIGN KEY (DeklarationenZEICHEN) REFERENCES Deklarationen(Zeichen)
200);
201
202CREATE TABLE gehörtZu (
203 FHAngehörigenNummer INT UNSIGNED NOT NULL,
204 FachbereicheID INT UNSIGNED NOT NULL,
205 PRIMARY KEY (FHAngehörigenNummer, FachbereicheID),
206 FOREIGN KEY (FHAngehörigenNummer) REFERENCES `FH Angehörige`(BenutzerNummer),
207 FOREIGN KEY (FachbereicheID) REFERENCES Fachbereiche(ID)
208);
209
210INSERT INTO Benutzer(Nutzername, Vorname, Nachname, `E-Mail`, Aktiv, `Hash`)
211VALUES ('M and M', 'Marko', 'Mamic', 'mm@gmail.com', TRUE, '111111111111111111111111111111111111111111111111111111111111'),
212 ('B and B', 'Barko', 'Bamic', 'bb@gmail.com', FALSE, '111111111111111111111111111111111111111111111111111111111111'),
213 ('C and C', 'Carko', 'Camic', 'cc@gmail.com', TRUE, '111111111111111111111111111111111111111111111111111111111111'),
214 ('D and D', 'Darko', 'Damic', 'dd@gmail.com', TRUE, '111111111111111111111111111111111111111111111111111111111111');