5mMazGty

1DROP TABLE IF EXISTS artikl CASCADE;
2DROP TABLE IF EXISTS dobavljac CASCADE;
3DROP TABLE IF EXISTS porudzbina CASCADE;
4DROP TABLE IF EXISTS stavka_porudzbine CASCADE;
5
6DROP SEQUENCE IF EXISTS artikl_seq; 
7DROP SEQUENCE IF EXISTS dobavljac_seq; 
8DROP SEQUENCE IF EXISTS porudzbina_seq; 
9DROP SEQUENCE IF EXISTS stavka_porudzbine_seq;
10
11CREATE TABLE artikl (
12	id integer NOT NULL,
13    naziv varchar(50) NOT NULL,
14    proizvodjac varchar(50)
15);
16
17CREATE TABLE dobavljac(
18	id integer NOT NULL,
19    naziv varchar(50) NOT NULL,
20    adresa varchar(200) NOT NULL,
21    kontakt varchar(100) NOT NULL
22);
23
24CREATE TABLE porudzbina(
25	id integer NOT NULL,
26    datum date NOT NULL,
27    isporuceno date NOT NULL,
28    iznos numeric NOT NULL,
29    placeno boolean,
30    dobavljac integer NOT NULL
31);
32
33CREATE TABLE stavka_porudzbine(
34	id integer NOT NULL,
35    redni_broj integer NOT NULL,
36    kolicina numeric NOT NULL,
37    jedinica_mere varchar(50) NOT NULL,
38    cena numeric NOT NULL,
39    porudzbina integer NOT NULL,
40    artikl integer NOT NULL
41);
42
43ALTER TABLE artikl ADD CONSTRAINT PK_Artikl
44	PRIMARY KEY(id);
45    
46ALTER TABLE dobavljac ADD CONSTRAINT PK_Dobavljac
47	PRIMARY KEY(id);
48
49ALTER TABLE porudzbina ADD CONSTRAINT PK_Porudzbina
50	PRIMARY KEY(id);
51    
52ALTER TABLE stavka_porudzbine ADD CONSTRAINT PK_Stavka_porudzbine
53	PRIMARY KEY(id);
54    
55ALTER TABLE porudzbina ADD CONSTRAINT FK_Porudzbina_Dobavljac
56	FOREIGN KEY(dobavljac) REFERENCES dobavljac(id);
57   
58   
59    
60ALTER TABLE stavka_porudzbine ADD CONSTRAINT FK_Stavka_porudzbine_Porudzbina
61	FOREIGN KEY(porudzbina) REFERENCES porudzbina(id);
62        
63ALTER TABLE stavka_porudzbine ADD CONSTRAINT FK_Stavka_porudzbine_Artikl
64	FOREIGN KEY(artikl) REFERENCES artikl(id);
65    
66    
67CREATE INDEX IDXFK_Porudzbina_Dobavljac 
68	on porudzbina(dobavljac);
69    
70CREATE INDEX IDXFK_Stavka_porudzbine_Porudzbina
71	on stavka_porudzbine(porudzbina);
72    
73CREATE INDEX IDXFK_Stavka_porudzbine_Artikl
74	on stavka_porudzbine(artikl);
75
76CREATE SEQUENCE artikl_seq
77INCREMENT 1;
78
79CREATE SEQUENCE dobavljac_seq
80INCREMENT 1;
81
82CREATE SEQUENCE porudzbina_seq
83INCREMENT 1;
84
85CREATE SEQUENCE stavka_porudzbine_seq
86INCREMENT 1;