----------------------------------------------------------------------
-- Brisanje baze podataka mstud
----------------------------------------------------------------------

drop db mstud;

----------------------------------------------------------------------
-- Pravljenje baze podataka mstud
----------------------------------------------------------------------

create db mstud automatic storage yes  using codeset utf-8 territory sp collate using system pagesize 32768;

----------------------------------------------------------------------
-- Prijavljivanje na bazu podataka mstud
----------------------------------------------------------------------

connect to mstud;

----------------------------------------------------------------------
-- Pravljenje tabele DOSIJE
----------------------------------------------------------------------

create table dosije (

       indeks           integer      not null,
       ime              varchar(25)  not null,
       prezime          varchar(25)  not null,
       datum_upisa      date         not null,
       datum_rodjenja   date                 ,
       mesto_rodjenja   varchar(100)         ,
       primary key      (indeks)
);

----------------------------------------------------------------------
-- Unos podataka u tabelu DOSIJE
----------------------------------------------------------------------

insert into dosije(indeks,ime,prezime,datum_upisa,datum_rodjenja,mesto_rodjenja) values
(20140021, 'Milos'   , 'Peric'      , '06.07.2014', '20.01.1995', 'Beograd' ),
(20140022, 'Marijana', 'Savkovic'   , '05.07.2014', '11.03.1995', 'Kraljevo'),
(20130023, 'Sanja'   , 'Terzic'     , '04.07.2013', '09.11.1994', 'Beograd' ),
(20130024, 'Nikola'  , 'Vukovic'    , '04.07.2013', '17.09.1994',  null     ),
(20140025, 'Marijana', 'Savkovic'   , '06.07.2014', '04.02.1995', 'Kraljevo'),
(20140026, 'Zorica'  , 'Miladinovic', '06.07.2014', '08.10.1995', 'Vranje'  ),
(20130027, 'Milena'  , 'Stankovic'  , '03.09.2013',  null, null     );

----------------------------------------------------------------------
-- Pravljenje tabele PREDMET
----------------------------------------------------------------------

create table predmet (
       id_predmeta     integer      not null,
       sifra           varchar(20)  not null,
       naziv           varchar(200) not null,
       bodovi          smallint     not null,
       primary key(id_predmeta)
);

----------------------------------------------------------------------
-- Unos podataka u tabelu PREDMET
----------------------------------------------------------------------

insert into predmet values
(1001, 'M111', 'Analiza 1', 6)                          ,
(1002, 'M112', 'Analiza 2', 6)                          ,
(1003, 'M113', 'Analiza 3', 6)                          ,
(1021, 'M131', 'Geometrija', 6)                         ,
(1101, 'M105', 'Diskretne strukture 1', 6)              ,
(1102, 'M106', 'Diskretne strukture 2', 6)              ,
(2001, 'P101', 'Programiranje 1', 8)                    ,
(2002, 'P102', 'Programiranje 2', 8)                    ,
(2003, 'P103', 'Objektno orijentisano programiranje', 6),
(2004, 'P104', 'Algoritmi i strukture podataka', 6)     ,
(3001, 'S1'  , 'Engleski jezik 1', 5)                   ,
(3002, 'S2'  , 'Engleski jezik 2', 5)                   ,
(4001, 'R101', 'Uvod u organizaciju racunara', 5)       ,
(4002, 'R102', 'Uvod u Veb i Internet tehnologije', 5)  ;

----------------------------------------------------------------------
-- Pravljenje tabele ISPITNI_ROK
----------------------------------------------------------------------

create table ispitni_rok (
       godina_roka     smallint     not null,
       oznaka_roka     varchar(5)   not null,
       naziv           varchar(50)  not null,
       primary key (godina_roka, oznaka_roka)
);

----------------------------------------------------------------------
-- Unos podataka u tabelu ISPITNI_ROK
----------------------------------------------------------------------

insert into ispitni_rok values
(2015, 'jan', 'Januar 2015')   ,
(2015, 'feb', 'Februar 2015')  ,
(2015, 'apr', 'April 2015')    ,
(2015, 'jun', 'Jun 2015')      ,
(2015, 'sep', 'Septembar 2015'),
(2015, 'okt', 'Oktobar 2015')  ;

----------------------------------------------------------------------
-- Pravljenje tabele ISPIT
----------------------------------------------------------------------

create table ispit (
       indeks          integer      not null                        ,
       id_predmeta     integer      not null                        ,
       godina_roka     smallint     not null                        ,
       oznaka_roka     char(5)      not null                        ,
       ocena           smallint     not null with default 5         ,
       datum_ispita    date                                         ,
       bodovi          smallint                                     ,
       primary key (indeks, id_predmeta, godina_roka, oznaka_roka)  ,
       foreign key (godina_roka, oznaka_roka) references ispitni_rok,
       foreign key (indeks)                   references dosije     ,
       foreign key (id_predmeta)              references predmet)   ;

----------------------------------------------------------------------
-- Unos podataka u tabelu ISPIT
----------------------------------------------------------------------
       
insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena, datum_ispita, bodovi)  values 
(20140021, 1001, 2015, 'jan', 9, '20.01.2015', 81),
(20140022, 1001, 2015, 'jan', 8, '20.01.2015', 75),
(20130023, 1001, 2015, 'jan', 8, '20.01.2015', 76),
(20130024, 1001, 2015, 'jan', 10,'20.01.2015', 100),
(20140025, 1001, 2015, 'jan', 6, '20.01.2015', 55),
(20140026, 1001, 2015, 'jan', 5, '20.01.2015', 25),
(20130027, 1001, 2015, 'jan', 8, '20.01.2015', 76),


(20140021, 2001, 2015, 'jan', 10,'25.01.2015', 98),
(20140022, 2001, 2015, 'jan', 9, '25.01.2015', 86),
(20130023, 2001, 2015, 'jan', 8, '25.01.2015', 72),
(20130024, 2001, 2015, 'jan', 7, '25.01.2015', 63),
(20140025, 2001, 2015, 'jan', 5, '25.01.2015', 0),

(20140021, 3001, 2015, 'jan', 7, '27.01.2015', 67),
(20130023, 3001, 2015, 'jan', 5, '27.01.2015', 30),
(20130024, 3001, 2015, 'jan', 6, '28.01.2015', 59),
(20140026, 3001, 2015, 'jan', 6, '28.01.2015', 52),

(20140026, 1001, 2015, 'feb', 7, '10.02.2015', 68),

(20140025, 2001, 2015, 'feb', 6, '10.02.2015', 55),
(20140026, 2001, 2015, 'feb', 7, '10.02.2015', 65),

(20140021, 1021, 2015, 'apr', 7, '03.04.2015', 63),
(20130023, 1021, 2015, 'apr', 10,'03.04.2015', 95);

insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, datum_ispita)  values 
(20140022, 1021, 2015, 'apr',    '03.04.2015');

insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena)  values 
(20130024, 1021, 2015, 'apr', 6)              ,
(20140026, 1021, 2015, 'jan', 7)              ,
(20140026, 1021, 2015, 'feb', 7)              ,
(20140026, 2001, 2015, 'jan', -7)             ,
(20140026, 1021, 2015, 'apr', 8)              ,
(20130027, 1021, 2015, 'jan', 7)              ,
(20130027, 1021, 2015, 'feb', 7)              ,
(20130027, 1021, 2015, 'apr', 8)              ;
