/*zadatak 1
kreiranje seme, tabela i ogranicenja
*/
create schema HotelPlus
go

create table HotelPlus.hotel(
	idhot numeric(8) not null,
	adresa varchar(60),
	naziv varchar(30) not null,
	kat numeric(1) not null,
	brziror varchar(20),
	tel varchar(18),

	constraint ck_tel check(tel>=11),
	constraint pk_hotel primary key(idhot)
);

create table HotelPlus.recepcija(
	idhot numeric(8) not null,
	brrec numeric(2)not null,
	lok numeric(4) not null,
	mestor varchar(60),

	constraint pk_recepcija primary key(idhot, brrec),
	constraint fk_recepcija_hotel foreign key(idhot) references HotelPlus.hotel(idhot)
);

create table HotelPlus.soba(
	idhot numeric(8) not null,
	brsob numeric(8)not null,
	tipsob varchar(25) not null,
	opis varchar(60),
	napom char(80),

	constraint pk_soba primary key(idhot, brsob),
	constraint fk_soba_hotel foreign key(idhot) references HotelPlus.hotel(idhot)
);

create table HotelPlus.radnik(
	jmbg varchar(13) not null,
	ime varchar(20)not null,
	prz varchar(50) not null,
	email varchar(60),
	uloga char(1) not null,

	constraint pk_radnik primary key(jmbg)
);
create table HotelPlus.smena(
	idsme numeric(8) not null,
	nazsme varchar(60)not null,
	vremeod numeric(2) not null,
	vremedo numeric(2) not null,
	napsme char(300) default(' '),

	constraint pk_smena primary key(idsme)
);
create table HotelPlus.radi(
	jmbg varchar(13) not null,
	idsme numeric(8)not null,
	datum date not null,
	idhot numeric(8),
	brrec numeric(2),

	constraint pk_radi primary key(jmbg, idsme),
	constraint fk_radi_radnik foreign key(jmbg) references HotelPlus.radnik(jmbg),
	constraint fk_radi_smena foreign key(idsme) references HotelPlus.smena(idsme),
	constraint fk_radi_recepcja foreign key(idhot, brrec) references HotelPlus.recepcija(idhot, brrec)
);
--------------------------------------------------------------------------------------------------------
/*zadatak 2
prebrojavanje koliko je objekata koje vrste kreirano nakoji dan u nedelji
*/
select datename(dw, create_date) as 'Dan u nedelji', type_desc as 'Tip objekta', COUNT(object_id) as 'Broj objekata te vrste'
from sys.all_objects
where DATEPART(YEAR,create_date)=2018
group by datename(dw, create_date), type_desc
go

--------------------------------------------------------------------------------------------------------
/*zadatak 3
kreiranje sekvence za unos celih brojeva; pocinje sa 1 i uvecava se za 5
na maksimalnoj vrednosti ispisuje poruku da je dostigla max vrednost
dodavanje novog ogranicenja za koriscenje sekvence u tabeli smena
*/
create sequence SEQ_smena_id as int
start with 1
increment by 5
go
alter table HotelPlus.smena add constraint DFT_smena_id default (next value for SEQ_smena_id) for idsme
go
--------------------------------------------------------------------------------------------------------
/*zadatak 4
dodavanje obolezja oznaka_sobe u tabeli soba
*/
alter table HotelPlus.soba
add oznaka_sobe char(1) not null
go
/*obrazlozenje:
biramo tip podatka char jer obelezje oznaka_sobe nece biti cesto menjano
pa nema potrebe za tipom podatka vrchar
*/
--------------------------------------------------------------------------------------------------------
/*zadatak 5
brisanje tabele radi, uz prethocnu proveru postojanja tabele
*/
if OBJECT_ID('HotelPlus.radi','U') is not null
drop table HotelPlus.radi
go