· 8 years ago · Jan 03, 2018, 12:34 PM
1-- MySQL Script generated by MySQL Workbench
2-- Wed Jan 3 13:31:19 2018
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='TRADITIONAL,ALLOW_INVALID_DATES';
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`.`grupa`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`grupa` (
24 `ID_grupa` INT UNSIGNED ZEROFILL NOT NULL,
25 `naziv_grupa` VARCHAR(45) NULL DEFAULT NULL,
26 PRIMARY KEY (`ID_grupa`),
27 UNIQUE INDEX `idtip_kuhinja_jelo_UNIQUE` (`ID_grupa` ASC))
28ENGINE = InnoDB;
29
30
31-- -----------------------------------------------------
32-- Table `mydb`.`tip`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `mydb`.`tip` (
35 `ID_tip` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
36 `naziv_tip` VARCHAR(45) NULL DEFAULT NULL,
37 PRIMARY KEY (`ID_tip`),
38 UNIQUE INDEX `id_tip_UNIQUE` (`ID_tip` ASC))
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `mydb`.`kuhar`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `mydb`.`kuhar` (
46 `ID_kuhar` BIGINT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
47 `ime_kuhar` VARCHAR(45) NULL DEFAULT NULL,
48 `prezime_kuhar` VARCHAR(45) NULL DEFAULT NULL,
49 `datum_rod` DATE NULL DEFAULT NULL,
50 `adresa_kuhar` VARCHAR(45) NULL DEFAULT NULL,
51 `grad_kuhar` VARCHAR(45) NULL DEFAULT NULL,
52 `postanski_broj_kuhar` VARCHAR(45) NULL DEFAULT NULL,
53 `placa_kuhar` FLOAT NULL DEFAULT NULL,
54 `tel_kuhar` VARCHAR(45) NULL DEFAULT NULL,
55 `mail_kuhar` VARCHAR(45) NULL DEFAULT NULL,
56 `lozinka_kuhar` VARCHAR(45) NULL DEFAULT NULL,
57 PRIMARY KEY (`ID_kuhar`),
58 UNIQUE INDEX `id_kuhar_UNIQUE` (`ID_kuhar` ASC))
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `mydb`.`jelo`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `mydb`.`jelo` (
66 `ID_jelo` INT(100) UNSIGNED ZEROFILL NOT NULL,
67 `tip_ID_tip` INT ZEROFILL UNSIGNED NOT NULL,
68 `grupa_ID_grupa` INT UNSIGNED ZEROFILL NOT NULL,
69 `kuhar_ID_kuhar` INT ZEROFILL UNSIGNED NOT NULL,
70 `ime_jelo` VARCHAR(45) NULL DEFAULT NULL,
71 `cijena` INT(4) UNSIGNED NULL DEFAULT NULL,
72 `opis` VARCHAR(100) NULL DEFAULT NULL,
73 PRIMARY KEY (`ID_jelo`, `tip_ID_tip`, `grupa_ID_grupa`, `kuhar_ID_kuhar`),
74 UNIQUE INDEX `idjelo_UNIQUE` (`ID_jelo` ASC),
75 INDEX `fk_jelo_tip_kuhinja_jelo1_idx` (`grupa_ID_grupa` ASC),
76 INDEX `fk_jelo_tip1_idx` (`tip_ID_tip` ASC),
77 INDEX `fk_jelo_kuhar1_idx` (`kuhar_ID_kuhar` ASC),
78 CONSTRAINT `fk_jelo_tip_kuhinja_jelo1`
79 FOREIGN KEY (`grupa_ID_grupa`)
80 REFERENCES `mydb`.`grupa` (`ID_grupa`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION,
83 CONSTRAINT `fk_jelo_tip1`
84 FOREIGN KEY (`tip_ID_tip`)
85 REFERENCES `mydb`.`tip` (`ID_tip`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION,
88 CONSTRAINT `fk_jelo_kuhar1`
89 FOREIGN KEY (`kuhar_ID_kuhar`)
90 REFERENCES `mydb`.`kuhar` (`ID_kuhar`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `mydb`.`sastojak`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `mydb`.`sastojak` (
100 `ID_sastojak` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
101 `naziv_sastojak` VARCHAR(45) NULL DEFAULT NULL,
102 `kolicina_sastojak` FLOAT(15) NULL DEFAULT NULL,
103 `cijena_sastojak` FLOAT(15) NULL DEFAULT NULL,
104 `kalorije_sastojak` INT NULL DEFAULT NULL,
105 PRIMARY KEY (`ID_sastojak`),
106 UNIQUE INDEX `id_sastojci_UNIQUE` (`ID_sastojak` ASC))
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table `mydb`.`konobar`
112-- -----------------------------------------------------
113CREATE TABLE IF NOT EXISTS `mydb`.`konobar` (
114 `ID_konobar` BIGINT UNSIGNED ZEROFILL NOT NULL,
115 `ime_konobar` VARCHAR(45) NULL DEFAULT NULL,
116 `prezime_konobar` VARCHAR(45) NULL DEFAULT NULL,
117 `datum_rod_konobar` DATE NULL DEFAULT NULL,
118 `adresa_konobar` VARCHAR(45) NULL DEFAULT NULL,
119 `grad_konobar` VARCHAR(45) NULL DEFAULT NULL,
120 `postanski_broj_konobar` VARCHAR(45) NULL DEFAULT NULL,
121 `placa_konobar` FLOAT NULL DEFAULT NULL,
122 `tel_konobar` VARCHAR(45) NULL DEFAULT NULL,
123 `mail_konobar` VARCHAR(45) NULL DEFAULT NULL,
124 `lozinka_konobar` VARCHAR(45) NULL DEFAULT NULL,
125 `ocjena_konobar` VARCHAR(45) NULL DEFAULT NULL,
126 PRIMARY KEY (`ID_konobar`))
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `mydb`.`gost`
132-- -----------------------------------------------------
133CREATE TABLE IF NOT EXISTS `mydb`.`gost` (
134 `ID_gost` BIGINT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
135 `ime_gost` VARCHAR(45) NULL DEFAULT NULL,
136 `prezime_gost` VARCHAR(45) NULL DEFAULT NULL,
137 `datum_rod_gost` DATE NULL DEFAULT NULL,
138 `adresa_gost` VARCHAR(45) NULL DEFAULT NULL,
139 `grad_gost` VARCHAR(45) NULL DEFAULT NULL,
140 `postanski_broj_gost` VARCHAR(45) NULL DEFAULT NULL,
141 `spol_gost` VARCHAR(45) NULL DEFAULT NULL,
142 `bodovi_gost` INT NULL DEFAULT NULL,
143 `mail_gost` VARCHAR(45) NULL DEFAULT NULL,
144 `lozinka_gost` VARCHAR(15) NULL DEFAULT NULL,
145 PRIMARY KEY (`ID_gost`))
146ENGINE = InnoDB;
147
148
149-- -----------------------------------------------------
150-- Table `mydb`.`racun`
151-- -----------------------------------------------------
152CREATE TABLE IF NOT EXISTS `mydb`.`racun` (
153 `ID_racun` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
154 `konobar_ID_konobar` INT UNSIGNED ZEROFILL NOT NULL,
155 `gost_ID_gost` INT ZEROFILL UNSIGNED NOT NULL,
156 `datum` DATE NULL DEFAULT NULL,
157 `iznos_racun` FLOAT(45) NULL DEFAULT NULL,
158 `broj_stola` INT NULL DEFAULT NULL,
159 PRIMARY KEY (`ID_racun`, `konobar_ID_konobar`, `gost_ID_gost`),
160 INDEX `fk_raÄun_konobar1_idx` (`konobar_ID_konobar` ASC),
161 INDEX `fk_raÄun_gost1_idx` (`gost_ID_gost` ASC),
162 CONSTRAINT `fk_raÄun_konobar1`
163 FOREIGN KEY (`konobar_ID_konobar`)
164 REFERENCES `mydb`.`konobar` (`ID_konobar`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION,
167 CONSTRAINT `fk_raÄun_gost1`
168 FOREIGN KEY (`gost_ID_gost`)
169 REFERENCES `mydb`.`gost` (`ID_gost`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION)
172ENGINE = InnoDB;
173
174
175-- -----------------------------------------------------
176-- Table `mydb`.`sastojci_jela`
177-- -----------------------------------------------------
178CREATE TABLE IF NOT EXISTS `mydb`.`sastojci_jela` (
179 `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
180 `sastojak_ID_sastojak` INT ZEROFILL UNSIGNED NOT NULL,
181 PRIMARY KEY (`jelo_ID_jelo`, `sastojak_ID_sastojak`),
182 INDEX `fk_jelo_has_sastojak_sastojak1_idx` (`sastojak_ID_sastojak` ASC),
183 INDEX `fk_jelo_has_sastojak_jelo_idx` (`jelo_ID_jelo` ASC),
184 CONSTRAINT `fk_jelo_has_sastojak_jelo`
185 FOREIGN KEY (`jelo_ID_jelo`)
186 REFERENCES `mydb`.`jelo` (`ID_jelo`)
187 ON DELETE NO ACTION
188 ON UPDATE NO ACTION,
189 CONSTRAINT `fk_jelo_has_sastojak_sastojak1`
190 FOREIGN KEY (`sastojak_ID_sastojak`)
191 REFERENCES `mydb`.`sastojak` (`ID_sastojak`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION)
194ENGINE = InnoDB;
195
196
197-- -----------------------------------------------------
198-- Table `mydb`.`narudzba`
199-- -----------------------------------------------------
200CREATE TABLE IF NOT EXISTS `mydb`.`narudzba` (
201 `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
202 `racun_ID_racun` INT ZEROFILL UNSIGNED NOT NULL,
203 PRIMARY KEY (`jelo_ID_jelo`, `racun_ID_racun`),
204 INDEX `fk_jelo_has_raÄun_raÄun1_idx` (`racun_ID_racun` ASC),
205 INDEX `fk_jelo_has_raÄun_jelo1_idx` (`jelo_ID_jelo` ASC),
206 CONSTRAINT `fk_jelo_has_raÄun_jelo1`
207 FOREIGN KEY (`jelo_ID_jelo`)
208 REFERENCES `mydb`.`jelo` (`ID_jelo`)
209 ON DELETE NO ACTION
210 ON UPDATE NO ACTION,
211 CONSTRAINT `fk_jelo_has_raÄun_raÄun1`
212 FOREIGN KEY (`racun_ID_racun`)
213 REFERENCES `mydb`.`racun` (`ID_racun`)
214 ON DELETE NO ACTION
215 ON UPDATE NO ACTION)
216ENGINE = InnoDB
217COMMENT = ' ';
218
219
220-- -----------------------------------------------------
221-- Table `mydb`.`pice`
222-- -----------------------------------------------------
223CREATE TABLE IF NOT EXISTS `mydb`.`pice` (
224 `ID_pice` INT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
225 `naziv_pice` VARCHAR(45) NULL DEFAULT NULL,
226 `cijena_pice` INT UNSIGNED NULL DEFAULT NULL,
227 `kolicina_pice` FLOAT NULL DEFAULT NULL,
228 PRIMARY KEY (`ID_pice`))
229ENGINE = InnoDB;
230
231
232-- -----------------------------------------------------
233-- Table `mydb`.`pica_na_racunu`
234-- -----------------------------------------------------
235CREATE TABLE IF NOT EXISTS `mydb`.`pica_na_racunu` (
236 `pice_ID_pice` INT UNSIGNED ZEROFILL NOT NULL,
237 `raÄun_ID_raÄun` INT ZEROFILL UNSIGNED NOT NULL,
238 PRIMARY KEY (`pice_ID_pice`, `raÄun_ID_raÄun`),
239 INDEX `fk_pice_has_raÄun_raÄun1_idx` (`raÄun_ID_raÄun` ASC),
240 INDEX `fk_pice_has_raÄun_pice1_idx` (`pice_ID_pice` ASC),
241 CONSTRAINT `fk_pice_has_raÄun_pice1`
242 FOREIGN KEY (`pice_ID_pice`)
243 REFERENCES `mydb`.`pice` (`ID_pice`)
244 ON DELETE NO ACTION
245 ON UPDATE NO ACTION,
246 CONSTRAINT `fk_pice_has_raÄun_raÄun1`
247 FOREIGN KEY (`raÄun_ID_raÄun`)
248 REFERENCES `mydb`.`racun` (`ID_racun`)
249 ON DELETE NO ACTION
250 ON UPDATE NO ACTION)
251ENGINE = InnoDB;
252
253
254-- -----------------------------------------------------
255-- Table `mydb`.`kuhar_priprema_jelo`
256-- -----------------------------------------------------
257CREATE TABLE IF NOT EXISTS `mydb`.`kuhar_priprema_jelo` (
258 `kuhar_ID_kuhar` INT ZEROFILL UNSIGNED NOT NULL,
259 `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
260 PRIMARY KEY (`kuhar_ID_kuhar`, `jelo_ID_jelo`),
261 INDEX `fk_kuhar_has_jelo_jelo1_idx` (`jelo_ID_jelo` ASC),
262 INDEX `fk_kuhar_has_jelo_kuhar1_idx` (`kuhar_ID_kuhar` ASC),
263 CONSTRAINT `fk_kuhar_has_jelo_kuhar1`
264 FOREIGN KEY (`kuhar_ID_kuhar`)
265 REFERENCES `mydb`.`kuhar` (`ID_kuhar`)
266 ON DELETE NO ACTION
267 ON UPDATE NO ACTION,
268 CONSTRAINT `fk_kuhar_has_jelo_jelo1`
269 FOREIGN KEY (`jelo_ID_jelo`)
270 REFERENCES `mydb`.`jelo` (`ID_jelo`)
271 ON DELETE NO ACTION
272 ON UPDATE NO ACTION)
273ENGINE = InnoDB;
274
275
276-- -----------------------------------------------------
277-- Table `mydb`.`recenzija`
278-- -----------------------------------------------------
279CREATE TABLE IF NOT EXISTS `mydb`.`recenzija` (
280 `ID_preporuka` INT NOT NULL,
281 `jelo_ID_jelo` INT(100) UNSIGNED ZEROFILL NOT NULL,
282 `gost_ID_gost` INT ZEROFILL UNSIGNED NOT NULL,
283 `komentar` VARCHAR(100) NULL DEFAULT NULL,
284 `ocjena` INT NULL DEFAULT NULL,
285 PRIMARY KEY (`ID_preporuka`, `jelo_ID_jelo`, `gost_ID_gost`),
286 INDEX `fk_preporuka_jelo1_idx` (`jelo_ID_jelo` ASC),
287 INDEX `fk_preporuka_gost1_idx` (`gost_ID_gost` ASC),
288 CONSTRAINT `fk_preporuka_jelo1`
289 FOREIGN KEY (`jelo_ID_jelo`)
290 REFERENCES `mydb`.`jelo` (`ID_jelo`)
291 ON DELETE NO ACTION
292 ON UPDATE NO ACTION,
293 CONSTRAINT `fk_preporuka_gost1`
294 FOREIGN KEY (`gost_ID_gost`)
295 REFERENCES `mydb`.`gost` (`ID_gost`)
296 ON DELETE NO ACTION
297 ON UPDATE NO ACTION)
298ENGINE = InnoDB;
299
300
301SET SQL_MODE=@OLD_SQL_MODE;
302SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
303SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;