| 1 | drop table prof_term_za_kons;
|
|---|
| 2 | create table prof_term_za_kons
|
|---|
| 3 | (
|
|---|
| 4 | ptk_id serial primary key,
|
|---|
| 5 | termin timestamp not null,
|
|---|
| 6 | p_id integer not null,
|
|---|
| 7 | CONSTRAINT fk_prof_term_za_kons_profesori FOREIGN KEY (p_id) REFERENCES profesori (p_id)
|
|---|
| 8 | );
|
|---|
| 9 |
|
|---|
| 10 | insert into prof_term_za_kons(ptk_id, p_id, termin)
|
|---|
| 11 | values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
|
|---|
| 12 | '2022-12-12 10:00:00'),
|
|---|
| 13 | (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
|
|---|
| 14 | '2022-12-15 10:00:00'),
|
|---|
| 15 | (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'),
|
|---|
| 16 | '2022-12-10 9:30:00');
|
|---|
| 17 |
|
|---|
| 18 | drop table dava_reakcija cascade;
|
|---|
| 19 | create table dava_reakcija
|
|---|
| 20 | (
|
|---|
| 21 | dr_id serial primary key,
|
|---|
| 22 | p_id integer not null,
|
|---|
| 23 | o_id integer not null,
|
|---|
| 24 | reakcija boolean not null,
|
|---|
| 25 | constraint fk_dava_reakcija_profesori foreign key (p_id)
|
|---|
| 26 | references profesori (p_id),
|
|---|
| 27 | constraint fk_dava_reakcija_odgovori foreign key (o_id)
|
|---|
| 28 | references odgovori (o_id)
|
|---|
| 29 | );
|
|---|
| 30 |
|
|---|
| 31 | insert into dava_reakcija(dr_id, p_id, o_id, reakcija)
|
|---|
| 32 | values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
|
|---|
| 33 | (select o.o_id
|
|---|
| 34 | from odgovori as o
|
|---|
| 35 | where o.o_sodrzina =
|
|---|
| 36 | 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
|
|---|
| 37 | true),
|
|---|
| 38 | (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
|
|---|
| 39 | (select o.o_id
|
|---|
| 40 | from odgovori as o
|
|---|
| 41 | where o.o_sodrzina =
|
|---|
| 42 | 'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'),
|
|---|
| 43 | true),
|
|---|
| 44 | (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'),
|
|---|
| 45 | (select o.o_id
|
|---|
| 46 | from odgovori as o
|
|---|
| 47 | where o.o_sodrzina =
|
|---|
| 48 | 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
|
|---|
| 49 | true);
|
|---|
| 50 |
|
|---|
| 51 | drop table e_oznaceno_so;
|
|---|
| 52 | create table e_oznaceno_so
|
|---|
| 53 | (
|
|---|
| 54 | os_id serial primary key,
|
|---|
| 55 | pra_id integer not null,
|
|---|
| 56 | ka_id integer not null,
|
|---|
| 57 | constraint fk_e_oznaceno_so_prasanja foreign key (pra_id)
|
|---|
| 58 | references prasanja (pra_id),
|
|---|
| 59 | constraint fk_e_oznaceno_so_kategorii foreign key (ka_id)
|
|---|
| 60 | references kategorii (ka_id)
|
|---|
| 61 | );
|
|---|
| 62 |
|
|---|
| 63 | insert into e_oznaceno_so(os_id, pra_id, ka_id)
|
|---|
| 64 | values (0, (select pra.pra_id
|
|---|
| 65 | from prasanja as pra
|
|---|
| 66 | where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
|
|---|
| 67 | (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi')),
|
|---|
| 68 | (1, (select pra.pra_id
|
|---|
| 69 | from prasanja as pra
|
|---|
| 70 | where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
|
|---|
| 71 | (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Laboratoriski vezbi')),
|
|---|
| 72 | (2, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'),
|
|---|
| 73 | (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Predavanja')),
|
|---|
| 74 | (3, (select pra.pra_id
|
|---|
| 75 | from prasanja as pra
|
|---|
| 76 | where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'),
|
|---|
| 77 | (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi'));
|
|---|
| 78 |
|
|---|
| 79 |
|
|---|
| 80 | drop table e_prof_na;
|
|---|
| 81 | create table e_prof_na
|
|---|
| 82 | (
|
|---|
| 83 | pn_id serial primary key,
|
|---|
| 84 | p_id integer not null,
|
|---|
| 85 | ku_id integer not null,
|
|---|
| 86 | constraint fk_e_prof_na_profesori foreign key (p_id)
|
|---|
| 87 | references profesori (p_id),
|
|---|
| 88 | constraint fk_e_prof_na_kursevi foreign key (ku_id)
|
|---|
| 89 | references kursevi (ku_id)
|
|---|
| 90 | );
|
|---|
| 91 |
|
|---|
| 92 | insert into e_prof_na(pn_id, p_id, ku_id)
|
|---|
| 93 | values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
|
|---|
| 94 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
|
|---|
| 95 | (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'),
|
|---|
| 96 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
|
|---|
| 97 | (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'),
|
|---|
| 98 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV'));
|
|---|
| 99 |
|
|---|
| 100 | drop table e_vo_vrska_so;
|
|---|
| 101 | create table e_vo_vrska_so
|
|---|
| 102 | (
|
|---|
| 103 | vs_id serial primary key,
|
|---|
| 104 | pra_id integer not null,
|
|---|
| 105 | m_id integer not null,
|
|---|
| 106 | constraint fk_e_vp_vrska_so_prasanja foreign key (pra_id)
|
|---|
| 107 | references prasanja (pra_id),
|
|---|
| 108 | constraint fk_e_vp_vrska_so_materijali foreign key (m_id)
|
|---|
| 109 | references materijali (m_id)
|
|---|
| 110 | );
|
|---|
| 111 |
|
|---|
| 112 | insert into e_vo_vrska_so(vs_id, pra_id, m_id)
|
|---|
| 113 | values (0, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'),
|
|---|
| 114 | (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts')),
|
|---|
| 115 | (1, (select pra.pra_id
|
|---|
| 116 | from prasanja as pra
|
|---|
| 117 | where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'),
|
|---|
| 118 | (select m.m_id from materijali as m where m.m_ime = 'Spring Data Persistence')),
|
|---|
| 119 | (2, (select pra.pra_id
|
|---|
| 120 | from prasanja as pra
|
|---|
| 121 | where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
|
|---|
| 122 | (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts'));
|
|---|
| 123 |
|
|---|
| 124 | drop table moze_da_slusa;
|
|---|
| 125 | create table moze_da_slusa
|
|---|
| 126 | (
|
|---|
| 127 | mds_id serial primary key,
|
|---|
| 128 | s_id integer not null,
|
|---|
| 129 | ku_id integer not null,
|
|---|
| 130 | constraint fk_moze_da_slusa_studenti foreign key (s_id)
|
|---|
| 131 | references studenti (s_id),
|
|---|
| 132 | constraint fk_moze_da_slusa_kursevi foreign key (ku_id)
|
|---|
| 133 | references kursevi (ku_id)
|
|---|
| 134 | );
|
|---|
| 135 |
|
|---|
| 136 | insert into moze_da_slusa(mds_id, s_id, ku_id)
|
|---|
| 137 | values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
|
|---|
| 138 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
|
|---|
| 139 | (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
|
|---|
| 140 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
|
|---|
| 141 | (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
|
|---|
| 142 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
|
|---|
| 143 | (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
|
|---|
| 144 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
|
|---|
| 145 | (4, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
|
|---|
| 146 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV')),
|
|---|
| 147 | (5, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
|
|---|
| 148 | (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje'));
|
|---|
| 149 |
|
|---|
| 150 | drop table reagira_na;
|
|---|
| 151 | create table reagira_na
|
|---|
| 152 | (
|
|---|
| 153 | rn_id serial primary key,
|
|---|
| 154 | s_id integer not null,
|
|---|
| 155 | o_id integer not null,
|
|---|
| 156 | reakcija boolean not null,
|
|---|
| 157 | constraint fk_reagira_na_studenti foreign key (s_id)
|
|---|
| 158 | references studenti (s_id),
|
|---|
| 159 | constraint fk_reagira_na_odgovori foreign key (o_id)
|
|---|
| 160 | references odgovori (o_id)
|
|---|
| 161 | );
|
|---|
| 162 |
|
|---|
| 163 | insert into reagira_na(rn_id, s_id, o_id, reakcija)
|
|---|
| 164 | values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
|
|---|
| 165 | (select o.o_id
|
|---|
| 166 | from odgovori as o
|
|---|
| 167 | where o.o_sodrzina =
|
|---|
| 168 | 'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'),
|
|---|
| 169 | false),
|
|---|
| 170 | (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
|
|---|
| 171 | (select o.o_id
|
|---|
| 172 | from odgovori as o
|
|---|
| 173 | where o.o_sodrzina =
|
|---|
| 174 | 'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'),
|
|---|
| 175 | true),
|
|---|
| 176 | (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
|
|---|
| 177 | (select o.o_id
|
|---|
| 178 | from odgovori as o
|
|---|
| 179 | where o.o_sodrzina =
|
|---|
| 180 | 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
|
|---|
| 181 | true),
|
|---|
| 182 | (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
|
|---|
| 183 | (select o.o_id
|
|---|
| 184 | from odgovori as o
|
|---|
| 185 | where o.o_sodrzina =
|
|---|
| 186 | 'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'),
|
|---|
| 187 | true);
|
|---|
| 188 |
|
|---|
| 189 | drop table materijali cascade;
|
|---|
| 190 | create table materijali
|
|---|
| 191 | (
|
|---|
| 192 | m_id serial,
|
|---|
| 193 | m_ime varchar(150) not null,
|
|---|
| 194 | p_id integer not null,
|
|---|
| 195 | ka_id integer,
|
|---|
| 196 | ku_id integer not null,
|
|---|
| 197 | constraint pk_materijali primary key (m_id),
|
|---|
| 198 | constraint fk_materijali_profesori foreign key (p_id)
|
|---|
| 199 | references profesori (p_id),
|
|---|
| 200 | constraint fk_materijali_kategorii foreign key (ka_id)
|
|---|
| 201 | references kategorii (ka_id),
|
|---|
| 202 | constraint fk_materijali_kursevi foreign key (ku_id)
|
|---|
| 203 | references kursevi (ku_id)
|
|---|
| 204 | );
|
|---|
| 205 |
|
|---|
