| 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 | ); |
|---|
