· 4 years ago · Apr 26, 2021, 12:32 PM
1/*----------------------AddAcademicYear----------------------*/
2-- Add [Academic Year] information into table <AcademicYear>
3-- Input: AYear Id
4-- Output: Status Code
5/*------------------------------------------------------------*/
6DELIMITER // DROP PROCEDURE IF EXISTS AddAcademicYear // CREATE PROCEDURE AddAcademicYear(IN inAYearId VARCHAR(10), OUT statusCode INT) BEGIN CASE
7 WHEN inAYearId IS NULL THEN
8 SET
9 statusCode = 401;
10-- NON-EXISTENT/INVALID Academic Year Id
11 WHEN inAYearId IN (
12 SELECT
13 AYearId
14 FROM
15 AcademicYear
16 ) THEN
17 SET
18 statusCode = 490;
19-- DUPLICATION ERROR
20 ELSE
21 SET
22 statusCode = 200;
23-- SUCCESS
24 INSERT INTO
25 AcademicYear (AYearId)
26 VALUES
27 (inAYearId);
28END CASE;
29END //
30/*-----------------------AddSemester--------------------------*/
31-- Add [Semester] information into table <Semester>
32-- Input: Semester Id, AYear Id
33-- Output: Status Code
34/*--------------------------------- --------------------------*/
35DROP PROCEDURE IF EXISTS AddSemester // CREATE PROCEDURE AddSemester(
36 IN inSemesterId VARCHAR(10),
37 IN inAYearId VARCHAR(10),
38 OUT statusCode INT
39) BEGIN CASE
40 WHEN inAYearId IS NULL
41 OR inAYearId NOT IN (
42 SELECT
43 AYearId
44 FROM
45 AcademicYear
46 ) THEN
47 SET
48 statusCode = 401;
49-- NON-EXISTENT/INVALID Academic Year Id
50 WHEN inSemesterId IS NULL THEN
51 SET
52 statusCode = 402;
53-- NON-EXISTENT/INVALID Semester Id
54 WHEN inSemesterId IN (
55 SELECT
56 SemesterId
57 FROM
58 Semester
59 ) THEN
60 SET
61 statusCode = 490;
62-- DUPLICATION ERROR
63 ELSE
64 SET
65 statusCode = 200;
66-- SUCCESS
67 INSERT INTO
68 Semester (SemesterId, SemYear)
69 VALUES
70 (
71 inSemesterId,(
72 SELECT
73 AYearPK
74 FROM
75 AcademicYear
76 WHERE
77 AYearId = inAYearId
78 )
79 );
80END CASE;
81END //
82/*--------------------------AddFaculty--------------------------*/
83-- Add [Faculty] information into table <Faculty>
84-- Input: Faculty Id, Faculty Name
85-- Output: Status Code
86/*--------------------------------------------------------------*/
87DROP PROCEDURE IF EXISTS AddFaculty // CREATE PROCEDURE AddFaculty(
88 IN inFacultyId VARCHAR(10),
89 IN inFacultyName VARCHAR(100),
90 OUT statusCode INT
91) BEGIN CASE
92 WHEN inFacultyId IS NULL THEN
93 SET
94 statusCode = 413;
95-- NON-EXISTENT/INVALID Faculty Id
96 WHEN inFacultyName IS NULL THEN
97 SET
98 statusCode = 403;
99-- NON-EXISTENT/INVALID Faculty Name
100 WHEN inFacultyId IN (
101 SELECT
102 FacultyId
103 FROM
104 Faculty
105 ) THEN
106 SET
107 statusCode = 490;
108-- DUPLICATION ERROR
109 ELSE
110 SET
111 statusCode = 200;
112-- SUCCESS
113 INSERT INTO
114 Faculty (FacultyId, FacultyName)
115 VALUES
116 (inFacultyId, inFacultyName);
117END CASE;
118END //
119/*-------------------------AddFacultyInAcademicYear----------------------------*/
120-- Add the relation between [Faculty] & [AcademicYear] into table <FacultyInAcademicYear>
121-- Input: Faculty Id, AYear Id
122-- Output: Status Code
123/*-----------------------------------------------------------------------------*/
124DROP PROCEDURE IF EXISTS AddFacultyInAcademicYear // CREATE PROCEDURE AddFacultyInAcademicYear(
125 IN inFacultyId VARCHAR(10),
126 IN inAYearId VARCHAR(10),
127 OUT statusCode INT
128) BEGIN CASE
129 WHEN inAYearId IS NULL
130 OR inAYearId NOT IN (
131 SELECT
132 AYearId
133 FROM
134 AcademicYear
135 ) THEN
136 SET
137 statusCode = 401;
138-- NON-EXISTENT/INVALID Academic Year Id
139 WHEN inFacultyId IS NULL
140 OR inFacultyId NOT IN (
141 SELECT
142 FacultyId
143 FROM
144 Faculty
145 ) THEN
146 SET
147 statusCode = 413;
148-- NON-EXISTENT/INVALID Faculty Id
149 WHEN (
150 (
151 SELECT
152 AYearPK,
153 FacultyPK
154 FROM
155 AcademicYear NATURAL
156 JOIN Faculty
157 WHERE
158 FacultyId = inFacultyId
159 AND AYearId = inAYearId
160 )
161 ) IN (
162 SELECT
163 FalYear,
164 FacultyFK
165 FROM
166 FacultyInAcademicYear
167 ) THEN
168 SET
169 statusCode = 490;
170-- DUPLICATION ERROR
171 ELSE
172 SET
173 statusCode = 200;
174-- SUCCESS
175 INSERT INTO
176 FacultyInAcademicYear (FacultyFK, FalYear)
177 VALUES
178 (
179 (
180 SELECT
181 FacultyPK
182 FROM
183 Faculty
184 WHERE
185 FacultyId = inFacultyId
186 ),(
187 SELECT
188 AYearPK
189 FROM
190 AcademicYear
191 WHERE
192 AYearId = inAYearId
193 )
194 );
195END CASE;
196END //
197/*-----------------------AddProgram-------------------------*/
198-- Add [Program] information into table <Program>
199-- Input: Program Id, Program Name
200-- Output: Status Code
201/*----------------------------------------------------------*/
202DROP PROCEDURE IF EXISTS AddProgram // CREATE PROCEDURE AddProgram(
203 IN inProgramId VARCHAR(10),
204 IN inProgramName VARCHAR(100),
205 OUT statusCode INT
206) BEGIN CASE
207 WHEN inProgramId IS NULL THEN
208 SET
209 statusCode = 414;
210-- NON-EXISTENT/INVALID Program Id
211 WHEN inProgramName IS NULL THEN
212 SET
213 statusCode = 404;
214-- NON-EXISTENT/INVALID Program Name
215 WHEN inProgramId IN (
216 SELECT
217 ProgramId
218 FROM
219 Program
220 ) THEN
221 SET
222 statusCode = 490;
223-- DUPLICATION ERROR
224 ELSE
225 SET
226 statusCode = 200;
227-- SUCCESS
228 INSERT INTO
229 Program (ProgramId, ProgramName)
230 VALUES
231 (inProgramId, inProgramName);
232END CASE;
233END //
234/*------------------------------------AddProgramInFacultyInAcademicYear-----------------------------------------*/
235-- Add the relation between [Program] & [Faculty] & [AcademicYear] into table <ProgramInFacultyInAcademicYear>
236-- Input: Program Id, Faculty Id, AYear Id
237-- Output: Status Code
238/*--------------------------------------------------------------------------------------------------------------*/
239DROP PROCEDURE IF EXISTS AddProgramInFacultyInAcademicYear // CREATE PROCEDURE AddProgramInFacultyInAcademicYear(
240 IN inProgramId VARCHAR(10),
241 IN inFacultyId VARCHAR(10),
242 IN inAYearId VARCHAR(10),
243 OUT statusCode INT
244) BEGIN CASE
245 WHEN inAYearId IS NULL
246 OR inAYearId NOT IN (
247 SELECT
248 AYearId
249 FROM
250 AcademicYear
251 INNER JOIN FacultyInAcademicYear ON AYearPK = FalYear
252 ) THEN
253 SET
254 statusCode = 401;
255-- NON-EXISTENT/INVALID Academic Year Id
256 WHEN inFacultyId IS NULL
257 OR inFacultyId NOT IN (
258 SELECT
259 FacultyId
260 FROM
261 Faculty
262 INNER JOIN FacultyInAcademicYear ON FacultyPK = FacultyFK
263 ) THEN
264 SET
265 statusCode = 413;
266-- NON-EXISTENT/INVALID Faculty Id
267 WHEN inProgramId IS NULL
268 OR inProgramId NOT IN (
269 SELECT
270 ProgramId
271 FROM
272 Program
273 ) THEN
274 SET
275 statusCode = 414;
276-- NON-EXISTENT/INVALID Program Id
277 WHEN (
278 (
279 SELECT
280 ProgramPK,
281 FA_PK
282 FROM
283 Program NATURAL
284 JOIN FacultyInAcademicYear
285 INNER JOIN Faculty ON FacultyFK = FacultyPK
286 INNER JOIN AcademicYear ON FalYear = AYearPK
287 WHERE
288 ProgramId = inProgramId
289 AND FacultyId = inFacultyId
290 AND AYearId = inAYearId
291 )
292 ) IN (
293 SELECT
294 ProgramFK,
295 FacultyYear
296 FROM
297 ProgramInFacultyInAcademicYear
298 ) THEN
299 SET
300 statusCode = 490;
301-- DUPLICATION ERROR
302 ELSE
303 SET
304 statusCode = 200;
305-- SUCCESS
306 INSERT INTO
307 ProgramInFacultyInAcademicYear (ProgramFK, FacultyYear)
308 SELECT
309 ProgramPK,
310 FA_PK
311 FROM
312 Program NATURAL
313 JOIN FacultyInAcademicYear
314 INNER JOIN Faculty ON FacultyFK = FacultyPK
315 INNER JOIN AcademicYear ON FalYear = AYearPK
316 WHERE
317 ProgramId = inProgramId
318 AND FacultyId = inFacultyId
319 AND AYearId = inAYearId;
320END CASE;
321END //
322/*---------------------AddLecturer------------------------*/
323-- Add [Lecturer] information into the table <Lecturer>
324-- Input: Lecturer Id, Lecturer Name, Username
325-- Output: Status Code
326/*---------------------------------------------------------*/
327DROP PROCEDURE IF EXISTS AddLecturer // CREATE PROCEDURE AddLecturer(
328 IN inLecturerId VARCHAR(10),
329 IN inLecturerName VARCHAR(100),
330 IN inUserName VARCHAR(20),
331 OUT statusCode INT
332) BEGIN CASE
333 WHEN inLecturerId IS NULL THEN
334 SET
335 statusCode = 416;
336-- NON-EXISTENT/INVALID LecturerId
337 WHEN inLecturerId IN (
338 SELECT
339 LecturerId
340 FROM
341 Lecturer
342 ) THEN
343 SET
344 statusCode = 490;
345-- DUPLICATION ERROR
346 WHEN inLecturerName IS NULL THEN
347 SET
348 statusCode = 406;
349-- NON-EXISTENT/INVALID Lecturer Name
350 WHEN inUserName IS NULL
351 OR inUserName NOT IN (
352 SELECT
353 UserName
354 FROM
355 Users
356 ) THEN
357 SET
358 statusCode = 420;
359-- NON-EXISTENT/INVALID Username
360 ELSE
361 SET
362 statusCode = 200;
363-- SUCCESS
364 INSERT INTO
365 Lecturer (LecturerId, LecturerName, LecturerUser)
366 VALUES
367 (
368 inLecturerId,
369 inLecturerName,(
370 SELECT
371 UserPK
372 FROM
373 Users
374 WHERE
375 UserName = inUserName
376 )
377 );
378END CASE;
379END //
380/*-------------------------AddModule-------------------------*/
381-- Add [Module] information into the table <Module>
382-- Input: Module Id, Module Name
383-- Output: Status Code
384/*-----------------------------------------------------------*/
385DROP PROCEDURE IF EXISTS AddModule // CREATE PROCEDURE AddModule(
386 IN inModuleId VARCHAR(10),
387 IN inModuleName VARCHAR(100),
388 OUT statusCode INT
389) BEGIN CASE
390 WHEN inModuleId IS NULL THEN
391 SET
392 statusCode = 415;
393 WHEN inModuleName IS NULL THEN
394 SET
395 statusCode = 405;
396 WHEN inModuleId IN (
397 SELECT
398 ModuleId
399 FROM
400 Module
401 ) THEN
402 SET
403 statusCode = 490;
404-- DUPLICATION ERROR
405 ELSE
406 SET
407 statusCode = 200;
408-- SUCCESS
409 INSERT INTO
410 Module (ModuleId, ModuleName)
411 VALUES
412 (inModuleId, inModuleName);
413END CASE;
414END //
415/*--------------------------------AddModuleInProgramInAcademicYear-------------------------------*/
416-- Add the relation between [Module] & [Program] & [AcademicYear] into table <ModuleInProgramInAcademicYear>
417-- Input: Module Id, Size, Program Id, AYear Id
418-- Output: Status Code
419/*-----------------------------------------------------------------------------------------------*/
420DROP PROCEDURE IF EXISTS AddModuleInProgramInAcademicYear // CREATE PROCEDURE AddModuleInProgramInAcademicYear(
421 IN inModuleId VARCHAR(10),
422 IN inProgramId VARCHAR(10),
423 IN inAYearId VARCHAR(10),
424 OUT statusCode INT
425) BEGIN CASE
426 WHEN inAYearId IS NULL
427 OR inAYearId NOT IN (
428 SELECT
429 AYearId
430 FROM
431 AcademicYear
432 INNER JOIN FacultyInAcademicYear ON AYearPK = FalYear
433 INNER JOIN ProgramInFacultyInAcademicYear ON FA_PK = FacultyYear
434 ) THEN
435 SET
436 statusCode = 401;
437-- NON-EXISTENT/INVALID Academic Year Id
438 WHEN inProgramId IS NULL
439 OR inProgramId NOT IN (
440 SELECT
441 ProgramId
442 FROM
443 Program
444 INNER JOIN ProgramInFacultyInAcademicYear ON ProgramPK = ProgramFK
445 ) THEN
446 SET
447 statusCode = 414;
448-- NON-EXISTENT/INVALID Program Id
449 WHEN inModuleId IS NULL
450 OR inModuleId NOT IN (
451 SELECT
452 ModuleId
453 FROM
454 Module
455 ) THEN
456 SET
457 statusCode = 415;
458-- NON-EXISTENT/INVALID Module Id
459 WHEN (
460 SELECT
461 ModulePK,
462 PFA_PK
463 FROM
464 Module NATURAL
465 JOIN ProgramInFacultyInAcademicYear
466 INNER JOIN FacultyInAcademicYear ON FacultyYear = FA_PK
467 INNER JOIN Program ON ProgramFK = ProgramPK
468 INNER JOIN AcademicYear ON FalYear = AYearPK
469 WHERE
470 AYearId = inAYearId
471 AND ProgramId = inProgramId
472 AND ModuleId = inModuleId
473 ) IN (
474 SELECT
475 ModuleFK,
476 ProgramFacultyYear
477 FROM
478 ModuleInProgramInAcademicYear
479 ) THEN
480 SET
481 statusCode = 490;
482-- DUPLICATION ERROR
483 ELSE
484 SET
485 statusCode = 200;
486-- SUCCESS
487 INSERT INTO
488 ModuleInProgramInAcademicYear (ModuleFK, ProgramFacultyYear)
489 SELECT
490 ModulePK,
491 PFA_PK
492 FROM
493 Module NATURAL
494 JOIN ProgramInFacultyInAcademicYear
495 INNER JOIN FacultyInAcademicYear ON FacultyYear = FA_PK
496 INNER JOIN Program ON ProgramFK = ProgramPK
497 INNER JOIN AcademicYear ON FalYear = AYearPK
498 WHERE
499 ModuleId = inModuleId
500 AND ProgramId = inProgramId
501 AND AYearId = inAYearId;
502END CASE;
503END //
504/*---------------------AddClass----------------------*/
505-- Add Class information into table <Class>
506-- Input: Class Id, Size, Semester Id, Module Id
507-- Output: Status Code
508/*---------------------------------------------------*/
509DROP PROCEDURE IF EXISTS AddClass // CREATE PROCEDURE AddClass(
510 IN inClassId VARCHAR(10),
511 IN inSize INT,
512 IN inSemesterId VARCHAR(10),
513 IN inModuleId VARCHAR(10),
514 OUT statusCode INT
515) BEGIN CASE
516 WHEN inClassId IS NULL THEN
517 SET
518 statusCode = 407;
519-- NON-EXISTENT/INVALID Class Id
520 WHEN inSize IS NULL THEN
521 SET
522 statusCode = 427;
523-- NON-EXISTENT/INVALID Class Size
524 WHEN inModuleId IS NULL
525 OR inModuleId NOT IN (
526 SELECT
527 ModuleId
528 FROM
529 Module
530 INNER JOIN ModuleInProgramInAcademicYear ON ModulePK = ModuleFK
531 ) THEN
532 SET
533 statusCode = 415;
534-- NON-EXISTENT/INVALID Module Id
535 WHEN inSemesterId IS NULL
536 OR inSemesterId NOT IN (
537 SELECT
538 SemesterId
539 FROM
540 Semester
541 ) THEN
542 SET
543 statusCode = 402;
544-- NON-EXISTENT/INVALID Semester Id
545 WHEN inClassId IN (
546 SELECT
547 ClassId
548 FROM
549 Class
550 ) THEN
551 SET
552 statusCode = 490;
553-- DUPLICATION ERROR
554 ELSE
555 SET
556 statusCode = 200;
557-- SUCCESS
558 INSERT INTO
559 Class (ClassId, Size, ClassSemester, ClassModule)
560 VALUES
561 (
562 inClassId,
563 inSize,
564 (
565 SELECT
566 SemesterPK
567 FROM
568 Semester
569 WHERE
570 SemesterId = inSemesterId
571 ),
572 (
573 SELECT
574 MPA_PK
575 FROM
576 ModuleInProgramInAcademicYear
577 INNER JOIN Module ON ModuleFK = ModulePK
578 WHERE
579 ModuleId = inModuleId
580 )
581 );
582END CASE;
583END //
584/*-----------------------AddTeaching----------------------*/
585-- Add the relation between [Lecturer] & [Class] into table <Teaching>
586-- Input: LecturerId, ClassId
587-- Output: Status Code
588/*--------------------------------------------------------*/
589DROP PROCEDURE IF EXISTS AddTeaching // CREATE PROCEDURE AddTeaching(
590 IN inLecturerId VARCHAR(10),
591 IN inClassId VARCHAR(10),
592 OUT statusCode INT
593) BEGIN CASE
594 WHEN inLecturerId IS NULL
595 OR inLecturerId NOT IN (
596 SELECT
597 LecturerId
598 FROM
599 Lecturer
600 ) THEN
601 SET
602 statusCode = 416;
603-- NON-EXISTENT/INVALID Lecturer Id
604 WHEN inClassId IS NULL
605 OR inClassId NOT IN (
606 SELECT
607 ClassId
608 FROM
609 Class
610 ) THEN
611 SET
612 statusCode = 407;
613-- NON-EXISTENT/INVALID Class Id
614 WHEN(
615 (
616 SELECT
617 LecturerPK,
618 ClassPK
619 FROM
620 Lecturer,
621 Class
622 WHERE
623 LecturerId = inLecturerId
624 AND ClassId = inClassId
625 )
626 ) IN (
627 SELECT
628 LecturerT,
629 ClassT
630 FROM
631 Teaching
632 ) THEN
633 SET
634 statusCode = 490;
635-- DUPLICATION ERROR
636 ELSE
637 SET
638 statusCode = 200;
639-- SUCCESS
640 INSERT INTO
641 Teaching (LecturerT, ClassT)
642 VALUES
643 (
644 (
645 SELECT
646 LecturerPK
647 FROM
648 Lecturer
649 WHERE
650 LecturerId = inLecturerId
651 ),
652 (
653 SELECT
654 ClassPK
655 FROM
656 Class
657 WHERE
658 ClassId = inClassId
659 )
660 );
661END CASE;
662END //
663/*---------------------AddQuestionnaire----------------------*/
664-- Add [questionnaire] content into table <questionnaire>
665-- Input: LecturerId, ClassId, Gender, Q1 - Q17, comment
666-- Output: Status Code
667/*-----------------------------------------------------------*/
668DROP PROCEDURE IF EXISTS AddQuestionnaire // CREATE PROCEDURE AddQuestionnaire(
669 IN inLecturerId VARCHAR(10),
670 IN inClassId VARCHAR(10),
671 IN inGender ENUM('M', 'F', 'O'),
672 IN inQuestion0 ENUM('1', '2', '3', '4', '5'),
673 IN inQuestion1 ENUM('1', '2', '3', '4', '5', 'N/A'),
674 IN inQuestion2 ENUM('1', '2', '3', '4', '5', 'N/A'),
675 IN inQuestion3 ENUM('1', '2', '3', '4', '5', 'N/A'),
676 IN inQuestion4 ENUM('1', '2', '3', '4', '5', 'N/A'),
677 IN inQuestion5 ENUM('1', '2', '3', '4', '5'),
678 IN inQuestion6 ENUM('1', '2', '3', '4', '5'),
679 IN inQuestion7 ENUM('1', '2', '3', '4', '5'),
680 IN inQuestion8 ENUM('1', '2', '3', '4', '5', 'N/A'),
681 IN inQuestion9 ENUM('1', '2', '3', '4', '5', 'N/A'),
682 IN inQuestion10 ENUM('1', '2', '3', '4', '5', 'N/A'),
683 IN inQuestion11 ENUM('1', '2', '3', '4', '5', 'N/A'),
684 IN inQuestion12 ENUM('1', '2', '3', '4', '5', 'N/A'),
685 IN inQuestion13 ENUM('1', '2', '3', '4', '5', 'N/A'),
686 IN inQuestion14 ENUM('1', '2', '3', '4', '5', 'N/A'),
687 IN inQuestion15 ENUM('1', '2', '3', '4', '5', 'N/A'),
688 IN inQuestion16 ENUM('1', '2', '3', '4', '5', 'N/A'),
689 IN inQuestion17 ENUM('1', '2', '3', '4', '5', 'N/A'),
690 IN inComment TEXT,
691 OUT statusCode INT
692) BEGIN CASE
693 WHEN inLecturerId IS NULL
694 OR inLecturerId NOT IN (
695 SELECT
696 LecturerId
697 FROM
698 Lecturer
699 INNER JOIN Teaching ON LecturerPK = LecturerT
700 ) THEN
701 SET
702 statusCode = 416;
703-- NON-EXISTENT/INVALID Lecturer Id
704 WHEN inClassId IS NULL
705 OR inClassId NOT IN (
706 SELECT
707 ClassId
708 FROM
709 Class
710 INNER JOIN Teaching ON ClassPK = ClassT
711 ) THEN
712 SET
713 statusCode = 407;
714-- NON-EXISTENT/INVALID Class Id
715 ELSE
716 SET
717 statusCode = 200;
718-- SUCCESS
719 INSERT INTO
720 Questionnaire (
721 ClassAndLecturer,
722 Gender,
723 Question0,
724 Question1,
725 Question2,
726 Question3,
727 Question4,
728 Question5,
729 Question6,
730 Question7,
731 Question8,
732 Question9,
733 Question10,
734 Question11,
735 Question12,
736 Question13,
737 Question14,
738 Question15,
739 Question16,
740 Question17,
741 Comment
742 )
743 SELECT
744 Teaching_PK,
745 inGender,
746 inQuestion0,
747 inQuestion1,
748 inQuestion2,
749 inQuestion3,
750 inQuestion4,
751 inQuestion5,
752 inQuestion6,
753 inQuestion7,
754 inQuestion8,
755 inQuestion9,
756 inQuestion10,
757 inQuestion11,
758 inQuestion12,
759 inQuestion13,
760 inQuestion14,
761 inQuestion15,
762 inQuestion16,
763 inQuestion17,
764 inComment
765 FROM
766 Teaching
767 INNER JOIN Lecturer ON LecturerT = LecturerPK
768 INNER JOIN Class ON ClassT = ClassPK
769 WHERE
770 LecturerId = inLecturerId
771 AND ClassId = inClassId;
772END CASE;
773END // DELIMITER;
774/*--------------------END--------------------------*/
775