productionDB_draft_BCNF.sql

-- MySQL Script generated by MySQL Workbench
-- Fri Aug 16 14:30:58 2019
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

-- -----------------------------------------------------
-- Table `mydb`.`Activity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Activity` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Activity` (
  `UniqueCode` INT NOT NULL,
  `Previous Timestamp` TIME NOT NULL,
  `Current Timestamp` TIME NOT NULL,
  `Activity Name` VARCHAR(45) NULL,
  `Dolly No.` VARCHAR(20) NULL,
  `Kitting Line` INT(255) NULL,
  `Delivery Day` DATE NULL,
  `Production Day` DATE NULL,
  `Recipe ID` TINYINT(10) NULL,
  INDEX `OneRecipeNActivities` (`Recipe ID` ASC) VISIBLE,
  PRIMARY KEY (`UniqueCode`),
  INDEX `OneKittingLineNActivities` (`Kitting Line` ASC) INVISIBLE,
  INDEX `OneDeliveryDayNActivities_idx` (`Delivery Day` ASC) VISIBLE,
  INDEX `OneProductionDayNActivities_idx` (`Production Day` ASC) VISIBLE,
  CONSTRAINT `OneRecipeNActivities`
    FOREIGN KEY (`Recipe ID`)
    REFERENCES `mydb`.`Recipe` (`Recipe ID`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT `OneKittingLineNActivities`
    FOREIGN KEY (`Kitting Line`)
    REFERENCES `mydb`.`Kitting Line` (`idKitting Line ID`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT `OneDeliveryDayNActivities`
    FOREIGN KEY (`Delivery Day`)
    REFERENCES `mydb`.`Delivery Day` (`idDelivery Day`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT `OneProductionDayNActivities`
    FOREIGN KEY (`Production Day`)
    REFERENCES `mydb`.`Production Day` (`idProduction Day`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Activity_has_Staff Table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Activity_has_Staff Table` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Activity_has_Staff Table` (
  `Activity_UniqueCode` INT NOT NULL,
  `Staff Table_Staff ID` INT NOT NULL,
  `staff role in this activity` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Activity_UniqueCode`, `Staff Table_Staff ID`),
  INDEX `fk_Activity_has_Staff Table_Staff Table1_idx` (`Staff Table_Staff ID` ASC) VISIBLE,
  INDEX `fk_Activity_has_Staff Table_Activity1_idx` (`Activity_UniqueCode` ASC) VISIBLE,
  CONSTRAINT `fk_Activity_has_Staff Table_Activity1`
    FOREIGN KEY (`Activity_UniqueCode`)
    REFERENCES `mydb`.`Activity` (`UniqueCode`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_Activity_has_Staff Table_Staff Table1`
    FOREIGN KEY (`Staff Table_Staff ID`)
    REFERENCES `mydb`.`Staff Table` (`Staff ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Box Info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Box Info` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Box Info` (
  `BoxID` INT NOT NULL,
  `Delivery Day` DATE NOT NULL,
  `Assembly Line` TINYINT NOT NULL,
  `Production Mode` INT NOT NULL,
  `Delivery Region` VARCHAR(10) NOT NULL,
  `Delivery Route` VARCHAR(20) NOT NULL,
  `Courier` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`BoxID`),
  INDEX `fk_Box Info_Delivery Day1_idx` (`Delivery Day` ASC) VISIBLE,
  CONSTRAINT `fk_Box Info_Delivery Day1`
    FOREIGN KEY (`Delivery Day`)
    REFERENCES `mydb`.`Delivery Day` (`idDelivery Day`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Delivery Day`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Delivery Day` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Delivery Day` (
  `idDelivery Day` DATE NOT NULL,
  `Logistics info` VARCHAR(45) NULL,
  PRIMARY KEY (`idDelivery Day`))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Error`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Error` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Error` (
  `ErrorID` INT NOT NULL,
  `BoxID` INT NOT NULL,
  `Error Category` VARCHAR(45) NOT NULL,
  `Ingredient_SKU` VARCHAR(20) NOT NULL,
  `Complain Mapping` VARCHAR(20) NOT NULL COMMENT 'Where the problem happenned',
  `Error Description` VARCHAR(45) NULL,
  PRIMARY KEY (`ErrorID`),
  INDEX `fk_Error_Ingredient1_idx` (`Ingredient_SKU` ASC) VISIBLE,
  INDEX `fk_Error_Box Info1_idx` (`BoxID` ASC) VISIBLE,
  CONSTRAINT `fk_Error_Ingredient1`
    FOREIGN KEY (`Ingredient_SKU`)
    REFERENCES `mydb`.`Ingredient` (`SKU`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Error_Box Info1`
    FOREIGN KEY (`BoxID`)
    REFERENCES `mydb`.`Box Info` (`BoxID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Ingredient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Ingredient` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Ingredient` (
  `SKU` VARCHAR(45) NOT NULL COMMENT 'PK for eack ingredient',
  `Ingredient Name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`SKU`))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Kitting Line`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Kitting Line` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Kitting Line` (
  `idKitting Line ID` INT NULL,
  `Pick Faces` VARCHAR(45) NULL,
  PRIMARY KEY (`idKitting Line ID`))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Production Day`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Production Day` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Production Day` (
  `idProduction Day` DATE NULL,
  `Attendence rate` VARCHAR(45) NOT NULL,
  `Shift leader` INT NOT NULL,
  PRIMARY KEY (`idProduction Day`))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Recipe`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Recipe` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Recipe` (
  `Recipe ID` TINYINT(10) NOT NULL,
  `Recipe Name` VARCHAR(20) NOT NULL,
  `Week` TINYINT NULL,
  `Year` YEAR(2) NULL,
  PRIMARY KEY (`Recipe ID`))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Recipe_has_Ingredient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Recipe_has_Ingredient` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Recipe_has_Ingredient` (
  `Recipe_Recipe ID` TINYINT(10) NOT NULL,
  `Ingredient_SKU` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Recipe_Recipe ID`, `Ingredient_SKU`),
  INDEX `fk_Recipe_has_Ingredient_Ingredient1_idx` (`Ingredient_SKU` ASC) VISIBLE,
  INDEX `fk_Recipe_has_Ingredient_Recipe1_idx` (`Recipe_Recipe ID` ASC) VISIBLE,
  CONSTRAINT `fk_Recipe_has_Ingredient_Recipe1`
    FOREIGN KEY (`Recipe_Recipe ID`)
    REFERENCES `mydb`.`Recipe` (`Recipe ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Recipe_has_Ingredient_Ingredient1`
    FOREIGN KEY (`Ingredient_SKU`)
    REFERENCES `mydb`.`Ingredient` (`SKU`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`Staff Table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Staff Table` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `mydb`.`Staff Table` (
  `Staff ID` INT NOT NULL,
  `Staff Name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Staff ID`))
ENGINE = InnoDB;

SHOW WARNINGS;

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