RelationalDesign: kreiranje.sql

--Create Tables
CREATE TABLE Firma (
    FirmaID SERIAL PRIMARY KEY,
    Adresa VARCHAR(255),
    Broj VARCHAR(255)
)

CREATE TABLE Artikl (
    ArtiklID SERIAL PRIMARY KEY,
    Cena DECIMAL,
    EdMera VARCHAR(255),
    StapkaDDV DECIMAL,
    Zaliha INT
)

CREATE TABLE Naracka (
    NarackaID SERIAL PRIMARY KEY,
    Kolicina INT,
    Datum DATE,
    Opis TEXT
)

CREATE TABLE IzrabotkaPlan (
    DatumStart DATE,
    DatumEnd DATE
)

CREATE TABLE Faktura (
    FakturaID SERIAL PRIMARY KEY,
    Datum DATE,
    Iznos DECIMAL,
    Broj VARCHAR(255)
)


CREATE TABLE Naracka_Artikl (
    NarackaID INT,
    ArtiklID INT,
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    FOREIGN KEY (ArtiklID) REFERENCES Artikl(ArtiklID),
    PRIMARY KEY (NarackaID, ArtiklID)
)

CREATE TABLE Naracka_IzrabotkaPlan (
    NarackaID INT,
    DatumStart DATE,
    DatumEnd DATE,
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    PRIMARY KEY (NarackaID, DatumStart, DatumEnd)
)

CREATE TABLE Naracka_Firma (
    NarackaID INT,
    FirmaID INT,
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    FOREIGN KEY (FirmaID) REFERENCES Firma(FirmaID),
    PRIMARY KEY (NarackaID, FirmaID)
)

CREATE TABLE Faktura_Firma (
    FakturaID INT,
    FirmaID INT,
    FOREIGN KEY (FakturaID) REFERENCES Faktura(FakturaID),
    FOREIGN KEY (FirmaID) REFERENCES Firma(FirmaID),
    PRIMARY KEY (FakturaID, FirmaID)
)

CREATE TABLE Faktura_Naracka (
    FakturaID INT,
    NarackaID INT,
    FOREIGN KEY (FakturaID) REFERENCES Faktura(FakturaID),
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    PRIMARY KEY (FakturaID, NarackaID)
)

--delete Tables
DROP TABLE IF EXISTS Faktura_Naracka;
DROP TABLE IF EXISTS Faktura_Firma;
DROP TABLE IF EXISTS Naracka_Firma;
DROP TABLE IF EXISTS Naracka_IzrabotkaPlan;
DROP TABLE IF EXISTS Naracka_Artikl;
DROP TABLE IF EXISTS Faktura;
DROP TABLE IF EXISTS IzrabotkaPlan;
DROP TABLE IF EXISTS Naracka;
DROP TABLE IF EXISTS Artikl;
DROP TABLE IF EXISTS Firma;