QhHPPv9N

1-- -----------------------------------------------------
2-- Schema zapori
3-- -----------------------------------------------------
4CREATE SCHEMA IF NOT EXISTS `zapori`;
5USE `zapori` ;
6
7-- -----------------------------------------------------
8-- Table `zapori`.`bolezen`
9-- -----------------------------------------------------
10CREATE TABLE IF NOT EXISTS `zapori`.`bolezen` (
11  `ID_BOLEZEN` INT(11) NOT NULL,
12  `BOLEZEN` VARCHAR(30) NOT NULL 
13);
14
15ALTER TABLE bolezen
16ADD CONSTRAINT PK_bolezen PRIMARY KEY (ID_BOLEZEN);
17
18ALTER TABLE bolezen
19CHANGE ID_BOLEZEN ID_BOLEZEN INT(10) AUTO_INCREMENT;
20
21-- -----------------------------------------------------
22-- Table `zapori`.`tip_oddelka`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `zapori`.`tip_oddelka` (
25  `ID_TIP_ODDELKA` INT(11) NOT NULL,
26  `TIP` VARCHAR(30) NOT NULL);
27
28ALTER TABLE tip_oddelka
29ADD CONSTRAINT PK_tip_oddelka PRIMARY KEY (ID_TIP_ODDELKA);
30
31
32ALTER TABLE tip_oddelka
33CHANGE ID_TIP_ODDELKA ID_TIP_ODDELKA INT(10) AUTO_INCREMENT;
34
35
36
37
38-- -----------------------------------------------------
39-- Table `zapori`.`zapo_oddelek`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `zapori`.`zapo_oddelek` (
42  `ID_ZAPO_ODDELEK` INT(11) NOT NULL,
43  `tip_oddelka_ID_TIP_ODDELKA` INT(11) NOT NULL);
44
45ALTER TABLE zapo_oddelek
46ADD CONSTRAINT PK_zapo_oddelek PRIMARY KEY (ID_ZAPO_ODDELEK);
47
48ALTER TABLE zapo_oddelek 
49ADD CONSTRAINT FK_tip_oddelka FOREIGN KEY (tip_oddelka_ID_TIP_ODDELKA) REFERENCES tip_oddelka(ID_TIP_ODDELKA);
50
51ALTER TABLE zapo_oddelek
52CHANGE ID_ZAPO_ODDELEK ID_ZAPO_ODDELEK INT(10) AUTO_INCREMENT;
53
54-- -----------------------------------------------------
55-- Table `zapori`.`soba`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `zapori`.`soba` (
58  `ID_SOBA` INT(11) NOT NULL,
59  `OZNAKA_SOBE` VARCHAR(10) NOT NULL,
60  `KAPACITETA_SOBE` INT(11) NOT NULL,
61  `STEVILO_PROSTIH_MEST` INT(11) NOT NULL);
62ALTER TABLE soba
63ADD CONSTRAINT PK_soba PRIMARY KEY (ID_SOBA);
64
65
66ALTER TABLE soba
67CHANGE ID_SOBA ID_SOBA INT(10) AUTO_INCREMENT;
68
69-- -----------------------------------------------------
70-- Table `zapori`.`zapornik`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `zapori`.`zapornik` (
73  `ID_ZAPORNIK` INT(11) NOT NULL,
74  `IME` VARCHAR(15) NOT NULL,
75  `PRIIMEK` VARCHAR(25) NOT NULL,
76  `DATUM_ROJSTVA` DATE NOT NULL,
77  `SPOL` VARCHAR(6) NOT NULL,
78  `KRAJ_ROJSTVA` VARCHAR(20) NOT NULL,
79  `DRZAVA_ROJSTVA` VARCHAR(20) NOT NULL,
80  `STEVILKA_OSEBNE` VARCHAR(30) NOT NULL,
81  `zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
82  `soba_ID_SOBA` INT(11) NOT NULL);
83
84 ALTER TABLE zapornik
85ADD CONSTRAINT PK_zapornik PRIMARY KEY (ID_ZAPORNIK);
86
87ALTER TABLE zapornik 
88ADD CONSTRAINT FK_zapo_oddelek FOREIGN KEY (zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
89
90ALTER TABLE zapornik
91ADD CONSTRAINT FK_soba FOREIGN KEY (soba_ID_SOBA) REFERENCES soba(ID_SOBA);
92
93
94ALTER TABLE zapornik
95CHANGE ID_ZAPORNIK ID_ZAPORNIK INT(10) AUTO_INCREMENT;
96
97
98-- -----------------------------------------------------
99-- Table `zapori`.`zdravljenje`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `zapori`.`zdravljenje` (
102  `ID_ZDRAVLJENJE` INT(11) NOT NULL,
103  `ZACETEK_ZDRAVLJENJA` DATE NOT NULL,
104  `KONEC_ZDRAVLJENJA` DATE NOT NULL,
105  `zdravilo` VARCHAR(45) NOT NULL,
106  `zdravilo_kolicina` INT NULL);
107
108ALTER TABLE zdravljenje
109ADD CONSTRAINT PK_zdravljenje PRIMARY KEY (ID_ZDRAVLJENJE);
110
111
112ALTER TABLE zdravljenje
113CHANGE ID_ZDRAVLJENJE ID_ZDRAVLJENJE INT(10) AUTO_INCREMENT;
114-- -----------------------------------------------------
115-- Table `zapori`.`bolezen_zapornik`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `zapori`.`bolezen_zapornik` (
118  `ID_BOLEZEN_ZAPORNIK` INT(11) NOT NULL,
119  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL,
120  `zapornik_zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
121  `zdravljenje_ID_ZDRAVLJENJE` INT(11) NOT NULL,
122  `bolezen_ID_BOLEZEN` INT(11) NOT NULL
123);
124 
125ALTER TABLE bolezen_zapornik
126ADD CONSTRAINT PK_bolezen_zapornik PRIMARY KEY (ID_BOLEZEN_ZAPORNIK);
127
128ALTER TABLE bolezen_zapornik 
129ADD CONSTRAINT FK_zapornik FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
130
131ALTER TABLE bolezen_zapornik 
132ADD CONSTRAINT FK_zapo_oddelek1 FOREIGN KEY (zapornik_zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
133
134ALTER TABLE bolezen_zapornik 
135ADD CONSTRAINT FK_zdravljenje FOREIGN KEY (zdravljenje_ID_ZDRAVLJENJE) REFERENCES zdravljenje(ID_ZDRAVLJENJE);
136
137ALTER TABLE bolezen_zapornik 
138ADD CONSTRAINT FK_bolezen FOREIGN KEY (bolezen_ID_BOLEZEN) REFERENCES bolezen(ID_BOLEZEN);
139
140
141ALTER TABLE bolezen_zapornik
142CHANGE ID_BOLEZEN_ZAPORNIK ID_BOLEZEN_ZAPORNIK INT(10) AUTO_INCREMENT;
143
144
145-- -----------------------------------------------------
146-- Table `zapori`.`izhodi`
147-- -----------------------------------------------------
148CREATE TABLE IF NOT EXISTS `zapori`.`izhodi` (
149  `ID_IZHODI` INT(11) NOT NULL,
150  `DATUM_IZHODA` DATE NOT NULL,
151  `DATUM_PRIHODA` DATE NOT NULL);
152 ALTER TABLE izhodi
153ADD CONSTRAINT PK_izhodi PRIMARY KEY (ID_IZHODI);
154
155ALTER TABLE izhodi
156CHANGE ID_IZHODI ID_IZHODI INT(10) AUTO_INCREMENT;
157
158-- -----------------------------------------------------
159-- Table `zapori`.`vrsta_kaznivega_dejanja`
160-- -----------------------------------------------------
161CREATE TABLE IF NOT EXISTS `zapori`.`vrsta_kaznivega_dejanja` (
162  `ID_VRSTA_KAZNIVEGA_DEJANJA` INT(11) NOT NULL,
163  `VRSTA` VARCHAR(30) NOT NULL);
164 ALTER TABLE vrsta_kaznivega_dejanja
165ADD CONSTRAINT PK_vrsta_kaznivega_dejanja PRIMARY KEY (ID_VRSTA_KAZNIVEGA_DEJANJA);
166
167ALTER TABLE vrsta_kaznivega_dejanja
168CHANGE ID_VRSTA_KAZNIVEGA_DEJANJA ID_VRSTA_KAZNIVEGA_DEJANJA INT(10) AUTO_INCREMENT;
169
170
171
172-- -----------------------------------------------------
173-- Table `zapori`.`kazen`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `zapori`.`kazen` (
176  `ID_KAZEN` INT(11) NOT NULL,
177  `vrsta_kaznivega_dejanja_ID_VRSTA_KAZNIVEGA_DEJANJA` INT(11) NOT NULL,
178  `soba_ID_SOBA` INT(11) NOT NULL,
179  `ime` VARCHAR(45) NOT NULL);
180 ALTER TABLE kazen
181ADD CONSTRAINT PK_kazen PRIMARY KEY (ID_KAZEN);
182
183ALTER TABLE kazen 
184ADD CONSTRAINT FK_vrsta_kaznivega_dejanja FOREIGN KEY (vrsta_kaznivega_dejanja_ID_VRSTA_KAZNIVEGA_DEJANJA) REFERENCES vrsta_kaznivega_dejanja(ID_VRSTA_KAZNIVEGA_DEJANJA);
185
186ALTER TABLE kazen 
187ADD CONSTRAINT FK_soba1 FOREIGN KEY (soba_ID_SOBA) REFERENCES soba(ID_SOBA);
188
189
190
191ALTER TABLE kazen
192CHANGE ID_KAZEN ID_KAZEN INT(10) AUTO_INCREMENT;
193
194-- -----------------------------------------------------
195-- Table `zapori`.`kraj`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `zapori`.`kraj` (
198  `ID_KRAJ` INT(11) NOT NULL,
199  `POSTNA_STEVLKA` INT(11) NOT NULL,
200  `IME_POSTE` VARCHAR(30) NOT NULL);
201 ALTER TABLE kraj
202ADD CONSTRAINT PK_kraj PRIMARY KEY (ID_KRAJ);
203
204ALTER TABLE kraj
205CHANGE ID_KRAJ ID_KRAJ INT(10) AUTO_INCREMENT;
206
207
208-- -----------------------------------------------------
209-- Table `zapori`.`naslov`
210-- -----------------------------------------------------
211CREATE TABLE IF NOT EXISTS `zapori`.`naslov` (
212  `ID_NASLOV` INT(11) NOT NULL,
213  `HISNA_STEVILKA` INT(11) NOT NULL,
214  `ULICA` VARCHAR(25) NOT NULL,
215  `kraj_ID_KRAJ` INT(11) NOT NULL,
216  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL,
217  `zapornik_zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
218  `zapornik_soba_ID_SOBA` INT(11) NOT NULL);
219
220 ALTER TABLE naslov
221ADD CONSTRAINT PK_naslov PRIMARY KEY (ID_NASLOV);
222
223ALTER TABLE naslov 
224ADD CONSTRAINT FK_kraj FOREIGN KEY (kraj_ID_KRAJ) REFERENCES kraj(ID_KRAJ);
225
226ALTER TABLE naslov 
227ADD CONSTRAINT FK_zapornik1 FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
228
229ALTER TABLE naslov 
230ADD CONSTRAINT FK_zapo_oddelek2 FOREIGN KEY (zapornik_zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
231
232ALTER TABLE naslov 
233ADD CONSTRAINT FK_soba2 FOREIGN KEY (zapornik_soba_ID_SOBA) REFERENCES soba(ID_SOBA);
234
235
236ALTER TABLE naslov
237CHANGE ID_NASLOV ID_NASLOV INT(10) AUTO_INCREMENT;
238
239
240-- -----------------------------------------------------
241-- Table `zapori`.`paznik`
242-- -----------------------------------------------------
243CREATE TABLE IF NOT EXISTS `zapori`.`paznik` (
244  `ID_PAZNIK` INT(11) NOT NULL,
245  `IME` VARCHAR(15) NOT NULL,
246  `PRIIMEK` VARCHAR(25) NOT NULL,
247  `SPOL` VARCHAR(6) NOT NULL
248 );
249  
250 ALTER TABLE paznik
251ADD CONSTRAINT PK_paznik PRIMARY KEY (ID_PAZNIK);
252
253ALTER TABLE paznik
254CHANGE ID_PAZNIK ID_PAZNIK INT(10) AUTO_INCREMENT;
255
256
257-- -----------------------------------------------------
258-- Table `zapori`.`tip_dela`
259-- -----------------------------------------------------
260CREATE TABLE IF NOT EXISTS `zapori`.`tip_dela` (
261  `ID_TIP_DELA` INT(11) NOT NULL,
262  `VRSTA_URE` VARCHAR(30) NOT NULL);
263
264 ALTER TABLE tip_dela
265ADD CONSTRAINT PK_tip_dela PRIMARY KEY (ID_TIP_DELA);
266
267ALTER TABLE tip_dela
268CHANGE ID_TIP_DELA ID_TIP_DELA INT(10) AUTO_INCREMENT;
269
270
271-- -----------------------------------------------------
272-- Table `zapori`.`zapornik_izhodi`
273-- -----------------------------------------------------
274CREATE TABLE IF NOT EXISTS `zapori`.`zapornik_izhodi` (
275  `ID_ZAPORNIK_IZHODI` INT(11) NOT NULL,
276  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL,
277  `zapornik_zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
278  `izhodi_ID_IZHODI` INT(11) NOT NULL);
279 ALTER TABLE zapornik_izhodi
280ADD CONSTRAINT PK_zapornik_izhodi PRIMARY KEY (ID_ZAPORNIK_IZHODI);
281
282ALTER TABLE zapornik_izhodi 
283ADD CONSTRAINT FK_zapornik2 FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
284
285ALTER TABLE zapornik_izhodi 
286ADD CONSTRAINT FK_zapo_oddelek3 FOREIGN KEY (zapornik_zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
287
288ALTER TABLE zapornik_izhodi 
289ADD CONSTRAINT FK_izhodi FOREIGN KEY (izhodi_ID_IZHODI) REFERENCES izhodi(ID_IZHODI);
290
291ALTER TABLE zapornik_izhodi
292CHANGE ID_ZAPORNIK_IZHODI ID_ZAPORNIK_IZHODI INT(10) AUTO_INCREMENT;
293
294
295-- -----------------------------------------------------
296-- Table `zapori`.`zapornik_kazen`
297-- -----------------------------------------------------
298CREATE TABLE IF NOT EXISTS `zapori`.`zapornik_kazen` (
299  `ID_ZAPORNIK_KAZEN` INT(11) NOT NULL,
300  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL,
301  `zapornik_zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
302  `zapornik_soba_ID_SOBA` INT(11) NOT NULL,
303  `kazen_ID_KAZEN` INT(11) NOT NULL,
304  `zacetek_kazni` DATE NOT NULL,
305  `konec_kazni` DATE NOT NULL
306 );
307
308 ALTER TABLE zapornik_kazen
309ADD CONSTRAINT PK_zapornik_kazen PRIMARY KEY (ID_ZAPORNIK_KAZEN);
310
311ALTER TABLE zapornik_kazen 
312ADD CONSTRAINT FK_zapornik3 FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
313
314ALTER TABLE zapornik_kazen 
315ADD CONSTRAINT FK_zapo_oddelek4 FOREIGN KEY (zapornik_zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
316
317ALTER TABLE zapornik_kazen 
318ADD CONSTRAINT FK_soba3 FOREIGN KEY (zapornik_soba_ID_SOBA) REFERENCES soba(ID_SOBA);
319
320ALTER TABLE zapornik_kazen 
321ADD CONSTRAINT FK_kazen FOREIGN KEY (kazen_ID_KAZEN) REFERENCES kazen(ID_KAZEN);
322
323
324ALTER TABLE zapornik_kazen
325CHANGE ID_ZAPORNIK_KAZEN ID_ZAPORNIK_KAZEN INT(10) AUTO_INCREMENT;
326
327-- -----------------------------------------------------
328-- Table `zapori`.`zapornik_paznik`
329-- -----------------------------------------------------
330CREATE TABLE IF NOT EXISTS `zapori`.`zapornik_paznik` (
331  `ID_ZAPORNIK_PAZNIK` INT(11) NOT NULL,
332  `paznik_ID_PAZNIK` INT(11) NOT NULL,
333  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL);
334 
335 ALTER TABLE zapornik_paznik
336ADD CONSTRAINT PK_zapornik_paznik PRIMARY KEY (ID_ZAPORNIK_PAZNIK);
337 
338 ALTER TABLE zapornik_paznik 
339ADD CONSTRAINT FK_paznik FOREIGN KEY (paznik_ID_PAZNIK) REFERENCES paznik(ID_PAZNIK);
340
341ALTER TABLE zapornik_paznik 
342ADD CONSTRAINT FK_zapornik4 FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
343
344ALTER TABLE zapornik_paznik
345CHANGE ID_ZAPORNIK_PAZNIK ID_ZAPORNIK_PAZNIK INT(10) AUTO_INCREMENT;
346
347
348
349-- -----------------------------------------------------
350-- Table `zapori`.`zaposlitev`
351-- -----------------------------------------------------
352CREATE TABLE IF NOT EXISTS `zapori`.`zaposlitev` (
353  `ID_ZAPOSLITEV` INT(11) NOT NULL,
354  `MESTO_ZAPOSLITVE` VARCHAR(20) NOT NULL,
355  `tip_dela_ID_TIP_DELA` INT(11) NOT NULL);
356ALTER TABLE zaposlitev
357ADD CONSTRAINT PK_zaposlitev PRIMARY KEY (ID_ZAPOSLITEV);
358 
359ALTER TABLE zaposlitev 
360ADD CONSTRAINT FK_tip_dela FOREIGN KEY (tip_dela_ID_TIP_DELA) REFERENCES tip_dela(ID_TIP_DELA);
361
362
363ALTER TABLE zaposlitev
364CHANGE ID_ZAPOSLITEV ID_ZAPOSLITEV INT(10) AUTO_INCREMENT;
365
366-- -----------------------------------------------------
367-- Table `zapori`.`zapos_zapornik`
368-- -----------------------------------------------------
369CREATE TABLE IF NOT EXISTS `zapori`.`zapos_zapornik` (
370  `ID_ZAPOS_ZAPORNIK` INT(11) NOT NULL,
371  `termin` VARCHAR(45) NOT NULL,
372  `zapornik_ID_ZAPORNIK` INT(11) NOT NULL,
373  `zapornik_zapo_oddelek_ID_ZAPO_ODDELEK` INT(11) NOT NULL,
374  `zapornik_soba_ID_SOBA` INT(11) NOT NULL,
375  `zaposlitev_ID_ZAPOSLITEV` INT(11) NOT NULL);
376
377ALTER TABLE zapos_zapornik
378ADD CONSTRAINT PK_zapos_zapornik PRIMARY KEY (ID_ZAPOS_ZAPORNIK);
379 
380ALTER TABLE zapos_zapornik 
381ADD CONSTRAINT FK_zapornik5 FOREIGN KEY (zapornik_ID_ZAPORNIK) REFERENCES zapornik(ID_ZAPORNIK);
382
383ALTER TABLE zapos_zapornik 
384ADD CONSTRAINT FK_zapo_oddelek5 FOREIGN KEY (zapornik_zapo_oddelek_ID_ZAPO_ODDELEK) REFERENCES zapo_oddelek(ID_ZAPO_ODDELEK);
385
386ALTER TABLE zapos_zapornik 
387ADD CONSTRAINT FK_soba4 FOREIGN KEY (zapornik_soba_ID_SOBA) REFERENCES soba(ID_SOBA);
388
389ALTER TABLE zapos_zapornik 
390ADD CONSTRAINT FK_zaposlitev FOREIGN KEY (zaposlitev_ID_ZAPOSLITEV) REFERENCES zaposlitev(ID_ZAPOSLITEV);
391
392ALTER TABLE zapos_zapornik
393CHANGE ID_ZAPOS_ZAPORNIK ID_ZAPOS_ZAPORNIK INT(10) AUTO_INCREMENT;