SQL 40
Sean Forward Engineer Guest on 18th November 2021 04:02:57 AM
  1. -- these tables need to be inserted with foreign key checks off
  2.  
  3. -- MySQL Workbench Forward Engineering
  4.  
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. 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';
  8.  
  9. -- -----------------------------------------------------
  10. -- Schema mydb
  11. -- -----------------------------------------------------
  12. -- -----------------------------------------------------
  13. -- Schema azziedev_seandb
  14. -- -----------------------------------------------------
  15.  
  16. -- -----------------------------------------------------
  17. -- Schema azziedev_seandb
  18. -- -----------------------------------------------------
  19. CREATE SCHEMA IF NOT EXISTS `azziedev_seandb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
  20. USE `azziedev_seandb` ;
  21.  
  22. -- -----------------------------------------------------
  23. -- Table `azziedev_seandb`.`EMPLOYEE`
  24. -- -----------------------------------------------------
  25. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`EMPLOYEE` (
  26.   `FNAME` VARCHAR(45) NULL DEFAULT NULL,
  27.   `MINIT` VARCHAR(45) NULL DEFAULT NULL,
  28.   `LNAME` VARCHAR(45) NULL DEFAULT NULL,
  29.   `SSN` INT(9) NOT NULL,
  30.   `BDATE` DATE NULL DEFAULT NULL,
  31.   `ADDRESS` VARCHAR(45) NULL DEFAULT NULL,
  32.   `SEX` VARCHAR(45) NULL DEFAULT NULL,
  33.   `SALARY` INT(8) NULL DEFAULT NULL,
  34.   `SUPERSSN` INT(9) NULL DEFAULT NULL,
  35.   `DNO` INT(1) NULL DEFAULT NULL,
  36.   PRIMARY KEY (`SSN`),
  37.   INDEX `SUPERSSN_idx` (`SUPERSSN` ASC),
  38.   INDEX `DNO_idx` (`DNO` ASC),
  39.   CONSTRAINT `DNO`
  40.     FOREIGN KEY (`DNO`)
  41.     REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
  42.     ON DELETE NO ACTION
  43.     ON UPDATE NO ACTION,
  44.   CONSTRAINT `SUPERSSN`
  45.     FOREIGN KEY (`SUPERSSN`)
  46.     REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
  47.     ON DELETE NO ACTION
  48.     ON UPDATE NO ACTION)
  49. ENGINE = InnoDB
  50. DEFAULT CHARACTER SET = utf8
  51. COLLATE = utf8_unicode_ci;
  52.  
  53.  
  54. -- -----------------------------------------------------
  55. -- Table `azziedev_seandb`.`DEPARTMENT`
  56. -- -----------------------------------------------------
  57. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPARTMENT` (
  58.   `DNAME` VARCHAR(45) NULL DEFAULT NULL,
  59.   `DNUMBER` INT(1) NOT NULL,
  60.   `MGRSSN` INT(9) NULL DEFAULT NULL,
  61.   `MGRSTARTDATE` DATE NULL DEFAULT NULL,
  62.   PRIMARY KEY (`DNUMBER`),
  63.   INDEX `MGRSSN_idx` (`MGRSSN` ASC),
  64.   CONSTRAINT `MGRSSN`
  65.     FOREIGN KEY (`MGRSSN`)
  66.     REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
  67.     ON DELETE NO ACTION
  68.     ON UPDATE NO ACTION)
  69. ENGINE = InnoDB
  70. DEFAULT CHARACTER SET = utf8
  71. COLLATE = utf8_unicode_ci;
  72.  
  73.  
  74. -- -----------------------------------------------------
  75. -- Table `azziedev_seandb`.`DEPENDENT`
  76. -- -----------------------------------------------------
  77. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPENDENT` (
  78.   `ESSN` INT(9) NOT NULL,
  79.   `DEPENDENT_NAME` VARCHAR(45) NOT NULL,
  80.   `SEX` VARCHAR(45) NULL DEFAULT NULL,
  81.   `BDATE` DATE NULL DEFAULT NULL,
  82.   `RELATIONSHIP` VARCHAR(45) NULL DEFAULT NULL,
  83.   PRIMARY KEY (`ESSN`, `DEPENDENT_NAME`),
  84.   CONSTRAINT `ESSN`
  85.     FOREIGN KEY (`ESSN`)
  86.     REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
  87.     ON DELETE NO ACTION
  88.     ON UPDATE NO ACTION)
  89. ENGINE = InnoDB
  90. DEFAULT CHARACTER SET = utf8
  91. COLLATE = utf8_unicode_ci;
  92.  
  93.  
  94. -- -----------------------------------------------------
  95. -- Table `azziedev_seandb`.`DEPT_LOCATIONS`
  96. -- -----------------------------------------------------
  97. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`DEPT_LOCATIONS` (
  98.   `DNUMBER` INT(1) NOT NULL,
  99.   `DLOCATION` VARCHAR(45) NOT NULL,
  100.   PRIMARY KEY (`DNUMBER`, `DLOCATION`),
  101.   INDEX `DEPT_idx` (`DNUMBER` ASC),
  102.   CONSTRAINT `DEPT`
  103.     FOREIGN KEY (`DNUMBER`)
  104.     REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
  105.     ON DELETE NO ACTION
  106.     ON UPDATE NO ACTION)
  107. ENGINE = InnoDB
  108. DEFAULT CHARACTER SET = utf8
  109. COLLATE = utf8_unicode_ci;
  110.  
  111.  
  112. -- -----------------------------------------------------
  113. -- Table `azziedev_seandb`.`PROJECT`
  114. -- -----------------------------------------------------
  115. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`PROJECT` (
  116.   `PNAME` VARCHAR(45) NULL DEFAULT NULL,
  117.   `PNUMBER` INT(2) NOT NULL,
  118.   `PLOCATION` VARCHAR(45) NULL DEFAULT NULL,
  119.   `DNUM` INT(1) NULL DEFAULT NULL,
  120.   PRIMARY KEY (`PNUMBER`),
  121.   INDEX `DNUM_idx` (`DNUM` ASC),
  122.   CONSTRAINT `DNUM`
  123.     FOREIGN KEY (`DNUM`)
  124.     REFERENCES `azziedev_seandb`.`DEPARTMENT` (`DNUMBER`)
  125.     ON DELETE NO ACTION
  126.     ON UPDATE NO ACTION)
  127. ENGINE = InnoDB
  128. DEFAULT CHARACTER SET = utf8
  129. COLLATE = utf8_unicode_ci;
  130.  
  131.  
  132. -- -----------------------------------------------------
  133. -- Table `azziedev_seandb`.`WORKS_ON`
  134. -- -----------------------------------------------------
  135. CREATE TABLE IF NOT EXISTS `azziedev_seandb`.`WORKS_ON` (
  136.   `ESSN` INT(9) NOT NULL,
  137.   `PNO` INT(2) NOT NULL,
  138.   `HOURS` DECIMAL(3,1) NULL DEFAULT NULL,
  139.   PRIMARY KEY (`ESSN`, `PNO`),
  140.   INDEX `PNOw_idx` (`PNO` ASC),
  141.   CONSTRAINT `ESSNw`
  142.     FOREIGN KEY (`ESSN`)
  143.     REFERENCES `azziedev_seandb`.`EMPLOYEE` (`SSN`)
  144.     ON DELETE NO ACTION
  145.     ON UPDATE NO ACTION,
  146.   CONSTRAINT `PNOw`
  147.     FOREIGN KEY (`PNO`)
  148.     REFERENCES `azziedev_seandb`.`PROJECT` (`PNUMBER`)
  149.     ON DELETE NO ACTION
  150.     ON UPDATE NO ACTION)
  151. ENGINE = InnoDB
  152. DEFAULT CHARACTER SET = utf8
  153. COLLATE = utf8_unicode_ci;
  154.  
  155.  
  156.  
  157.  
  158.  
  159.  
  160. -- these need to be inserted with foreign key checks off
  161. INSERT INTO `EMPLOYEE` VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Houston TX', 'M', '25000', NULL, '1');
  162. INSERT INTO `EMPLOYEE` VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss Houston TX', 'M', '40000', '888665555', '5');
  163. INSERT INTO `EMPLOYEE` VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry Bellaire TX', 'F', '43000', '888665555', '4');
  164.  
  165. SET SQL_MODE=@OLD_SQL_MODE;
  166. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  167. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  168.  
  169.  
  170. -- these values don't need foreign key checks disabled
  171.  
  172. INSERT INTO `DEPARTMENT` VALUES ('Headquarters', '1', '888665555', '1981-06-19');
  173. INSERT INTO `DEPARTMENT` VALUES ('Research', '5', '333445555', '1988-05-22');
  174. INSERT INTO `DEPARTMENT` VALUES ('Administration', '4', '987654321', '1995-01-01');
  175. INSERT INTO `EMPLOYEE` VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren Houston TX', 'M', '30000', '333445555', '5');
  176. INSERT INTO `EMPLOYEE` VALUES ('Ramesh', 'K', 'Narayan', '666884444', '1962-09-16', '975 Fire Oak Humble TX', 'M', '38000', '333445555', '5');
  177. INSERT INTO `EMPLOYEE` VALUES ('Joyce', 'A', 'English', '453453453', '1972-07-31', '5631 Rice Houston TX', 'F', '25000', '333445555', '5');
  178. INSERT INTO `EMPLOYEE` VALUES ('Alicia', 'J', 'Zelaya', '999887777', '1968-07-19', '3321 Castle Spring TX', 'F', '25000', '987654321', '4');
  179. INSERT INTO `EMPLOYEE` VALUES ('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', '980 Dallas Houston TX', 'M', '25000', '987654321', '4');
  180. INSERT INTO `DEPENDENT` VALUES ('333445555', 'Alice', 'F', '1986-04-05', 'DAUGHTER');
  181. INSERT INTO `DEPENDENT` VALUES ('333445555', 'Theodore', 'M', '1983-10-25', 'SON');
  182. INSERT INTO `DEPENDENT` VALUES ('333445555', 'Joy', 'F', '1958-05-03', 'SPOUSE');
  183. INSERT INTO `DEPENDENT` VALUES ('987654321', 'Abner', 'M', '1942-02-28', 'SPOUSE');
  184. INSERT INTO `DEPENDENT` VALUES ('123456789', 'Michael', 'M', '1988-01-04', 'SON');
  185. INSERT INTO `DEPENDENT` VALUES ('123456789', 'Alice', 'F', '1988-12-30', 'DAUGHTER');
  186. INSERT INTO `DEPENDENT` VALUES ('123456789', 'Elizabeth', 'F', '1967-05-05', 'SPOUSE');
  187. INSERT INTO `DEPT_LOCATIONS` VALUES ('1', 'Houston');
  188. INSERT INTO `DEPT_LOCATIONS` VALUES ('4', 'Stafford');
  189. INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Bellaire');
  190. INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Sugarland');
  191. INSERT INTO `DEPT_LOCATIONS` VALUES ('5', 'Houston');
  192. INSERT INTO `PROJECT` VALUES ('ProductX', '1', 'Bellaire', '5');
  193. INSERT INTO `PROJECT` VALUES ('ProductY', '2', 'Sugarland', '5');
  194. INSERT INTO `PROJECT` VALUES ('ProductZ', '3', 'Houston', '5');
  195. INSERT INTO `PROJECT` VALUES ('Computerization', '10', 'Stafford', '4');
  196. INSERT INTO `PROJECT` VALUES ('Reorganization', '20', 'Houston', '1');
  197. INSERT INTO `PROJECT` VALUES ('Newbenefits', '30', 'Stafford', '4');
  198. INSERT INTO `WORKS_ON` VALUES ('123456789', '1', '32.5');
  199. INSERT INTO `WORKS_ON` VALUES ('123456789', '2', '7.5');
  200. INSERT INTO `WORKS_ON` VALUES ('666884444', '3', '40.0');
  201. INSERT INTO `WORKS_ON` VALUES ('453453453', '1', '20.0');
  202. INSERT INTO `WORKS_ON` VALUES ('453453453', '2', '20.0');
  203. INSERT INTO `WORKS_ON` VALUES ('333445555', '2', '10.0');
  204. INSERT INTO `WORKS_ON` VALUES ('333445555', '3', '10.0');
  205. INSERT INTO `WORKS_ON` VALUES ('333445555', '10', '10.0');
  206. INSERT INTO `WORKS_ON` VALUES ('333445555', '20', '10.0');
  207. INSERT INTO `WORKS_ON` VALUES ('999887777', '30', '30.0');
  208. INSERT INTO `WORKS_ON` VALUES ('999887777', '10', '10.0');
  209. INSERT INTO `WORKS_ON` VALUES ('987987987', '10', '35.0');
  210. INSERT INTO `WORKS_ON` VALUES ('987987987', '30', '5.0');
  211. INSERT INTO `WORKS_ON` VALUES ('987654321', '30', '20.0');
  212. INSERT INTO `WORKS_ON` VALUES ('987654321', '20', '15.0');
  213. INSERT INTO `WORKS_ON` VALUES ('888665555', '20', NULL);

Paste is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.

Copyright © 2026 Paste. All rights reserved.
Powered by Paste