RelationalDesign: schema_creation.sql

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;
SET search_path TO project;

CREATE TABLE KORISNICI (
    korisnik_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ime VARCHAR(100) NOT NULL,
    prezime VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    lozinka VARCHAR(255) NOT NULL,
    datum_registracija DATE NOT NULL,
    uloga VARCHAR(20) NOT NULL
);

CREATE TABLE KNIGI (
    kniga_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    naslov VARCHAR(255) NOT NULL,
    isbn VARCHAR(20) NOT NULL UNIQUE,
    opis TEXT,
    korica_url TEXT,
    godina_izdavanje SMALLINT,
    kolicina_na_zaliha INTEGER NOT NULL,
    cena NUMERIC(10,2) NOT NULL
);

CREATE TABLE AVTORI (
    avtor_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ime VARCHAR(100) NOT NULL,
    prezime VARCHAR(100) NOT NULL,
    biografija TEXT
);

CREATE TABLE ZHANROVI (
    zhanr_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    naziv VARCHAR(100) NOT NULL,
    opis TEXT
);

CREATE TABLE KATEGORII (
    kategorija_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    naziv VARCHAR(100) NOT NULL,
    opis TEXT
);

CREATE TABLE RASPOLOZENIJA (
    raspolozenie_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    naziv VARCHAR(100) NOT NULL,
    opis TEXT
);

CREATE TABLE NARACKI (
    naracka_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    datum TIMESTAMP NOT NULL,
    status VARCHAR(30) NOT NULL,
    vkupna_cena NUMERIC(10,2) NOT NULL,
    korisnik_id BIGINT NOT NULL,
    FOREIGN KEY (korisnik_id) REFERENCES KORISNICI(korisnik_id)
);

CREATE TABLE PLAKANJA (
    plakjanje_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    datum TIMESTAMP NOT NULL,
    iznos NUMERIC(10,2) NOT NULL,
    nacin_na_plakjanje VARCHAR(50) NOT NULL,
    status VARCHAR(30) NOT NULL,
    naracka_id BIGINT NOT NULL UNIQUE,
    FOREIGN KEY (naracka_id) REFERENCES NARACKI(naracka_id)
);

CREATE TABLE SODRZI (
    naracka_id BIGINT NOT NULL,
    kniga_id BIGINT NOT NULL,
    kolicina INTEGER NOT NULL,
    edinechna_cena NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (naracka_id, kniga_id),
    FOREIGN KEY (naracka_id) REFERENCES NARACKI(naracka_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id)
);

CREATE TABLE NAPISANA_OD (
    kniga_id BIGINT NOT NULL,
    avtor_id BIGINT NOT NULL,
    PRIMARY KEY (kniga_id, avtor_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id),
    FOREIGN KEY (avtor_id) REFERENCES AVTORI(avtor_id)
);

CREATE TABLE IMA_ZHANR (
    kniga_id BIGINT NOT NULL,
    zhanr_id BIGINT NOT NULL,
    PRIMARY KEY (kniga_id, zhanr_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id),
    FOREIGN KEY (zhanr_id) REFERENCES ZHANROVI(zhanr_id)
);

CREATE TABLE IMA_KATEGORIJA (
    kniga_id BIGINT NOT NULL,
    kategorija_id BIGINT NOT NULL,
    PRIMARY KEY (kniga_id, kategorija_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id),
    FOREIGN KEY (kategorija_id) REFERENCES KATEGORII(kategorija_id)
);

CREATE TABLE POVRZANA_SO (
    kniga_id BIGINT NOT NULL,
    raspolozenie_id BIGINT NOT NULL,
    PRIMARY KEY (kniga_id, raspolozenie_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id),
    FOREIGN KEY (raspolozenie_id) REFERENCES RASPOLOZENIJA(raspolozenie_id)
);

CREATE TABLE OMILENI (
    korisnik_id BIGINT NOT NULL,
    kniga_id BIGINT NOT NULL,
    datum_dodavanje DATE NOT NULL,
    PRIMARY KEY (korisnik_id, kniga_id),
    FOREIGN KEY (korisnik_id) REFERENCES KORISNICI(korisnik_id),
    FOREIGN KEY (kniga_id) REFERENCES KNIGI(kniga_id)
);

CREATE TABLE PREFERIRA_ZHANR (
    korisnik_id BIGINT NOT NULL,
    zhanr_id BIGINT NOT NULL,
    datum_dodavanje DATE NOT NULL,
    PRIMARY KEY (korisnik_id, zhanr_id),
    FOREIGN KEY (korisnik_id) REFERENCES KORISNICI(korisnik_id),
    FOREIGN KEY (zhanr_id) REFERENCES ZHANROVI(zhanr_id)
);

CREATE TABLE PREFERIRA_KATEGORIJA (
    korisnik_id BIGINT NOT NULL,
    kategorija_id BIGINT NOT NULL,
    datum_dodavanje DATE NOT NULL,
    PRIMARY KEY (korisnik_id, kategorija_id),
    FOREIGN KEY (korisnik_id) REFERENCES KORISNICI(korisnik_id),
    FOREIGN KEY (kategorija_id) REFERENCES KATEGORII(kategorija_id)
);

CREATE TABLE IZBIRA_RASPOLOZENIE (
    korisnik_id BIGINT NOT NULL,
    raspolozenie_id BIGINT NOT NULL,
    datum_izbor DATE NOT NULL,
    PRIMARY KEY (korisnik_id, raspolozenie_id),
    FOREIGN KEY (korisnik_id) REFERENCES KORISNICI(korisnik_id),
    FOREIGN KEY (raspolozenie_id) REFERENCES RASPOLOZENIJA(raspolozenie_id)
);