CREATE TABLE `sjdupl01CECS535Project`.`HOTEL` (
  `hotelid` INT NOT NULL,
  `number` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `zip` VARCHAR(45) NULL,
  `manager-name` VARCHAR(45) NULL,
  `number-rooms` INT UNSIGNED NULL,
  `has-pool` TINYINT NULL,
  `has-bar` TINYINT NULL,
  `has-restaurant` TINYINT NULL,
  PRIMARY KEY (`hotelid`));

CREATE TABLE `sjdupl01CECS535Project`.`ROOM` (
  `type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `occupancy` INT NULL,
  `number-beds` INT UNSIGNED NULL,
  `type-beds` VARCHAR(45) NULL,
  `price` INT NULL,
  PRIMARY KEY (`type`));

CREATE TABLE `sjdupl01CECS535Project`.`ROOMHOTEL` (
  `hotelid` INT NOT NULL,
  `room-type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `number` INT UNSIGNED NULL,
  PRIMARY KEY (`hotelid`, `room-type`),
  INDEX `room-type_idx` (`room-type` ASC) VISIBLE,
  CONSTRAINT `hotelid`
    FOREIGN KEY (`hotelid`)
    REFERENCES `sjdupl01CECS535Project`.`HOTEL` (`hotelid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `room-type`
    FOREIGN KEY (`room-type`)
    REFERENCES `sjdupl01CECS535Project`.`ROOM` (`type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `sjdupl01CECS535Project`.`CUSTOMER` (
  `cust-id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `number` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `zip` VARCHAR(45) NULL,
  `status` ENUM('gold', 'silver', 'business') NULL,
  PRIMARY KEY (`cust-id`));

CREATE TABLE `sjdupl01CECS535Project`.`RESERVATION` (
  `hotel-id` INT NOT NULL,
  `cust-id` INT NOT NULL,
  `room-type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `begin-date` DATE NULL,
  `end-date` DATE NULL,
  `credit-card-number` VARCHAR(45) NULL,
  `exp-date` VARCHAR(45) NULL,
  PRIMARY KEY (`hotel-id`, `cust-id`, `room-type`),
  INDEX `cust-id_idx` (`cust-id` ASC) VISIBLE,
  INDEX `room-type_idx` (`room-type` ASC) VISIBLE,
  CONSTRAINT `hotel-id`
    FOREIGN KEY (`hotel-id`)
    REFERENCES `sjdupl01CECS535Project`.`HOTEL` (`hotelid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cust-id`
    FOREIGN KEY (`cust-id`)
    REFERENCES `sjdupl01CECS535Project`.`CUSTOMER` (`cust-id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `room-type`
    FOREIGN KEY (`room-type`)
    REFERENCES `sjdupl01CECS535Project`.`ROOM` (`type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);