connect to vstud;
-----------------------------------------------------------------
-- NIVO_KVALIFIKACIJE
-----------------------------------------------------------------


create table nivo_kvalifikacije (
       id_nivoa               smallint     not null,       
       naziv            varchar(200) not null,
       stepen           varchar(10)  not null,
       primary          key(id_nivoa)
                                );


-----------------------------------------------------------------
-- SMER
-----------------------------------------------------------------


create table smer (
       id_smera         integer      not null,
       oznaka           varchar(10)  not null,
       naziv            varchar(200) not null,
       semestara        smallint     not null with default 6
             constraint chk_semestara check(
                  semestara between 1 and 10),       
       bodovi           smallint     not null with default 180
             constraint chk_bodovi check( 
                 bodovi between 30 and 300  ),
       id_nivoa         smallint     not null,
       zvanje           varchar(200) not null with default,
       opis             long varchar         ,
       primary          key(id_smera)        ,
       foreign          key fk_smer_nivo(id_nivoa)
             references nivo_kvalifikacije,
             constraint uk_sifra unique(oznaka)       
             
                  );



-----------------------------------------------------------------
-- PREDMET
-----------------------------------------------------------------


create table predmet (
       id_predmeta      integer      not null,
       sifra            varchar(20)  not null,
       naziv            varchar(200) not null,
       broj_semestara          smallint     not null with default 1
             constraint chk_broj_semestara check( 
                    broj_semestara between 1 and 10),
       bodovi           smallint not null 
             constraint chk_bodovi check( 
                     bodovi between 1 and 50),
       primary          key(id_predmeta)     ,
             constraint uk_sifra unique(sifra)       
                     );



-----------------------------------------------------------------
-- USLOVNI_PREDMET
-----------------------------------------------------------------

create table uslovni_predmet (
       id_predmeta      integer      not null,
       id_uslovnog      integer      not null,
       primary          key(id_predmeta,id_uslovnog),
       foreign          key fk_upr_predmet(id_predmeta)
             references predmet,              
       foreign          key fk_upr_uslovni(id_uslovnog)
             references predmet
                             );

create index upr_pup on uslovni_predmet(id_uslovnog,id_predmeta);



-----------------------------------------------------------------
-- OBAVEZAN_PREDMET
-----------------------------------------------------------------


create table obavezan_predmet (
       id_smera         integer      not null,
       id_predmeta      integer      not null,
       semestar         integer      not null with default 1
             constraint chk_semestar check( 
                  semestar between 1 and 10 ),              
       primary          key(id_smera,id_predmeta),
       foreign          key fk_obp_smer(id_smera) 
             references smer                     ,
       foreign          key fk_obp_predmet(id_predmeta)
             references predmet
                              );


