gU2rxvmz

1/*==============================================================*/
2/* BRISANJE BAZE I KREIRANJE NOVE */
3/*==============================================================*/
4DROP SCHEMA IF EXISTS `bazaodeljenja`;
5CREATE SCHEMA `bazaodeljenja` ;
6USE `bazaodeljenja`;
7/*==============================================================*/
8/* Table: ADRESA */
9/*==============================================================*/
10create table ADRESA
11(
12AdresaId int not null,
13MestoId int,
14Ulica varchar(25) not null,
15Broj varchar(5) not null,
16primary key (AdresaId)
17)engine InnoDB
18collate latin2_bin;
19/*==============================================================*/
20/* Table: IZDAVAC_RESENJA */
21/*==============================================================*/
22create table IZDAVAC_RESENJA
23(
24IzdavacId int not null,
25NazivIzdavaca varchar(100) not null,
26primary key (IzdavacId)
27)engine InnoDB
28collate latin2_bin;
29/*==============================================================*/
30/* Table: MESTO_STANOVANJA */
31/*==============================================================*/
32create table MESTO_STANOVANJA
33(
34MestoId int not null,
35OpstinaId int,
36MestoNaziv varchar(50) not null,
37BrojMesta int,
38primary key (MestoId)
39)engine InnoDB
40collate latin2_bin;
41/*==============================================================*/
42/* Table: OBRAZOVNI_PROFIL */
43/*==============================================================*/
44create table OBRAZOVNI_PROFIL
45(
46ProfilId int not null,
47PodrucjeId int,
48IzdavacId int,
49ProfilNaziv varchar(75) not null,
50primary key (ProfilId)
51)engine InnoDB
52collate latin2_bin;
53/*==============================================================*/
54/* Table: OCENA */
55/*==============================================================*/
56create table OCENA
57(
58OcenaId int not null,
59TipOceneId int,
60OcenaVrednost int not null,
61primary key (OcenaId)
62)engine InnoDB
63collate latin2_bin;
64/*==============================================================*/
65/* Table: OCENA2PREDMET */
66/*==============================================================*/
67create table OCENA2PREDMET
68(
69Id2 int not null auto_increment,
70OcenaId int,
71PredmetId int,
72UcenikId int,
73primary key(Id2)
74)engine InnoDB
75collate latin2_bin;
76/*==============================================================*/
77/* Table: ODELJENJE */
78/*==============================================================*/
79create table ODELJENJE
80(
81OdeljenjeId int not null,
82RazredId int,
83OdeljenjeOznaka varchar(10) not null,
84primary key (OdeljenjeId)
85)engine InnoDB
86collate latin2_bin;
87/*==============================================================*/
88/* Table: OPTÅ INA_STANOVANJA */
89/*==============================================================*/
90create table OPSTINA_STANOVANJA
91(
92OpstinaId int not null,
93OpstinaNaziv varchar(50) not null,
94primary key (OpstinaId)
95)engine InnoDB
96collate latin2_bin;
97/*==============================================================*/
98/* Table: PODRUCJE_RADA */
99/*==============================================================*/
100create table PODRUCJE_RADA
101(
102PodrucjeId int not null,
103PodrucjeNaziv varchar(50) not null,
104primary key (PodrucjeId)
105)engine InnoDB
106collate latin2_bin;
107/*==============================================================*/
108/* Table: PREDMET */
109/*==============================================================*/
110create table PREDMET
111(
112PredmetId int not null,
113TipPredmetaId int,
114PredmetNaziv varchar(75) not null,
115primary key (PredmetId),
116UNIQUE(PredmetNaziv)
117)engine InnoDB
118collate latin2_bin;
119/*==============================================================*/
120/* Table: PROFESOR */
121/*==============================================================*/
122create table PROFESOR
123(
124ProfesorId int not null,
125MestoId int,
126AdresaId int,
127ProfesorPrezime varchar(25) not null,
128ProfesorIme varchar(25) not null,
129ProfesorSta int,
130primary key (ProfesorId)
131)engine InnoDB
132collate latin2_bin;
133/*==============================================================*/
134/* Table: PROFESOR2PREDMET */
135/*==============================================================*/
136create table PROFESOR2PREDMET
137(
138IdProf2predmet int not null auto_increment,
139ProfesorId int,
140PredmetId int,
141primary key(IdProf2predmet)
142)engine InnoDB
143collate latin2_bin;
144/*==============================================================*/
145/* Table: PROFIL2ODELJENJE */
146/*==============================================================*/
147create table PROFIL2ODELJENJE
148(
149IdProfil2Odeljenje int not null auto_increment,
150OdeljenjeId int,
151ProfilId int,
152primary key(IdProfil2Odeljenje)
153)engine InnoDB
154collate latin2_bin;
155/*==============================================================*/
156/* Table: PROFIL2PREDMET */
157/*==============================================================*/
158create table PROFIL2PREDMET
159(
160IdProfil2Predmet int not null auto_increment,
161ProfilId int,
162PredmetId int,
163primary key(IdProfil2Predmet)
164)engine InnoDB
165collate latin2_bin;
166/*==============================================================*/
167/* Table: RAZRED */
168/*==============================================================*/
169create table RAZRED
170(
171RazredId int not null,
172RazredOznaka varchar(7) not null,
173primary key (RazredId)
174)engine InnoDB
175collate latin2_bin;
176/*==============================================================*/
177/* Table: RODITELJ */
178/*==============================================================*/
179create table RODITELJ
180(
181RoditeljId int not null,
182RoditeljIme varchar(50) not null,
183primary key (RoditeljId)
184)engine InnoDB
185collate latin2_bin;
186/*==============================================================*/
187/* Table: RODITELJ2UCENIK */
188/*==============================================================*/
189create table RODITELJ2UCENIK
190(
191IdRoditelj2Ucenik int not null auto_increment,
192RoditeljId int,
193UcenikId int,
194primary key(IdRoditelj2Ucenik)
195)engine InnoDB
196collate latin2_bin;
197/*==============================================================*/
198/* Table: TIP_OCENE */
199/*==============================================================*/
200create table TIP_OCENE
201(
202TipOceneId int not null,
203NazivTipaOcene varchar(50) not null,
204primary key (TipOceneId)
205)engine InnoDB
206collate latin2_bin;
207/*==============================================================*/
208/* Table: TIP_PREDMETA */
209/*==============================================================*/
210create table TIP_PREDMETA
211(
212TipPredmetaId int not null,
213NazivTipaPredmeta varchar(50) not null,
214primary key (TipPredmetaId)
215)engine InnoDB
216collate latin2_bin;
217/*==============================================================*/
218/* Table: UCENIK */
219/*==============================================================*/
220create table UCENIK
221(
222UcenikId int not null,
223MestoId int,
224ProfilId int,
225AdresaId int,
226UcenikPrezime varchar(50) not null,
227UcenikIme varchar(50) not null,
228UcenikTelefon varchar(50),
229UcenikMejl varchar(50),
230primary key (UcenikId)
231)engine InnoDB
232collate latin2_bin;
233/*==============================================================*/
234/* Table: UCENIK2PREDMET */
235/*==============================================================*/
236create table UCENIK2PREDMET
237(
238Id3 int not null auto_increment,
239UcenikId int,
240PredmetId int,
241SkolskaGodinaId int,
242primary key(Id3)
243)engine InnoDB
244collate latin2_bin;
245/*==============================================================*/
246/* Table: KOLSKA_GODINA */
247/*==============================================================*/
248create table SKOLSKA_GODINA
249(
250SkolskaGodinaId int not null,
251SkolskaGodina varchar(10) not null,
252primary key (SkolskaGodinaId)
253)engine InnoDB
254collate latin2_bin;
255alter table ADRESA add constraint FK_adresa2mesto foreign key (MestoId)
256references MESTO_STANOVANJA (MestoId) on delete restrict on update restrict;
257alter table MESTO_STANOVANJA add constraint FK_mesto2opstina foreign key (OpstinaId)
258references OPSTINA_STANOVANJA (OpstinaId) on delete restrict on update restrict;
259alter table OBRAZOVNI_PROFIL add constraint FK_profil2izdavac foreign key (IzdavacId)
260references IZDAVAC_RESENJA (IzdavacId) on delete restrict on update restrict;
261alter table OBRAZOVNI_PROFIL add constraint FK_profil2podrucje foreign key (PodrucjeId)
262references PODRUCJE_RADA (PodrucjeId) on delete restrict on update restrict;
263alter table OCENA add constraint FK_ocena2tip foreign key (TipOceneId)
264references TIP_OCENE (TipOceneId) on delete restrict on update restrict;
265alter table OCENA2PREDMET add constraint FK_ocena2predmet_1 foreign key (OcenaId)
266references OCENA (OcenaId) on delete restrict on update restrict;
267alter table OCENA2PREDMET add constraint FK_ocena2predmet_2 foreign key (PredmetId)
268references PREDMET (PredmetId) on delete restrict on update restrict;
269alter table OCENA2PREDMET add constraint FK_ucenik2ocena foreign key (UcenikId)
270references UCENIK (UcenikId) on delete restrict on update restrict;
271alter table ODELJENJE add constraint FK_odeljenje2razred foreign key (RazredId)
272references RAZRED (RazredId) on delete restrict on update restrict;
273alter table PREDMET add constraint FK_predmet2tippredmeta foreign key (TipPredmetaId)
274references TIP_PREDMETA (TipPredmetaId) on delete restrict on update restrict;
275alter table PROFESOR add constraint FK_profesor2adresa foreign key (AdresaId)
276references ADRESA (AdresaId) on delete restrict on update restrict;
277alter table PROFESOR add constraint FK_profesor2mesto foreign key (MestoId)
278references MESTO_STANOVANJA (MestoId) on delete restrict on update restrict;
279alter table PROFESOR2PREDMET add constraint FK_prof2predmet_1 foreign key (ProfesorId)
280references PROFESOR (ProfesorId) on delete restrict on update restrict;
281alter table PROFESOR2PREDMET add constraint FK_prof2predmet_2 foreign key (PredmetId)
282references PREDMET (PredmetId) on delete restrict on update restrict;
283alter table PROFIL2ODELJENJE add constraint FK_profil2odeljenje_1 foreign key (OdeljenjeId)
284references ODELJENJE (OdeljenjeId) on delete restrict on update restrict;
285alter table PROFIL2ODELJENJE add constraint FK_profil2odeljenje_2 foreign key (ProfilId)
286references OBRAZOVNI_PROFIL (ProfilId) on delete restrict on update restrict;
287alter table PROFIL2PREDMET add constraint FK_profil2predmet_1 foreign key (ProfilId)
288references OBRAZOVNI_PROFIL (ProfilId) on delete restrict on update restrict;
289alter table PROFIL2PREDMET add constraint FK_profil2predmet_2 foreign key (PredmetId)
290references PREDMET (PredmetId) on delete restrict on update restrict;
291alter table RODITELJ2UCENIK add constraint FK_roditelj2ucenik_1 foreign key (RoditeljId)
292references RODITELJ (RoditeljId) on delete restrict on update restrict;
293alter table RODITELJ2UCENIK add constraint FK_roditelj2ucenik_2 foreign key (UcenikId)
294references UCENIK (UcenikId) on delete restrict on update restrict;
295alter table UCENIK add constraint FK_ucenik2adresa foreign key (AdresaId)
296references ADRESA (AdresaId) on delete restrict on update restrict;
297alter table UCENIK add constraint FK_ucenik2mesto foreign key (MestoId)
298references MESTO_STANOVANJA (MestoId) on delete restrict on update restrict;
299alter table UCENIK add constraint FK_ucenik2profil foreign key (ProfilId)
300references OBRAZOVNI_PROFIL (ProfilId) on delete restrict on update restrict;
301alter table UCENIK2PREDMET add constraint FK_predmet2ucenik2skgod foreign key (SkolskaGodinaId)
302references SKOLSKA_GODINA (SkolskaGodinaId) on delete restrict on update restrict;
303alter table UCENIK2PREDMET add constraint FK_ucenik2predmet_1 foreign key (UcenikId)
304references UCENIK (UcenikId) on delete restrict on update restrict;
305alter table UCENIK2PREDMET add constraint FK_ucenik2predmet_2 foreign key (PredmetId)
306references PREDMET (PredmetId) on delete restrict on update restrict;
307/*==============================================================*/
308/* UNOS PODATAKA U BAZU */
309/*==============================================================*/
310INSERT INTO `bazaodeljenja`.`PODRUCJE_RADA` (`PodrucjeId`, `PodrucjeNaziv`) VALUES ('1',
311'ELEKTROTEHNIKA');
312INSERT INTO `bazaodeljenja`.`PODRUCJE_RADA` (`PodrucjeId`, `PodrucjeNaziv`) VALUES ('2',
313'MAÅ INSTVO I OBRADA METALA');
314INSERT INTO `bazaodeljenja`.`PODRUCJE_RADA` (`PodrucjeId`, `PodrucjeNaziv`) VALUES ('3',
315'TEKSTILSTVO');
316INSERT INTO `bazaodeljenja`.`PODRUCJE_RADA` (`PodrucjeId`, `PodrucjeNaziv`) VALUES ('4',
317'KOŽARSTVO');