5s6zxBfa

1Create schema LAB5;
2use LAB5;
3SET GLOBAL log_bin_trust_function_creators = 1;
4
5create table Department (
6	Dnumber INT Primary key Not Null,
7    Dname varchar(50),
8    Founded date,
9    Mgr_ssn INT,
10    Budget float
11);
12
13create table Employee (
14	Ssn INT primary key Not null,
15    Ename Varchar(50),
16    Bdate date,
17    Dno INT,
18    Salary float,
19    constraint e_fk Foreign key (Dno) references Department(Dnumber)
20);
21
22
23alter table Department add constraint d_fk Foreign key (mgr_ssn) references Employee(ssn);
24
25
26DELIMITER $$
27DROP TRIGGER IF EXISTS number_of_employees$$
28CREATE TRIGGER number_of_employees
29    BEFORE INSERT ON employee FOR EACH ROW
30    BEGIN
31        declare number_of_employees INT default 0;
32        select lab5.Count_Emp(new.Dno) into number_of_employees;
33        IF  number_of_employees = 8 THEN
34            signal sqlstate '45000' set message_text = 'Error: Department already have 8 employees.';
35        END IF;
36    END$$
37DELIMITER ;
38
39
40DELIMITER $$
41DROP TRIGGER IF EXISTS cascade_on_update_department$$
42CREATE TRIGGER cascade_on_update_department
43    after update ON department FOR EACH ROW
44    BEGIN
45        if (new.Dnumber != old.Dnumber) then
46			update employee set Dno = New.Dnumber where Dno = Old.Dnumber;
47		end if;
48    END$$
49DELIMITER ;
50
51set FOREIGN_KEY_CHECKS = 0;
52insert into employee values (1, 'Ahmed', curdate(), 1, 1000);
53insert into employee values (2, 'Mohamed', curdate(), 1, 2000);
54insert into employee values (3, 'Mohsen', curdate(), 1, 3501);
55insert into employee values (4, 'Ibrahem', curdate(), 1, 14512);
56insert into employee values (5, 'Nehmdo', curdate(), 1, 2235.4);
57insert into employee values (6, 'Moustafa', curdate(), 1, 65162);
58insert into employee values (7, 'Abdela', curdate(), 1, 23211);
59insert into employee values (8, 'Khaled', curdate(), 1, 1505);
60insert into employee values (9, 'Sma3en', curdate(), 1, 2354.6);
61set FOREIGN_KEY_CHECKS = 1;
62
63insert into department values (1, 'Computer', '1159-12-12', 6, 1235444);
64call Check_Founded();
65select * from department;
66
67set FOREIGN_KEY_CHECKS = 0;
68update department set Dnumber = 2 where Dnumber = 1;
69set FOREIGN_KEY_CHECKS = 1;
70select * from employee;
71
72call Raise_Salary(1, 1000000);
73select * from employee;
74
75
76set FOREIGN_KEY_CHECKS = 0;
77insert into department values (1, 'Electrical', curdate(), 9, 1356548);
78insert into employee values(9, 'Sma3en', curdate(), 1, 16546);
79set FOREIGN_KEY_CHECKS = 1;
80
81select lab5.Count_Emp(1);
82select lab5.Count_Emp(2);