1 | DROP SCHEMA IF EXISTS project CASCADE;
|
---|
2 |
|
---|
3 | DROP TABLE IF EXISTS project.Bank CASCADE;
|
---|
4 | DROP TABLE IF EXISTS project.DDV CASCADE;
|
---|
5 | DROP TABLE IF EXISTS project.Grad CASCADE;
|
---|
6 | DROP TABLE IF EXISTS project.Klient CASCADE;
|
---|
7 | DROP TABLE IF EXISTS project.TipDok CASCADE;
|
---|
8 | DROP TABLE IF EXISTS project.Vraboteni CASCADE;
|
---|
9 | DROP TABLE IF EXISTS project.Artikal CASCADE;
|
---|
10 | DROP TABLE IF EXISTS project.ZiroSmetki CASCADE;
|
---|
11 | DROP TABLE IF EXISTS project.Stavka CASCADE;
|
---|
12 | DROP TABLE IF EXISTS project.LagerList CASCADE;
|
---|
13 |
|
---|
14 | CREATE SCHEMA project;
|
---|
15 |
|
---|
16 | CREATE TABLE project.Bank (
|
---|
17 | BankID SERIAL PRIMARY KEY,
|
---|
18 | BankIme VARCHAR(100) NOT NULL UNIQUE
|
---|
19 | );
|
---|
20 |
|
---|
21 | CREATE TABLE project.DDV (
|
---|
22 | ddvID SERIAL PRIMARY KEY,
|
---|
23 | ddvVal SMALLINT NOT NULL UNIQUE
|
---|
24 | );
|
---|
25 |
|
---|
26 | CREATE TABLE project.Grad (
|
---|
27 | GradID SERIAL PRIMARY KEY,
|
---|
28 | GradIme VARCHAR(50) NOT NULL UNIQUE,
|
---|
29 | PostBroj SMALLINT UNIQUE
|
---|
30 | );
|
---|
31 |
|
---|
32 | CREATE TABLE project.Klient (
|
---|
33 | KlientID SERIAL PRIMARY KEY,
|
---|
34 | KlientIme VARCHAR(100) NOT NULL,
|
---|
35 | Adresa VARCHAR(100),
|
---|
36 | GradID SMALLINT REFERENCES project.Grad ON DELETE SET NULL,
|
---|
37 | Phone VARCHAR(20),
|
---|
38 | EMail VARCHAR(100),
|
---|
39 | EDB CHAR(13) NOT NULL UNIQUE
|
---|
40 | );
|
---|
41 |
|
---|
42 | CREATE TABLE project.TipDok (
|
---|
43 | TipID SMALLINT PRIMARY KEY,
|
---|
44 | TipIme VARCHAR(50) NOT NULL UNIQUE
|
---|
45 | );
|
---|
46 |
|
---|
47 | CREATE TABLE project.Vraboteni (
|
---|
48 | UserID SERIAL PRIMARY KEY,
|
---|
49 | Username VARCHAR(100) NOT NULL UNIQUE,
|
---|
50 | Password VARCHAR(100) NOT NULL,
|
---|
51 | Email VARCHAR(100) NOT NULL,
|
---|
52 | DisplayName VARCHAR(100),
|
---|
53 | isAdmin BOOLEAN
|
---|
54 | );
|
---|
55 |
|
---|
56 | CREATE TABLE project.Artikal (
|
---|
57 | Artikal_ID SERIAL PRIMARY KEY,
|
---|
58 | Sifra VARCHAR(10) NOT NULL,
|
---|
59 | Artikal_Ime VARCHAR(100) NOT NULL,
|
---|
60 | Cena NUMERIC(10,2) NOT NULL,
|
---|
61 | CenaSoDDV NUMERIC(10,2) NOT NULL,
|
---|
62 | ddvID SMALLINT REFERENCES project.DDV
|
---|
63 | );
|
---|
64 |
|
---|
65 | CREATE TABLE project.ZiroSmetki (
|
---|
66 | ZiroSmetkiID SERIAL PRIMARY KEY,
|
---|
67 | KlientID SMALLINT REFERENCES project.Klient ON DELETE CASCADE,
|
---|
68 | BankID SMALLINT REFERENCES project.Bank ON DELETE CASCADE,
|
---|
69 | Broj CHAR(15) NOT NULL UNIQUE
|
---|
70 | );
|
---|
71 |
|
---|
72 | CREATE TABLE project.Stavka (
|
---|
73 | StavkaID SERIAL PRIMARY KEY,
|
---|
74 | TipID SMALLINT REFERENCES project.TipDok,
|
---|
75 | Created_By SMALLINT REFERENCES project.Vraboteni ON DELETE SET NULL,
|
---|
76 | Datum DATE NOT NULL,
|
---|
77 | Broj INTEGER NOT NULL,
|
---|
78 | KlientID SMALLINT REFERENCES project.Klient ON DELETE RESTRICT,
|
---|
79 | Cena_Osnova NUMERIC(10,2) NOT NULL,
|
---|
80 | Cena_DDV NUMERIC(10,2) NOT NULL,
|
---|
81 | Cena_Vkupno NUMERIC(10,2) NOT NULL
|
---|
82 | );
|
---|
83 |
|
---|
84 | CREATE TABLE project.LagerList (
|
---|
85 | LagerList_ID SERIAL PRIMARY KEY,
|
---|
86 | StavkaID SMALLINT REFERENCES project.Stavka ON DELETE CASCADE,
|
---|
87 | Artikal_ID SMALLINT REFERENCES project.Artikal ON DELETE CASCADE,
|
---|
88 | Kolicina SMALLINT NOT NULL,
|
---|
89 | Avg_Cena NUMERIC(10,2) NOT NULL
|
---|
90 | ); |
---|