Index: DB/kreiranje.sql =================================================================== --- DB/kreiranje.sql (revision cd1523675c69f93db1557a3da9401a0d791a45db) +++ DB/kreiranje.sql (revision cd1523675c69f93db1557a3da9401a0d791a45db) @@ -0,0 +1,175 @@ +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, + p_id integer not null, + ka_id integer, + 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( + ptk_id serial primary key, + termin timestamp not null, + p_id integer not null, + constraint fk_prof_term_za_kons_profesori foreign key (p_id) references profesori (p_id) +); + +create table e_vo_vrska_so( + vs_id serial primary key, + 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) +); + +create table moze_da_slusa( + mds_id serial primary key, + 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) +); + +create table e_prof_na( + pn_id serial primary key, + 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) +); + +create table e_oznaceno_so( + os_id serial primary key, + 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) +); + +create table dava_reakcija( + dr_id serial primary key, + 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) +); + +create table reagira_na( + rn_id serial primary key, + 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) +); Index: DB/polnenje.sql =================================================================== --- DB/polnenje.sql (revision cd1523675c69f93db1557a3da9401a0d791a45db) +++ DB/polnenje.sql (revision cd1523675c69f93db1557a3da9401a0d791a45db) @@ -0,0 +1,104 @@ +insert into administratori(a_ime, a_prezime, a_korisnicko_ime, a_email, a_lozinka) +values + ('Lina', 'Nakova', 'admin1', 'lina.nakova@admin.com', 'admin'), + ('Angel', 'Nasev', 'admin2', 'angel.nasev@admin.com', 'admin'), + ('Katerina', 'Smileska', 'admin3', 'katerina.smileska@admin.com', 'admin'); + +insert into studenti(s_ime, s_prezime, s_korisnicko_ime, s_email, s_lozinka, s_index) +values + ('Lina', 'Nakova', 'lina.nakova', 'lina.nakova@students.finki.ukim.mk', 'linan201050', 201050), + ('Angel', 'Nasev', 'angel.nasev', 'angel.nasev@students.finki.ukim.mk', 'angeln201051', 201051), + ('Katerina', 'Smileska', 'katerina.smileska', 'katerina.smileska@students.finki.ukim.mk', 'katerinas201052', 201052); + +insert into profesori(p_ime, p_prezime, p_korisnicko_ime, p_email, p_lozinka) +values + ('Vangel', 'Ajanovski', 'vangel.ajanovski', 'vangel.ajanovski@finki.ukim.mk', 'vangela'), + ('Nenad', 'Ancev', 'nenad.ancev', 'nenad.ancev@finki.ukim.mk', 'nenada'), + ('Sasho', 'Gramatikov', 'sasho.gramatikov', 'sasho.gramatikov@finki.ukim.mk', 'sashog'), + ('Goran', 'Velinov', 'goran.velinov', 'goran.velinov@finki.ukim.mk', 'goranv'); + +insert into predmeti(pr_ime, a_id) +values + ('Bazi na podatoci', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Veb programiranje', (select a.a_id from administratori as a where a.a_korisnicko_ime = 'admin1')), + ('Kompjuterski mrezi i bezbednost', (select a.a_id from administratori as a where a.a_korisnicko_ime = 'admin1')), + ('Kompjuterska etika', (select a.a_id from administratori as a where a.a_korisnicko_ime = 'admin1')); + +insert into kursevi(ku_ime, ku_opis, pr_id) +values + ('Bazi na podatoci - VA', 'Predmet na fakultetot finki', (select p.pr_id from predmeti as p where pr_ime = 'Bazi na podatoci')), + ('Bazi na podatoci - GV',null, (select p.pr_id from predmeti as p where pr_ime = 'Bazi na podatoci')), + ('Veb programiranje','Izucuvanje na tehnologiite na Spring', (select p.pr_id from predmeti as p where pr_ime = 'Veb programiranje')); + +insert into kategorii(ka_ime, ka_opis, a_id) +values + ('Auditoriski vezbi', 'Prasanja povrzani so auditoriskite vezbi', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Predavanja', 'Prasanja povrzani so predavanjata', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Laboratoriski vezbi', 'Prasanja povrzani so laboratoriskite vezbi', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Ispit', 'Prasanja povrzani so ispiti', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Kolokvium', 'Prasanja povrzani so kolokvium', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')), + ('Administracija', 'Prasanja povrzani so administracija', (select a.a_id from administratori as a where a.a_korisnicko_ime= 'admin1')); + +insert into prof_term_za_kons(ptk_id, p_id, termin) +values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'), + '2022-12-12 10:00:00'), + (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'), + '2022-12-15 10:00:00'), + (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'), + '2022-12-10 9:30:00'); + +insert into e_prof_na(pn_id, p_id, ku_id) +values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')), + (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')), + (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV')); + +insert into materijali(m_ime,p_id,ka_id,ku_id) +values + ('Database System Concepts',(select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),(select ka.ka_id from kategorii as ka where ka.ka_ime = 'Predavanja'),(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')), + ('Spring Data Persistence',(select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'),(select ka.ka_id from kategorii as ka where ka.ka_ime = 'Predavanja'),(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')), + ('Mapiracka Transformacija',(select p.p_id from profesori as p where p.p_korisnicko_ime = 'nenad.ancev'),(select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi'),(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')); + +insert into prasanja(pra_naslov,pra_sodrzina,pra_datum,ku_id,s_id) +values + ('Kako da ponistam transakcija','Koristev transakcija za izvrsuvanje nekolku naredbi, no edna od niv bese neuspesna. Sega kako mozam da se vratam na sostojbata pred zapocnuvanjeto na transakcijata?','2022-12-05 20:38:00',(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA'),(select s.s_id from studenti as s where s.s_korisnicko_ime='angel.nasev')), + ('Tip na podatok za datoteki vo PostgreSQL','Sakam vo mojot proekt da dodadam atribut koj ke cuva datoteka. Kako mozam da go napravam toa?','2022-12-03 10:00:00',(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA'),(select s.s_id from studenti as s where s.s_korisnicko_ime='lina.nakova')), + ('Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ','Vo auditoriskite vezbi, profesorot ima kopce na koe moze da gi gleda site tabeli i semata na bazata. Kaj mene toa kopce ne postoi. Sto e problemot? Kako bi go resil?','2022-12-01 12:30:00',(select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje'),(select s.s_id from studenti as s where s.s_korisnicko_ime='katerina.smileska')); + +insert into odgovori(o_sodrzina,o_validen,o_datum,pra_id,s_id,p_id) +values + ('Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot',true,'2022-12-03 17:45:00',(select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),null,(select p.p_id from profesori as p where p.p_korisnicko_ime = 'nenad.ancev')), + ('Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata',false,'2022-12-06 08:05:00',(select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'),(select s.s_id from studenti as s where s.s_korisnicko_ime='lina.nakova'),null), + ('Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail',true,'2022-12-03 15:27:00',(select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'),null,(select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov')); + +insert into e_vo_vrska_so(vs_id, pra_id, m_id) +values (0, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'), (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts')), + (1, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'), (select m.m_id from materijali as m where m.m_ime = 'Spring Data Persistence')), + (2, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'), (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts')); + +insert into moze_da_slusa(mds_id, s_id, ku_id) +values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')), + (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')), + (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')), + (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')), + (4, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV')), + (5, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'), (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')); + +insert into e_oznaceno_so(os_id, pra_id, ka_id) +values (0, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'), (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi')), + (1, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'), (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Laboratoriski vezbi')), + (2, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'), (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Predavanja')), + (3, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'), (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi')); + +insert into dava_reakcija(dr_id, p_id, o_id, reakcija) +values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'), + (select o.o_id from odgovori as o where o.o_sodrzina = 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'), true), + (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'), + (select o.o_id from odgovori as o where o.o_sodrzina = 'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'), true), + (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'), + (select o.o_id from odgovori as o where o.o_sodrzina = 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'), true); + +insert into reagira_na(rn_id, s_id, o_id, reakcija) +values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'), (select o.o_id from odgovori as o where o.o_sodrzina = 'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'), false), + (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'), (select o.o_id from odgovori as o where o.o_sodrzina = 'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'), true), + (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'), (select o.o_id from odgovori as o where o.o_sodrzina = 'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'), true), + (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'), (select o.o_id from odgovori as o where o.o_sodrzina = 'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'), true);