RelationalDesign: kreiranje v3.sql

-- Create Firma table
CREATE TABLE Firma (
    FirmaID SERIAL PRIMARY KEY,
    Naziv VARCHAR(100) NOT NULL,
    Adresa VARCHAR(150),
    Broj VARCHAR(20)
);

-- Create Artikl table
CREATE TABLE Artikl (
    ArtiklID SERIAL PRIMARY KEY,
    Naziv VARCHAR(100) NOT NULL,
    Cena DECIMAL(10,2) NOT NULL,
    EdMera VARCHAR(50),
    Stapka DECIMAL(5,2)
);

-- Create Naracka table
CREATE TABLE Naracka (
    NarackaID SERIAL PRIMARY KEY,
    FirmaID INT NOT NULL,
    Datum DATE NOT NULL,
    Opis TEXT,
    FOREIGN KEY (FirmaID) REFERENCES Firma(FirmaID)
);

-- Create Faktura table
CREATE TABLE Faktura (
    FakturaID SERIAL PRIMARY KEY,
    FirmaID INT NOT NULL,
    Datum DATE NOT NULL,
    Iznos DECIMAL(10,2) NOT NULL,
    DDV DECIMAL(5,2),
    FOREIGN KEY (FirmaID) REFERENCES Firma(FirmaID)
);

-- Create Zaliha table
CREATE TABLE Zaliha (
    DokumentID SERIAL PRIMARY KEY,
    ArtiklID INT NOT NULL,
    ArtiklNaziv VARCHAR(100) NOT NULL,
    Kolicina INT NOT NULL,
    FOREIGN KEY (ArtiklID) REFERENCES Artikl(ArtiklID)
);

-- Create Naracka_Artikl table (junction table)
CREATE TABLE Naracka_Artikl (
    NarackaID INT NOT NULL,
    ArtiklID INT NOT NULL,
    Kolicina INT NOT NULL,
    Cena DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (NarackaID, ArtiklID),
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    FOREIGN KEY (ArtiklID) REFERENCES Artikl(ArtiklID)
);

-- Create IzrabotkaPlan table
CREATE TABLE IzrabotkaPlan (
    IzrabotkaPlanID SERIAL PRIMARY KEY,
    DatumStart DATE NOT NULL,
    DatumEnd DATE NOT NULL,
    DatumPlan DATE
);

-- Create Naracka_IzrabotkaPlan table (junction table)
CREATE TABLE Naracka_IzrabotkaPlan (
    NarackaID INT NOT NULL,
    IzrabotkaPlanID INT NOT NULL,
    PRIMARY KEY (NarackaID, IzrabotkaPlanID),
    FOREIGN KEY (NarackaID) REFERENCES Naracka(NarackaID),
    FOREIGN KEY (IzrabotkaPlanID) REFERENCES IzrabotkaPlan(IzrabotkaPlanID)
);