(a) (2 marks) Name the two different types of variables that have been used in the above stored procedure. Describe these variable types with one characteristic each.

(b) (3 marks) Discuss all the error conditions that you may come across as a result of the execution of the procedure. Add an appropriate error handler to the procedure in order to handle such errors.

(c) (2 marks) Create a MySQL procedure that takes department ID as an input and prints the details of all the students including Student ID, name, and the description of the major that they are currently enrolled in.

You may use the following procedure template: CREATE PROCEDURE Student_Major (IN I_deptID INT)

BEGIN . . .

END

QUESTION B1. This question assumes the following relational schema: (7 marks) Student (StudentID, Lname, Fname, Address, BirthDate, Phone, MajorID, DeptID ) Primary Key: StudentID Foreign key: MajorID refers to MajorID in Major Major (MajorID, MajorDesc) Primary Key: MajorID You have been given the following stored MySQL procedure: CREATE PROCEDURE StudentBirthDate (IN V_DOB DATE) BEGIN DECLARE V_SID INT; DECLARE V_NAME VARCHAR (60) SELECT StudentID, CONCAT(Fname, ' FROM Student WHERE BirthDate - V_DOB; % name INTO V-SID, V-NAME END