A. Lab 4 of 7 : Exception Handlers

B. Lab Overview - Scenario / Summary:

You have been asked to implement exception handlers to cater to various errors and conditions. 

Upon the completion of this lab, students will have hands-on experience coding and using exceptions.

C. Deliverables:

Lab Steps (all in IBM System z) Points
Step 1 Aggregate Functions 15 Pts.
Step 2 Parent Key Not Found Exception Handler 15 Pts.
Step 3 Referential Integrity Exception Handler 15 Pts.
Step 4 Duplicate Key Found Exception Handler 15 Pts.
Total Lab Points 60 Pts.

D. Lab Steps:
Each program is independent of any other programs and is to be executed using the related database objects in the student database schema.

Step 1: Aggregate Functions
a) Code an SQL PL Procedure that will accept a student ID and count the number of courses in which that student is enrolled. (Count the number of records in the Enrollment table that correspond to the student.)
b) Output the message: [student_id] is enrolled in [tot_sec] courses. 
c) For those students not enrolled in any courses, output the message: [student_id] is not enrolled in any courses. 
d) Test your procedure with the following values: 102, 124, 292.

Step 2: Parent Key Not Found Exception Handler
a) Code an SQL PL Procedure to add a record to the Section table. 
b) The procedure should accept the Section ID and Course No as parameters. 
c) Include the following for the other columns:

Attribute Value
Section number 5
Instructor identification 106
Start date Today's date
Location Null
Capacity Null

d) Use USER and CURRENT DATE for the auditing columns (Created By, Created Date, Modified By, Modified Date). 
e) When a record can successfully be inserted, output the message: Section [section_id] of course number [course_no] was inserted. 
f) Use an exception to handle the error when the course parent does not exist; in that case, output the message: The INSERT was not successful. You must insert the course parent first. 
g) Execute your procedure with the following values: 

• section id 77 with course number 10
• section id 78 with course number 400 
Step 3: Referential Integrity Exception Handler

a) Create a stored procedure to delete Student records; the student ID should be passed into the procedure as a parameter.
b) Use an exception handler to trap the errors that arise when the procedure tries to delete a student who is still in the enrollment table. 
c) For successful deletions, output a message which says:
Student id [student_id] has been deleted
d) For unsuccessful deletions, output a message which says:
The delete was not successful. Delete the student from enrollment first. 
e) Execute the procedure for students 124 and 158.

Step 4: Duplicate Key Found

a) Create a stored procedure to insert Student records; the student ID should be passed into the procedure as a parameter. Default values can be used to set the other fields in the table.
b) Use an exception handler to trap the errors that arise when the procedure tries to insert a student with a number that is already in the table.
c) For successful insertions, output a message which says:
Student id [student_id] has been inserted
d) For unsuccessful insertions, output a message which says:
Student id [student_id] is already in the table 
e) Execute the procedure using student ids of 110 and 190.

END OF LAB 4

For Submission into Week 4 iLab Dropbox:
Zipped file containing:
• SQL PL code for each task
• Output screenshots from executing each task