· 8 years ago · May 07, 2018, 09:08 PM
1-- MySQL Workbench Forward Engineering
2
3DROP SCHEMA IF EXISTS DEPT_EMPLOYEE;
4SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
5SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
6SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
7
8-- -----------------------------------------------------
9-- Schema DEPT_EMPLOYEE
10-- -----------------------------------------------------
11
12-- -----------------------------------------------------
13-- Schema DEPT_EMPLOYEE
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `DEPT_EMPLOYEE` DEFAULT CHARACTER SET utf8 ;
16USE `DEPT_EMPLOYEE` ;
17
18-- -----------------------------------------------------
19-- Table `DEPT_EMPLOYEE`.`EMPLOYEE`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `DEPT_EMPLOYEE`.`EMPLOYEE` (
22 `SSN` VARCHAR(10) NOT NULL,
23 `ENAME` VARCHAR(45) NOT NULL,
24 `BDATE` DATE NULL,
25 `SALARY` FLOAT NOT NULL,
26 `DNO` INT NOT NULL,
27 PRIMARY KEY (`SSN`),
28 UNIQUE INDEX `SSN_UNIQUE` (`SSN` ASC),
29 INDEX `fk_EMPLOYEE_DEPT1_idx` (`DNO` ASC),
30 CONSTRAINT `fk_EMPLOYEE_DEPT1`
31 FOREIGN KEY (`DNO`)
32 REFERENCES `DEPT_EMPLOYEE`.`DEPT` (`DNUMBER`)
33 ON DELETE NO ACTION
34 ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `DEPT_EMPLOYEE`.`DEPT`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `DEPT_EMPLOYEE`.`DEPT` (
42 `DNUMBER` INT NOT NULL,
43 `DNAME` VARCHAR(45) NOT NULL,
44 `FOUNDED` DATE NOT NULL,
45 `BUDGET` FLOAT NOT NULL,
46 `MGR_SSN` VARCHAR(10) NOT NULL,
47 PRIMARY KEY (`DNUMBER`),
48 UNIQUE INDEX `DNAME_UNIQUE` (`DNAME` ASC),
49 UNIQUE INDEX `DNUMBER_UNIQUE` (`DNUMBER` ASC),
50 INDEX `fk_DEPT_EMPLOYEE_idx` (`MGR_SSN` ASC),
51 CONSTRAINT `fk_DEPT_EMPLOYEE`
52 FOREIGN KEY (`MGR_SSN`)
53 REFERENCES `DEPT_EMPLOYEE`.`EMPLOYEE` (`SSN`)
54 ON DELETE NO ACTION
55 ON UPDATE CASCADE)
56ENGINE = InnoDB;
57
58USE `DEPT_EMPLOYEE`;
59
60DELIMITER $$
61USE `DEPT_EMPLOYEE`$$
62CREATE DEFINER = CURRENT_USER TRIGGER `DEPT_EMPLOYEE`.`LESS_THAN_EIGHT` BEFORE INSERT ON `EMPLOYEE` FOR EACH ROW
63BEGIN
64 IF((SELECT COUNT(*) FROM EMPLOYEE WHERE DNO = NEW.DNO) > 8) THEN
65 SIGNAL SQLSTATE '45000';
66 END IF;
67END$$
68
69USE `DEPT_EMPLOYEE`$$
70CREATE DEFINER = CURRENT_USER TRIGGER `DEPT_EMPLOYEE`.`SALARY_RAISE` BEFORE UPDATE ON `EMPLOYEE` FOR EACH ROW
71BEGIN
72 SET SQL_SAFE_UPDATES = 0;
73 UPDATE EMPLOYEE SET Salary = NEW.Salary WHERE Ssn IN (
74 SELECT Ssn FROM DEPT JOIN EMPLOYEE ON Mgr_ssn = Ssn
75 WHERE Dnumber = NEW.Dno AND Salary < NEW.Salary);
76 SET SQL_SAFE_UPDATES = 1;
77END$$
78
79USE `DEPT_EMPLOYEE`$$
80CREATE DEFINER = CURRENT_USER TRIGGER `DEPT_EMPLOYEE`.`CASCADE_ON_UPDATE` BEFORE UPDATE ON `DEPT` FOR EACH ROW
81BEGIN
82 IF(NEW.DNUMBER != OLD.DNUMBER) THEN
83 UPDATE EMPLOYEE SET DNO = NEW.DNUMBER WHERE DNO = OLD.DNUMBER;
84 END IF;
85END$$
86
87
88DELIMITER ;
89
90DELIMITER $$
91USE `DEPT_EMPLOYEE`$$
92CREATE FUNCTION COUNT_EMP (DNUMBER INT)
93RETURNS INT
94BEGIN
95 DECLARE RESULT INT;
96 SET RESULT = (SELECT COUNT(SSN) FROM EMPLOYEE WHERE DNO = DNUMBER);
97 RETURN RESULT;
98END$$
99
100DELIMITER ;
101
102DELIMITER $$
103USE `DEPT_EMPLOYEE`$$
104CREATE PROCEDURE YEAR_CHECKS()
105BEGIN
106 UPDATE DEPT SET FOUNDED = '1960-01-01' WHERE YEAR(FOUNDED) < 1960 ;
107END$$
108
109DELIMITER ;
110
111SET SQL_MODE=@OLD_SQL_MODE;
112SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
113SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
114
115INSERT INTO EMPLOYEE VALUES('1', 'MOHAMMED', '1996-06-16', 200.2, '1');
116INSERT INTO EMPLOYEE VALUES('2', 'MOHAMMED', '1996-06-16', 200.2, '1');
117INSERT INTO DEPT VALUES('1', 'COMPUTER', '1996-06-16', 200.2, '1');
118
119select * from employee;
120
121SELECT COUNT_EMP(1);