[cd15236] | 1 | create table studenti (
|
---|
| 2 | s_id serial,
|
---|
| 3 | s_ime varchar(20) not null,
|
---|
| 4 | s_prezime varchar(20) not null,
|
---|
| 5 | s_korisnicko_ime varchar(50) not null,
|
---|
| 6 | s_email varchar(100) not null,
|
---|
| 7 | s_lozinka varchar(50) not null,
|
---|
| 8 | s_index varchar(10) not null,
|
---|
| 9 | constraint pk_studenti primary key (s_id)
|
---|
| 10 | );
|
---|
| 11 |
|
---|
| 12 | create table administratori (
|
---|
| 13 | a_id serial,
|
---|
| 14 | a_ime varchar(20) not null,
|
---|
| 15 | a_prezime varchar(20) not null,
|
---|
| 16 | a_korisnicko_ime varchar(50) not null,
|
---|
| 17 | a_email varchar(100) not null,
|
---|
| 18 | a_lozinka varchar(50) not null,
|
---|
| 19 | constraint pk_administratori primary key (a_id)
|
---|
| 20 | );
|
---|
| 21 |
|
---|
| 22 | create table profesori (
|
---|
| 23 | p_id serial,
|
---|
| 24 | p_ime varchar(20) not null,
|
---|
| 25 | p_prezime varchar(20) not null,
|
---|
| 26 | p_korisnicko_ime varchar(50) not null,
|
---|
| 27 | p_email varchar(100) not null,
|
---|
| 28 | p_lozinka varchar(50) not null,
|
---|
| 29 | constraint pk_profesori primary key (p_id)
|
---|
| 30 | );
|
---|
| 31 |
|
---|
| 32 | create table predmeti (
|
---|
| 33 | pr_id serial,
|
---|
| 34 | pr_ime varchar(200) not null,
|
---|
| 35 | a_id integer not null,
|
---|
| 36 | constraint pk_predmeti primary key (pr_id),
|
---|
| 37 | constraint fk_predmeti_administratori foreign key (a_id)
|
---|
| 38 | references administratori (a_id)
|
---|
| 39 | );
|
---|
| 40 |
|
---|
| 41 | create table kursevi(
|
---|
| 42 | ku_id serial,
|
---|
| 43 | ku_ime varchar(200) not null,
|
---|
| 44 | ku_opis varchar(500),
|
---|
| 45 | pr_id integer not null,
|
---|
| 46 | constraint pk_kursevi primary key (ku_id),
|
---|
| 47 | constraint fk_kursevi_predmeti foreign key (pr_id)
|
---|
| 48 | references predmeti (pr_id)
|
---|
| 49 | );
|
---|
| 50 |
|
---|
| 51 | create table kategorii(
|
---|
| 52 | ka_id serial,
|
---|
| 53 | ka_ime varchar(40) not null,
|
---|
| 54 | ka_opis varchar(150),
|
---|
| 55 | a_id integer not null,
|
---|
| 56 | constraint pk_kategorii primary key (ka_id),
|
---|
| 57 | constraint fk_kategorii_administratori foreign key (a_id)
|
---|
| 58 | references administratori (a_id)
|
---|
| 59 | );
|
---|
| 60 |
|
---|
| 61 | create table materijali(
|
---|
| 62 | m_id serial,
|
---|
| 63 | m_ime varchar(150) not null,
|
---|
| 64 | p_id integer not null,
|
---|
| 65 | ka_id integer,
|
---|
| 66 | ku_id integer not null,
|
---|
| 67 | constraint pk_materijali primary key (m_id),
|
---|
| 68 | constraint fk_materijali_profesori foreign key (p_id)
|
---|
| 69 | references profesori (p_id),
|
---|
| 70 | constraint fk_materijali_kategorii foreign key (ka_id)
|
---|
| 71 | references kategorii (ka_id),
|
---|
| 72 | constraint fk_materijali_kursevi foreign key (ku_id)
|
---|
| 73 | references kursevi (ku_id)
|
---|
| 74 | );
|
---|
| 75 |
|
---|
| 76 | create table prasanja(
|
---|
| 77 | pra_id serial,
|
---|
| 78 | pra_naslov varchar(100) not null,
|
---|
| 79 | pra_sodrzina text not null,
|
---|
| 80 | pra_datum timestamp default now(),
|
---|
| 81 | ku_id integer not null,
|
---|
| 82 | s_id integer not null,
|
---|
| 83 | constraint pk_prasanja primary key (pra_id),
|
---|
| 84 | constraint fk_prasanja_kursevi foreign key (ku_id)
|
---|
| 85 | references kursevi (ku_id),
|
---|
| 86 | constraint fk_prasanja_studenti foreign key (s_id)
|
---|
| 87 | references studenti (s_id)
|
---|
| 88 | );
|
---|
| 89 |
|
---|
| 90 | create table odgovori(
|
---|
| 91 | o_id serial,
|
---|
| 92 | o_sodrzina text not null ,
|
---|
| 93 | o_validen boolean default false,
|
---|
| 94 | o_datum timestamp default now(),
|
---|
| 95 | pra_id integer not null,
|
---|
| 96 | s_id integer ,
|
---|
| 97 | p_id integer,
|
---|
| 98 | constraint pk_odgovori primary key (o_id),
|
---|
| 99 | constraint fk_odgovori_prasanja foreign key (pra_id)
|
---|
| 100 | references prasanja (pra_id),
|
---|
| 101 | constraint fk_odgovori_studenti foreign key (s_id)
|
---|
| 102 | references studenti (s_id),
|
---|
| 103 | constraint fk_odgovori_profesori foreign key (p_id)
|
---|
| 104 | references profesori (p_id),
|
---|
| 105 | constraint chk_not_null_avtor check (s_id is not null or p_id is not null)
|
---|
| 106 | );
|
---|
| 107 |
|
---|
| 108 | create table prof_term_za_kons(
|
---|
| 109 | ptk_id serial primary key,
|
---|
| 110 | termin timestamp not null,
|
---|
| 111 | p_id integer not null,
|
---|
| 112 | constraint fk_prof_term_za_kons_profesori foreign key (p_id) references profesori (p_id)
|
---|
| 113 | );
|
---|
| 114 |
|
---|
| 115 | create table e_vo_vrska_so(
|
---|
| 116 | vs_id serial primary key,
|
---|
| 117 | pra_id integer not null,
|
---|
| 118 | m_id integer not null,
|
---|
| 119 | constraint fk_e_vp_vrska_so_prasanja foreign key (pra_id)
|
---|
| 120 | references prasanja (pra_id),
|
---|
| 121 | constraint fk_e_vp_vrska_so_materijali foreign key (m_id)
|
---|
| 122 | references materijali (m_id)
|
---|
| 123 | );
|
---|
| 124 |
|
---|
| 125 | create table moze_da_slusa(
|
---|
| 126 | mds_id serial primary key,
|
---|
| 127 | s_id integer not null,
|
---|
| 128 | ku_id integer not null,
|
---|
| 129 | constraint fk_moze_da_slusa_studenti foreign key (s_id)
|
---|
| 130 | references studenti (s_id),
|
---|
| 131 | constraint fk_moze_da_slusa_kursevi foreign key (ku_id)
|
---|
| 132 | references kursevi (ku_id)
|
---|
| 133 | );
|
---|
| 134 |
|
---|
| 135 | create table e_prof_na(
|
---|
| 136 | pn_id serial primary key,
|
---|
| 137 | p_id integer not null,
|
---|
| 138 | ku_id integer not null,
|
---|
| 139 | constraint fk_e_prof_na_profesori foreign key (p_id)
|
---|
| 140 | references profesori (p_id),
|
---|
| 141 | constraint fk_e_prof_na_kursevi foreign key (ku_id)
|
---|
| 142 | references kursevi (ku_id)
|
---|
| 143 | );
|
---|
| 144 |
|
---|
| 145 | create table e_oznaceno_so(
|
---|
| 146 | os_id serial primary key,
|
---|
| 147 | pra_id integer not null,
|
---|
| 148 | ka_id integer not null,
|
---|
| 149 | constraint fk_e_oznaceno_so_prasanja foreign key (pra_id)
|
---|
| 150 | references prasanja (pra_id),
|
---|
| 151 | constraint fk_e_oznaceno_so_kategorii foreign key (ka_id)
|
---|
| 152 | references kategorii (ka_id)
|
---|
| 153 | );
|
---|
| 154 |
|
---|
| 155 | create table dava_reakcija(
|
---|
| 156 | dr_id serial primary key,
|
---|
| 157 | p_id integer not null,
|
---|
| 158 | o_id integer not null,
|
---|
| 159 | reakcija boolean not null,
|
---|
| 160 | constraint fk_dava_reakcija_profesori foreign key (p_id)
|
---|
| 161 | references profesori (p_id),
|
---|
| 162 | constraint fk_dava_reakcija_odgovori foreign key (o_id)
|
---|
| 163 | references odgovori (o_id)
|
---|
| 164 | );
|
---|
| 165 |
|
---|
| 166 | create table reagira_na(
|
---|
| 167 | rn_id serial primary key,
|
---|
| 168 | s_id integer not null,
|
---|
| 169 | o_id integer not null,
|
---|
| 170 | reakcija boolean not null,
|
---|
| 171 | constraint fk_reagira_na_studenti foreign key (s_id)
|
---|
| 172 | references studenti (s_id),
|
---|
| 173 | constraint fk_reagira_na_odgovori foreign key (o_id)
|
---|
| 174 | references odgovori (o_id)
|
---|
| 175 | );
|
---|