CREATE TABLE City
(
 CityName VARCHAR(20) NOT NULL,
 CONSTRAINT City_pk PRIMARY KEY (CityName)
)
/

CREATE TABLE Hotel
(
 Name VARCHAR(20) NOT NULL,
 Address VARCHAR(50),
 Telephone VARCHAR(16),
 Manager VARCHAR(30),
 CityName VARCHAR(20),
 NoOfRooms NUMBER,
 CONSTRAINT Hotel_pk PRIMARY KEY (Name),
 CONSTRAINT Hotel_City_fk FOREIGN KEY (CityName)
 REFERENCES City(CityName)
)
/

CREATE TABLE RoomType
(
 RoomType VARCHAR(15) NOT NULL,
 MaxOccup NUMBER NOT NULL,
 CONSTRAINT RoomType_pk PRIMARY KEY (RoomType)
)
/

CREATE TABLE Room
(
 RoomNo NUMBER NOT NULL,
 HotelName VARCHAR(20),
 RoomType VARCHAR(15),
 Beds NUMBER,
 Smoking VARCHAR(5),
 CONSTRAINT Room_pk PRIMARY KEY (RoomNo, HotelName),
 CONSTRAINT Room_Hotel_fk FOREIGN KEY (HotelName)
 REFERENCES Hotel(Name),
 CONSTRAINT Room_RoomType_fk FOREIGN KEY (RoomType)
 REFERENCES RoomType(RoomType)
)
/

CREATE TABLE RoomTarrif
(
 Single NUMBER,
 Double NUMBER,
 ExtraPerson NUMBER,
 HotelName VARCHAR(20),
 RoomType VARCHAR(15),
 CONSTRAINT RoomTarrif_pk PRIMARY KEY (HotelName, RoomType),
 CONSTRAINT RoomTarrif_Hotel_fk FOREIGN KEY (HotelName)
 REFERENCES Hotel(Name),
 CONSTRAINT RoomTarrif_RoomType_fk FOREIGN KEY (RoomType)
 REFERENCES RoomType(RoomType)
)
/

CREATE TABLE Guest
(
 Name VARCHAR(30) NOT NULL,
 Address VARCHAR(50),
 Telephone VARCHAR(16),
 CreditCardNo VARCHAR(16),
 CreditCardType VARCHAR(20),
 CONSTRAINT Guest_pk PRIMARY KEY (Name)
)
/

CREATE TABLE AdvanceBooking
(
 ArrDate DATE NOT NULL,
 DepDate DATE,
 Deposit NUMBER,
 RoomAll NUMBER,
 Persons NUMBER NOT NULL,
 HotelName VARCHAR(20) NOT NULL,
 GuestName VARCHAR(30) NOT NULL,
 CONSTRAINT AdvanceBooking_pk PRIMARY KEY (GuestName, HotelName, ArrDate),
 CONSTRAINT AdvanceBooking_Room_fk FOREIGN KEY (RoomAll, HotelName)
 REFERENCES Room (RoomNo, HotelName),
 CONSTRAINT AdvanceBooking_Guest_fk FOREIGN KEY (GuestName)
 REFERENCES Guest (Name)
)
/

CREATE TABLE Allocation
(
 Persons NUMBER,
 AllocDate DATE,
 Reg VARCHAR(10),
 RoomNo NUMBER,
 GuestName VARCHAR(30),
 HotelName VARCHAR(20),
 CONSTRAINT Allocation_pk PRIMARY KEY (AllocDate, GuestName),
 CONSTRAINT Allocation_Room_fk FOREIGN KEY (RoomNo, HotelName)
 REFERENCES Room (RoomNo, HotelName),
 CONSTRAINT Allocation_Guest_fk FOREIGN KEY (GuestName)
 REFERENCES Guest (Name)
)
/

CREATE TABLE ResturauntCharge
(
 Meal VARCHAR(20) NOT NULL,
 Persons NUMBER,
 ChargeType VARCHAR(20),
 Amount NUMBER,
 ChargeDate DATE,
 GuestName VARCHAR(30),
 CONSTRAINT ResturauntCharge_pk PRIMARY KEY (ChargeDate, GuestName, Meal),
 CONSTRAINT ResturauntCharge_Allocation_fk
 FOREIGN KEY (ChargeDate, GuestName)
 REFERENCES Allocation (AllocDate, GuestName)
)
/

CREATE TABLE TelephoneCharge
(
 TelNo VARCHAR(16) NOT NULL,
 CallTime VARCHAR(8) NOT NULL, --time?
 ChargeType VARCHAR(20),
 Amount NUMBER,
 ChargeDate DATE,
 GuestName VARCHAR(30) NOT NULL,
 CONSTRAINT TelephoneCharge_pk
 PRIMARY KEY (ChargeDate, GuestName, TelNo, CallTime),
 CONSTRAINT TelephoneCharge_Allocation_fk
 FOREIGN KEY (ChargeDate, GuestName)
 REFERENCES Allocation (AllocDate, GuestName)
)
/

CREATE TABLE RoomService
(
 Service VARCHAR(20) NOT NULL,
 ServiceTime VARCHAR(8) NOT NULL, --time?
 ChargeType VARCHAR(20),
 Amount NUMBER,
 ChargeDate DATE,
 GuestName VARCHAR(30) NOT NULL,
 CONSTRAINT RoomService_pk 
 PRIMARY KEY (Service, GuestName, ServiceTime, ChargeDate),
 CONSTRAINT RoomService_Allocation_fk
 FOREIGN KEY (ChargeDate, GuestName)
 REFERENCES Allocation (AllocDate, GuestName)
)
/