Логички и физички дизајн - Креирање база податоци (со SQL DDL): kreiranje_ddl.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 unique,
    uloga varchar(20),
    password varchar(100) not null
);

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

create table Klient (
    user_id_klient int primary key, 
    ime varchar(50) not null,
    prezime varchar(50) not null,
    email varchar(100) unique not null, 
    tel_broj varchar(15),
    constraint fk20 foreign key (user_id_klient) references Korisnici(user_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,
    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)
);

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 Tip (
    trening_id_tip int primary key,
    individualen varchar(20) not null,
    grupen varchar(20) not null,
    constraint fk5 foreign key (trening_id_tip) references Trening(trening_id)
);

create table Termin (
    termin_id int,
    trening_id_termin int not null,
    user_id_klient_termin int,
    pocetok timestamp not null,
    vremetraenje int,
    primary key (termin_id, trening_id_termin, user_id_klient_termin),
    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 Rezervacija (
    termin_id int not null,
    trening_id_rez int not null,
    user_id_klient_rez int not null,
    primary key (termin_id, trening_id_rez, user_id_klient_rez),
    constraint fk10 foreign key (termin_id, trening_id_rez, user_id_klient_rez) references Termin(termin_id, trening_id_termin, user_id_klient_termin)
);

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)
);