· 8 years ago · Jan 21, 2018, 07:48 PM
1/*
2INFANL02-2, Introduction to Database, 1718
3Student: Liam de Waal, 0943700, INF1L
4Assignment 2
5*/
6
7-- Question 1
8
9/*
10SELECT first_name, surname, name, review.coursecode
11FROM teacher, teaches, course, review
12WHERE teacher.bsn = teaches.teacherbsn AND teaches.coursecode = course.code AND teacher.bsn = review.teacherbsn
13GROUP BY teacher.first_name, surname,name ,review.coursecode
14ORDER BY surname;
15*/
16
17-- Question 2
18
19/*
20SELECT first_name, surname, SUM(points)
21FROM has_completed, student, teaches, course
22WHERE has_completed.student_id = student.id AND teaches.studentid = has_completed.student_id AND teaches.coursecode = course.code
23
24GROUP BY first_name, surname, points
25ORDER BY surname
26*/
27
28-- Question 3
29
30/*
31SELECT course.name
32FROM teacher, course, teaches
33WHERE teacher.bsn = teaches.teacherbsn AND course.code = teaches.coursecode AND teacher.scale = 13
34
35GROUP BY course.name
36*/
37
38-- Question 4
39
40/*
41SELECT distinct first_name, surname, code, count(assignment.assignmentcode)
42FROM student, course, assignment, aregiven
43WHERE course.code = assignment.coursecode AND aregiven.assignmentcode = assignment.assignmentcode AND student.id = aregiven.student_id
44GROUP BY first_name, surname, code
45ORDER BY first_name asc
46*/
47
48-- Question 5
49
50 /*
51 SELECT first_name,surname
52FROM teacher, workson
53
54WHERE bsn NOT IN (SELECT teacherbsn FROM workson)
55GROUP BY bsn
56ORDER BY bsn
57*/
58
59-- SQL commands to create tables
60-- write the commands here
61
62drop table if exists teacher Cascade;
63Create table teacher (bsn char(11) Primary key,
64 first_name varchar(12),
65 surname varchar (30),
66 scale INt,
67 salary real,
68 check (scale >= 9 AND scale <= 13),
69 check (salary >= 20*scale AND salary <= 35*scale));
70
71drop table if exists course Cascade;
72Create table course (code char(5) Primary key,
73 name varchar(30),
74 poINts INt,
75 Check (poINts >= 1 AND poINts <= 12));
76
77drop table if exists student Cascade;
78create table student (id char(7)Primary key,
79 first_name varchar(12),
80 surname varchar(30),
81 bsn char(11),
82 start_date date);
83
84drop table if exists designs Cascade;
85Create Table designs (teacherbsn char(11) References teacher(bsn),
86 coursecode char(5) References course(code),
87 Primary key (teacherbsn, coursecode));
88
89drop table if exists review Cascade;
90Create Table review (teacherbsn char(11) References teacher(bsn),
91 coursecode char(5) References course(code),
92 Primary key (teacherbsn, coursecode));
93
94drop table if exists teaches Cascade;
95Create Table teaches (teacherbsn char(11) References teacher(bsn),
96 coursecode char(5) References course(code),
97 studentid char(7) references student(id),
98 Primary key (teacherbsn, coursecode, studentid));
99
100drop table if exists assignment Cascade;
101Create table assignment (coursecode char(5) references course(code),
102 assignmentcode char(5),
103 week INt,
104 year INt,
105 fINal boolean,
106 Primary key (coursecode, assignmentcode));
107
108drop table if exists requires Cascade;
109Create table requires (cc1 char(5),
110 ac1 char(5),
111 cc2 char(5),
112 ac2 char(5),
113 Primary key (cc1, ac1, cc2, ac2),
114 Foreign key (cc1, ac1) references assignment(coursecode, assignmentcode),
115 Foreign key (cc2, ac2) references assignment(coursecode, assignmentcode));
116
117drop type teacherrole Cascade;
118Create type teacherrole as enum ('designer', 'reviewer', 'solver');
119Drop table if exists workson cascade;
120Create table workson (teacherbsn char(11) references teacher(bsn),
121 coursecode char(5),
122 assignmentcode char(5),
123 role_of_teacher teacherrole,
124 primary key (teacherbsn, coursecode, assignmentcode),
125 Foreign key (coursecode, assignmentcode) references assignment(coursecode, assignmentcode));
126
127drop table if exists study_program cascade;
128Create table study_program (level char(3),
129 study_name varchar(45),
130 duration INt,
131 Primary key (level, study_name));
132
133drop table if exists has_completed Cascade;
134Create table has_completed (student_id char(7),
135 study_level char(3),
136 study_name varchar(45),
137
138 primary key (student_id, study_level, study_name),
139 Foreign key (study_level, study_name) references study_program(level, study_name),
140 Foreign key (student_id) references student(id)
141 );
142
143drop table if exists aregiven;
144Create table aregiven (student_id char(7),
145 coursecode char(5),
146 assignmentcode char(5),
147
148 primary key(student_id, coursecode, assignmentcode),
149 Foreign key (coursecode, assignmentcode) references assignment(coursecode, assignmentcode),
150 Foreign key (student_id) references student(id));
151
152-- SQL commands to INSERT values
153-- write the commands here
154
155INSERT INTO teacher values
156 ('52269-69987', 'Claudius', 'Streather', '10', '200'),
157 ('59614-58753', 'Dona', 'Milbank', '12', '380'),
158 ('25856-40101', 'Riley', 'Pugsley', '12', '272'),
159 ('98208-93540', 'Garwood', 'Hattrick', '11', '220'),
160 ('64756-68937', 'Juieta', 'Kunzelmann', '11', '354'),
161 ('61092-02471', 'Sallyanne', 'Delort', '13', '350'),
162 ('94203-26749', 'Leann', 'KleINhaus', '11', '220'),
163 ('42374-12317', 'Leigha', 'Cianelli', '11', '220'),
164 ('83265-24378', 'Karel', 'Eisak', '11', '246'),
165 ('78910-58311', 'Tomi', 'Lowis', '10', '350');
166
167
168INSERT INTO student values
169 ('0817347', 'Adelaide', 'Kemp', '55540-32016', '2012-02-29'),
170 ('0889300', 'Addison', 'Lewis', '62874-60266', '2012-11-14'),
171 ('0838756', 'ChANDler', 'Mcgowan', '31573-29574', '2011-10-07'),
172 ('0832660', 'Averi', 'Horn', '57137-29593', '2013-01-28'),
173 ('0820356', 'Paola', 'Montoya', '92971-17968', '2010-11-29'),
174 ('0874162', 'Joslyn', 'Mann', '28097-88031', '2011-10-17'),
175 ('0855759', 'Mckenna', 'Hodge', '10062-01495', '2011-06-03'),
176 ('0861973', 'Ayanna', 'Everett', '72133-12008', '2013-01-07'),
177 ('0891964', 'RuBY', 'Snow', '29773-16346', '2011-12-18'),
178 ('0827171', 'Arya', 'Campos', '18120-95884', '2013-01-21'),
179 ('0886733', 'Yaretzi', 'Joseph', '88344-15559', '2010-10-21'),
180 ('0885275', 'Leah', 'Roman', '89744-83342', '2010-01-03'),
181 ('0837712', 'CorINne', 'Huffman', '66587-05136', '2011-04-03'),
182 ('0803756', 'Enrique', 'Tyson', '41401-89947', '2012-12-30'),
183 ('0832106', 'Holly', 'KINney', '96961-48255', '2011-11-04'),
184 ('0871899', 'Jaxton', 'Huffman', '58680-81239', '2010-12-24'),
185 ('0834667', 'LelAND', 'Mullen', '49088-99950', '2013-08-23'),
186 ('0848042', 'QuINcy', 'Merritt', '13619-35297', '2010-11-07'),
187 ('0882325', 'Zara', 'Alford', '40831-15016', '2010-10-15'),
188 ('0891031', 'Samantha', 'Jackson', '24874-47775', '2012-09-13'),
189 ('0846211', 'Jaxen', 'James', '98978-28953', '2012-11-13'),
190 ('0853161', 'Jonas', 'Rivas', '47853-96551', '2012-03-10'),
191 ('0819175', 'Jaidyn', 'Clemons', '76470-06519', '2012-08-27'),
192 ('0839441', 'Ayleen', 'Lee', '29551-94776', '2010-12-19'),
193 ('0857631', 'Gauge', 'Stewart', '98473-70192', '2011-11-23'),
194 ('0857382', 'Bruce', 'Carey', '70786-61873', '2010-01-01'),
195 ('0816380', 'Amani', 'Love', '25672-23781', '2010-12-09'),
196 ('0808692', 'Taraji', 'Mills', '80141-75446', '2012-02-03'),
197 ('0875869', 'Keira', 'BYrd', '33465-41147', '2013-03-27'),
198 ('0800811', 'Deegan', 'Strong', '57118-37389', '2011-07-05'),
199 ('0838774', 'MarvIN', 'Mcmahon', '39361-84625', '2010-09-14'),
200 ('0848415', 'LINdsay', 'Walker', '90980-43462', '2011-06-28'),
201 ('0840712', 'Alden', 'Ware', '11362-49381', '2013-03-19'),
202 ('0889517', 'Heaven', 'Brady', '04490-83342', '2010-05-13'),
203 ('0804162', 'Arabella', 'Villarreal', '19814-98358', '2011-02-26'),
204 ('0870247', 'Stephen', 'Roy', '74814-12661', '2011-10-11'),
205 ('0823855', 'Lukas', 'Ortega', '53365-11487', '2010-10-14'),
206 ('0885385', 'Rylie', 'Gill', '59235-16274', '2013-01-21'),
207 ('0852696', 'Keira', 'Owens', '40390-46701', '2011-04-29'),
208 ('0882301', 'Steven', 'Thompson', '50374-80378', '2012-09-15'),
209 ('0835161', 'ShelBY', 'Buck', '71238-52210', '2011-04-14'),
210 ('0822564', 'CollIN', 'Santos', '53223-20765', '2012-01-06'),
211 ('0821986', 'ZaIN', 'Nelson', '36805-56788', '2013-10-28'),
212 ('0812982', 'Juliana', 'EwINg', '40267-88898', '2010-11-14'),
213 ('0843262', 'Madison', 'Malone', '39761-44632', '2013-11-25'),
214 ('0805577', 'Aria', 'Serrano', '90238-09850', '2010-03-24'),
215 ('0845288', 'Willa', 'Vance', '02587-13960', '2013-03-23'),
216 ('0859163', 'Nathanael', 'Hood', '63909-93284', '2013-08-06'),
217 ('0810022', 'Jaidyn', 'Bond', '99102-95164', '2012-03-03'),
218 ('0857370', 'Milo', 'Pollard', '80725-49081', '2010-10-12');
219
220
221INSERT INTO course values
222 ('CRS01', 'ProgrammINg Basics', '3'),
223 ('CRS02', 'OO ProgrammINg', '2'),
224 ('CRS03', 'Database Design', '3'),
225 ('CRS04', 'Software TestINg', '3'),
226 ('CRS05', 'ModellINg', '3'),
227 ('CRS06', 'Web ProgrammINg', '3'),
228 ('CRS07', 'Requirement EngINeerINg', '4'),
229 ('CRS08', 'OperatINg Systems', '2'),
230 ('CRS09', 'Hardware Design', '3'),
231 ('PRJ01', 'Game Development', '4'),
232 ('PRJ02', 'Data Analysis Applications', '4'),
233 ('PRJ03', 'Simulators', '2'),
234 ('PRJ04', 'Mobile Apps', '3'),
235 ('PRJ05', 'Parallel ProgrammINg', '3'),
236 ('PRJ06', 'Advanced Technologies', '3');
237
238
239INSERT INTO assignment values
240 ('CRS01', 'HMW37', '28', '2010', 'FALSE'),
241 ('CRS01', 'HMW54', '5', '2011', 'FALSE'),
242 ('CRS01', 'HMW41', '30', '2010', 'TRUE'),
243 ('CRS01', 'HMW44', '3', '2011', 'TRUE'),
244 ('CRS05', 'HMW39', '31', '2013', 'FALSE'),
245 ('CRS05', 'HMW03', '17', '2010', 'TRUE'),
246 ('CRS05', 'HMW66', '38', '2011', 'FALSE'),
247 ('CRS05', 'HMW99', '13', '2013', 'FALSE'),
248 ('PRJ01', 'HMW59', '36', '2012', 'TRUE'),
249 ('PRJ02', 'HMW59', '36', '2013', 'FALSE'),
250 ('PRJ01', 'HMW23', '13', '2012', 'FALSE'),
251 ('PRJ01', 'HMW04', '20', '2013', 'FALSE'),
252 ('PRJ04', 'HMW74', '18', '2012', 'FALSE'),
253 ('PRJ04', 'HMW13', '27', '2010', 'FALSE'),
254 ('PRJ04', 'HMW62', '14', '2010', 'FALSE'),
255 ('PRJ04', 'HMW78', '31', '2012', 'TRUE'),
256 ('PRJ04', 'HMW34', '24', '2010', 'TRUE'),
257 ('PRJ04', 'HMW55', '5', '2010', 'FALSE'),
258 ('PRJ04', 'HMW16', '26', '2012', 'FALSE'),
259 ('PRJ04', 'HMW77', '36', '2011', 'TRUE');
260
261
262INSERT INTO study_program values
263 ('BSC', 'INformatica', '4'),
264 ('MSC', 'INformatica', '2');
265
266
267INSERT INTO teaches values
268 ('52269-69987', 'CRS01', '0817347'),
269 ('59614-58753', 'CRS02', '0889300'),
270 ('25856-40101', 'CRS03', '0838756'),
271 ('98208-93540', 'CRS04', '0832660'),
272 ('64756-68937', 'CRS05', '0820356'),
273 ('61092-02471', 'CRS06', '0874162'),
274 ('94203-26749', 'CRS07', '0855759'),
275 ('42374-12317', 'CRS08', '0861973'),
276 ('83265-24378', 'CRS09', '0891964'),
277 ('78910-58311', 'PRJ01', '0827171'),
278 ('52269-69987', 'PRJ02', '0886733'),
279 ('59614-58753', 'PRJ03', '0885275'),
280 ('25856-40101', 'PRJ04', '0837712'),
281 ('98208-93540', 'PRJ05', '0803756'),
282 ('64756-68937', 'PRJ06', '0832106'),
283 ('52269-69987', 'CRS01', '0871899'),
284 ('59614-58753', 'CRS02', '0834667'),
285 ('25856-40101', 'CRS03', '0848042'),
286 ('98208-93540', 'CRS04', '0882325'),
287 ('64756-68937', 'CRS05', '0882325'),
288 ('64756-68937', 'CRS05', '0891031'),
289 ('61092-02471', 'CRS06', '0846211'),
290 ('94203-26749', 'CRS07', '0853161'),
291 ('42374-12317', 'CRS08', '0819175'),
292 ('83265-24378', 'CRS09', '0839441'),
293 ('78910-58311', 'PRJ01', '0857631'),
294 ('52269-69987', 'PRJ02', '0857382'),
295 ('59614-58753', 'PRJ03', '0816380'),
296 ('25856-40101', 'PRJ04', '0808692'),
297 ('98208-93540', 'PRJ05', '0875869'),
298 ('64756-68937', 'PRJ06', '0800811'),
299 ('52269-69987', 'CRS01', '0838774'),
300 ('59614-58753', 'CRS02', '0848415'),
301 ('25856-40101', 'CRS03', '0840712'),
302 ('98208-93540', 'CRS04', '0889517'),
303 ('64756-68937', 'CRS05', '0804162'),
304 ('61092-02471', 'CRS06', '0870247'),
305 ('94203-26749', 'CRS07', '0823855'),
306 ('42374-12317', 'CRS08', '0885385'),
307 ('83265-24378', 'CRS09', '0852696'),
308 ('78910-58311', 'PRJ01', '0882301'),
309 ('52269-69987', 'PRJ02', '0835161'),
310 ('59614-58753', 'PRJ03', '0822564'),
311 ('25856-40101', 'PRJ04', '0821986'),
312 ('98208-93540', 'PRJ05', '0812982'),
313 ('64756-68937', 'PRJ06', '0843262'),
314 ('52269-69987', 'CRS01', '0805577'),
315 ('59614-58753', 'CRS02', '0845288'),
316 ('25856-40101', 'CRS03', '0859163'),
317 ('98208-93540', 'CRS04', '0810022'),
318 ('64756-68937', 'CRS05', '0857370');
319
320
321INSERT INTO has_completed values
322 ('0871899', 'BSC', 'INformatica'),
323 ('0834667', 'BSC', 'INformatica'),
324 ('0848042', 'BSC', 'INformatica'),
325 ('0882325', 'BSC', 'INformatica'),
326 ('0891031', 'BSC', 'INformatica'),
327 ('0846211', 'MSC', 'INformatica'),
328 ('0819175', 'MSC', 'INformatica'),
329 ('0839441', 'MSC', 'INformatica'),
330 ('0857631', 'MSC', 'INformatica'),
331 ('0857382', 'MSC', 'INformatica'),
332 ('0816380', 'MSC', 'INformatica'),
333 ('0808692', 'MSC', 'INformatica'),
334 ('0875869', 'MSC', 'INformatica'),
335 ('0800811', 'BSC', 'INformatica'),
336 ('0838774', 'BSC', 'INformatica'),
337 ('0848415', 'BSC', 'INformatica'),
338 ('0840712', 'MSC', 'INformatica'),
339 ('0889517', 'MSC', 'INformatica'),
340 ('0804162', 'MSC', 'INformatica');
341
342
343INSERT INTO review values
344 ('94203-26749', 'CRS01'),
345 ('78910-58311', 'CRS02'),
346 ('61092-02471', 'CRS03'),
347 ('52269-69987', 'CRS04'),
348 ('59614-58753', 'CRS05'),
349 ('98208-93540', 'CRS06'),
350 ('59614-58753', 'CRS07'),
351 ('98208-93540', 'CRS08'),
352 ('83265-24378', 'CRS09'),
353 ('64756-68937', 'PRJ01'),
354 ('94203-26749', 'PRJ02'),
355 ('98208-93540', 'PRJ03'),
356 ('83265-24378', 'PRJ04'),
357 ('83265-24378', 'PRJ05'),
358 ('52269-69987', 'PRJ06');
359
360
361INSERT INTO designs values
362 ('25856-40101', 'CRS01'),
363 ('25856-40101', 'CRS02'),
364 ('61092-02471', 'CRS03'),
365 ('94203-26749', 'CRS04'),
366 ('61092-02471', 'CRS05'),
367 ('52269-69987', 'CRS06'),
368 ('59614-58753', 'CRS07'),
369 ('25856-40101', 'CRS08'),
370 ('98208-93540', 'CRS09'),
371 ('64756-68937', 'PRJ01'),
372 ('78910-58311', 'PRJ02'),
373 ('83265-24378', 'PRJ03'),
374 ('42374-12317', 'PRJ04'),
375 ('98208-93540', 'PRJ05'),
376 ('94203-26749', 'PRJ06');
377
378
379INSERT INTO requires values
380 ('CRS01', 'HMW37', 'CRS01', 'HMW44'),
381 ('CRS01', 'HMW54', 'CRS05', 'HMW39'),
382 ('CRS01', 'HMW41', 'CRS05', 'HMW03'),
383 ('CRS01', 'HMW44', 'CRS05', 'HMW66'),
384 ('CRS05', 'HMW39', 'CRS05', 'HMW99'),
385 ('CRS05', 'HMW03', 'PRJ01', 'HMW59'),
386 ('CRS05', 'HMW66', 'PRJ02', 'HMW59'),
387 ('CRS05', 'HMW99', 'PRJ01', 'HMW23'),
388 ('PRJ01', 'HMW59', 'PRJ01', 'HMW04'),
389 ('PRJ02', 'HMW59', 'PRJ04', 'HMW74'),
390 ('PRJ01', 'HMW23', 'PRJ04', 'HMW13'),
391 ('PRJ01', 'HMW04', 'PRJ04', 'HMW62'),
392 ('PRJ04', 'HMW74', 'PRJ04', 'HMW78'),
393 ('PRJ04', 'HMW13', 'PRJ04', 'HMW34'),
394 ('PRJ04', 'HMW62', 'PRJ04', 'HMW55'),
395 ('PRJ04', 'HMW78', 'PRJ04', 'HMW16'),
396 ('PRJ04', 'HMW34', 'PRJ04', 'HMW77');
397
398
399INSERT INTO aregiven values
400 ('0817347', 'CRS01', 'HMW54'),
401 ('0817347', 'CRS01', 'HMW37'),
402 ('0817347', 'CRS01', 'HMW41'),
403 ('0817347', 'CRS01', 'HMW44'),
404 ('0820356', 'CRS05', 'HMW39'),
405 ('0820356', 'CRS05', 'HMW99'),
406 ('0820356', 'CRS05', 'HMW03'),
407 ('0820356', 'CRS05', 'HMW66'),
408 ('0827171', 'PRJ01', 'HMW04'),
409 ('0827171', 'PRJ01', 'HMW59'),
410 ('0827171', 'PRJ01', 'HMW23'),
411 ('0886733', 'PRJ02', 'HMW59'),
412 ('0837712', 'PRJ04', 'HMW13'),
413 ('0837712', 'PRJ04', 'HMW62'),
414 ('0837712', 'PRJ04', 'HMW77'),
415 ('0837712', 'PRJ04', 'HMW74'),
416 ('0837712', 'PRJ04', 'HMW34'),
417 ('0837712', 'PRJ04', 'HMW55'),
418 ('0837712', 'PRJ04', 'HMW16'),
419 ('0837712', 'PRJ04', 'HMW78'),
420 ('0871899', 'CRS01', 'HMW54'),
421 ('0871899', 'CRS01', 'HMW37'),
422 ('0871899', 'CRS01', 'HMW41'),
423 ('0871899', 'CRS01', 'HMW44'),
424 ('0882325', 'CRS05', 'HMW39'),
425 ('0882325', 'CRS05', 'HMW99'),
426 ('0882325', 'CRS05', 'HMW03'),
427 ('0882325', 'CRS05', 'HMW66'),
428 ('0891031', 'CRS05', 'HMW39'),
429 ('0891031', 'CRS05', 'HMW99'),
430 ('0891031', 'CRS05', 'HMW03'),
431 ('0891031', 'CRS05', 'HMW66'),
432 ('0857631', 'PRJ01', 'HMW04'),
433 ('0857631', 'PRJ01', 'HMW59'),
434 ('0857631', 'PRJ01', 'HMW23'),
435 ('0857382', 'PRJ02', 'HMW59'),
436 ('0808692', 'PRJ04', 'HMW13'),
437 ('0808692', 'PRJ04', 'HMW62'),
438 ('0808692', 'PRJ04', 'HMW77'),
439 ('0808692', 'PRJ04', 'HMW74'),
440 ('0808692', 'PRJ04', 'HMW34'),
441 ('0808692', 'PRJ04', 'HMW55'),
442 ('0808692', 'PRJ04', 'HMW16'),
443 ('0808692', 'PRJ04', 'HMW78'),
444 ('0838774', 'CRS01', 'HMW54'),
445 ('0838774', 'CRS01', 'HMW37'),
446 ('0838774', 'CRS01', 'HMW41'),
447 ('0838774', 'CRS01', 'HMW44'),
448 ('0804162', 'CRS05', 'HMW39'),
449 ('0804162', 'CRS05', 'HMW99'),
450 ('0804162', 'CRS05', 'HMW03'),
451 ('0804162', 'CRS05', 'HMW66'),
452 ('0882301', 'PRJ01', 'HMW04'),
453 ('0882301', 'PRJ01', 'HMW59'),
454 ('0882301', 'PRJ01', 'HMW23'),
455 ('0835161', 'PRJ02', 'HMW59'),
456 ('0821986', 'PRJ04', 'HMW13'),
457 ('0821986', 'PRJ04', 'HMW62'),
458 ('0821986', 'PRJ04', 'HMW77'),
459 ('0821986', 'PRJ04', 'HMW74'),
460 ('0821986', 'PRJ04', 'HMW34'),
461 ('0821986', 'PRJ04', 'HMW55'),
462 ('0821986', 'PRJ04', 'HMW16'),
463 ('0821986', 'PRJ04', 'HMW78'),
464 ('0805577', 'CRS01', 'HMW54'),
465 ('0805577', 'CRS01', 'HMW37'),
466 ('0805577', 'CRS01', 'HMW41'),
467 ('0805577', 'CRS01', 'HMW44'),
468 ('0857370', 'CRS05', 'HMW39'),
469 ('0857370', 'CRS05', 'HMW99'),
470 ('0857370', 'CRS05', 'HMW03'),
471 ('0857370', 'CRS05', 'HMW66');
472
473
474INSERT INTO workson values
475 ('61092-02471', 'CRS01', 'HMW37', 'designer'),
476 ('25856-40101', 'CRS01', 'HMW54', 'solver'),
477 ('52269-69987', 'CRS01', 'HMW41', 'solver'),
478 ('94203-26749', 'CRS01', 'HMW44', 'designer'),
479 ('94203-26749', 'CRS05', 'HMW39', 'solver'),
480 ('98208-93540', 'CRS05', 'HMW03', 'reviewer'),
481 ('64756-68937', 'CRS05', 'HMW66', 'reviewer'),
482 ('78910-58311', 'CRS05', 'HMW99', 'solver'),
483 ('25856-40101', 'PRJ01', 'HMW59', 'designer'),
484 ('78910-58311', 'PRJ02', 'HMW59', 'reviewer'),
485 ('78910-58311', 'PRJ01', 'HMW23', 'designer'),
486 ('94203-26749', 'PRJ01', 'HMW04', 'designer'),
487 ('25856-40101', 'PRJ04', 'HMW74', 'solver'),
488 ('78910-58311', 'PRJ04', 'HMW13', 'solver'),
489 ('52269-69987', 'PRJ04', 'HMW62', 'reviewer'),
490 ('78910-58311', 'PRJ04', 'HMW78', 'solver'),
491 ('78910-58311', 'PRJ04', 'HMW34', 'designer'),
492 ('78910-58311', 'PRJ04', 'HMW55', 'designer'),
493 ('64756-68937', 'PRJ04', 'HMW16', 'designer'),
494 ('78910-58311', 'PRJ04', 'HMW77', 'reviewer');