
CREATE DATABASE Hotel
GO

USE Hotel
GO

CREATE TABLE Hotel
(
	IDHotel	INT IDENTITY(1,1),
	Naziv	NVARCHAR(25) NOT NULL,
	BrojZvjezdica	INT NOT NULL 
		CONSTRAINT DF_BrojZvjezdica DEFAULT 3
		CONSTRAINT CH_BrojZvjezdica CHECK (BrojZvjezdica>=1 AND BrojZvjezdica<=5),
	Adresa	NVARCHAR(25) NOT NULL,
	Grad	NVARCHAR(25) NOT NULL
	CONSTRAINT PK_Hotel PRIMARY KEY (IDHotel)
)
GO

CREATE TABLE Soba
(
	IDSoba	INT IDENTITY(1,1),
	Oznaka	CHAR(5) NOT NULL,
	Tip		NVARCHAR(25) NOT NULL,
	BrojGostiju INT NOT NULL,	
	HotelID	INT NOT NULL
	CONSTRAINT PK_Soba PRIMARY KEY (IDSoba),
	CONSTRAINT FK_Soba_Hotel FOREIGN KEY (HotelID) REFERENCES Hotel(IDHotel)
)
GO

CREATE TABLE Gost
(
	IDGost	INT IDENTITY(1,1),
	Ime	NVARCHAR(25) NOT NULL,
	Prezime	NVARCHAR(25) NOT NULL,
	OIB CHAR(11) NOT NULL 
		CONSTRAINT UQ_OIB UNIQUE
		CONSTRAINT CH_OIB CHECK (len(OIB)=11),
	CONSTRAINT PK_Gost PRIMARY KEY (IDGost)
)
GO

CREATE TABLE Zauzece
(
	IDZauzece	INT IDENTITY(1,1),
	GostID	INT NOT NULL,
	SobaID	INT NOT NULL,
	DatumOd	DATETIME NOT NULL,
	DatumDo	DATETIME NOT NULL,
	CONSTRAINT PK_Zauzece PRIMARY KEY (IDZauzece),
	CONSTRAINT FK_Zauzece_Gost FOREIGN KEY (GostID) REFERENCES Gost(IDGost),
	CONSTRAINT FK_Zauzece_Soba FOREIGN KEY (SobaID) REFERENCES Soba(IDSoba),
	CONSTRAINT UQ_Zauzece UNIQUE (GostID, SobaID, DatumOd, DatumDo)
)
GO
