· 5 years ago · May 10, 2020, 09:48 AM
1-- MySQL Script generated by MySQL Workbench
2-- Sun May 10 11:28:39 2020
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`dodatek`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`dodatek` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `nazwa` VARCHAR(45) NULL,
26 PRIMARY KEY (`id`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`umowa`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`umowa` (
34 `id` INT NOT NULL AUTO_INCREMENT,
35 `pracownik_id` INT NOT NULL,
36 `stawka` INT NOT NULL,
37 `stawka_za_nadgodziny` INT NULL,
38 `rodzaj_umowy` ENUM("b2b", "uop", "uod", "uz") NOT NULL,
39 `termin_rozpoczęcia_umowy` DATE NOT NULL,
40 `termin_zakończenia_umowy` DATE NULL,
41 PRIMARY KEY (`id`))
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `mydb`.`restauracja`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `mydb`.`restauracja` (
49 `id` INT NOT NULL AUTO_INCREMENT,
50 `nazwa` VARCHAR(45) NOT NULL,
51 `miasto` VARCHAR(45) NOT NULL,
52 `ulica` VARCHAR(45) NOT NULL,
53 `nr_domu` VARCHAR(5) NOT NULL,
54 `nr_lokalu` VARCHAR(5) NOT NULL,
55 `nr_telefonu` VARCHAR(11) NOT NULL,
56 `kod_pocztowy` VARCHAR(5) NOT NULL,
57 PRIMARY KEY (`id`),
58 UNIQUE INDEX `nr_telefonu_UNIQUE` (`nr_telefonu` ASC) VISIBLE)
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `mydb`.`pracownik`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `mydb`.`pracownik` (
66 `id` INT NOT NULL AUTO_INCREMENT,
67 `imie` VARCHAR(45) NOT NULL,
68 `nazwisko` VARCHAR(45) NOT NULL,
69 `pesel` VARCHAR(11) NOT NULL,
70 `miasto` VARCHAR(45) NOT NULL,
71 `ulica` VARCHAR(45) NOT NULL,
72 `nr_domu` VARCHAR(5) NOT NULL,
73 `nr_mieszkania` VARCHAR(5) NULL,
74 `kod_pocztowy` VARCHAR(5) NULL,
75 `telefon` VARCHAR(11) NULL,
76 `umowa_id` INT NOT NULL,
77 `restauracja_id` INT NOT NULL,
78 PRIMARY KEY (`id`, `umowa_id`, `restauracja_id`),
79 INDEX `fk_pracownik_umowa1_idx` (`umowa_id` ASC) VISIBLE,
80 INDEX `fk_pracownik_restauracja1_idx` (`restauracja_id` ASC) VISIBLE,
81 UNIQUE INDEX `pesel_UNIQUE` (`pesel` ASC) VISIBLE,
82 CONSTRAINT `fk_pracownik_umowa1`
83 FOREIGN KEY (`umowa_id`)
84 REFERENCES `mydb`.`umowa` (`id`)
85 ON DELETE NO ACTION
86 ON UPDATE NO ACTION,
87 CONSTRAINT `fk_pracownik_restauracja1`
88 FOREIGN KEY (`restauracja_id`)
89 REFERENCES `mydb`.`restauracja` (`id`)
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION)
92ENGINE = InnoDB;
93
94
95-- -----------------------------------------------------
96-- Table `mydb`.`okres_urlopowy`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `mydb`.`okres_urlopowy` (
99 `id` INT NOT NULL AUTO_INCREMENT,
100 `termin_od` DATE NOT NULL,
101 `termin_do` DATE NOT NULL,
102 `pracownik_id` INT NOT NULL,
103 `pracownik_umowa_id` INT NOT NULL,
104 PRIMARY KEY (`id`, `pracownik_id`, `pracownik_umowa_id`),
105 INDEX `fk_okres_urlopowy_pracownik1_idx` (`pracownik_id` ASC, `pracownik_umowa_id` ASC) VISIBLE,
106 CONSTRAINT `fk_okres_urlopowy_pracownik1`
107 FOREIGN KEY (`pracownik_id` , `pracownik_umowa_id`)
108 REFERENCES `mydb`.`pracownik` (`id` , `umowa_id`)
109 ON DELETE NO ACTION
110 ON UPDATE NO ACTION)
111ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `mydb`.`dostawca`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `mydb`.`dostawca` (
118 `id` INT NOT NULL AUTO_INCREMENT,
119 `nip` DECIMAL(10) NOT NULL,
120 `nazwa_firmy` VARCHAR(45) NOT NULL,
121 `miasto` VARCHAR(45) NOT NULL,
122 `ulica` VARCHAR(45) NOT NULL,
123 `nr_domu` VARCHAR(5) NOT NULL,
124 `nr_lokalu` VARCHAR(5) NULL,
125 `nr_telefonu` VARCHAR(11) NULL,
126 `nr_konta` VARCHAR(26) NOT NULL,
127 `kod_pocztowy` VARCHAR(5) NOT NULL,
128 PRIMARY KEY (`id`),
129 UNIQUE INDEX `nip_UNIQUE` (`nip` ASC) VISIBLE)
130ENGINE = InnoDB;
131
132
133-- -----------------------------------------------------
134-- Table `mydb`.`zamowienie`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `mydb`.`zamowienie` (
137 `id` INT NOT NULL AUTO_INCREMENT,
138 `typ` ENUM("zamowienie od klienta", "dostawa") NOT NULL,
139 `status` ENUM("zrealizowane", "w trakcie") NOT NULL,
140 `koszt_dostawy` DOUBLE NOT NULL,
141 `restauracja_id` INT NOT NULL,
142 `dostawca_id` INT NOT NULL,
143 PRIMARY KEY (`id`, `restauracja_id`, `dostawca_id`),
144 INDEX `fk_zamowienie_restauracja1_idx` (`restauracja_id` ASC) VISIBLE,
145 INDEX `fk_zamowienie_dostawca1_idx` (`dostawca_id` ASC) VISIBLE,
146 CONSTRAINT `fk_zamowienie_restauracja1`
147 FOREIGN KEY (`restauracja_id`)
148 REFERENCES `mydb`.`restauracja` (`id`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION,
151 CONSTRAINT `fk_zamowienie_dostawca1`
152 FOREIGN KEY (`dostawca_id`)
153 REFERENCES `mydb`.`dostawca` (`id`)
154 ON DELETE NO ACTION
155 ON UPDATE NO ACTION)
156ENGINE = InnoDB;
157
158
159-- -----------------------------------------------------
160-- Table `mydb`.`magazyn`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `mydb`.`magazyn` (
163 `id` INT NOT NULL AUTO_INCREMENT,
164 `restauracja_id` INT NOT NULL,
165 PRIMARY KEY (`id`, `restauracja_id`),
166 INDEX `fk_magazyn_restauracja1_idx` (`restauracja_id` ASC) VISIBLE,
167 CONSTRAINT `fk_magazyn_restauracja1`
168 FOREIGN KEY (`restauracja_id`)
169 REFERENCES `mydb`.`restauracja` (`id`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION)
172ENGINE = InnoDB;
173
174
175-- -----------------------------------------------------
176-- Table `mydb`.`produkt`
177-- -----------------------------------------------------
178CREATE TABLE IF NOT EXISTS `mydb`.`produkt` (
179 `id` INT NOT NULL AUTO_INCREMENT,
180 `nazwa` VARCHAR(45) NOT NULL,
181 PRIMARY KEY (`id`))
182ENGINE = InnoDB;
183
184
185-- -----------------------------------------------------
186-- Table `mydb`.`potrawa`
187-- -----------------------------------------------------
188CREATE TABLE IF NOT EXISTS `mydb`.`potrawa` (
189 `id` INT NOT NULL AUTO_INCREMENT,
190 `kategoria_potrawy_id` INT NOT NULL,
191 `region_id` INT NOT NULL,
192 `nazwa` VARCHAR(45) NOT NULL,
193 `cena` DOUBLE NOT NULL,
194 `skladnik` VARCHAR(45) NOT NULL,
195 PRIMARY KEY (`id`))
196ENGINE = InnoDB;
197
198
199-- -----------------------------------------------------
200-- Table `mydb`.`wymaganie_potrawy`
201-- -----------------------------------------------------
202CREATE TABLE IF NOT EXISTS `mydb`.`wymaganie_potrawy` (
203 `id` INT NOT NULL AUTO_INCREMENT,
204 `ilosc` INT NOT NULL,
205 `produkt_id` INT NOT NULL,
206 `potrawa_id` INT NOT NULL,
207 PRIMARY KEY (`id`, `produkt_id`, `potrawa_id`),
208 INDEX `fk_wymaganie_potrawy_produkt1_idx` (`produkt_id` ASC) VISIBLE,
209 INDEX `fk_wymaganie_potrawy_potrawa1_idx` (`potrawa_id` ASC) VISIBLE,
210 CONSTRAINT `fk_wymaganie_potrawy_produkt1`
211 FOREIGN KEY (`produkt_id`)
212 REFERENCES `mydb`.`produkt` (`id`)
213 ON DELETE NO ACTION
214 ON UPDATE NO ACTION,
215 CONSTRAINT `fk_wymaganie_potrawy_potrawa1`
216 FOREIGN KEY (`potrawa_id`)
217 REFERENCES `mydb`.`potrawa` (`id`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION)
220ENGINE = InnoDB;
221
222
223-- -----------------------------------------------------
224-- Table `mydb`.`kategoria_potrawy`
225-- -----------------------------------------------------
226CREATE TABLE IF NOT EXISTS `mydb`.`kategoria_potrawy` (
227 `id` INT NOT NULL AUTO_INCREMENT,
228 `nazwa` VARCHAR(45) NOT NULL,
229 PRIMARY KEY (`id`))
230ENGINE = InnoDB;
231
232
233-- -----------------------------------------------------
234-- Table `mydb`.`region`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `mydb`.`region` (
237 `id` INT NOT NULL AUTO_INCREMENT,
238 `nazwa` VARCHAR(45) NOT NULL,
239 PRIMARY KEY (`id`))
240ENGINE = InnoDB;
241
242
243-- -----------------------------------------------------
244-- Table `mydb`.`menu`
245-- -----------------------------------------------------
246CREATE TABLE IF NOT EXISTS `mydb`.`menu` (
247 `id` INT NOT NULL AUTO_INCREMENT,
248 `cena` DOUBLE NOT NULL,
249 `ilosc` INT NOT NULL,
250 `restauracja_id` INT NOT NULL,
251 `potrawa_id` INT NOT NULL,
252 PRIMARY KEY (`id`, `restauracja_id`, `potrawa_id`),
253 INDEX `fk_menu_restauracja1_idx` (`restauracja_id` ASC) VISIBLE,
254 INDEX `fk_menu_potrawa1_idx` (`potrawa_id` ASC) VISIBLE,
255 CONSTRAINT `fk_menu_restauracja1`
256 FOREIGN KEY (`restauracja_id`)
257 REFERENCES `mydb`.`restauracja` (`id`)
258 ON DELETE NO ACTION
259 ON UPDATE NO ACTION,
260 CONSTRAINT `fk_menu_potrawa1`
261 FOREIGN KEY (`potrawa_id`)
262 REFERENCES `mydb`.`potrawa` (`id`)
263 ON DELETE NO ACTION
264 ON UPDATE NO ACTION)
265ENGINE = InnoDB;
266
267
268-- -----------------------------------------------------
269-- Table `mydb`.`zamawiany_produkt`
270-- -----------------------------------------------------
271CREATE TABLE IF NOT EXISTS `mydb`.`zamawiany_produkt` (
272 `id` INT NOT NULL AUTO_INCREMENT,
273 `ilosc` INT NOT NULL,
274 `cena` DOUBLE NOT NULL,
275 `produkt_id` INT NOT NULL,
276 `zamowienie_id` INT NOT NULL,
277 PRIMARY KEY (`id`, `produkt_id`, `zamowienie_id`),
278 INDEX `fk_zamawiany_produkt_produkt1_idx` (`produkt_id` ASC) VISIBLE,
279 INDEX `fk_zamawiany_produkt_zamowienie1_idx` (`zamowienie_id` ASC) VISIBLE,
280 CONSTRAINT `fk_zamawiany_produkt_produkt1`
281 FOREIGN KEY (`produkt_id`)
282 REFERENCES `mydb`.`produkt` (`id`)
283 ON DELETE NO ACTION
284 ON UPDATE NO ACTION,
285 CONSTRAINT `fk_zamawiany_produkt_zamowienie1`
286 FOREIGN KEY (`zamowienie_id`)
287 REFERENCES `mydb`.`zamowienie` (`id`)
288 ON DELETE NO ACTION
289 ON UPDATE NO ACTION)
290ENGINE = InnoDB;
291
292
293-- -----------------------------------------------------
294-- Table `mydb`.`produkt_w_magazynie`
295-- -----------------------------------------------------
296CREATE TABLE IF NOT EXISTS `mydb`.`produkt_w_magazynie` (
297 `id` INT NOT NULL AUTO_INCREMENT,
298 `ilosc` INT NOT NULL,
299 `prog_dostepnosci` INT NOT NULL,
300 `magazyn_id` INT NOT NULL,
301 `produkt_id` INT NOT NULL,
302 PRIMARY KEY (`id`, `magazyn_id`, `produkt_id`),
303 INDEX `fk_produkt_w_magazynie_magazyn1_idx` (`magazyn_id` ASC) VISIBLE,
304 INDEX `fk_produkt_w_magazynie_produkt1_idx` (`produkt_id` ASC) VISIBLE,
305 CONSTRAINT `fk_produkt_w_magazynie_magazyn1`
306 FOREIGN KEY (`magazyn_id`)
307 REFERENCES `mydb`.`magazyn` (`id`)
308 ON DELETE NO ACTION
309 ON UPDATE NO ACTION,
310 CONSTRAINT `fk_produkt_w_magazynie_produkt1`
311 FOREIGN KEY (`produkt_id`)
312 REFERENCES `mydb`.`produkt` (`id`)
313 ON DELETE NO ACTION
314 ON UPDATE NO ACTION)
315ENGINE = InnoDB;
316
317
318-- -----------------------------------------------------
319-- Table `mydb`.`umowa_has_dodatek`
320-- -----------------------------------------------------
321CREATE TABLE IF NOT EXISTS `mydb`.`umowa_has_dodatek` (
322 `umowa_id` INT NOT NULL,
323 `dodatek_id` INT NOT NULL,
324 PRIMARY KEY (`umowa_id`, `dodatek_id`),
325 INDEX `fk_umowa_has_dodatek_dodatek1_idx` (`dodatek_id` ASC) VISIBLE,
326 INDEX `fk_umowa_has_dodatek_umowa1_idx` (`umowa_id` ASC) VISIBLE,
327 CONSTRAINT `fk_umowa_has_dodatek_umowa1`
328 FOREIGN KEY (`umowa_id`)
329 REFERENCES `mydb`.`umowa` (`id`)
330 ON DELETE NO ACTION
331 ON UPDATE NO ACTION,
332 CONSTRAINT `fk_umowa_has_dodatek_dodatek1`
333 FOREIGN KEY (`dodatek_id`)
334 REFERENCES `mydb`.`dodatek` (`id`)
335 ON DELETE NO ACTION
336 ON UPDATE NO ACTION)
337ENGINE = InnoDB;
338
339
340-- -----------------------------------------------------
341-- Table `mydb`.`potrawa_has_kategoria_potrawy`
342-- -----------------------------------------------------
343CREATE TABLE IF NOT EXISTS `mydb`.`potrawa_has_kategoria_potrawy` (
344 `potrawa_id` INT NOT NULL,
345 `kategoria_potrawy_id` INT NOT NULL,
346 PRIMARY KEY (`potrawa_id`, `kategoria_potrawy_id`),
347 INDEX `fk_potrawa_has_kategoria_potrawy_potrawa1_idx` (`potrawa_id` ASC) VISIBLE,
348 INDEX `fk_potrawa_has_kategoria_potrawy_kategoria_potrawy1_idx` (`kategoria_potrawy_id` ASC) VISIBLE,
349 CONSTRAINT `fk_potrawa_has_kategoria_potrawy_potrawa1`
350 FOREIGN KEY (`potrawa_id`)
351 REFERENCES `mydb`.`potrawa` (`id`)
352 ON DELETE NO ACTION
353 ON UPDATE NO ACTION,
354 CONSTRAINT `fk_potrawa_has_kategoria_potrawy_kategoria_potrawy1`
355 FOREIGN KEY (`kategoria_potrawy_id`)
356 REFERENCES `mydb`.`kategoria_potrawy` (`id`)
357 ON DELETE NO ACTION
358 ON UPDATE NO ACTION)
359ENGINE = InnoDB;
360
361
362-- -----------------------------------------------------
363-- Table `mydb`.`potrawa_has_region`
364-- -----------------------------------------------------
365CREATE TABLE IF NOT EXISTS `mydb`.`potrawa_has_region` (
366 `region_id` INT NOT NULL,
367 `potrawa_id` INT NOT NULL,
368 INDEX `fk_potrawa_has_region_region1_idx` (`region_id` ASC) VISIBLE,
369 PRIMARY KEY (`potrawa_id`, `region_id`),
370 CONSTRAINT `fk_potrawa_has_region_region1`
371 FOREIGN KEY (`region_id`)
372 REFERENCES `mydb`.`region` (`id`)
373 ON DELETE NO ACTION
374 ON UPDATE NO ACTION,
375 CONSTRAINT `fk_potrawa_has_region_potrawa1`
376 FOREIGN KEY (`potrawa_id`)
377 REFERENCES `mydb`.`potrawa` (`id`)
378 ON DELETE NO ACTION
379 ON UPDATE NO ACTION)
380ENGINE = InnoDB;
381
382CREATE USER 'Wlasciciel';
383
384GRANT ALL ON `mydb`.* TO 'Wlasciciel';
385GRANT SELECT ON TABLE `mydb`.* TO 'Wlasciciel';
386GRANT SELECT, INSERT, TRIGGER ON TABLE `mydb`.* TO 'Wlasciciel';
387GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `mydb`.* TO 'Wlasciciel';
388GRANT EXECUTE ON ROUTINE `mydb`.* TO 'Wlasciciel';
389CREATE USER 'Ksiegowa(y)';
390
391GRANT SELECT ON TABLE `mydb`.* TO 'Ksiegowa(y)';
392GRANT EXECUTE ON ROUTINE `mydb`.* TO 'Ksiegowa(y)';
393GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `mydb`.* TO 'Ksiegowa(y)';
394CREATE USER 'Pracownik';
395
396GRANT SELECT ON TABLE `mydb`.* TO 'Pracownik';
397GRANT SELECT, INSERT, TRIGGER ON TABLE `mydb`.* TO 'Pracownik';
398CREATE USER 'Kucharz';
399
400GRANT SELECT ON TABLE `mydb`.* TO 'Kucharz';
401
402SET SQL_MODE=@OLD_SQL_MODE;
403SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
404SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;