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

drop schema if exists project cascade;
create schema project;

drop table if exists Ocenka;
drop table if exists Rezervacija;
drop table if exists Klient cascade;
drop table if exists Termin;
drop table if exists Trening; 
drop table if exists Fitnes_Instruktor;
drop table if exists Sopstvenik;
drop table if exists Fitnes_Centar cascade;
drop table if exists Korisnici cascade;

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

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

create table Klient (
    user_id_klient int primary key, 
    id_gym int,
    ime varchar(50),
    prezime varchar(50),
    email varchar(100), 
    tel_br varchar(15),
    foreign key (user_id_klient) references Korisnici(user_id),
        foreign key (id_gym) references Fitnes_Centar(id) 
    );

create table Sopstvenik (
    user_id_sopstvenik int primary key,
    id_gym int,
    ime varchar(100) ,
    prezime varchar(100),
    email varchar(150),
    tel_br varchar(15),
    foreign key (user_id_sopstvenik) references Korisnici(user_id),
    foreign key (id_gym) references Fitnes_Centar(id) 
);

create table Fitnes_Instruktor (
    user_id_instruktor int primary key,
    id_gym int,
    ime varchar(100),
    prezime varchar(100),
    kratka_bio text,
    rab_iskustvo int,
    email varchar(150),
    tel_br varchar(15),
    foreign key (user_id_instruktor) references Korisnici(user_id),
        foreign key (id_gym) references Fitnes_Centar(id) 

    );

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

create table Termin (
        termin_id int primary key,
        instruktor_id int,
    trening_id int,
    pocetok time ,
    vremetraenje int,
    datum date,
    foreign key (instruktor_id) references Fitnes_Instruktor(user_id_instruktor),
    foreign key (trening_id) references Trening(trening_id)
    );





create table Rezervacija (
    rez_id int primary key,
    user_id_klient_rez int not null,
    termin_id int not null,
    foreign key (user_id_klient_rez) references Klient(user_id_klient),
    foreign key (termin_id) references Termin(termin_id)
);



create table Ocenka (
        ocenka_id int primary key,
        rez_id int,
    vrednost int,
    komentar text,
    foreign key (rez_id) references Rezervacija(rez_id)
);