LRHENfiU

1drop database if exists pizza_express;
2create database pizza_express;
3use pizza_express;
4
5create table plz (
6	id		serial, -- bigint unsigned not null auto_increment
7	plz		smallint(4) unsigned not null,
8	ort		varchar(255) not null,
9	
10	primary key(id),
11	unique(plz, ort)
12);
13
14create table kunde (
15	id			serial,
16	plz_id		bigint unsigned not null,
17	vorname		varchar(255) not null,
18	nachname	varchar(255) not null,
19	strasse		varchar(255) not null, -- inkl. Hausnummer
20	email		varchar(384) not null unique,
21	passwort	varchar(255) not null,
22	telefon		varchar(255),
23	
24	primary key(id),
25	foreign key(plz_id) references plz(id) on update cascade on delete cascade
26);
27
28create table kategorie (
29	id			serial,
30	kategorie	varchar(255) not null unique,
31	primary key(id)
32);
33
34create table produkt (
35	id				serial,
36	kategorie_id	bigint unsigned not null,
37	bezeichnung		varchar(255) not null,
38	beschreibung	varchar(255) not null,
39	preis			decimal(6,2) not null, -- Preis des Produktes im Shop
40	
41	primary key(id),
42	foreign key(kategorie_id) references kategorie(id) on update cascade on delete cascade
43);
44
45create table bestellung (
46	id				serial,
47	kunde_id		bigint unsigned not null,
48	eingang			datetime not null default current_timestamp,
49	geliefert		datetime null,
50	
51	primary key(id),
52	foreign key(kunde_id) references kunde(id) on update cascade on delete cascade
53);
54
55create table produkt_bestellung (
56	id				serial,
57	produkt_id		bigint unsigned not null,
58	bestellung_id	bigint unsigned not null,
59	anzahl			int unsigned not null default 1,
60	preis			decimal(6,2) null, -- Gültiger Preis zum Zeitpunkt der Bestellung
61	
62	primary key(id),
63	foreign key(produkt_id) references produkt(id) on update cascade on delete cascade,
64	foreign key(bestellung_id) references bestellung(id) on update cascade on delete cascade
65);
66
67
68use pizza_express;
69
70-- Transaktion starten
71begin;
72
73insert into plz (plz, ort) values
74	(8001, 'Zürich'),
75	(8002, 'Zürich'),
76	(8048, 'Zürich'),
77	(8008, 'Zürich'),
78	(8049, 'Zürich'),
79	(8051, 'Zürich'),
80	(8902, 'Urdorf'),
81	(8952, 'Schlieren'),
82	(8600, 'Dübendorf'),
83	(8402, 'Winterthur');
84	
85insert into kunde (plz_id, vorname, nachname, strasse, email, passwort, telefon) values
86	(2, 'Hans', 'Muster', 'Nordstrasse 1', 'hans@muster.com', sha('insecure'), '044 123 45 67'), 
87	(5, 'Barbara', 'Meier', 'Baslerstrasse 20', 'b.meier@gmail.com', sha('1234'), '044 101 45 80'),
88	(6, 'Fritz', 'Müller', 'Bernerstrasse 209', 'fritz@mueller.com', sha('spiderman'), NULL),
89	(3, 'Freddy', 'Brunner', 'Blumenweg 10', 'fred@outlook.com', sha('porsche'), '044 330 23 09'),
90	(4, 'Peter', 'Keller', 'Seestrasse 171', 'peter.keller@outlook.com', sha('letmein'), NULL),
91	(7, 'Bruno', 'Kuster', 'Dorfstrasse 21', 'bk@hotmail.com', sha('kakadu'), '044 880 13 50'),
92	(8, 'Brigitte', 'Maler', 'Alte Landstrasse 201', 'brigitte@gmail.com', sha('starwars'), '043 627 89 14'),
93	(6, 'Andrea', 'Pfister', 'Stadthausstrasse 9a', 'apfister98@gmail.com', sha('4ocean'), NULL),
94	(9, 'Heinz', 'Fuhrer', 'Überlandstrasse 21', 'hfuhrer@outlook.com', sha('foobar'), '044 443 50 84'),
95	(10, 'Stephanie', 'Gerber', 'Schulhausstrasse 4', 'stephi@gmail.com', sha('password'), '078 210 40 53');
96
97insert into kategorie (kategorie) values
98	('Pizza'),
99	('Salate'),
100	('Getränke'),
101	('Pasta'),
102	('Dessert');
103	
104insert into produkt (kategorie_id, bezeichnung, beschreibung, preis) values
105	(1, 'Margherita', 'Tomaten, Mozzarella', 17.00),
106	(1, 'Prosciutto', 'Tomaten, Schinken, Mozzarella', 18.00),
107	(1, 'Siziliana', 'Tomaten, Salami, Mozzarella', 19.00),
108	(1, 'Quattro Stagioni', 'Tomaten, Schinken, Peperoni, Artischocken, Mozzarella', 17.00),
109	(1, 'Padrone', 'Tomaten, Kalbfleisch, Mozzarella', 17.00),
110	(2, 'Kleiner grüner Salat', 'Grüner Blattsalat', 6.00),
111	(2, 'Kleiner gemischter Salat', 'Gemischter Blattsalat', 7.00),
112	(3, 'Coca-Cola', '5dl', 4.50),
113	(3, 'Fanta', '5dl', 4.50),
114	(3, 'Valser', '5dl', 4.50),
115	(3, 'Red Bull', '33cl', 5.50),
116	(1, 'Hawaii', 'Tomaten, Schinken, Ananas, Mozzarella', 18.00),
117	(1, 'Tonno', 'Tomaten, Thunfisch, Mozzarella', 18.00),
118	(1, 'Fiorentina', 'Tomaten, Zwiebeln, Spinat, Mascarpone, Mozzarella', 19.00),
119	(1, 'Calzone', 'Tomaten, Schinken, Champignons, Ei, Mozzarella', 20.00),
120	(1, 'Ai Funghi', 'Tomaten, Champignons, Mozzarella', 18.00),
121	(3, 'Sprite', '5dl', 4.50),
122	(3, 'Red Bull', '50cl', 4.50),
123	(2, 'Insalata Caprese', 'Tomaten, Mozzarella, Balsamico', 11.00),
124	(4, 'Spaghetti Carbonara', 'Rahmsauce, Zwiebel, Speck, Käse', 17.00),
125	(4, 'Spaghetti Bolognese', 'Tomatensauce mit Rindfleisch', 18.00),
126	(4, 'Spaghetti Aglio e olio', 'Tomatensauce mit Rindfleisch', 16.00),
127	(4, 'Penne Arrabiata', 'Scharfe Tomatensauce', 16.00);
128	
129
130insert into bestellung (kunde_id, eingang) values
131	(2, current_timestamp - interval 5 day),
132	(1, current_timestamp - interval 5 day),
133	(4, current_timestamp - interval 5 day),
134	(2, current_timestamp - interval 5 day),
135	(2, current_timestamp - interval 4 day),
136	(1, current_timestamp - interval 4 day),
137	(3, current_timestamp - interval 4 day),
138	(5, current_timestamp - interval 4 day),
139	(6, current_timestamp - interval 4 day),
140	(1, current_timestamp - interval 4 day),
141	(3, current_timestamp - interval 4 day),
142	(7, current_timestamp - interval 3 day),
143	(4, current_timestamp - interval 3 day),
144	(4, current_timestamp - interval 3 day),
145	(8, current_timestamp - interval 2 day),
146	(9, current_timestamp - interval 2 day),
147	(1, current_timestamp - interval 1 day),
148	(5, current_timestamp - interval 1 day),
149	(8, current_timestamp - interval 1 day),
150	(7, current_timestamp - interval 1 day),
151	(5, current_timestamp - interval 1 day),
152	(9, current_timestamp),
153	(2, current_timestamp),
154	(5, current_timestamp),
155	(3, current_timestamp),
156	(6, current_timestamp);
157	
158insert into produkt_bestellung (produkt_id, bestellung_id, anzahl) values
159	(3, 1, 1),
160	(4, 1, 2),
161	(5, 1, 3),
162	(1, 2, 1),
163	(3, 2, 1),
164	(2, 3, 1),
165	(2, 4, 2),
166	(7, 4, 2),
167	(8, 5, 1),
168	(6, 5, 1),
169	(1, 5, 4),
170	(3, 5, 1),
171	(1, 6, 1),
172	(6, 6, 3),
173	(2, 6, 1),
174	(3, 7, 1),
175	(4, 7, 2),
176	(3, 1, 1),
177	(4, 1, 2),
178	(5, 1, 3),
179	(1, 2, 1),
180	(3, 2, 1),
181	(2, 3, 1),
182	(2, 4, 2),
183	(7, 4, 2),
184	(8, 5, 1),
185	(6, 5, 1),
186	(1, 5, 4),
187	(3, 5, 1),
188	(1, 6, 1),
189	(6, 6, 3),
190	(2, 6, 1),
191	(3, 7, 1),
192	(4, 7, 2),
193	(3, 1, 1),
194	(4, 1, 2),
195	(5, 1, 3),
196	(1, 2, 1),
197	(3, 2, 1),
198	(2, 3, 1),
199	(2, 4, 2),
200	(7, 4, 2),
201	(8, 5, 1),
202	(6, 5, 1),
203	(1, 5, 4),
204	(3, 5, 1),
205	(1, 6, 1),
206	(6, 6, 3),
207	(2, 6, 1),
208	(3, 7, 1),
209	(4, 7, 2),
210	(3, 8, 1),
211	(4, 8, 2),
212	(5, 8, 3),
213	(1, 8, 1),
214	(3, 8, 1),
215	(2, 9, 1),
216	(2, 9, 2),
217	(17, 10, 1),
218	(5, 11, 1),
219	(15, 11, 1),
220	(10, 11, 1),
221	(13, 12, 1),
222	(1, 12, 2),
223	(13, 13, 2),
224	(12, 14, 1),
225	(11, 14, 1),
226	(18, 14, 2),
227	(3, 14, 1),
228	(14, 15, 2),
229	(15, 15, 3),
230	(11, 15, 1),
231	(13, 15, 1),
232	(5, 15, 4),
233	(16, 15, 2),
234	(7, 15, 2),
235	(8, 16, 3),
236	(14, 16, 1),
237	(13, 16, 2),
238	(17, 17, 1),
239	(16, 18, 1),
240	(6, 19, 1),
241	(2, 19, 1),
242	(6, 19, 1),
243	(4, 20, 1),
244	(8, 20, 1),
245	(14, 21, 1),
246	(19, 21, 1),
247	(9, 21, 2),
248	(2, 22, 1),
249	(12, 23, 1),
250	(8, 23, 3),
251	(18, 23, 1),
252	(12, 24, 5),
253	(15, 24, 2),
254	(3, 24, 2),
255	(3, 25, 1),
256	(6, 26, 1),
257	(14, 26, 1),
258	(9, 26, 1);
259	
260-- Aktuellen Preis setzen
261update
262	produkt p inner join produkt_bestellung pb on p.id = produkt_id
263	set pb.preis = p.preis
264;
265
266-- Transaktion abschliessen
267commit;
268
269
270-- Ändern Sie den Namen eines Kunden
271update
272	kunde
273set
274	nachname = 'Kuster'
275where
276	id = 6;
277
278-- Ändern Sie die Beschreibung und den Preis eines Produkts
279update
280	produkt
281set
282	beschreibung = 'Olivenöl, Knoblauch',
283	preis = 17.00
284where
285	id = 22;
286
287-- Erhöhen Sie den Preis aller Produkte um 10%
288update
289	produkt
290set
291	preis = preis * 1.1
292;
293
294-- Ändern Sie Kunde und Produkt einer Bestellung
295update
296	bestellung
297set
298	kunde_id = 8
299where
300	id = 25
301;
302
303update
304	produkt_bestellung
305set
306	produkt_id = 5
307where
308	id = 97
309;
310
311-- Alternative mit JOIN
312update
313	produkt
314		join produkt_bestellung on produkt_id = produkt.id
315		join bestellung on bestellung_id = bestellung.id
316set
317	kunde_id = 8,
318	produkt_id = 5
319where
320	bestellung_id = 25
321;
322
323
324-- Löschen Sie eine Bestellung
325delete from bestellung where id = 3;
326
327-- Löschen Sie einen Kunden
328delete from kunde where id = 5;
329
330-- Löschen Sie einen Kunden
331delete from kategorie;
332
333
334-- Erzeugen Sie einen neuen lokalen Datenbankbenutzer namens backup
335CREATE USER backup@'localhost';
336
337-- Benutzer mit Passort
338CREATE USER backup@'localhost' IDENTIFIED BY 'passwort';
339
340-- Geben Sie dem neuen Benutzer das Recht aus allen Tabellen ihrer Datenbank zu lesen.
341GRANT SELECT ON pizza_express.* TO backup@'localhost';
342
343-- Ändern Sie das Passwort des neuen Benutzers.
344SET PASSWORD FOR backup@'localhost' = PASSWORD('StrongPassword');
345
346-- Testen Sie die Rechte des neuen Benutzers mit der MariaDB Konsole.
347mysql -u backup -p
348> SELECT * FROM plz; -- Funktioniert
349> DELETE FROM plz WHERE id = 1; -- Permission denied
350
351-- Löschen Sie den Benutzer wieder
352DROP USER backup@'localhost';
353
354
355-- Erzeugen Sie einen neuen globalen Benutzer namens backoffice.
356CREATE USER backoffice@'%' IDENTIFIED BY 'passwort';
357
358-- Geben Sie dem neuen Benutzer das Recht aus allen Tabellen ihrer Datenbank zu lesen.
359GRANT SELECT ON pizza_express.* TO backoffice@'localhost';
360
361-- Geben Sie dem neuen Benutzer das Recht Kunden und Bestellungen zu erfassen, zu ändern und zu löschen.
362GRANT INSERT,UPDATE,DELETE ON pizza_express.kunde TO backoffice@'localhost';
363GRANT INSERT,UPDATE,DELETE ON pizza_express.bestellung TO backoffice@'localhost';
364
365-- Testen Sie die Rechte des neuen Benutzers mit der MariaDB Konsole.