RelationalDesign: kreiranje.sql

create table studenti (
    s_id serial,
    s_ime varchar(20) not null,
    s_prezime varchar(20) not null,
    s_korisnicko_ime varchar(50) not null,
    s_email varchar(100) not null,
    s_lozinka varchar(50) not null,
    s_index varchar(10) not null,
    constraint pk_studenti primary key (s_id)
);

create table administratori (
    a_id serial,
    a_ime varchar(20) not null,
    a_prezime varchar(20) not null,
    a_korisnicko_ime varchar(50) not null,
    a_email varchar(100) not null,
    a_lozinka varchar(50) not null,
    constraint pk_administratori primary key (a_id)
);

create table profesori (
    p_id serial,
    p_ime varchar(20) not null,
    p_prezime varchar(20) not null,
    p_korisnicko_ime varchar(50) not null,
    p_email varchar(100) not null,
    p_lozinka varchar(50) not null,
    constraint pk_profesori primary key (p_id)
);

create table predmeti (
        pr_id serial,
        pr_ime varchar(200) not null,
        a_id integer not null,
        constraint pk_predmeti primary key (pr_id),
        constraint fk_predmeti_administratori foreign key (a_id)
        references administratori (a_id)
);

create table kursevi(
        ku_id serial,
        ku_ime varchar(200) not null,
        ku_opis varchar(500),
        pr_id integer not null,
        constraint pk_kursevi primary key (ku_id),
        constraint fk_kursevi_predmeti foreign key (pr_id)
        references predmeti (pr_id)
);

create table kategorii(
        ka_id serial,
        ka_ime varchar(40) not null,
        ka_opis varchar(150),
        a_id integer not null,
        constraint pk_kategorii primary key (ka_id),
        constraint fk_kategorii_administratori foreign key (a_id)
        references administratori (a_id)
);

create table materijali(
        m_id serial,
        m_ime varchar(150) not null,
        m_bytes text,
        p_id integer not null,
        ka_id integer not null,
        ku_id integer not null,
        constraint pk_materijali primary key (m_id),
        constraint fk_materijali_profesori foreign key (p_id)
        references profesori (p_id),
        constraint fk_materijali_kategorii foreign key (ka_id)
        references kategorii (ka_id),
        constraint fk_materijali_kursevi foreign key (ku_id)
        references kursevi (ku_id)
);

create table prasanja(
        pra_id serial,
        pra_naslov varchar(100) not null,
        pra_sodrzina text not null,
        pra_datum timestamp default now(),
        ku_id integer not null,
        s_id integer not null,
        constraint pk_prasanja primary key (pra_id),
        constraint fk_prasanja_kursevi foreign key (ku_id)
        references kursevi (ku_id),
        constraint fk_prasanja_studenti foreign key (s_id)
        references studenti (s_id)
);

create table odgovori(
        o_id serial,
        o_sodrzina text not null ,
        o_validen boolean default false,
        o_datum timestamp default now(),
        pra_id integer not null,
        s_id integer ,
        p_id integer,
        constraint pk_odgovori primary key (o_id),
        constraint fk_odgovori_prasanja foreign key (pra_id)
        references prasanja (pra_id),
        constraint fk_odgovori_studenti foreign key (s_id)
        references studenti (s_id),
        constraint fk_odgovori_profesori foreign key (p_id)
        references profesori (p_id),
        constraint chk_not_null_avtor check (s_id is not null or p_id is not null)
);

create table prof_term_za_kons(
        p_id integer not null,
        termin timestamp not null,
        constraint pk_prof_term_za_kons primary key (p_id, termin),
        constraint fk_prof_term_za_kons_profesori foreign key (p_id)
        references profesori (p_id)
);

create table e_vo_vrska_so(
        pra_id integer not null,
        m_id integer not null,
        constraint fk_e_vp_vrska_so_prasanja foreign key (pra_id)
        references prasanja (pra_id),
        constraint fk_e_vp_vrska_so_materijali foreign key (m_id)
        references materijali (m_id),
        constraint pk_e_vo_vrska_so primary key (pra_id, m_id)
);

create table moze_da_slusa(
        s_id integer not null,
        ku_id integer not null,
        constraint fk_moze_da_slusa_studenti foreign key (s_id)
        references studenti (s_id),
        constraint fk_moze_da_slusa_kursevi foreign key (ku_id)
        references kursevi (ku_id),
        constraint pk_moze_da_slusa primary key (s_id, ku_id)
);

create table e_prof_na(
        p_id integer not null,
        ku_id integer not null,
        constraint fk_e_prof_na_profesori foreign key (p_id)
        references profesori (p_id),
        constraint fk_e_prof_na_kursevi foreign key (ku_id)
        references kursevi (ku_id),
        constraint pk_e_prof_na primary key (p_id, ku_id)
);

create table e_oznaceno_so(
        pra_id integer not null,
        ka_id integer not null,
        constraint fk_e_oznaceno_so_prasanja foreign key (pra_id)
        references prasanja (pra_id),
        constraint fk_e_oznaceno_so_kategorii foreign key (ka_id)
        references kategorii (ka_id),
        constraint pk_e_oznacneo_so primary key (pra_id, ka_id)
);

create table dava_reakcija(
        p_id integer not null,
        o_id integer not null,
        reakcija boolean not null,
        constraint fk_dava_reakcija_profesori foreign key (p_id)
        references profesori (p_id),
        constraint fk_dava_reakcija_odgovori foreign key (o_id)
        references odgovori (o_id),
        constraint pk_dava_reakcija primary key (p_id, o_id)
);

create table reagira_na(
        s_id integer not null,
        o_id integer not null,
        reakcija boolean not null,
        constraint fk_reagira_na_studenti foreign key (s_id)
        references studenti (s_id),
        constraint fk_reagira_na_odgovori foreign key (o_id)
        references odgovori (o_id),
        constraint pk_reagira_na primary key (s_id, o_id)
);