-- MySQL Script generated by MySQL Workbench
-- 04/28/18 19:18:29
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`credit cards`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`credit cards` (
  `number` VARCHAR(45) NOT NULL,
  `type` VARCHAR(45) NULL,
  `security code` VARCHAR(5) NULL,
  PRIMARY KEY (`number`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `costomer_id` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`costomer_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`location` (
  `code` DECIMAL(45) NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`code`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hotel` (
  `address` VARCHAR(45) NOT NULL,
  `location_code` DECIMAL(45) NOT NULL,
  PRIMARY KEY (`address`, `location_code`),
  INDEX `fk_hotel_location1_idx` (`location_code` ASC),
  CONSTRAINT `fk_hotel_location1`
    FOREIGN KEY (`location_code`)
    REFERENCES `mydb`.`location` (`code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`reservations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reservations` (
  `reservation_id` VARCHAR(45) NOT NULL,
  `hotel name` VARCHAR(45) NULL,
  `city name` VARCHAR(45) NULL,
  `check in date` DATE NULL,
  `check out date` DATE NULL,
  `numbers of room` INT NULL,
  `price` FLOAT NULL,
  `costomer name` VARCHAR(45) NULL,
  `isSmoking` TINYINT(1) NULL,
  `credit cards_number` VARCHAR(45) NOT NULL,
  `customer_costomer_id` VARCHAR(45) NOT NULL,
  `hotel_address` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`reservation_id`, `credit cards_number`, `customer_costomer_id`, `hotel_address`),
  INDEX `fk_reservations_credit cards_idx` (`credit cards_number` ASC),
  INDEX `fk_reservations_customer1_idx` (`customer_costomer_id` ASC),
  INDEX `fk_reservations_hotel1_idx` (`hotel_address` ASC),
  CONSTRAINT `fk_reservations_credit cards`
    FOREIGN KEY (`credit cards_number`)
    REFERENCES `mydb`.`credit cards` (`number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservations_customer1`
    FOREIGN KEY (`customer_costomer_id`)
    REFERENCES `mydb`.`customer` (`costomer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservations_hotel1`
    FOREIGN KEY (`hotel_address`)
    REFERENCES `mydb`.`hotel` (`address`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`request`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`request` (
  `request_id` INT NOT NULL,
  `destination name` VARCHAR(45) NULL,
  `check in date` DATE NULL,
  `number of nights` INT NULL,
  `customer_costomer_id` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`request_id`, `customer_costomer_id`),
  INDEX `fk_request_customer1_idx` (`customer_costomer_id` ASC),
  CONSTRAINT `fk_request_customer1`
    FOREIGN KEY (`customer_costomer_id`)
    REFERENCES `mydb`.`customer` (`costomer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`room-desc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room-desc` (
  `idroom_desc_id` INT NOT NULL,
  `price` FLOAT NULL,
  PRIMARY KEY (`idroom_desc_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room` (
  `room_id` VARCHAR(45) NULL,
  `type` VARCHAR(45) NOT NULL,
  `hotel_address` VARCHAR(45) NOT NULL,
  `room-desc_idroom_desc_id` INT NOT NULL,
  PRIMARY KEY (`type`, `hotel_address`, `room-desc_idroom_desc_id`),
  INDEX `fk_room_hotel1_idx` (`hotel_address` ASC),
  INDEX `fk_room_room-desc1_idx` (`room-desc_idroom_desc_id` ASC),
  CONSTRAINT `fk_room_hotel1`
    FOREIGN KEY (`hotel_address`)
    REFERENCES `mydb`.`hotel` (`address`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_room_room-desc1`
    FOREIGN KEY (`room-desc_idroom_desc_id`)
    REFERENCES `mydb`.`room-desc` (`idroom_desc_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;