zvF7m7eu

1use kulturna_dediscina;
2drop table if exists popravilo;
3drop table if exists spomenik;
4drop table if exists lokacija;
5drop table if exists posta;
6drop table if exists tip_spomenika;
7drop table if exists vrednost;
8drop table if exists stanje;
9drop table if exists tip_popravila;
10drop table if exists izvajalec_popravil_opravil_popravilo;
11drop table if exists izvajalec_popravil;
12drop table if exists lastnik;
13drop table if exists avtor_ima_spomenik;
14drop table if exists avtor;
15drop table if exists tip_osebe;
16
17create table if not exists posta
18(
19postna_stevilka int,
20kraj varchar(45) not null
21);
22alter table posta add constraint postna_stevilka_pk primary key (postna_stevilka);
23alter table posta modify postna_stevilka int auto_increment;
24
25create table if not exists lokacija
26(
27id_lokacija int,
28ime_lokacija varchar(45),
29ulica varchar(45) not null,
30hisna_stevilka varchar(5),
31posta_postna_stevilka int
32);
33alter table lokacija add constraint id_lokacija_pk primary key (id_lokacija);
34alter table lokacija add constraint posta_postna_stevilka_fk foreign key (posta_postna_stevilka) references posta (postna_stevilka);
35alter table lokacija modify id_lokacija int auto_increment;
36
37
38create table if not exists tip_spomenika
39(
40id_tip_spomenika int,
41naziv varchar(45) not null
42);
43alter table tip_spomenika add constraint id_tip_spomenika_pk primary key (id_tip_spomenika);
44alter table tip_spomenika modify id_tip_spomenika int auto_increment;
45
46create table if not exists vrednost
47(
48id_vrednost int,
49kulturna_vrednost int not null, 
50cenovna_vrednost int not null,
51opis varchar(255)
52);
53alter table vrednost add constraint id_vrednost_pk primary key (id_vrednost);
54alter table vrednost modify id_vrednost int auto_increment;
55
56create table if not exists stanje
57(
58id_stanje int, 
59ocena int not null, 
60opis varchar(255)
61);
62alter table stanje add constraint id_stanje_pk primary key (id_stanje);
63alter table stanje modify id_stanje int auto_increment;
64
65create table if not exists tip_popravila
66(
67id_tip_popravila int, 
68naziv varchar(45) not null
69);
70alter table tip_popravila add constraint id_tip_popravila_pk primary key (id_tip_popravila);
71alter table tip_popravila modify id_tip_popravila int auto_increment;
72
73create table if not exists tip_osebe
74(
75id_tip_osebe int,
76naziv varchar(45) not null
77);
78alter table tip_osebe add constraint id_tip_osebe_pk primary key (id_tip_osebe);
79alter table tip_osebe modify id_tip_osebe int auto_increment;
80
81create table if not exists lastnik
82(
83id_lastnik int,
84ime varchar(45),
85priimek varchar(45),
86naziv varchar(45),
87razpolozljiva_sredstva int not null,
88tip_osebe_id_tip_osebe int not null
89);
90alter table lastnik add constraint id_lastnik_pk primary key (id_lastnik);
91alter table lastnik add constraint tip_osebe_id_tip_osebe_fk_lastnik foreign key (tip_osebe_id_tip_osebe) references tip_osebe (id_tip_osebe);
92alter table lastnik modify id_lastnik int auto_increment;
93
94create table if not exists avtor
95(
96id_avtor int,
97ime varchar(45),
98priimek varchar(45),
99naziv varchar(45),
100tip_osebe_id_tip_osebe int not null
101);
102alter table avtor add constraint id_avtor_pk primary key (id_avtor);
103alter table avtor add constraint tip_osebe_id_tip_osebe_fk_avtor foreign key (tip_osebe_id_tip_osebe) references tip_osebe (id_tip_osebe);
104alter table avtor modify id_avtor int auto_increment;
105
106create table if not exists izvajalec_popravil
107(
108id_izvajalec_popravil int,
109naziv varchar(45) not null,
110leto_ustanovitve year not null,
111ocena int
112);
113alter table izvajalec_popravil add constraint id_izvajalec_popravil_pk primary key (id_izvajalec_popravil);
114alter table izvajalec_popravil modify id_izvajalec_popravil int auto_increment;
115
116create table if not exists spomenik
117(
118id_spomenik int,
119ime varchar(45) not null,
120leto_izdelave year not null,
121nazadnje_obnovljen year,
122tip_spomenika_id_tip_spomenika int not null,
123vrednost_id_vrednost int not null,
124stanje_id_stanje int not null,
125lastnik_id_lastnik int not null,
126lokacija_id_lokacija int not null
127);
128alter table spomenik add constraint id_spomenik_pk primary key (id_spomenik);
129alter table spomenik add constraint tip_spomenika_id_tip_spomenika_fk foreign key (tip_spomenika_id_tip_spomenika) references tip_spomenika (id_tip_spomenika);
130alter table spomenik add constraint vrednost_id_vrednost_fk foreign key (vrednost_id_vrednost) references vrednost (id_vrednost);
131alter table spomenik add constraint stanje_id_stanje_fk foreign key (stanje_id_stanje) references stanje (id_stanje);
132alter table spomenik add constraint lastnik_id_lastnik_fk foreign key (lastnik_id_lastnik) references lastnik (id_lastnik);
133alter table spomenik add constraint lokacija_id_lokacija_fk foreign key (lokacija_id_lokacija) references lokacija (id_lokacija);
134alter table spomenik modify id_spomenik int auto_increment;
135
136create table if not exists popravilo
137(
138id_popravilo int,
139opis_popravila varchar(255) not null,
140predviden_zacetek date, 
141predviden_konec date,
142cena int not null,
143spomenik_id_spomenik int not null,
144tip_popravila_id_tip_popravila int not null
145);
146alter table popravilo add constraint id_popravilo_pk primary key (id_popravilo);
147alter table popravilo add constraint spomenik_id_spomenik_fk foreign key (spomenik_id_spomenik) references spomenik (id_spomenik);
148alter table popravilo add constraint tip_popravila_id_tip_popravila_fk foreign key (tip_popravila_id_tip_popravila) references tip_popravila (id_tip_popravila);
149alter table popravilo modify id_popravilo int auto_increment;
150
151create table if not exists izvajalec_popravil_opravil_popravilo
152(
153id_izvajalec_popravil_opravil_popravilo int not null,
154izvajalec_popravil_id_izvajalec_popravil int not null,
155popravilo_id_popravilo int not null
156);
157alter table izvajalec_popravil_opravil_popravilo add constraint id_izvajalec_popravil_opravil_popravilo_pk primary key (id_izvajalec_popravil_opravil_popravilo);
158alter table izvajalec_popravil_opravil_popravilo modify id_izvajalec_popravil_opravil_popravilo int auto_increment;
159-- alter table izvajalec_popravil_opravil_popravilo add constraint id_izvajalec_popravil_id_popravilo_pk primary key (izvajalec_popravil_id_izvajalec_popravil, popravilo_id_popravilo);
160
161create table if not exists avtor_ima_spomenik
162(
163id_avtor_ima_spomenika int not null,
164avtor_id_avtor int not null,
165spomenik_id_spomenik int not null
166);
167alter table avtor_ima_spomenik add constraint id_avtor_ima_spomenika_pk primary key (id_avtor_ima_spomenika);
168alter table avtor_ima_spomenik modify id_avtor_ima_spomenika int auto_increment;
169-- alter table avtor_ima_spomenik add constraint id_avtor_id_spomenik_pk primary key (avtor_id_avtor, spomenik_id_spomenik);
170
171
172drop trigger if exists dodajanje_popravila;
173delimiter &
174create trigger dodajanje_popravila
175after insert on popravilo
176for each row
177begin
178update spomenik
179set nazadnje_obnovljen = if(new.predviden_zacetek is not null and new.predviden_zacetek > nazadnje_obnovljen, year(new.predviden_zacetek), nazadnje_obnovljen)
180where spomenik.id_spomenik = new.spomenik_id_spomenik;
181end &
182delimiter ;
183
184drop trigger if exists posodabljanje_popravila;
185delimiter &
186create trigger posodabljanje_popravila
187after update on popravilo
188for each row
189begin
190update lastnik
191set razpolozljiva_sredstva = razpolozljiva_sredstva - (new.cena - old.cena)
192where lastnik.id_lastnik = (select lastnik_id_lastnik from spomenik s inner join popravilo p on s.id_spomenik = p.spomenik_id_spomenik where id_popravilo = new.id_popravilo);
193end &
194delimiter ;
195
196drop trigger if exists brisanje_izvajalca;
197delimiter &
198create trigger brisanje_izvajalca
199after delete on izvajalec_popravil
200for each row
201begin
202update izvajalec_popravil_opravil_popravilo
203set izvajalec_popravil_id_izvajalec_popravil = 1
204where izvajalec_popravil_id_izvajalec_popravil = old.id_izvajalec_popravil;
205end &
206delimiter ;
207
208-- insert into posta values (1000, "ljubljana");
209-- insert into lokacija values (1, null, "brezova ulica", "13a", 1000);
210-- insert into tip_spomenika values (1, "fasada");
211-- insert into vrednost values (1, 82, 76, "zelo kulturno vredno");
212-- insert into stanje values (1, 65, "bolj slabo stanje");
213-- insert into tip_osebe values (1, "fizicna oseba");
214-- insert into lastnik values (1, "rok", "amon", null, 1000000, 1);
215-- insert into avtor values (1, "ziga", "palcniK", null, 1);
216-- insert into tip_popravila values (1, "barvanje");
217-- insert into izvajalec_popravil values (1, "pobarvanci d.o.o", 2003, 82);
218-- insert into izvajalec_popravil values (2, "kiparji d.o.o", 2007, 82);
219
220-- insert into spomenik values (1, "presernov kip", 1989, 2013, 1, 1, 1, 1, 1);
221-- insert into avtor_ima_spomenik values (1, 1);
222-- insert into popravilo values (1, "barvanje celotnega kipa", null, '2018-01-02', 20000, 1 ,1);
223-- insert into izvajalec_popravil_opravil_popravilo values (2, 1);
224
225-- update popravilo 
226-- set cena = 10000
227-- where id_popravilo = 1;
228
229-- delete from izvajalec_popravil
230-- where naziv = "kiparji d.o.o" and id_izvajalec_popravil > 0