12/8/2021 HW 6

-- 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
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema azziedev_jeremy
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema azziedev_jeremy
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `azziedev_jeremy` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `azziedev_jeremy` ;

-- -----------------------------------------------------
-- Table `azziedev_jeremy`.`J`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jeremy`.`J` (
  `j_num` VARCHAR(15) NOT NULL,
  `jname` VARCHAR(45) NULL DEFAULT NULL,
  `city` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`j_num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_jeremy`.`P`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jeremy`.`P` (
  `p_num` VARCHAR(45) NOT NULL,
  `pname` VARCHAR(45) NULL DEFAULT NULL,
  `color` VARCHAR(45) NULL DEFAULT NULL,
  `weight` INT(11) NULL DEFAULT NULL,
  `city` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`p_num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_jeremy`.`S`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jeremy`.`S` (
  `s_num` VARCHAR(45) NOT NULL,
  `s_name` VARCHAR(45) NULL DEFAULT NULL,
  `status` VARCHAR(45) NULL DEFAULT NULL,
  `city` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`s_num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `azziedev_jeremy`.`SPJ`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jeremy`.`SPJ` (
  `s_num` VARCHAR(45) NOT NULL,
  `p_num` VARCHAR(45) NOT NULL,
  `j_num` VARCHAR(45) NOT NULL,
  `qty` INT(11) NULL DEFAULT NULL,
  INDEX `snum_idx` (`s_num` ASC),
  INDEX `pnum_idx` (`p_num` ASC),
  INDEX `jnum_idx` (`j_num` ASC),
  PRIMARY KEY (`s_num`, `p_num`, `j_num`),
  CONSTRAINT `jnum`
    FOREIGN KEY (`j_num`)
    REFERENCES `azziedev_jeremy`.`J` (`j_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pnum`
    FOREIGN KEY (`p_num`)
    REFERENCES `azziedev_jeremy`.`P` (`p_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `snum`
    FOREIGN KEY (`s_num`)
    REFERENCES `azziedev_jeremy`.`S` (`s_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


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