Нормализација и подобрувања на дизајнот: normlizacija_ddl_schema.3.sql

-- Brisenje na postoecki tabeli vo правилен редослед
DROP TABLE IF EXISTS trenira CASCADE;
DROP TABLE IF EXISTS raboti CASCADE;
DROP TABLE IF EXISTS poseti CASCADE;
DROP TABLE IF EXISTS clenstvo CASCADE;
DROP TABLE IF EXISTS clenovi CASCADE;
DROP TABLE IF EXISTS paketi CASCADE;
DROP TABLE IF EXISTS tip_vremetranje CASCADE;
DROP TABLE IF EXISTS lokacii CASCADE;
DROP TABLE IF EXISTS vraboteni CASCADE;

-- Kreiranje na tabeli

CREATE TABLE clenovi (
    clenid SERIAL PRIMARY KEY,
    ime VARCHAR(50) NOT NULL,
    prezime VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    broj_poseti INT DEFAULT 0
);

CREATE TABLE paketi (
    paketid SERIAL PRIMARY KEY,
    ime VARCHAR(50) NOT NULL,
    cena NUMERIC(10,2) NOT NULL
);

CREATE TABLE tip_vremetranje (
    vremetraenjeid SERIAL PRIMARY KEY,
    vremetraenjedenovi INT NOT NULL,
    ime VARCHAR(50) NOT NULL
);

CREATE TABLE clenstvo (
    clenstvoid SERIAL PRIMARY KEY,
    clenid INT REFERENCES clenovi(clenid) ON DELETE CASCADE,
    paketid INT REFERENCES paketi(paketid) ON DELETE CASCADE,
    vremetraenjeid INT REFERENCES tip_vremetranje(vremetraenjeid) ON DELETE CASCADE,
    start_datum DATE NOT NULL
);

CREATE TABLE lokacii (
    lokacijaid SERIAL PRIMARY KEY,
    ime VARCHAR(50) NOT NULL,
    adresa VARCHAR(255) NOT NULL,
    telefon VARCHAR(15) NOT NULL
);

CREATE TABLE poseti (
    posetaid SERIAL PRIMARY KEY,
    clenid INT REFERENCES clenovi(clenid) ON DELETE CASCADE,
    lokacijaid INT REFERENCES lokacii(lokacijaid) ON DELETE CASCADE,
    datum DATE NOT NULL
);

CREATE TABLE vraboteni (
    vrabotenid SERIAL PRIMARY KEY,
    ime VARCHAR(50) NOT NULL,
    pozicija VARCHAR(50) NOT NULL
);

CREATE TABLE trenira (
    clenid INT REFERENCES clenovi(clenid) ON DELETE CASCADE,
    vrabotenid INT REFERENCES vraboteni(vrabotenid) ON DELETE CASCADE,
    PRIMARY KEY(clenid, vrabotenid)
);

CREATE TABLE raboti (
    vrabotenid INT REFERENCES vraboteni(vrabotenid) ON DELETE CASCADE,
    lokacijaid INT REFERENCES lokacii(lokacijaid) ON DELETE CASCADE,
    PRIMARY KEY(vrabotenid, lokacijaid)
);