-- these tables need to be inserted with foreign key checks off

-- 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_seandb
-- -----------------------------------------------------
 
-- -----------------------------------------------------
-- Schema azziedev_seandb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `azziedev_seandb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `azziedev_seandb` ;
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`EMPLOYEE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`EMPLOYEE` (
  `FNAME` VARCHAR(45) NULL DEFAULT NULL,
  `MINIT` VARCHAR(45) NULL DEFAULT NULL,
  `LNAME` VARCHAR(45) NULL DEFAULT NULL,
  `SSN` INT(9) NOT NULL,
  `BDATE` DATE NULL DEFAULT NULL,
  `ADDRESS` VARCHAR(45) NULL DEFAULT NULL,
  `SEX` VARCHAR(45) NULL DEFAULT NULL,
  `SALARY` INT(8) NULL DEFAULT NULL,
  `SUPERSSN` INT(9) NULL DEFAULT NULL,
  `DNO` INT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`SSN`),
  INDEX `SUPERSSN_idx` (`SUPERSSN` ASC),
  INDEX `DNO_idx` (`DNO` ASC),
  CONSTRAINT `DNO`
    FOREIGN KEY (`DNO`)
    REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `SUPERSSN`
    FOREIGN KEY (`SUPERSSN`)
    REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`DEPARTMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPARTMENT` (
  `DNAME` VARCHAR(45) NULL DEFAULT NULL,
  `DNUMBER` INT(1) NOT NULL,
  `MGRSSN` INT(9) NULL DEFAULT NULL,
  `MGRSTARTDATE` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`DNUMBER`),
  INDEX `MGRSSN_idx` (`MGRSSN` ASC),
  CONSTRAINT `MGRSSN`
    FOREIGN KEY (`MGRSSN`)
    REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`DEPENDENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPENDENT` (
  `ESSN` INT(9) NOT NULL,
  `DEPENDENT_NAME` VARCHAR(45) NOT NULL,
  `SEX` VARCHAR(45) NULL DEFAULT NULL,
  `BDATE` DATE NULL DEFAULT NULL,
  `RELATIONSHIP` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`ESSN`, `DEPENDENT_NAME`),
  CONSTRAINT `ESSN`
    FOREIGN KEY (`ESSN`)
    REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPT_LOCATIONS` (
  `DNUMBER` INT(1) NOT NULL,
  `DLOCATION` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`DNUMBER`, `DLOCATION`),
  INDEX `DEPT_idx` (`DNUMBER` ASC),
  CONSTRAINT `DEPT`
    FOREIGN KEY (`DNUMBER`)
    REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`PROJECT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`PROJECT` (
  `PNAME` VARCHAR(45) NULL DEFAULT NULL,
  `PNUMBER` INT(2) NOT NULL,
  `PLOCATION` VARCHAR(45) NULL DEFAULT NULL,
  `DNUM` INT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`PNUMBER`),
  INDEX `DNUM_idx` (`DNUM` ASC),
  CONSTRAINT `DNUM`
    FOREIGN KEY (`DNUM`)
    REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_seandb`.`WORKS_ON`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`WORKS_ON` (
  `ESSN` INT(9) NOT NULL,
  `PNO` INT(2) NOT NULL,
  `HOURS` DECIMAL(3,1) NULL DEFAULT NULL,
  PRIMARY KEY (`ESSN`, `PNO`),
  INDEX `PNOw_idx` (`PNO` ASC),
  CONSTRAINT `ESSNw`
    FOREIGN KEY (`ESSN`)
    REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `PNOw`
    FOREIGN KEY (`PNO`)
    REFERENCES `azziedev_seandb`.`PROJECT` (`PNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 




-- these need to be inserted with foreign key checks off
INSERT INTO `EMPLOYEE` VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Houston TX', 'M', '25000', NULL, '1');
INSERT INTO `EMPLOYEE` VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss Houston TX', 'M', '40000', '888665555', '5');
INSERT INTO `EMPLOYEE` VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry Bellaire TX', 'F', '43000', '888665555', '4');

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


-- these values don't need foreign key checks disabled

