create table Gradiliste (
	IDG int PRIMARY KEY,
	Naziv VARCHAR(500) NOT NULL UNIQUE
);

create table Zaposleni (
	IDZ int PRIMARY KEY,
	Ime VARCHAR(100) NOT NULL,
	JMBG CHAR(13) NOT NULL UNIQUE,
	IDG int references Gradiliste(IDG)
);

create table Magacin(
	IDM int PRIMARY KEY,
	RdBr int NOT NULL UNIQUE check(RdBr= (select max(Magacin.RdBr) + 1 from Magacin)),
	IDG int references Gradiliste(IDG),
	Kapacitet int NOT NULL

);

create table Roba (
	IDR int PRIMARY KEY,
	Naziv VARCHAR(200) NOT NULL UNIQUE
);

create table Oprema(
	IDR int references Roba(IDR),
	RokTrajanja int NOT NULL
);

create table PotrosniMaterijal(
	IDR int references Roba(IDR),
	IDM int references Magacin(IDM),
	Kolicina int NOT NULL
);

create table ZaduzioRazduzio(
	IDZR int PRIMARY KEY,
	IDZ int references Zaposleni(IDZ),
	IDR int references Roba(IDR),
	IDM int references Magacin(IDM),
	DatumZaduzenja int NOT NULL,
	DatumRazduzenja int
);


alter table Zaposleni
add constraint Ogranicenje_Zaposleni
check(
	not exists(
		select zr.IDZR from Zaposleni z, ZaduzioRazduzio zr, Roba r, Oprema o
		where z.IDZ=zr.IDZ and zr.IDR=r.IDR and o.IDR=r.IDR
	)
);

alter table ZaduzioRazduzio
add constraint Ogranicenje_ZaduzioRazduzio
check(
	not exists(
		select zr.IDZR from Zaposleni z, ZaduzioRazduzio zr, Roba r, Oprema o
		where z.IDZ=zr.IDZ and zr.IDR=r.IDR and o.IDR=r.IDR
	)
);

alter table Roba
add constraint Ogranicenje_Roba
check(
	not exists(
		select zr.IDZR from Zaposleni z, ZaduzioRazduzio zr, Roba r, Oprema o
		where z.IDZ=zr.IDZ and zr.IDR=r.IDR and o.IDR=r.IDR
	)
);

alter table Oprema
add constraint Ogranicenje_Oprema
check(
	not exists(
		select zr.IDZR from Zaposleni z, ZaduzioRazduzio zr, Roba r, Oprema o
		where z.IDZ=zr.IDZ and zr.IDR=r.IDR and o.IDR=r.IDR
	)
);

alter table ZaduzioRazduzio
add constraint Ogranicenje1_ZaduzioRazduzio
check(
	not exists(
		select zr.IDZR from Zaposleni z, ZaduzioRazduzio zr, Magacin m, Gradiliste g
		where z.IDZ=zr.IDZ and zr.IDM=m.IDM and g.IDG =m.IDG
	)
);