· 8 years ago · May 08, 2018, 12:18 AM
1-- created by Amr Hendy
2-- Date : 2018-05-08
3-- user : hendy
4
5DROP SCHEMA IF EXISTS LAB5;
6CREATE SCHEMA LAB5;
7USE LAB5;
8
9CREATE TABLE IF NOT EXISTS DEPT(
10 DNUMBER INT,
11 DNAME VARCHAR(50) DEFAULT 'HENDY DEPT',
12 FOUNDED DATE DEFAULT '2018-05-08',
13 MGR_SSN INT,
14 BUDGET INT DEFAULT 10,
15 PRIMARY KEY(DNUMBER)
16);
17
18CREATE TABLE IF NOT EXISTS EMPLOYEE(
19 SSN INT,
20 ENAME VARCHAR(50) DEFAULT 'HENDY',
21 BDATE DATE DEFAULT '2018-05-08',
22 DNO INT,
23 SALARY INT,
24 PRIMARY KEY(SSN)
25);
26
27ALTER TABLE DEPT ADD CONSTRAINT FK_DEPT FOREIGN KEY(MGR_SSN) REFERENCES EMPLOYEE(SSN) ON UPDATE CASCADE ON DELETE CASCADE;
28ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE FOREIGN KEY(DNO) REFERENCES DEPT(DNUMBER) ON UPDATE CASCADE ON DELETE CASCADE;
29
30
31-- stored function to get the number of employees of certain department
32
33DROP FUNCTION IF EXISTS COUNT_EMP;
34DELIMITER $$
35CREATE FUNCTION COUNT_EMP(DNUMBER_ INT) RETURNS INT NOT DETERMINISTIC
36BEGIN
37 DECLARE EMPLOYEE_CNT INT;
38 SELECT COUNT(*) INTO EMPLOYEE_CNT FROM EMPLOYEE AS E
39 WHERE E.DNO = DNUMBER_;
40 RETURN(EMPLOYEE_CNT);
41END$$
42
43
44
45-- stored procedure that ensures that Year(DEPT.Founded) >=1960 for
46-- all departments; if a row violates this constraint then
47-- set its date to be ’01-JAN-1960
48DROP PROCEDURE IF EXISTS VALIDATE_YEAR$$
49DELIMITER $$
50CREATE PROCEDURE VALIDATE_YEAR()
51BEGIN
52 UPDATE DEPT
53 SET FOUNDED = '1960-01-01'
54 WHERE YEAR(FOUNDED) < 1960;
55END$$
56
57
58-- a trigger to ensure that no department has more than 8 employees
59-- Notice that we use FOR EACH ROW to check the matched rows only not all rows of the table.
60DROP TRIGGER IF EXISTS BEFORE_INSERT_EMP$$
61CREATE TRIGGER BEFORE_INSERT_EMP BEFORE INSERT ON EMPLOYEE FOR EACH ROW
62BEGIN
63 DECLARE DEPT_NUM INT;
64 DECLARE TOTAL_EMP_CNT INT;
65 SET DEPT_NUM = NEW.DNO;
66 SELECT COUNT(*) INTO TOTAL_EMP_CNT FROM EMPLOYEE
67 WHERE DNO = DEPT_NUM;
68 IF TOTAL_EMP_CNT >= 8
69 THEN
70 SIGNAL SQLSTATE '45000'
71 SET MESSAGE_TEXT = 'INVALID INSERT AS MAX OF DEP IS 8 EMPLOYEES';
72 END IF;
73END$$
74
75
76
77-- a trigger to implement "ON UPDATE CASCADE" for the foreign key EMPLOYEE.Dno
78DROP TRIGGER IF EXISTS ON_UPDATE_CASCADE_IMPLEMENTATION$$
79CREATE TRIGGER ON_UPDATE_CASCADE_IMPLEMENTATION AFTER UPDATE ON DEPT FOR EACH ROW
80BEGIN
81 -- USE <=> for safe checking for null then use ! outside
82 -- As there is no !<=> operator in my sql
83 -- Notice that we can do that trigger easily in oracle by using a certain column by:
84 -- AFTER UPDATE OF DNUMBER ON DEPT
85 IF !(NEW.DNUMBER <=> OLD.DNUMBER)
86 THEN
87 UPDATE EMPLOYEE
88 SET DNO = NEW.DNUMBER
89 WHERE DNO = OLD.DNUMBER;
90 END IF;
91END$$
92
93
94
95-- a trigger to ensure that whenever an employee is given a raise in salary,
96-- his department manager's salary must be increased to be at least as much.
97DROP TRIGGER IF EXISTS AFTER_UPDATE_SALARY$$
98CREATE TRIGGER AFTER_UPDATE_SALARY AFTER UPDATE ON EMPLOYEE FOR EACH ROW
99BEGIN
100 DECLARE MGR_SALARY INT;
101 DECLARE MGR_ID INT;
102 DECLARE DEPT_ID INT;
103 -- USE <=> for safe checking for null then use ! outside
104 -- As there is no !<=> operator in my sql
105 IF !(NEW.SALARY <=> OLD.SALARY) AND NEW.SALARY > OLD.SALARY
106 THEN
107 SET DEPT_ID = OLD.DNO;
108 SELECT MGR_SSN INTO MGR_ID FROM DEPT WHERE DNUMBER = DEPT_ID;
109 SELECT SALARY INTO MGR_SALARY FROM EMPLOYEE WHERE SSN = MGR_ID;
110
111 IF NEW.SSN != MGR_ID AND MGR_SALARY < NEW.SALARY THEN
112 CALL UPDATE_SALARY_OUTSIDE(NEW.SALARY, MGR_ID);
113 END IF;
114 END IF;
115END$$
116
117
118
119DROP PROCEDURE IF EXISTS UPDATE_SALARY_OUTSIDE$$
120DELIMITER $$
121CREATE PROCEDURE UPDATE_SALARY_OUTSIDE(IN NEW_SALARY INT, IN MGR_ID INT)
122BEGIN
123 UPDATE EMPLOYEE
124 SET SALARY = NEW_SALARY
125 WHERE SSN = MGR_ID;
126END$$
127
128
129DELIMITER ;
130
131-- INSERT SOME DATA
132INSERT INTO DEPT (DNUMBER, FOUNDED)
133VALUES (1, '1970-05-01'),
134 (2, '1950-11-01');
135
136INSERT INTO EMPLOYEE (SSN, SALARY, DNO)
137VALUES (1, 20, 1),
138 (2, 21, 2),
139 (3, 22, 2);
140
141UPDATE DEPT SET MGR_SSN = 2 WHERE DNUMBER = 1;
142UPDATE DEPT SET MGR_SSN = 3 WHERE DNUMBER = 2;
143
144SELECT * FROM EMPLOYEE;
145SELECT * FROM DEPT;
146
147
148-- TESTING
149
150-- Q1)
151SELECT DNUMBER, COUNT_EMP(DNUMBER) FROM DEPT GROUP BY DNUMBER;
152
153-- Q2)
154CALL VALIDATE_YEAR();
155SELECT * FROM DEPT;
156
157-- Q3)
158INSERT INTO EMPLOYEE (SSN, SALARY, DNO)
159VALUES (4, 40, 2),
160 (5, 40, 2),
161 (6, 40, 2),
162 (7, 40, 2),
163 (8, 40, 2),
164 (9, 40, 2);
165
166-- now department 2 has 8 employees so we will get error for new insert with dno = 2
167INSERT INTO EMPLOYEE (SSN, SALARY, DNO)
168VALUES (4, 40, 2);
169
170-- remove the testing rows as they are redundant
171DELETE FROM EMPLOYEE WHERE SSN >= 4;
172SELECT * FROM EMPLOYEE;
173
174
175-- Q4)
176UPDATE DEPT SET DNUMBER = 200 WHERE DNUMBER = 1;
177-- the dno with 1 will be 200, check that in EMPLOYEE table
178SELECT * FROM EMPLOYEE;
179-- return the prev state of DEPT table
180UPDATE DEPT SET DNUMBER = 1 WHERE DNUMBER = 200;
181
182
183-- Q5)
184UPDATE EMPLOYEE SET SALARY = 200 WHERE SSN = 1;
185SELECT * FROM EMPLOYEE;