-- MySQL Workbench Forward Engineering (regenerated from coursework/1118472.mwb)

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

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

-- -----------------------------------------------------
-- Table `mydb`.`Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Ticket` (
  `Ticket ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Each visitor has unique ticket ID, unique bar code record ticket ID',
  `Invoices_Invoice ID` INT NOT NULL COMMENT 'Use as ticket purchase reference',
  `Transportation` ENUM('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other') NULL COMMENT 'As visitor first time enter the museum, the transportation must be record.',
  `Booked ID` INT NULL COMMENT 'Use to check special exhibition booking',
  `Exhibition Booking ID` INT NULL COMMENT 'Special exhibition book ID',
  PRIMARY KEY (`Ticket ID`),
  INDEX `fk_Ticket_Invoices1_idx` (`Invoices_Invoice ID` ASC) VISIBLE,
  INDEX `fk_Ticket_Booking Ticket1_idx` (`Booked ID` ASC, `Exhibition Booking ID` ASC) VISIBLE,
  CONSTRAINT `fk_Ticket_Invoices1`
    FOREIGN KEY (`Invoices_Invoice ID`)
    REFERENCES `mydb`.`Invoices` (`Invoice ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Ticket_Booking Ticket1`
    FOREIGN KEY (`Booked ID` , `Exhibition Booking ID`)
    REFERENCES `mydb`.`Booking Ticket` (`Booked ID` , `Exhibition Booking ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Invoices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Invoices` (
  `Invoice ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique invoice number',
  `Product ID` INT NOT NULL COMMENT 'Use to reference which product were purchased',
  `Payment Method_Payment ID` VARCHAR(45) NOT NULL COMMENT 'Use to reference the payment method',
  `Purchase Quantity` INT NOT NULL,
  PRIMARY KEY (`Invoice ID`, `Product ID`),
  INDEX `fk_Invoices_Payment Method1_idx` (`Payment Method_Payment ID` ASC) VISIBLE,
  INDEX `fk_Invoices_Product1_idx` (`Product ID` ASC) VISIBLE,
  CONSTRAINT `fk_Invoices_Payment Method1`
    FOREIGN KEY (`Payment Method_Payment ID`)
    REFERENCES `mydb`.`Payment Methods` (`Payment Method ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Invoices_Product1`
    FOREIGN KEY (`Product ID`)
    REFERENCES `mydb`.`Product` (`Product ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
  `Product ID` INT NOT NULL,
  `Product Name` VARCHAR(20) NOT NULL,
  `Product Price` FLOAT NOT NULL,
  PRIMARY KEY (`Product ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`EFTPOS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`EFTPOS` (
  `EFTPOS ID` INT NOT NULL AUTO_INCREMENT,
  `EFTPOS Types` ENUM('Credit Card', 'Debit Card', 'Digital Wallet') NOT NULL COMMENT 'Record card type',
  `Purchase Channel` ENUM('Online', 'Offline') NOT NULL COMMENT 'Channel: Online / Offline',
  `Card Issued Country` VARCHAR(45) NOT NULL,
  `Financial Institution Name` VARCHAR(45) NOT NULL,
  `Issued Bank Branch` VARCHAR(45) NOT NULL,
  `Account Name` VARCHAR(30) NOT NULL,
  `Card Number` INT(8) NOT NULL COMMENT 'Card Number only record the first 4 and end 4 numbers.',
  `Expiry Date` DATE NOT NULL,
  PRIMARY KEY (`EFTPOS ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Cash`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Cash` (
  `Cash Payment ID` INT NOT NULL AUTO_INCREMENT,
  `City` VARCHAR(50) NOT NULL,
  `Country` VARCHAR(50) NOT NULL,
  `First Name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`Cash Payment ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Payment Methods`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Payment Methods` (
  `Payment Method ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Check the payment method',
  `EFTPOS_EFTPOS ID` INT NULL COMMENT 'EFTPOS is for card and digital wallet payment method',
  `Cash_Cash ID` INT NULL COMMENT 'All cash payment will consider as a offline trade',
  PRIMARY KEY (`Payment Method ID`, `EFTPOS_EFTPOS ID`, `Cash_Cash ID`),
  INDEX `fk_Payment Method_EFTPOS1_idx` (`EFTPOS_EFTPOS ID` ASC) VISIBLE,
  INDEX `fk_Payment Method_Cash1_idx` (`Cash_Cash ID` ASC) VISIBLE,
  CONSTRAINT `fk_Payment Method_EFTPOS1`
    FOREIGN KEY (`EFTPOS_EFTPOS ID`)
    REFERENCES `mydb`.`EFTPOS` (`EFTPOS ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Payment Method_Cash1`
    FOREIGN KEY (`Cash_Cash ID`)
    REFERENCES `mydb`.`Cash` (`Cash Payment ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Entrance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Entrance` (
  `Gate Name` ENUM('Pyramid', 'Carrousel de Louvre', '99 rue de Rivioli', 'Passage Richelieu', 'Portes de Lion') NOT NULL COMMENT 'Identify which gate',
  `Ticket ID` INT NOT NULL COMMENT 'Record Ticket ID',
  `Entrance Date/Time` DATETIME NOT NULL,
  `Business Day` ENUM('True', 'False') NOT NULL COMMENT 'To justify is working day or not',
  PRIMARY KEY (`Gate Name`, `Ticket ID`),
  INDEX `fk_Entrance_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Entrance_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Wings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Wings` (
  `Wings Name` ENUM('Richelieu', 'Denon', 'Sully') NOT NULL COMMENT 'Identify which wing',
  `Ticket_Ticket ID` INT NOT NULL COMMENT 'Record ticket id',
  `Visit Time` DATETIME NOT NULL,
  PRIMARY KEY (`Wings Name`),
  INDEX `fk_Wings_Ticket1_idx` (`Ticket_Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Wings_Ticket1`
    FOREIGN KEY (`Ticket_Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Hired Audio Guide`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hired Audio Guide` (
  `Hired ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique hiring id',
  `Serial Number` VARCHAR(16) NOT NULL COMMENT 'Audio device serial number',
  `Language Setting` ENUM('French', 'English', 'Italian', 'Russian', 'Chinese Mandarin', 'Spanish', 'Portuguese', 'Korean', 'Dutch', 'Polish', 'Swedish', 'Norwegian', 'Finnish') NOT NULL,
  `Issued Wing` ENUM('Richelieu', 'Denon', 'Sully') NOT NULL,
  `Returned Wing` ENUM('Richelieu', 'Denon', 'Sully') NULL,
  `Issued Time` DATETIME NOT NULL,
  `Return Time` DATETIME NULL,
  `Invoice ID` INT NOT NULL,
  `Product ID` INT NOT NULL,
  `Ticket ID` INT NOT NULL,
  PRIMARY KEY (`Hired ID`, `Serial Number`),
  INDEX `fk_Hiring Audio Guide_Audio Guide Device1_idx` (`Serial Number` ASC) VISIBLE,
  INDEX `fk_Hired Audio Guide_Invoices1_idx` (`Invoice ID` ASC, `Product ID` ASC) VISIBLE,
  INDEX `fk_Hired Audio Guide_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Hiring Audio Guide_Audio Guide Device1`
    FOREIGN KEY (`Serial Number`)
    REFERENCES `mydb`.`Audio Guide Device` (`Serial Number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hired Audio Guide_Invoices1`
    FOREIGN KEY (`Invoice ID` , `Product ID`)
    REFERENCES `mydb`.`Invoices` (`Invoice ID` , `Product ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hired Audio Guide_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Audio Guide Device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Audio Guide Device` (
  `Serial Number` VARCHAR(16) NOT NULL COMMENT 'Audio device serial number',
  `Activated Date` DATE NOT NULL,
  `Retired Date` DATE NOT NULL,
  PRIMARY KEY (`Serial Number`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Exhibition Booking`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Exhibition Booking` (
  `Booking ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Booking id',
  `Exhibition ID` INT NOT NULL COMMENT 'Identify booked exhibition',
  `Time Slot ID` INT NOT NULL COMMENT 'Identify which time slot has been selected',
  `Ticket ID` INT NULL COMMENT 'Record ticket ID / multi-value attribute',
  `Booking Date` DATE NOT NULL COMMENT 'Booking Date',
  PRIMARY KEY (`Booking ID`, `Exhibition ID`, `Time Slot ID`),
  INDEX `fk_Exhibition Booking_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  INDEX `fk_Exhibition Booking_Time Slot1_idx` (`Time Slot ID` ASC) VISIBLE,
  INDEX `fk_Exhibition Booking_Special Exhibition1_idx` (`Exhibition ID` ASC) VISIBLE,
  CONSTRAINT `fk_Exhibition Booking_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Exhibition Booking_Time Slot1`
    FOREIGN KEY (`Time Slot ID`)
    REFERENCES `mydb`.`Time Slot` (`Time Slot ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Exhibition Booking_Special Exhibition1`
    FOREIGN KEY (`Exhibition ID`)
    REFERENCES `mydb`.`Special Exhibition` (`Exhibition ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Booking Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Booking Ticket` (
  `Booked ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Booking ID',
  `Exhibition Booking ID` INT NOT NULL,
  PRIMARY KEY (`Booked ID`, `Exhibition Booking ID`),
  INDEX `fk_Booking Ticket_Exhibition Booking1_idx` (`Exhibition Booking ID` ASC) VISIBLE,
  CONSTRAINT `fk_Booking Ticket_Exhibition Booking1`
    FOREIGN KEY (`Exhibition Booking ID`)
    REFERENCES `mydb`.`Exhibition Booking` (`Booking ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Special Exhibition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Special Exhibition` (
  `Exhibition ID` INT NOT NULL,
  `Exhibition Title` VARCHAR(45) NOT NULL COMMENT 'Name of exhibition',
  `Opening Date` DATETIME NOT NULL,
  `Closing Date` DATETIME NOT NULL,
  PRIMARY KEY (`Exhibition ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Time Slot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Time Slot` (
  `Time Slot ID` INT NOT NULL COMMENT 'Available time slot number',
  `Time Period` TIME NOT NULL COMMENT 'Time slot date is fixed',
  `Time Slot Capacity` INT NOT NULL,
  PRIMARY KEY (`Time Slot ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Hall Napoleon`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hall Napoleon` (
  `Exhibiton Log ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Record how many visitor enter the hall Napoleon',
  `Time Slot ID` INT NOT NULL COMMENT 'Record the time slot',
  `Date` DATE NOT NULL COMMENT 'Record the date',
  `Ticket_Ticket ID` INT NULL COMMENT 'Record visited visitor ticket id',
  PRIMARY KEY (`Exhibiton Log ID`),
  INDEX `fk_Hall Napoleon_Ticket1_idx` (`Ticket_Ticket ID` ASC) VISIBLE,
  INDEX `fk_Hall Napoleon_Time Slot1_idx` (`Time Slot ID` ASC) VISIBLE,
  CONSTRAINT `fk_Hall Napoleon_Ticket1`
    FOREIGN KEY (`Ticket_Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hall Napoleon_Time Slot1`
    FOREIGN KEY (`Time Slot ID`)
    REFERENCES `mydb`.`Time Slot` (`Time Slot ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Actual Number of Visitor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Actual Number of Visitor` (
  `Vistor ID` INT NOT NULL AUTO_INCREMENT,
  `Exhibiton Log ID` INT NOT NULL,
  `Ticket ID` INT NOT NULL,
  PRIMARY KEY (`Vistor ID`, `Exhibiton Log ID`),
  INDEX `fk_Actual Number of Visitor_Hall Napoleon1_idx` (`Exhibiton Log ID` ASC) VISIBLE,
  CONSTRAINT `fk_Actual Number of Visitor_Hall Napoleon1`
    FOREIGN KEY (`Exhibiton Log ID`)
    REFERENCES `mydb`.`Hall Napoleon` (`Exhibiton Log 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;