LogicDesign: ddl_final.sql

File ddl_final.sql, 9.2 KB (added by 153081, 3 years ago)
Line 
1create table Vraboteni(
2id_vraboten serial ,
3ime varchar(100) not null,
4prezime varchar(100) not null,
5embg varchar(13) not null,
6datum_ragjanje date not null,
7obrazovanie varchar(100) not null,
8constraint pk_vraboten primary key (id_vraboten)
9);
10create table Menadzeri(
11id_vraboten integer,
12constraint pk_menadzer primary key (id_vraboten),
13constraint fk_vraboteni_menadzer foreign key (id_vraboten)
14references vraboteni(id_vraboten)
15);
16create table Direktor(
17id_vraboten integer,
18constraint pk_director primary key (id_vraboten),
19constraint fk_vraboteni_direktor foreign key (id_vraboten)
20references vraboteni(id_vraboten)
21);
22create table Magacioneri(
23id_vraboten integer,
24constraint pk_magacioner primary key (id_vraboten),
25constraint fk_vraboteni_magacioner foreign key (id_vraboten)
26references vraboteni(id_vraboten)
27);
28create table Shefovi(
29id_vraboten integer,
30constraint pk_shef primary key (id_vraboten),
31constraint fk_vraboteni_shef foreign key (id_vraboten )
32references vraboteni(id_vraboten)
33);
34create table Vraboteni_vo_proizvodstvo(
35id_vraboten integer,
36constraint pk_vrab_proizvodstvo primary key (id_vraboten),
37constraint fk_vraboten_proizvodstvo foreign key(id_vraboten)
38references vraboteni(id_vraboten)
39);
40create table Korisnik(
41id_korisnik serial,
42korisnicko_ime varchar(100) not null,
43lozinka varchar(300) not null,
44constraint pk_korisnik primary key(id_korisnik)
45);
46create table Delovi(
47id_del serial,
48vid_na_del varchar(100) not null,
49cena double precision not null,
50constraint pk_delovi primary key(id_del)
51);
52create table Materijali(
53id_materijal serial,
54velicina double precision not null,
55naziv varchar(50) not null,
56dostapen_broj integer not null,
57constraint pk_materijal primary key (id_materijal)
58);
59create table Mashini(
60id_mashina serial,
61naziv varchar(50) not null,
62constraint pr_mashina primary key (id_mashina)
63);
64create table Oddelenija(
65id_oddelenie serial,
66naziv varchar(30) not null,
67constraint pk_oddelenie primary key(id_oddelenie)
68);
69create table Mebel(
70id_mebel serial,
71tip varchar(100) not null,
72naziv varchar(50) not null,
73cena double precision not null,
74constraint pk_mebel primary key (id_mebel)
75);
76create table Klienti(
77id_klient serial,
78korisnicko_ime varchar(50) not null,
79lozinka varchar(300) not null ,
80prezime varchar(100) not null,
81ime varchar(100) not null,
82constraint pk_klienti primary key(id_klient)
83);
84create table Nalozi(
85id_nalog serial ,
86krajna_data date ,
87pocetna_data date not null,
88id_vraboten integer not null,
89constraint pk_nalog primary key(id_nalog),
90constraint fk_nalog_menagzer foreign key (id_vraboten) references Menadzeri(id_vraboten)
91);
92create table Naracka(
93id_naracka serial,
94datum_napravena date not null,
95datum_isporacana date ,
96naslov varchar(100) not null,
97opis text not null,
98vkupna_cena double precision,
99id_klient integer not null,
100constraint pk_naracka primary key (id_naracka),
101constraint fk_naracal_klient foreign key (id_klient) references klienti(id_klient)
102);
103create table Dogovori(
104id_dogovor serial,
105pocetok date not null,
106plata double precision not null,
107kraj date not null,
108aktiven smallint not null,
109id_vraboten integer,
110constraint pk_dogovor primary key(id_dogovor),
111constraint fk_vraboten_dogovor foreign key (id_vraboten) references vraboteni(id_vraboten)
112);
113create table Evidencija_na_naracki(
114id_evidencija serial,
115datum date not null,
116id_naracka integer not null,
117constraint pk_evidencija_na_naracki primary key(id_evidencija),
118constraint fk_naracka_za_evidencija foreign key (id_naracka) references naracka(id_naracka)
119);
120create table ima_napraveno_servis(
121id_servis serial,
122id_mashina integer not null,
123datum date not null,
124zabeleska text not null,
125cena double precision not null,
126constraint pk_servis primary key (id_servis,id_mashina),
127constraint fk_mashina_servis foreign key(id_mashina) references mashini(id_mashina)
128);
129--relacii mxn
130create table naracka_se_sostoi_od_mebel(
131id_mebel integer,
132id_naracka integer,
133kolicina integer not null,
134constraint pk_naracka_se_sostoi_od_mebel primary key (id_mebel,id_naracka),
135constraint fk_naracka_se_sostoi_mebel foreign key(id_mebel) references mebel(id_mebel),
136constraint fk_naracka_se_sostoi_naracka foreign key(id_naracka) references naracka(id_naracka)
137);
138create table dopolniteli_delovi_za_naracka(
139id_naracka integer,
140id_del integer ,
141kolicina integer not null,
142constraint pk_dopolnistelni_delovi primary key(id_naracka,id_del),
143constraint fk_dopolnitelni_delovi_naracka foreign key (id_naracka) references naracka(id_naracka),
144constraint fk_dopolnitelni_delovi_delovi foreign key (id_del) references delovi(id_del)
145);
146create table mebel_se_sostoi_od_delovi(
147id_mebel integer ,
148id_del integer,
149kolicina integer not null,
150constraint pk_mebel_se_sostoi_od_delovi primary key(id_mebel,id_del),
151constraint fk_mebel_sostoi_od_delov_mebel foreign key (id_mebel) references mebel(id_mebel),
152constraint fk_mebel_sostoi_od_delov_del foreign key (id_del) references delovi(id_del)
153);
154create table evidencija_naracki_izraboteni_delovi(
155id_evidencija integer,
156id_del integer,
157br_na_zavrseni_delovi integer not null,
158constraint pk_evidencija_naracki_izraboteni_delovi primary key(id_evidencija,id_del),
159constraint fk_evidencija_naracki_izraboteni_delovi_evidencija foreign key (id_evidencija) references evidencija_na_naracki(id_evidencija),
160constraint fk_evidencija_naracki_izraboteni_delovi_delovi foreign key(id_del) references delovi(id_del)
161);
162
163--relacii nx1,nx1
164create table nalozi_kreira_naracka(
165id_nalog integer,
166id_naracka integer not null,
167constraint pk_nalozi_kreira_naracka primary key (id_nalog),
168constraint fk_nalozi_kreira_naracka_nalog foreign key (id_nalog) references nalozi(id_nalog),
169constraint fk_nalozi_kreira_naracka_naracka foreign key (id_naracka) references naracka(id_naracka)
170);
171create table evidencija_se_odnesuva_na_mebel(
172id_evidencija integer,
173id_mebel integer not null,
174procenti_zavrsen double precision,
175constraint pk_evidencija_se_odnesuva_na_mebel primary key (id_evidencija),
176constraint fk_evidencija_se_odnesuva_na_mebel_evidencija foreign key (id_evidencija) references evidencija_na_naracki(id_evidencija),
177constraint fk_evidencija_se_odnesuva_na_mebel_mebel foreign key (id_mebel) references mebel(id_mebel)
178);
179create table delovi_izraboteni_od_materijal(
180id_del integer,
181id_materijal integer not null,
182constraint pk_delovi_izraboteni_od_materijal primary key (id_del),
183constraint fk_delovi_izraboteni_od_materijal_delovi foreign key (id_del) references delovi(id_del),
184constraint fk_delovi_izraboteni_od_materijal_materijal foreign key (id_materijal) references materijali(id_materijal)
185);
186
187create table delovi_izraboteni_od_mashina(
188id_del integer,
189id_mashina integer not null,
190constraint pk_delovi_izraboteni_od_mashina primary key (id_mashina,id_del),
191constraint fk_delovi_izraboteni_od_mashina_del foreign key(id_del) references delovi(id_del),
192constraint fk_delovi_izraboteni_od_mashina_mashina foreign key (id_mashina) references mashini(id_mashina)
193);
194create table mashina_pripaga_oddelenie(
195id_mashina integer ,
196id_oddelenie integer not null,
197constraint pk_mashina_pripaga_oddelenie primary key (id_mashina),
198constraint fk_mashina_prigaha_oddelenie_mashina foreign key (id_mashina) references mashini(id_mashina),
199constraint fk_mashina_pripaga_oddelenie_oddelenie foreign key (id_oddelenie) references oddelenija(id_oddelenie)
200);
201create table vraboteni_se_korisnici(
202id_vraboten integer,
203id_korisnik integer,
204constraint pk_korisnici_vraboteni primary key (id_vraboten),
205constraint fk_korisnici_vraboteni_vraboten foreign key (id_vraboten)references vraboteni(id_vraboten),
206constraint fk_korisnici_vraboteni_korisnik foreign key (id_korisnik) references korisnik(id_korisnik)
207);
208create table vraboteni_vo_proizvodstvo_raboti_vo_oddelenija(
209id_vraboten integer,
210id_oddelenie integer,
211constraint pk_vraboteni_vo_proizvodstvo_raboti_vo_oddelenija primary key (id_vraboten),
212constraint fk_vraboteni_vo_proizvodstvo_raboti_vo_odd_vraboteni foreign key (id_vraboten) references vraboteni_vo_proizvodstvo(id_vraboten),
213constraint fk_vraboteni_vo_proizvodstvo_raboti_vo_odd_oddelenie foreign key (id_oddelenie)references oddelenija(id_oddelenie)
214);
215
216CREATE table if not exists vraboteni_vo_proizvodstvo_raboti_na_mashina (
217 id_mashina int4 NOT NULL,
218 id_vraboten int4 NULL,
219 CONSTRAINT pk_vprm PRIMARY KEY (id_mashina),
220 constraint fk_mashina foreign key(id_mashina) references mashini(id_mashina),
221 constraint fk_vraboten foreign key(id_mashina) references vraboteni_vo_proizvodstvo(id_vraboten)
222);
223
224
225
226ALTER TABLE vraboteni ADD column IF NOT exists "role" varchar(50);
227
228ALTER TABLE korisnik ADD column IF NOT EXISTS "role" varchar(50);
229
230
231ALTER TABLE korisnik ADD column IF NOT EXISTS email varchar(50);
232
233
234
235ALTER TABLE klienti ADD column IF NOT EXISTS "role" varchar(50);
236
237
238ALTER TABLE klienti ADD column IF NOT EXISTS email varchar(50);
239
240CREATE table if not exists kosnicka (
241 id_kosnicka serial4 NOT NULL,
242 datum_napravena date NOT NULL,
243 status varchar(20) NOT NULL,
244 id_klient int4 NULL,
245 CONSTRAINT pk_kosnicka PRIMARY KEY (id_kosnicka),
246 constraint fk_kos_klien foreign key klienti(id_klient)
247);
248CREATE TABLE if not exists kosnicka_se_sostoi_od_mebel (
249 id_kosnicka int4 NOT NULL,
250 id_mebel int4 NOT NULL,
251 CONSTRAINT pk_kosnicka_se_sostoi_od_mebel PRIMARY KEY (id_kosnicka, id_mebel),
252 constraint fk_kossm_kos foreign key(id_kosnicka) references kosnicka(id_kosnicka),
253 constraint fk_kossm_meb foreign key(id_mebel) references mebel(id_mebel)
254);
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271