RelationalDesign: kreiranje.sql

drop schema if exists project cascade;
create schema project;

drop table if exists REZERVACIJA_USLUGI;
drop table if exists VRABOTENI_USLUGI;
drop table if exists TERMIN;
drop table if exists REZERVACIJA;
drop table if exists BEAUTYCENTER;
drop table if exists UPLATA;
drop table if exists OCENA;
drop table if exists USLUGI;
drop table if exists KLIENTI;
drop table if exists VRABOTENI;
drop table if exists USERS;


create table USERS(
        User_ID serial primary key,
        Username varchar(100) not null unique,
        UPassword varchar(100) not null
);

create table KLIENTI(
        User_ID integer primary key,
        Ime varchar(100) not null,
        Prezime varchar(100),
        Tel_br varchar(12) not null,
        E_mail varchar(150),

        constraint fk_User_ID_klient foreign key (User_ID) references users(User_ID)
);

create table BEAUTYCENTER(
        Salon_ID varchar(100) primary key,
        Adresa varchar(100) not null,
        Tel_br varchar(12) not null,
        E_mail varchar(100)
);

create table VRABOTENI(
        User_ID integer primary key,
        First_name varchar(100) not null,
        Middle_name varchar(100),
        Prezime varchar(100),
        E_mail varchar(100) not null,
        Tel_br varchar(12),
        Rab_iskustvo integer,
        Salon_ID varchar(100) not null,
        raboti_od timestamp not null,
        raboti_do timestamp,

        constraint fk_Salon_ID foreign key (Salon_ID) references beautycenter(Salon_ID) ,
    constraint fk_User_ID_vraboten foreign key (User_ID) references users(User_ID)
);

create table UPLATA(
         Uplata_ID serial primary key,
         Informacii varchar(100)         
);
create table TERMINI(
        Termin_ID serial primary key, 
        Pocetok timestamp not null,
        Vremetraenje integer,
        User_ID_vraboten integer not null,
  
  constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID)
);

create table REZERVACIJA(
        Br_rez serial primary key,
        User_ID_klient integer not null,
        Termin_ID integer not null,
        Uplata_ID integer,
        User_ID_vraboten integer,
        
          
        constraint fk_User_ID_klient foreign key (User_ID_klient) references klienti(User_ID),
        constraint fk_Termin_ID foreign key (Termin_ID) references termini(Termin_ID),
        constraint fk_Uplata_ID foreign key (Uplata_ID) references uplata(Uplata_ID),
        constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID)
          
);

create table USLUGI(
          Usluga_ID varchar(100) primary key,
          Dejnost varchar(100),
          Cena varchar(100) not null
);

create table OCENA(
        Ocena_ID serial primary key,
        Vrednost integer not null,
        Komentar varchar(200),
        User_ID_klient integer not null,
        Usluga_ID varchar(100) not null,
        br_rez integer, 
        

        constraint fk_User_ID_klient foreign key (User_ID_klient) references klienti(User_ID),
        constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID),
        constraint fk_Br_rez foreign key (br_rez) references rezervacija(br_rez)
);

create table REZERVACIJA_USLUGI(
        Br_rez integer not null, 
        Usluga_ID varchar(100) not null,

        constraint pk_rezervacija_uslugi primary key (Br_rez, Usluga_ID),
        constraint fk_Br_rez foreign key (br_rez) references rezervacija(br_rez),
        constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID)
);

create table VRABOTENI_USLUGI(
        User_ID_vraboten integer not null,
        Usluga_ID varchar(100) not null,

        constraint pk_vraboteni_uslugi primary key (User_ID_vraboten, Usluga_ID),
        constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID),
        constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID)
);