RelationalDesign: kreiranje.sql

create table project.admini(
        id_admin integer primary key,
        ime varchar(50) not null,
        prezime varchar(50) not null,
        email varchar(50) not null,
        password varchar(300) not null
);

create table project.nastavnici(
        id_nastavnik serial primary key,
        ime varchar(50) not null,
        prezime varchar(50) not null,
        opis varchar(500) not null,
        email varchar(50) not null,
        password varchar(300) not null,
        telefonski_broj varchar(15),
        enabled bool,
        id_admin integer not null,
        constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
        on delete cascade on update cascade
);

create table project.kvalifikacii (
        id_kvalifikacija serial primary key,
        dokument text,
        id_nastavnik integer not null,
        id_admin integer not null,
        constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
        on delete cascade on update cascade,
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
        on delete cascade on update cascade
);

create table project.predmeti (
        id_predmet serial primary key,
        ime varchar(100) not null,
        id_admin integer not null,
        constraint fk_admin foreign key(id_admin) references project.admini(id_admin)
        on delete cascade on update cascade
);

create table project.ucenici (
        id_ucenik serial primary key,
        opis varchar(500),
        ime varchar(50) not null,
        prezime varchar(50) not null,
        email varchar(50) not null,
        password varchar(300) not null,
        telefonski_broj varchar(15)
);

create table project.casovi (
        id_cas serial primary key,
        vreme_pocetok timestamp,
        tema varchar(100) not null,
        id_nastavnik integer,
        id_predmet integer,
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
        on delete cascade on update cascade,
        constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
        on delete cascade on update cascade
);

create table project.zainteresiran_za (
        id_predmet integer,
        id_ucenik integer,
        datum date,
        constraint pk_zainteresiran_za primary key (id_ucenik,id_predmet),
        constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
        on delete cascade on update cascade,
        constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
        on delete cascade on update cascade
);

create table project.domasni_zadaci (
        id_domasno serial primary key,
        opis varchar(500),
        id_nastavnik integer not null,
        id_cas integer not null,
        constraint fk_cas foreign key (id_cas) references project.casovi(id_cas)
        on delete cascade on update cascade,
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
        on delete cascade on update cascade
);


create table project.ucenici_domasni (
        id_ucenik integer,
        id_domasno integer,
        dali_zavrsena bool,
        constraint pk_ucenik_domasno primary key (id_ucenik,id_domasno),
        constraint fk_domasno foreign key (id_domasno) references project.domasni_zadaci(id_domasno)
        on delete cascade on update cascade,
        constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
        on delete cascade on update cascade
);

create table project.plakjanja (
        id_plakjanja serial primary key,
        iznos integer not null,
        id_nastavnik integer not null,
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
);

create table project.slusanje (
        id_cas integer,
        id_slusanje integer,
        id_plakjanja integer,
        id_ucenik integer,
        plateno boolean,
        constraint pk_slusanje primary key (id_cas, id_slusanje),
        constraint fk_cas foreign key(id_cas) references project.casovi(id_cas)
        on delete cascade on update cascade,
        constraint fk_plakjanje foreign key (id_plakjanja) references project.plakjanja(id_plakjanja)
        on delete cascade on update cascade,
        constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
  on delete cascade on update cascade
);


create table project.predava_predmet (
        id_nastavnik integer,
        id_predmet integer,
        opis varchar(500) not null,
        
        constraint pk_predava_predmet primary key (id_nastavnik, id_predmet),
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
        on delete cascade on update cascade,
        constraint fk_predmet foreign key (id_predmet) references project.predmeti(id_predmet)
        on delete cascade on update cascade
);

create table project.predava_na (
        id_nastavnik integer,
        id_ucenik integer,
        cena_po_cas integer not null,
        broj_casovi_po_dogovor integer not null,
        rejting float,
        komentar varchar(500),
        hasrated boolean,
        constraint pk_predava_na primary key (id_nastavnik, id_ucenik),
        constraint fk_nastavnik foreign key (id_nastavnik) references project.nastavnici(id_nastavnik)
        on delete cascade on update cascade,
        constraint fk_ucenik foreign key (id_ucenik) references project.ucenici(id_ucenik)
        on delete cascade on update cascade
);

-- brisenje tabeli
drop table project.predava_na ;
drop table project.predava_predmet ;
drop table project.slusanje ;
drop table project.plakjanja ;
drop table project.ucenici_domasni ;
drop table project.zainteresiran_za ;
drop table project.ucenici ;
drop table project.domasni_zadaci ;
drop table project.casovi ;
drop table project.kvalifikacii ;
drop table project.nastavnici ;
drop table project.predmeti ;
drop table project.admini ;