create table org_jedinica (
SifOrgJed number(7,0),
Naziv varchar2(20),
DatumOsnivanja date,
BrojZaposlenih NUMBER(7,0),
constraint oj_pk primary key (SifOrgJed));

create table zaposleni1(
SifraZap number(7,0),
Ime varchar2(20),
Prezime varchar2(20),
DatumRodj date,
DatumZaposlenja date,
SifOrgJed number(7,0),
constraint zap_pk primary key (SifraZap),
constraint zap_fk foreign key (SifOrgJed) references org_jedinica(SifOrgJed));

create table plan_odsustva(
SifraPlana number(7,0),
NazivPlana varchar2(20),
constraint po_pk primary key (SifraPlana),
constraint po_check check(NazivPlana in ('Plan za 2016', 'Plan za 2017')));

create table osnov_odsustva(
SifraOsnova number(7,0),
NazivOsnova varchar2(20),
constraint oo_pk primary key (SifraOsnova),
constraint oo_check check (NazivOsnova in('Trudnicko','Porodiljsko','Usavrsavanje')));


create table odsustvo(
SifraPlana number(7,0),
SifraZap number(7,0),
BrojOdsustva number(7,0),
DatumOtvaranja date,
DatumZatvaranja date,
Status varchar2(20),
SifraOsnova number(7,0),
constraint od_pk primary key (SifraPlana,SifraZap),
constraint od_check check (Status in('Planirano','Realizovano')),
constraint od_fk foreign key (SifraOsnova) references osnov_odsustva (SifraOsnova));

insert into org_jedinica values(111,'Finansije','15-MAR-2001',20);
insert into org_jedinica values(222,'Prodaja','15-MAR-2000',100);
insert into org_jedinica values(333,'IT','15-MAR-2009',32);

insert into zaposleni1 values(10,'Marko','Filipovic','29-AUG-65','25-MAR-89',111);
insert into zaposleni1 values(11,'Goran','Filipovic','29-AUG-85','25-MAR-89',222);
insert into zaposleni1 values(12,'Zeljana','Filipovic','29-AUG-91','25-MAR-89',333);


insert into plan_odsustva values(20,'Plan za 2016');

insert into plan_odsustva values(21,'Plan za 2017');


insert into osnov_odsustva values(30,'Trudnicko');
insert into osnov_odsustva values(31,'Porodiljsko');
insert into osnov_odsustva values(32,'Usavrsavanje');

insert into odsustvo values(20,10,4,'22-DEC-2010','15-APR-2011','Realizovano',32);
insert into odsustvo values(21,11,2,'22-DEC-2014','15-APR-2015','Realizovano',32);
insert into odsustvo values(20,12,4,'22-DEC-2018','15-APR-2019','Planirano',31);