INSERT INTO `DEPARTMENT` VALUES ('Headquarters', '1', '888665555', '1981-06-19');
INSERT INTO `DEPARTMENT` VALUES ('Research', '5', '333445555', '1988-05-22');
INSERT INTO `DEPARTMENT` VALUES ('Administration', '4', '987654321', '1995-01-01');
INSERT INTO `EMPLOYEE` VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren Houston TX', 'M', '30000', '333445555', '5');
INSERT INTO `EMPLOYEE` VALUES ('Ramesh', 'K', 'Narayan', '666884444', '1962-09-16', '975 Fire Oak Humble TX', 'M', '38000', '333445555', '5');
INSERT INTO `EMPLOYEE` VALUES ('Joyce', 'A', 'English', '453453453', '1972-07-31', '5631 Rice Houston TX', 'F', '25000', '333445555', '5');
INSERT INTO `EMPLOYEE` VALUES ('Alicia', 'J', 'Zelaya', '999887777', '1968-07-19', '3321 Castle Spring TX', 'F', '25000', '987654321', '4');
INSERT INTO `EMPLOYEE` VALUES ('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', '980 Dallas Houston TX', 'M', '25000', '987654321', '4');
INSERT INTO `DEPENDENT` VALUES ('333445555', 'Alice', 'F', '1986-04-05', 'DAUGHTER');
INSERT INTO `DEPENDENT` VALUES ('333445555', 'Theodore', 'M', '1983-10-25', 'SON');
INSERT INTO `DEPENDENT` VALUES ('333445555', 'Joy', 'F', '1958-05-03', 'SPOUSE');
INSERT INTO `DEPENDENT` VALUES ('987654321', 'Abner', 'M', '1942-02-28', 'SPOUSE');
INSERT INTO `DEPENDENT` VALUES ('123456789', 'Michael', 'M', '1988-01-04', 'SON');
INSERT INTO `DEPENDENT` VALUES ('123456789', 'Alice', 'F', '1988-12-30', 'DAUGHTER');
INSERT INTO `DEPENDENT` VALUES ('123456789', 'Elizabeth', 'F', '1967-05-05', 'SPOUSE');
INSERT INTO `DEPT_LOCATIONS` VALUES ('1', 'Houston');
INSERT INTO `DEPT_LOCATIONS` VALUES ('4', 'Stafford');
INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Bellaire');
INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Sugarland');
INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Houston');
INSERT INTO `PROJECT` VALUES ('ProductX', '1', 'Bellaire', '5');
INSERT INTO `PROJECT` VALUES ('ProductY', '2', 'Sugarland', '5');
INSERT INTO `PROJECT` VALUES ('ProductZ', '3', 'Houston', '5');
INSERT INTO `PROJECT` VALUES ('Computerization', '10', 'Stafford', '4');
INSERT INTO `PROJECT` VALUES ('Reorganization', '20', 'Houston', '1');
INSERT INTO `PROJECT` VALUES ('Newbenefits', '30', 'Stafford', '4');
INSERT INTO `WORKS_ON` VALUES ('123456789', '1', '32.5');
INSERT INTO `WORKS_ON` VALUES ('123456789', '2', '7.5');
INSERT INTO `WORKS_ON` VALUES ('666884444', '3', '40.0');
INSERT INTO `WORKS_ON` VALUES ('453453453', '1', '20.0');
INSERT INTO `WORKS_ON` VALUES ('453453453', '2', '20.0');
INSERT INTO `WORKS_ON` VALUES ('333445555', '2', '10.0');
INSERT INTO `WORKS_ON` VALUES ('333445555', '3', '10.0');
INSERT INTO `WORKS_ON` VALUES ('333445555', '10', '10.0');
INSERT INTO `WORKS_ON` VALUES ('333445555', '20', '10.0');
INSERT INTO `WORKS_ON` VALUES ('999887777', '30', '30.0');
INSERT INTO `WORKS_ON` VALUES ('999887777', '10', '10.0');
INSERT INTO `WORKS_ON` VALUES ('987987987', '10', '35.0');
INSERT INTO `WORKS_ON` VALUES ('987987987', '30', '5.0');
INSERT INTO `WORKS_ON` VALUES ('987654321', '30', '20.0');
INSERT INTO `WORKS_ON` VALUES ('987654321', '20', '15.0');
INSERT INTO `WORKS_ON` VALUES ('888665555', '20', NULL);