-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 20:26:52 2018
-- 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`.`Tourist`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Tourist` (
  `TouristID` INT NOT NULL,
  `FirstName` VARCHAR(45) NOT NULL,
  `LastName` VARCHAR(45) NOT NULL,
  `Email` VARCHAR(45) NOT NULL,
  `Address` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`TouristID`),
  UNIQUE INDEX `ID_UNIQUE` (`TouristID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`City`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`City` (
  `CityID` INT NOT NULL,
  `RoomType` VARCHAR(45) NOT NULL,
  `RoomNum` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`CityID`),
  UNIQUE INDEX `CityID_UNIQUE` (`CityID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `HotelID` INT NOT NULL,
  `Description` VARCHAR(45) NOT NULL,
  `City_CityID` INT NOT NULL,
  PRIMARY KEY (`HotelID`),
  UNIQUE INDEX `HotelID_UNIQUE` (`HotelID` ASC),
  INDEX `fk_Hotel_City1_idx` (`City_CityID` ASC),
  CONSTRAINT `fk_Hotel_City1`
    FOREIGN KEY (`City_CityID`)
    REFERENCES `mydb`.`City` (`CityID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Reservation` (
  `ReservationID` INT NOT NULL,
  `CheckinDate` DATE NOT NULL,
  `CheckOutDate` DATE NOT NULL,
  `Tourist_ID` INT NOT NULL,
  `Hotel_HotelID` INT NOT NULL,
  PRIMARY KEY (`ReservationID`, `Tourist_ID`, `Hotel_HotelID`),
  UNIQUE INDEX `ReservationID_UNIQUE` (`ReservationID` ASC),
  INDEX `fk_Reservation_Tourist_idx` (`Tourist_ID` ASC),
  INDEX `fk_Reservation_Hotel1_idx` (`Hotel_HotelID` ASC),
  CONSTRAINT `fk_Reservation_Tourist`
    FOREIGN KEY (`Tourist_ID`)
    REFERENCES `mydb`.`Tourist` (`TouristID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Reservation_Hotel1`
    FOREIGN KEY (`Hotel_HotelID`)
    REFERENCES `mydb`.`Hotel` (`HotelID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Payment` (
  `PaymentID` INT NOT NULL,
  `PayDate` DATE NOT NULL,
  `Price` FLOAT NOT NULL,
  `Reservation_ReservationID` INT NOT NULL,
  `Reservation_Tourist_ID` INT NOT NULL,
  PRIMARY KEY (`PaymentID`, `Reservation_ReservationID`, `Reservation_Tourist_ID`),
  UNIQUE INDEX `PaymentID_UNIQUE` (`PaymentID` ASC),
  INDEX `fk_Payment_Reservation1_idx` (`Reservation_ReservationID` ASC, `Reservation_Tourist_ID` ASC),
  CONSTRAINT `fk_Payment_Reservation1`
    FOREIGN KEY (`Reservation_ReservationID` , `Reservation_Tourist_ID`)
    REFERENCES `mydb`.`Reservation` (`ReservationID` , `Tourist_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `RoomID` INT NOT NULL,
  `RoomType` VARCHAR(45) NOT NULL,
  `RoomNum` VARCHAR(45) NOT NULL,
  `Hotel_HotelID` INT NOT NULL,
  PRIMARY KEY (`RoomID`, `Hotel_HotelID`),
  UNIQUE INDEX `RoomID_UNIQUE` (`RoomID` ASC),
  INDEX `fk_Room_Hotel1_idx` (`Hotel_HotelID` ASC),
  CONSTRAINT `fk_Room_Hotel1`
    FOREIGN KEY (`Hotel_HotelID`)
    REFERENCES `mydb`.`Hotel` (`HotelID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CreditCard`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CreditCard` (
  `CreditCardID` INT NOT NULL,
  `CardNum` VARCHAR(45) NOT NULL,
  `Balance` DOUBLE NOT NULL,
  `Tourist_TouristID` INT NOT NULL,
  PRIMARY KEY (`CreditCardID`, `Tourist_TouristID`),
  INDEX `fk_CreditCard_Tourist1_idx` (`Tourist_TouristID` ASC),
  CONSTRAINT `fk_CreditCard_Tourist1`
    FOREIGN KEY (`Tourist_TouristID`)
    REFERENCES `mydb`.`Tourist` (`TouristID`)
    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;
