CREATE TABLE Room_Type (
  Room_Type VARCHAR(10),
  Sleeps INT,
  Description VARCHAR(100),
  PRIMARY KEY (Room_Type)
);

CREATE TABLE Room (
  Room_Number INT NOT NULL,
  Room_Type VARCHAR(10),
  Shower VARCHAR(10),
  Bath VARCHAR(10),
  Balcony VARCHAR(10),
  PRIMARY KEY (Room_Number),
  CONSTRAINT FK_Room_Room_Type
    FOREIGN KEY (Room_Type)
      REFERENCES Room_Type(Room_Type)
);

CREATE TABLE Price_List (
  Room_Number INT NOT NULL,
  Season VARCHAR(12),
  Weekday VARCHAR(15),
  Price INT,
  PRIMARY KEY (Room_Number, Season, Weekday),
  CONSTRAINT FK_Price_List_Room_Number
    FOREIGN KEY (Room_Number)
      REFERENCES Room(Room_Number)
);

CREATE TABLE Fixed_Extra (
  Room_Number INT,
  Extra_ID INT,
  PRIMARY KEY (Room_Number, Extra_ID),
  CONSTRAINT FK_Fixed_Extra_Room_Number
    FOREIGN KEY (Room_Number)
      REFERENCES Room(Room_Number)
);

CREATE TABLE Extra (
  Extra_ID INT NOT NULL,
  Object_Name VARCHAR(20),
  Object_Description VARCHAR(100),
  Object_Price INT,
  PRIMARY KEY (Extra_ID)
);

CREATE TABLE Guest (
  Guest_ID INT,
  Family_Name VARCHAR(50),
  Given_Name VARCHAR(50),
  Date_of_Birth DATE,
  Address VARCHAR(200),
  PRIMARY KEY (Guest_ID)
);

CREATE TABLE Booking (
  Booking_Number INT,
  Guest INT,
  Date_Made DATE,
  Reason VARCHAR(50),
  PRIMARY KEY (Booking_Number),
  CONSTRAINT FK_Booking_Guest
    FOREIGN KEY (Guest)
      REFERENCES Guest(Guest_ID)
);

CREATE TABLE Reservation (
  Booking_Number INT,
  Room_Number INT,
  Date_Reserved DATE,
  PRIMARY KEY (Booking_Number, Room_Number, Date_Reserved),
  CONSTRAINT FK_Reservation_Room_Number
    FOREIGN KEY (Room_Number)
      REFERENCES Room(Room_Number),
  CONSTRAINT FK_Reservation_Booking_Number
    FOREIGN KEY (Booking_Number)
      REFERENCES Booking(Booking_Number)
);

CREATE TABLE additional_extra(
Booking_Number INT, Room_Number
INT, Date_Reserved
DATE, Extra_ID INT, PRIMARY KEY (Booking_Number, Room_Number, Date_Reserved, Extra_ID), FOREIGN KEY (Extra_ID) REFERENCES Extra (Extra_ID))