CREATE DATABASE Hotel
GO
USE Hotel
GO

CREATE TABLE Hotel(
	IDHotel int constraint PK_Hotel primary key identity,
	Naziv nvarchar(25) not null,
	BrojZvjezdica int constraint CH_BrojZvjezdica check(BrojZvjezdica >= 1 AND BrojZvjezdica <= 5)
	constraint DH_BrojZvjezdica default(3) not null,
	Adresa nvarchar(25) not null,
	Grad nvarchar(25) not null
)

CREATE TABLE Soba(
	IDSoba int constraint PK_IDSoba primary key identity,
	Oznaka char(5) not null,
	Tip nvarchar(25) not null,
	BrojGostiju int not null,
	HotelID int foreign key references Hotel(IDHotel)
)


CREATE TABLE Gost(
	IDGost int constraint PK_IDGost primary key identity,
	Ime nvarchar(25) not null,
	Prezime nvarchar(25) not null,
	OIB char(11) constraint CH_OIB check(LEN(OIB) = 11) not null
)

CREATE TABLE Zauzece(
	IDZauzece int constraint PK_IDZauzece primary key identity,
	GostID int foreign key references Gost(IDGost),
	SobaID int foreign key references Soba(IDSoba),
	DatumOd datetime not null,
	DatumDo datetime not null
)