-- 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);