Логички и физички дизајн - Креирање база податоци (со SQL DDL): kreiranje_tabeli.sql

drop schema if exists project cascade;
create schema project;

drop table if exists Rezervacija;
drop table if exists Ocenka;
drop table if exists Tip;
drop table if exists Klient cascade;
drop table if exists Termin;

drop table if exists Trening;
drop table if exists Fitnes_Centar cascade;
drop table if exists Fitnes_Instruktor;
drop table if exists Sopstvenik;
drop table if exists Korisnici cascade;

create table Korisnici (
    user_id int primary key,
    username varchar(100) not null ,
    password varchar(100) not null
);

create table Fitnes_Centar (
    id int primary key,
    ime varchar(100) not null,
    tel_br varchar(15),
    lokacija varchar(150)
    
    
);

create table Klient (
    user_id_klient int primary key, 
    id_gym int,
    ime varchar(50) not null,
    prezime varchar(50) not null,
    email varchar(100) not null, 
    tel_broj varchar(15),
    constraint fk20 foreign key (user_id_klient) references Korisnici(user_id),
        constraint ttt foreign key (id_gym) references Fitnes_Centar(id) 
    );

create table Sopstvenik (
    user_id_sopstvenik int primary key,
    id_gym int,
    username varchar(30),
    ime varchar(100) not null,
    prezime varchar(100) not null,
    tel_br varchar(15),
    email varchar(150),
    constraint fk1 foreign key (user_id_sopstvenik) references Korisnici(user_id),
    constraint ff foreign key (id_gym) references Fitnes_Centar(id) 
);

create table Fitnes_Instruktor (
    user_id_instruktor int primary key,
    id_gym int,
    ime varchar(100) not null,
    prezime varchar(100) not null,
    kratka_bio text,
    rab_iskustvo int,
    tel_broj varchar(15),
    email varchar(150),
    constraint fk2 foreign key (user_id_instruktor) references Korisnici(user_id),
        constraint lll foreign key (id_gym) references Fitnes_Centar(id) 

    );

create table Trening (
    trening_id int primary key,
    
    tip varchar(50) not null,
    user_id_instruktor_trening int not null,
    constraint fk4 foreign key (user_id_instruktor_trening) references Fitnes_Instruktor(user_id_instruktor)

    );

create table Termin (
        termin_id int unique,
        instruktor_id int,
    trening_id_termin int not null,
    user_id_klient_termin int,
    pocetok time not null,
    vremetraenje int,
    datum date,
    primary key (termin_id,instruktor_id, trening_id_termin, user_id_klient_termin),
    constraint iii foreign key (instruktor_id) references Fitnes_Instruktor(user_id_instruktor),
    constraint fk6 foreign key (trening_id_termin) references Trening(trening_id),
    constraint fk7 foreign key (user_id_klient_termin) references Klient(user_id_klient)
    );


create table Ocenka (
    trening_id_o int not null,
    user_id_klient_o int not null,
    vrednost int not null,
    komentar text,
    primary key(trening_id_o, user_id_klient_o),
    constraint fk11 foreign key (trening_id_o) references Trening(trening_id),
    constraint fk12 foreign key (user_id_klient_o) references Klient(user_id_klient)
);

create table Rezervacija (
    rez_id int,
    termin_id int not null,
    instruktor_id int not null,
    trening_id_rez int not null,
    user_id_klient_rez int not null,
    primary key (rez_id, termin_id, instruktor_id, trening_id_rez, user_id_klient_rez),
    constraint fk_termin foreign key (termin_id, instruktor_id, trening_id_rez, user_id_klient_rez) 
        references Termin(termin_id, instruktor_id, trening_id_termin, user_id_klient_termin),
    constraint fk_trening foreign key (trening_id_rez) references Trening(trening_id),
    constraint fk_klient foreign key (user_id_klient_rez) references Klient(user_id_klient)
);