SQL_Scripts.txt

-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema COMPANYX
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `COMPANYX`.`DEPARTMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`DEPARTMENT` (
 `Dname` VARCHAR(15) NOT NULL,
 `Dnumber` INT NOT NULL,
 `Mgr_ssn` CHAR(9) NOT NULL,
 `Mgr_start_date` DATE NULL,
 PRIMARY KEY (`Dnumber`),
 UNIQUE INDEX `Dname_UNIQUE` (`Dname` ASC),
 INDEX `fk_Mgr_ssn_idx` (`Mgr_ssn` ASC),
 CONSTRAINT `fk_Mgr_ssn`
 FOREIGN KEY (`Mgr_ssn`)
 REFERENCES `COMPANYX`.`EMPLOYEE` (`Ssn`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `COMPANYX`.`EMPLOYEE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`EMPLOYEE` (
 `Fname` VARCHAR(15) NOT NULL,
 `Minit` CHAR NULL,
 `Lname` VARCHAR(15) NOT NULL,
 `Ssn` CHAR(9) NOT NULL,
 `Bdate` DATE NULL,
 `Address` VARCHAR(30) NULL,
 `Sex` CHAR NULL,
 `Salary` DECIMAL(10,2) NULL,
 `Super_ssn` CHAR(9) NULL,
 `Dno` INT NOT NULL,
 PRIMARY KEY (`Ssn`),
 INDEX `fk_Super_ssn_idx` (`Super_ssn` ASC),
 INDEX `fk_Dno_idx` (`Dno` ASC),
 CONSTRAINT `fk_Super_ssn`
 FOREIGN KEY (`Super_ssn`)
 REFERENCES `COMPANYX`.`EMPLOYEE` (`Ssn`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_Dno`
 FOREIGN KEY (`Dno`)
 REFERENCES `COMPANYX`.`DEPARTMENT` (`Dnumber`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `COMPANYX`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`DEPT_LOCATIONS` (
 `Dnumber` INT NOT NULL,
 `Dlocation` VARCHAR(15) NOT NULL,
 PRIMARY KEY (`Dnumber`, `Dlocation`),
 CONSTRAINT `fk_Dnumber`
 FOREIGN KEY (`Dnumber`)
 REFERENCES `COMPANYX`.`DEPARTMENT` (`Dnumber`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `COMPANYX`.`PROJECT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`PROJECT` (
 `Pname` VARCHAR(15) NOT NULL,
 `Pnumber` INT NOT NULL,
 `Plocation` VARCHAR(15) NULL,
 `Dnum` INT NOT NULL,
 PRIMARY KEY (`Pnumber`),
 UNIQUE INDEX `Pname_UNIQUE` (`Pname` ASC),
 INDEX `fk_Dnum_idx` (`Dnum` ASC),
 CONSTRAINT `fk_Dnum`
 FOREIGN KEY (`Dnum`)
 REFERENCES `COMPANYX`.`DEPARTMENT` (`Dnumber`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `COMPANYX`.`WORKS_ON`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`WORKS_ON` (
 `Essn` CHAR(9) NOT NULL,
 `Pno` INT NOT NULL,
 `Hours` DECIMAL(3,1) NOT NULL,
 PRIMARY KEY (`Essn`, `Pno`),
 INDEX `fk_Pno_idx` (`Pno` ASC),
 CONSTRAINT `fk_Essn`
 FOREIGN KEY (`Essn`)
 REFERENCES `COMPANYX`.`EMPLOYEE` (`Ssn`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_Pno`
 FOREIGN KEY (`Pno`)
 REFERENCES `COMPANYX`.`PROJECT` (`Pnumber`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `COMPANYX`.`DEPENDENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `COMPANYX`.`DEPENDENT` (
 `Essn` CHAR(9) NOT NULL,
 `Dependent_name` VARCHAR(15) NOT NULL,
 `Sex` CHAR NULL,
 `Bdate` DATE NULL,
 `Relationship` VARCHAR(8) NULL,
 PRIMARY KEY (`Essn`, `Dependent_name`),
 CONSTRAINT `fk_Essn2`
 FOREIGN KEY (`Essn`)
 REFERENCES `COMPANYX`.`EMPLOYEE` (`Ssn`)
 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;

-- -----------------------------------------------------
-- Data for table `COMPANYX`.`DEPARTMENT`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`DEPARTMENT` (`Dname`, `Dnumber`, `Mgr_ssn`, `Mgr_start_date`) VALUES ('Research', 5, '333445555', '1988-05-22');
INSERT INTO `COMPANYX`.`DEPARTMENT` (`Dname`, `Dnumber`, `Mgr_ssn`, `Mgr_start_date`) VALUES ('Administration', 4, '987654321', '1995-01-01');
INSERT INTO `COMPANYX`.`DEPARTMENT` (`Dname`, `Dnumber`, `Mgr_ssn`, `Mgr_start_date`) VALUES ('Headquarters', 1, '888665555', '1981-06-19');

COMMIT;


-- -----------------------------------------------------
-- Data for table `COMPANYX`.`EMPLOYEE`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5); 
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, '888665555', 5);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Alicia', 'J', 'Zelaya', '999887777', '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, '987654321', 4);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, '888665555', 4);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Ramesh', 'K', 'Narayan', '666884444', '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, '333445555', 5);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Joyce', 'A', 'English', '453453453', '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, '333445555', 5);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, '987654321', 4);
INSERT INTO `COMPANYX`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Super_ssn`, `Dno`) VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `COMPANYX`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`DEPT_LOCATIONS` (`Dnumber`, `Dlocation`) VALUES (1, 'Houston');
INSERT INTO `COMPANYX`.`DEPT_LOCATIONS` (`Dnumber`, `Dlocation`) VALUES (4, 'Stafford');
INSERT INTO `COMPANYX`.`DEPT_LOCATIONS` (`Dnumber`, `Dlocation`) VALUES (5, 'Bellaire');
INSERT INTO `COMPANYX`.`DEPT_LOCATIONS` (`Dnumber`, `Dlocation`) VALUES (5, 'Sugarland');
INSERT INTO `COMPANYX`.`DEPT_LOCATIONS` (`Dnumber`, `Dlocation`) VALUES (5, 'Houston');

COMMIT;


-- -----------------------------------------------------
-- Data for table `COMPANYX`.`PROJECT`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO `COMPANYX`.`PROJECT` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES ('Newbenefits', 30, 'Stafford', 4);

COMMIT;


-- -----------------------------------------------------
-- Data for table `COMPANYX`.`WORKS_ON`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('123456789', 1, 32.5);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('123456789', 2, 7.5);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('666884444', 3, 40.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('453453453', 1, 20.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('453453453', 2, 20.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('333445555', 2, 10.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('333445555', 3, 10.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('333445555', 10, 10.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('333445555', 20, 10.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('999887777', 30, 30.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('999887777', 10, 10.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('987987987', 10, 35.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('987987987', 30, 5.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('987654321', 30, 20.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('987654321', 20, 15.0);
INSERT INTO `COMPANYX`.`WORKS_ON` (`Essn`, `Pno`, `Hours`) VALUES ('888665555', 20, 10);

COMMIT;


-- -----------------------------------------------------
-- Data for table `COMPANYX`.`DEPENDENT`
-- -----------------------------------------------------
START TRANSACTION;
USE `COMPANYX`;
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('333445555', 'Alice', 'F', '1986-04-05', 'Daughter');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('333445555', 'Theodore', 'M', '1983-10-25', 'Son');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('333445555', 'Joy', 'F', '1958-05-03', 'Spouse');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('987654321', 'Abner', 'M', '1942-02-28', 'Spouse');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('123456789', 'Michael', 'M', '1988-01-04', 'Son');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('123456789', 'Alice', 'F', '1988-12-30', 'Daughter');
INSERT INTO `COMPANYX`.`DEPENDENT` (`Essn`, `Dependent_name`, `Sex`, `Bdate`, `Relationship`) VALUES ('123456789', 'Elizabeth', 'F', '1967-05-05', 'Spouse');

COMMIT;