· 8 years ago · Jan 09, 2018, 03:50 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Jan 9 16:48:23 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`.`Adresa`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Adresa` (
24 `idAdresa` INT NOT NULL,
25 `ulice` VARCHAR(45) NOT NULL,
26 `cisloPopisne` INT NOT NULL,
27 `mesto` VARCHAR(45) NOT NULL,
28 `PSC` INT NOT NULL,
29 PRIMARY KEY (`idAdresa`))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `mydb`.`Pacient`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `mydb`.`Pacient` (
37 `cisloPojistence` INT NOT NULL,
38 `jmenoPojistence` NVARCHAR(45) NOT NULL,
39 `prijmeniPojistence` NVARCHAR(45) NOT NULL,
40 `idAdresa` INT NOT NULL,
41 `emailPojistenec` NVARCHAR(45) NULL,
42 `telefonPojistenec` NVARCHAR(45) NULL,
43 `idParent` INT NOT NULL,
44 PRIMARY KEY (`cisloPojistence`),
45 INDEX `fk_Pacient_Adresa1_idx` (`idAdresa` ASC),
46 INDEX `fk_Pacient_Pacient1_idx` (`idParent` ASC),
47 CONSTRAINT `fk_Pacient_Adresa1`
48 FOREIGN KEY (`idAdresa`)
49 REFERENCES `mydb`.`Adresa` (`idAdresa`)
50 ON DELETE NO ACTION
51 ON UPDATE NO ACTION,
52 CONSTRAINT `fk_Pacient_Pacient1`
53 FOREIGN KEY (`idParent`)
54 REFERENCES `mydb`.`Pacient` (`cisloPojistence`)
55 ON DELETE NO ACTION
56 ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `mydb`.`Lekar`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `mydb`.`Lekar` (
64 `idLekar` INT NOT NULL,
65 `jmenoLekar` VARCHAR(45) NOT NULL,
66 `prijmeniLekar` VARCHAR(45) NOT NULL,
67 `idAdresa` INT NOT NULL,
68 `telefonLekar` VARCHAR(45) NULL,
69 PRIMARY KEY (`idLekar`),
70 INDEX `fk_Lekar_Adresa1_idx` (`idAdresa` ASC),
71 CONSTRAINT `fk_Lekar_Adresa1`
72 FOREIGN KEY (`idAdresa`)
73 REFERENCES `mydb`.`Adresa` (`idAdresa`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`Pojistovna`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `mydb`.`Pojistovna` (
83 `idPojistovna` INT NOT NULL,
84 `jmenoPojistovna` VARCHAR(45) NOT NULL,
85 `idAdresa` INT NOT NULL,
86 `telefonPojistovna` VARCHAR(45) NULL,
87 PRIMARY KEY (`idPojistovna`),
88 UNIQUE INDEX `jmenoPojistovna_UNIQUE` (`jmenoPojistovna` ASC),
89 UNIQUE INDEX `idPojistovna_UNIQUE` (`idPojistovna` ASC),
90 INDEX `fk_Pojistovna_Adresa1_idx` (`idAdresa` ASC),
91 CONSTRAINT `fk_Pojistovna_Adresa1`
92 FOREIGN KEY (`idAdresa`)
93 REFERENCES `mydb`.`Adresa` (`idAdresa`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `mydb`.`Ukony`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `mydb`.`Ukony` (
103 `cukr` INT NULL,
104 `tlak` VARCHAR(45) NULL,
105 `cholesterol` INT NULL,
106 `idProhlidka` INT NOT NULL,
107 PRIMARY KEY (`idProhlidka`))
108ENGINE = InnoDB;
109
110
111-- -----------------------------------------------------
112-- Table `mydb`.`Prohlidka`
113-- -----------------------------------------------------
114CREATE TABLE IF NOT EXISTS `mydb`.`Prohlidka` (
115 `idProhlidka` INT NOT NULL,
116 `typProhlidka` CHAR NOT NULL,
117 `Prohlidkacol` VARCHAR(45) NULL,
118 PRIMARY KEY (`idProhlidka`),
119 CONSTRAINT `fk_Prohlidka_Ukony1`
120 FOREIGN KEY (`idProhlidka`)
121 REFERENCES `mydb`.`Ukony` (`idProhlidka`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `mydb`.`Pojistovna_has_Pacient`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `mydb`.`Pojistovna_has_Pacient` (
131 `Pojistovna_idPojistovna` INT NOT NULL,
132 `Pacient_cisloPojistence` INT NOT NULL,
133 PRIMARY KEY (`Pojistovna_idPojistovna`, `Pacient_cisloPojistence`),
134 INDEX `fk_Pojistovna_has_Pacient_Pacient1_idx` (`Pacient_cisloPojistence` ASC),
135 INDEX `fk_Pojistovna_has_Pacient_Pojistovna_idx` (`Pojistovna_idPojistovna` ASC),
136 CONSTRAINT `fk_Pojistovna_has_Pacient_Pojistovna`
137 FOREIGN KEY (`Pojistovna_idPojistovna`)
138 REFERENCES `mydb`.`Pojistovna` (`idPojistovna`)
139 ON DELETE NO ACTION
140 ON UPDATE NO ACTION,
141 CONSTRAINT `fk_Pojistovna_has_Pacient_Pacient1`
142 FOREIGN KEY (`Pacient_cisloPojistence`)
143 REFERENCES `mydb`.`Pacient` (`cisloPojistence`)
144 ON DELETE NO ACTION
145 ON UPDATE NO ACTION)
146ENGINE = InnoDB;
147
148
149-- -----------------------------------------------------
150-- Table `mydb`.`Lekar_has_Prohlidka`
151-- -----------------------------------------------------
152CREATE TABLE IF NOT EXISTS `mydb`.`Lekar_has_Prohlidka` (
153 `Lekar_idLekar` INT NOT NULL,
154 `Prohlidka_idProhlidka` INT NOT NULL,
155 PRIMARY KEY (`Lekar_idLekar`, `Prohlidka_idProhlidka`),
156 INDEX `fk_Lekar_has_Prohlidka_Prohlidka1_idx` (`Prohlidka_idProhlidka` ASC),
157 INDEX `fk_Lekar_has_Prohlidka_Lekar1_idx` (`Lekar_idLekar` ASC),
158 CONSTRAINT `fk_Lekar_has_Prohlidka_Lekar1`
159 FOREIGN KEY (`Lekar_idLekar`)
160 REFERENCES `mydb`.`Lekar` (`idLekar`)
161 ON DELETE NO ACTION
162 ON UPDATE NO ACTION,
163 CONSTRAINT `fk_Lekar_has_Prohlidka_Prohlidka1`
164 FOREIGN KEY (`Prohlidka_idProhlidka`)
165 REFERENCES `mydb`.`Prohlidka` (`idProhlidka`)
166 ON DELETE NO ACTION
167 ON UPDATE NO ACTION)
168ENGINE = InnoDB;
169
170
171-- -----------------------------------------------------
172-- Table `mydb`.`Prohlidka_has_Pacient`
173-- -----------------------------------------------------
174CREATE TABLE IF NOT EXISTS `mydb`.`Prohlidka_has_Pacient` (
175 `Prohlidka_idProhlidka` INT NOT NULL,
176 `Pacient_cisloPojistence` INT NOT NULL,
177 PRIMARY KEY (`Prohlidka_idProhlidka`, `Pacient_cisloPojistence`),
178 INDEX `fk_Prohlidka_has_Pacient_Pacient1_idx` (`Pacient_cisloPojistence` ASC),
179 INDEX `fk_Prohlidka_has_Pacient_Prohlidka1_idx` (`Prohlidka_idProhlidka` ASC),
180 CONSTRAINT `fk_Prohlidka_has_Pacient_Prohlidka1`
181 FOREIGN KEY (`Prohlidka_idProhlidka`)
182 REFERENCES `mydb`.`Prohlidka` (`idProhlidka`)
183 ON DELETE NO ACTION
184 ON UPDATE NO ACTION,
185 CONSTRAINT `fk_Prohlidka_has_Pacient_Pacient1`
186 FOREIGN KEY (`Pacient_cisloPojistence`)
187 REFERENCES `mydb`.`Pacient` (`cisloPojistence`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION)
190ENGINE = InnoDB;
191
192
193-- -----------------------------------------------------
194-- Table `mydb`.`Lek`
195-- -----------------------------------------------------
196CREATE TABLE IF NOT EXISTS `mydb`.`Lek` (
197 `idLek` INT NOT NULL,
198 `jmenoLek` VARCHAR(45) NOT NULL,
199 `ucelLek` VARCHAR(45) NOT NULL,
200 PRIMARY KEY (`idLek`))
201ENGINE = InnoDB;
202
203
204-- -----------------------------------------------------
205-- Table `mydb`.`VedlejsiUcinky`
206-- -----------------------------------------------------
207CREATE TABLE IF NOT EXISTS `mydb`.`VedlejsiUcinky` (
208 `idVedlejsiUcinky` INT NOT NULL,
209 `popisVedlejsiUcinky` VARCHAR(45) NOT NULL,
210 PRIMARY KEY (`idVedlejsiUcinky`))
211ENGINE = InnoDB;
212
213
214-- -----------------------------------------------------
215-- Table `mydb`.`Lek_has_VedlejsiUcinky`
216-- -----------------------------------------------------
217CREATE TABLE IF NOT EXISTS `mydb`.`Lek_has_VedlejsiUcinky` (
218 `Lek_idLek` INT NOT NULL,
219 `VedlejsiUcinky_idVedlejsiUcinky` INT NOT NULL,
220 PRIMARY KEY (`Lek_idLek`, `VedlejsiUcinky_idVedlejsiUcinky`),
221 INDEX `fk_Lek_has_VedlejsiUcinky_VedlejsiUcinky1_idx` (`VedlejsiUcinky_idVedlejsiUcinky` ASC),
222 INDEX `fk_Lek_has_VedlejsiUcinky_Lek1_idx` (`Lek_idLek` ASC),
223 CONSTRAINT `fk_Lek_has_VedlejsiUcinky_Lek1`
224 FOREIGN KEY (`Lek_idLek`)
225 REFERENCES `mydb`.`Lek` (`idLek`)
226 ON DELETE NO ACTION
227 ON UPDATE NO ACTION,
228 CONSTRAINT `fk_Lek_has_VedlejsiUcinky_VedlejsiUcinky1`
229 FOREIGN KEY (`VedlejsiUcinky_idVedlejsiUcinky`)
230 REFERENCES `mydb`.`VedlejsiUcinky` (`idVedlejsiUcinky`)
231 ON DELETE NO ACTION
232 ON UPDATE NO ACTION)
233ENGINE = InnoDB;
234
235
236-- -----------------------------------------------------
237-- Table `mydb`.`Prohlidka_has_Lek`
238-- -----------------------------------------------------
239CREATE TABLE IF NOT EXISTS `mydb`.`Prohlidka_has_Lek` (
240 `Prohlidka_idProhlidka` INT NOT NULL,
241 `Lek_idLek` INT NOT NULL,
242 `davkovani` VARCHAR(45) NOT NULL,
243 PRIMARY KEY (`Prohlidka_idProhlidka`, `Lek_idLek`),
244 INDEX `fk_Prohlidka_has_Lek_Lek1_idx` (`Lek_idLek` ASC),
245 INDEX `fk_Prohlidka_has_Lek_Prohlidka1_idx` (`Prohlidka_idProhlidka` ASC),
246 CONSTRAINT `fk_Prohlidka_has_Lek_Prohlidka1`
247 FOREIGN KEY (`Prohlidka_idProhlidka`)
248 REFERENCES `mydb`.`Prohlidka` (`idProhlidka`)
249 ON DELETE NO ACTION
250 ON UPDATE NO ACTION,
251 CONSTRAINT `fk_Prohlidka_has_Lek_Lek1`
252 FOREIGN KEY (`Lek_idLek`)
253 REFERENCES `mydb`.`Lek` (`idLek`)
254 ON DELETE NO ACTION
255 ON UPDATE NO ACTION)
256ENGINE = InnoDB;
257
258
259-- -----------------------------------------------------
260-- Table `mydb`.`Nemocnice`
261-- -----------------------------------------------------
262CREATE TABLE IF NOT EXISTS `mydb`.`Nemocnice` (
263 `idNemocnice` INT NOT NULL,
264 `nazevNemocnice` VARCHAR(45) NOT NULL,
265 `idAdresa` INT NOT NULL,
266 `telefonNemocnice` VARCHAR(45) NULL,
267 PRIMARY KEY (`idNemocnice`),
268 UNIQUE INDEX `nazevNemocnice_UNIQUE` (`nazevNemocnice` ASC),
269 INDEX `fk_Nemocnice_Adresa1_idx` (`idAdresa` ASC),
270 CONSTRAINT `fk_Nemocnice_Adresa1`
271 FOREIGN KEY (`idAdresa`)
272 REFERENCES `mydb`.`Adresa` (`idAdresa`)
273 ON DELETE NO ACTION
274 ON UPDATE NO ACTION)
275ENGINE = InnoDB;
276
277
278-- -----------------------------------------------------
279-- Table `mydb`.`Nemocnice_has_Lekar`
280-- -----------------------------------------------------
281CREATE TABLE IF NOT EXISTS `mydb`.`Nemocnice_has_Lekar` (
282 `Nemocnice_idNemocnice` INT NOT NULL,
283 `Lekar_idLekar` INT NOT NULL,
284 PRIMARY KEY (`Nemocnice_idNemocnice`, `Lekar_idLekar`),
285 INDEX `fk_Nemocnice_has_Lekar_Lekar1_idx` (`Lekar_idLekar` ASC),
286 INDEX `fk_Nemocnice_has_Lekar_Nemocnice1_idx` (`Nemocnice_idNemocnice` ASC),
287 CONSTRAINT `fk_Nemocnice_has_Lekar_Nemocnice1`
288 FOREIGN KEY (`Nemocnice_idNemocnice`)
289 REFERENCES `mydb`.`Nemocnice` (`idNemocnice`)
290 ON DELETE NO ACTION
291 ON UPDATE NO ACTION,
292 CONSTRAINT `fk_Nemocnice_has_Lekar_Lekar1`
293 FOREIGN KEY (`Lekar_idLekar`)
294 REFERENCES `mydb`.`Lekar` (`idLekar`)
295 ON DELETE NO ACTION
296 ON UPDATE NO ACTION)
297ENGINE = InnoDB;
298
299
300-- -----------------------------------------------------
301-- Table `mydb`.`Specializace`
302-- -----------------------------------------------------
303CREATE TABLE IF NOT EXISTS `mydb`.`Specializace` (
304 `idSpecializace` INT NOT NULL,
305 `nazevSpecializace` VARCHAR(45) NOT NULL,
306 PRIMARY KEY (`idSpecializace`))
307ENGINE = InnoDB;
308
309
310-- -----------------------------------------------------
311-- Table `mydb`.`Specializace_has_Lekar`
312-- -----------------------------------------------------
313CREATE TABLE IF NOT EXISTS `mydb`.`Specializace_has_Lekar` (
314 `Specializace_idSpecializace` INT NOT NULL,
315 `Lekar_idLekar` INT NOT NULL,
316 PRIMARY KEY (`Specializace_idSpecializace`, `Lekar_idLekar`),
317 INDEX `fk_Specializace_has_Lekar_Lekar1_idx` (`Lekar_idLekar` ASC),
318 INDEX `fk_Specializace_has_Lekar_Specializace1_idx` (`Specializace_idSpecializace` ASC),
319 CONSTRAINT `fk_Specializace_has_Lekar_Specializace1`
320 FOREIGN KEY (`Specializace_idSpecializace`)
321 REFERENCES `mydb`.`Specializace` (`idSpecializace`)
322 ON DELETE NO ACTION
323 ON UPDATE NO ACTION,
324 CONSTRAINT `fk_Specializace_has_Lekar_Lekar1`
325 FOREIGN KEY (`Lekar_idLekar`)
326 REFERENCES `mydb`.`Lekar` (`idLekar`)
327 ON DELETE NO ACTION
328 ON UPDATE NO ACTION)
329ENGINE = InnoDB;
330
331
332-- -----------------------------------------------------
333-- Table `mydb`.`Lekar_has_Pacient`
334-- -----------------------------------------------------
335CREATE TABLE IF NOT EXISTS `mydb`.`Lekar_has_Pacient` (
336 `Lekar_idLekar` INT NOT NULL,
337 `Pacient_cisloPojistence` INT NOT NULL,
338 PRIMARY KEY (`Lekar_idLekar`, `Pacient_cisloPojistence`),
339 INDEX `fk_Lekar_has_Pacient_Pacient1_idx` (`Pacient_cisloPojistence` ASC),
340 INDEX `fk_Lekar_has_Pacient_Lekar1_idx` (`Lekar_idLekar` ASC),
341 CONSTRAINT `fk_Lekar_has_Pacient_Lekar1`
342 FOREIGN KEY (`Lekar_idLekar`)
343 REFERENCES `mydb`.`Lekar` (`idLekar`)
344 ON DELETE NO ACTION
345 ON UPDATE NO ACTION,
346 CONSTRAINT `fk_Lekar_has_Pacient_Pacient1`
347 FOREIGN KEY (`Pacient_cisloPojistence`)
348 REFERENCES `mydb`.`Pacient` (`cisloPojistence`)
349 ON DELETE NO ACTION
350 ON UPDATE NO ACTION)
351ENGINE = InnoDB;
352
353
354-- -----------------------------------------------------
355-- Table `mydb`.`Lekar_had_Pacient`
356-- -----------------------------------------------------
357CREATE TABLE IF NOT EXISTS `mydb`.`Lekar_had_Pacient` (
358 `Lekar_idLekar` INT NOT NULL,
359 `Pacient_cisloPojistence` INT NOT NULL,
360 `od` DATE NOT NULL,
361 `do` DATE NOT NULL,
362 PRIMARY KEY (`Lekar_idLekar`, `Pacient_cisloPojistence`, `od`, `do`),
363 INDEX `fk_Lekar_has_Pacient1_Pacient1_idx` (`Pacient_cisloPojistence` ASC),
364 INDEX `fk_Lekar_has_Pacient1_Lekar1_idx` (`Lekar_idLekar` ASC),
365 CONSTRAINT `fk_Lekar_has_Pacient1_Lekar1`
366 FOREIGN KEY (`Lekar_idLekar`)
367 REFERENCES `mydb`.`Lekar` (`idLekar`)
368 ON DELETE NO ACTION
369 ON UPDATE NO ACTION,
370 CONSTRAINT `fk_Lekar_has_Pacient1_Pacient1`
371 FOREIGN KEY (`Pacient_cisloPojistence`)
372 REFERENCES `mydb`.`Pacient` (`cisloPojistence`)
373 ON DELETE NO ACTION
374 ON UPDATE NO ACTION)
375ENGINE = InnoDB;
376
377
378SET SQL_MODE=@OLD_SQL_MODE;
379SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
380SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;