· 8 years ago · Mar 10, 2018, 02:28 PM
1create table employees (
2E_ID INT(5) primary key,
3E_Name varchar(30),
4Hiredate date,
5mgr int(5),
6Seniority char(6));##checked
7
8delimiter //
9//
10create trigger bin1_employees before insert on employees
11for each row
12begin
13 if new.Hiredate >= '2016-11-2' then set new.Seniority = 'newbie';
14 end if;
15 if new.Hiredate between '2012-11-2' and '2016-11-1' then set new.Seniority = 'junior';
16 end if;
17 if new.Hiredate <= '2012-11-1' then set new.Seniority = 'senior';
18 end if;
19end//
20
21create table products (
22P_Name varchar(30),
23P_Version varchar(30),
24P_Status enum('ready','usable','not-ready') default 'not-ready',
25primary key (P_Name, P_Version));##checked
26
27//
28create table components (
29C_Name varchar(30),
30C_Version char(3),
31Size int,
32Programming_Language enum ('C','C++','C#','Java','Javascript','PHP'),
33Components_Status enum('ready','usable','not-ready') default 'not-ready',
34Primary key (C_Name, C_Version)##checked
35);
36//
37//
38create table build (
39P_Name varchar(30),
40P_Version varchar(30),
41C_Name varchar(30),
42C_Version char(3),
43foreign key (P_name,P_version) references products(P_Name,P_Version),
44foreign key (C_Name,C_Version) references components(C_Name, C_Version));##checked
45//
46//
47create table inspections (
48C_Name varchar(30),
49C_Version char(3),
50inspection_date date,
51by_who INT(5),
52score int,
53description varchar(4000),
54foreign key(by_who) references employees(E_ID),
55foreign key(C_Name,C_Version) references components(C_Name,C_Version) ##checked
56);
57
58//
59create table own(
60C_Name varchar(30),
61C_Version char(3),
62E_ID INT(5),
63Primary key (C_Name, C_Version),
64foreign key (E_ID) references employees(E_ID),
65foreign key(C_Name,C_Version) references components(C_Name, C_Version)); ##checked
66//
67
68delimiter //
69create function Components_Status(score int)
70returns enum('ready','usable','not-ready')
71begin
72 if score > 90 then return 'ready';
73 elseif score < 75 then return 'not-ready';
74 else return 'usable';
75 end if;
76end//
77
78delimiter //
79drop trigger if exists bin1_components//
80create trigger bin1_components after insert on inspections
81for each row
82begin
83 update components, inspections
84 set components.Components_Status = Components_Status(new.Score)
85 where components.C_Name = new.C_Name
86 and components.C_Version = new.C_Version
87 and new.inspection_date =
88 (select max(inspection_date) from inspections
89 where inspections.C_Name = new.C_Name
90 and inspections.C_Version = new.C_Version);
91
92 call procedure_product1();
93end//
94
95drop procedure if exists procedure_product1//
96create Procedure procedure_product1()
97begin
98 declare i int default 0;
99 set @m = (select count(distinct P_Name,P_Version) from products);
100 loop1 : LOOP
101 if i=@m then leave loop1;
102 end if;
103 set @N = (select products.P_Name from products limit i,1);
104 set @V = (select products.P_Version from products limit i,1);
105 update products
106 set products.P_Status = 'ready'
107 where products.P_Name = @N
108 and products.P_Version = @V;
109 call procedure_product2(@N,@V);
110 set i = i+1;
111 end loop;
112end //
113
114delimiter //
115drop procedure if exists procedure_product2//
116create Procedure procedure_product2(N varchar(30), V varchar(30))
117begin
118 declare s1,s2 varchar(30);
119 declare j int default 0;
120 set@o =
121 (select count(*) from build
122 where build.P_Name = N and build.P_Version = V);
123 set@PS =
124 (select products.P_Status from products
125 where products.P_Name = N and products.P_Version = V);
126 loop2 : LOOP
127 if j = @o then leave loop2;
128 end if;
129 set @CN =
130 (select build.C_Name from build
131 where build.P_Name = N and build.P_Version = V limit j,1);
132 set @CV =
133 (select Build.C_Version from build
134 where build.P_Name = N and build.P_Version = V limit j,1);
135 set @CS =
136 (select components.Components_Status from components
137 where components.C_Name = @CN
138 and components.C_Version = @CV);
139 if (@CS = 'not-ready') then
140 set @PS = 'not-ready';
141 elseif(@CS = 'usable' and @PS = 'ready') then
142 set @PS = 'usable';
143 end if;
144 set j=j+1;
145 end loop;
146 update products
147 set products.P_Status = @PS
148 where products.P_Name = N
149 and products.P_Version = V;
150end //
151
152
153
154//
155##Insert Values
156insert into products(P_Name,P_Version,P_Status) values
157('Excel','2010','not-ready'),
158('Excel','2015','not-ready'),
159('Excel','2018beta','not-ready'),
160('Excel','secret','not-ready');
161
162//
163insert into components
164(C_Name, C_Version, Size, Programming_Language,Components_Status) values
165('Keyboard Driver','K11','1200','C','not-ready'),
166('Touch Screen Driver','T00','4000','C++','not-ready'),
167('Dbase Interface','D00','2500','C++','not-ready'),
168('Dbase Interface','D01','2500','C++','not-ready'),
169('Chart Generator','C11','6500','Java','not-ready'),
170('Pen Driver','P01','3575','C','not-ready'),
171('Math Unit','A01','5000','C','not-ready'),
172('Math Unit','A02','3500','Java','not-ready');
173
174//
175#Problem 9 (Phase 3)
176insert into components values
177('Dynamic Table Interface','D01','775','Javascript','not-ready');
178
179//
180insert into build values
181('Excel','2010','Keyboard Driver','K11'),
182('Excel','2010','Dbase Interface','D00'),
183('Excel','2015','Keyboard Driver','K11'),
184('Excel','2015','Dbase Interface','D01'),
185('Excel','2015','Pen Driver','P01'),
186('Excel','2018beta','Keyboard Driver','K11'),
187('Excel','2018beta','Touch Screen Driver','T00'),
188('Excel','2018beta','Chart Generator','C11'),
189('Excel','secret','Keyboard Driver','K11'),
190('Excel','secret','Touch Screen Driver','T00'),
191('Excel','secret','Chart Generator','C11'),
192('Excel','secret','Math Unit','A02');
193
194//
195##Problem 9 (Phase 3)
196insert into build values
197('Excel','2018beta','Dynamic Table Interface','D01');
198
199//
200insert into employees(E_ID, E_Name,Hiredate,mgr) values
201('10100','Employee-1','1984-11-08','10100'),
202('10200','Employee-2','1994-11-08','10100'),
203('10300','Employee-3','2004-11-08','10200'),
204('10400','Employee-4','2008-11-01','10200'),
205('10500','Employee-5','2015-11-01','10400'),
206('10600','Employee-6','2015-11-01','10400'),
207('10700','Employee-7','2016-11-01','10400'),
208('10800','Employee-8','2017-11-01','10200');
209
210//
211insert into own values
212('Keyboard Driver','K11','10100'),
213('Touch Screen Driver','T00','10100'),
214('Dbase Interface','D00','10200'),
215('Dbase Interface','D01','10300'),
216('Chart Generator','C11','10200'),
217('Pen Driver','P01','10700'),
218('Math Unit','A01','10200'),
219('Math Unit','A02','10200');
220
221//
222insert into inspections (C_Name,C_Version,inspection_date,by_who,score,description) values
223('Keyboard Driver','K11','2010-02-14','10100','100','legacy code which is already approved'),
224('Touch Screen Driver','T00','2017-06-01','10200','95','initial release ready for usage'),
225('Dbase Interface','D00','2010-02-22','10100','55','too many hard coded parameters, the software must be more maintainable and configurable because we want to use this in other '),
226('Dbase Interface','D00','2010-02-24','10100','78','improved, but only handles DB2 format'),
227('Dbase Interface','D00','2010-02-26','10100','95','Okay, handles DB3 format.'),
228('Dbase Interface','D00','2010-02-28','10100','100','satisifed'),
229('Dbase Interface','D01','2011-05-01','10200','100','Okay ready for use'),
230('Pen Driver','P01','2017-07-15','10300','80','Okay ready for beta testing'),
231('Math Unit','A01','2014-06-10','10100','90','almost ready'),
232('Math Unit','A02','2014-06-15','10100','70','Accuracy problems!'),
233('Math Unit','A02','2014-06-30','10100','100','Okay problems fixed'),
234('Math Unit','A02','2016-11-02','10700','100','re-review for new employee to gain experience in the process.');
235
236//
237##Problem 2 (Phase 3)
238select components.C_Name, components.C_Version, Components_Status from components
239where components.Components_Status = 'not-ready';
240//
241##Problem 3 (Phase 3)
242select components.C_Name,components.C_Version from components
243where components.C_Name not in (select inspections.C_Name from inspections)
244or components.C_Version not in (select inspections.C_Version from inspections);
245//
246//
247##Problem 4 (Phase 3)
248select count(components.C_Name) / count(employees.E_ID) as acp from employees, components
249//
250##Problem 5 (Phase 3)
251select avg(inspections.score) as a from inspections
252where inspections.C_Version in
253(select build.C_Version from build where build.P_Version = 'secret');
254//
255//
256##Problem 6 (Phase 3)
257create view a as select employees.E_ID,employees.Seniority,count(own.C_Name)
258from employees
259left outer join own
260on employees.E_ID = own.E_ID
261group by employees.E_ID;
262//
263create view b as select inspections.by_who, count(inspections.by_who),avg(inspections.score)
264from inspections
265group by inspections.by_who;
266//
267select * from a
268left outer join b on a.E_ID = b.by_who;
269//
270#Problem 10 (Phase 3)
271insert into inspections (C_Name,C_Version,inspection_date,by_who,score,description) values
272('Dynamic Table Interface','D01','2017-11-20','10500','80','minor fixes needed');
273//
274##Problem 8 (Phase 3)
275insert into inspections (C_Name,C_Version,inspection_date,by_who,score,description) values
276('Pen Driver','P01', '2017-08-15','10400','60','needs rework, introduced new errors');
277
278//
279##Problem 7 (Phase 3)
280create view c as select distinct
281employees.E_ID,employees.Seniority,components.Components_Status,
282sum(cost(components.Components_Status))
283from employees
284left join inspections on employees.E_ID = inspections.by_who
285left join components on components.C_Name = inspections.C_Name
286 and components.C_Version = inspections.C_Version
287where inspections.inspection_date between '2010-01-01' and '2010-12-31';
288//
289select * from c;
290//
291create function cost(Components_Status enum('ready','usable','not-ready'))
292returns int
293begin
294 if Components_Status = 'ready' then return 200;
295 else return 100;
296 end if;
297end//
298
299##Prblem 11 (Phase 3)
300alter table employees
301add column in_or_out varchar(15) after Seniority;
302//
303update employees
304set in_or_out = 'out'
305where employees.E_ID = '10700';
306//