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