Description

Notes:

·         For each procedure, turn in the following:

o   Turn spool on, then run the procedure with several values.  Turn spool off.

o   Turn in a copy of the code

o   Turn in a copy/results of the spool file (showing the output).

·         Prerequisites:

o   Run the following script: hw5ScriptEnrollment.sql

·         ER Diagram

o   The script will create the following tables and relationships

Complete the following

Tutorial 8

1.       Create a procedure, VALIDATE_HOURS_LIMIT(pStudent), that will return an error message “Student exceeds 15 hours limit after enrolling” if after the enrollment, the student will be enrolled in more than 15 credit hours.

Incorporate this procedure into the ENROLL_STUDENT procedure.  That is, a student can be enrolled to a course only when after the enrollment, the total semester credit hours does not exceed 15.

2.       Create a procedure, VALIDATE_STANDING(pStudent, pClass), that will return an error message “Course standing NOT met.” if the student does not have the standing requirement of the course.

Incorporate this procedure into the ENROLL_STUDENT procedure.  That is, a student can be enrolled to a course only if the course standing is met.

3.       Create a function, GRADE_POINT(pLetter), that will return the following:

4 – If the grade is ‘A’,

3 – If the grade is ‘B’,

2 – If the grade is ‘C’,

1 – If the grade is ‘D’,

0         – Otherwise.

4.       Create a function, STANDING(pTotalHours), that will return the following:

1         –  If total credit hours are between 0 – 30,

2         –  If total credit hours are between 31 – 60,

3         –  If total credit hours are between 61 – 90,

4         –  91 or greater

5.       Create a function, STANDING_MET(pStudent, pClass), that returns TRUE if the student has the appropriate standing to enroll in a course, FALSE otherwise.

6.       Create a function, CAPACITY_OK(pStudent, pClass), that returns TRUE if the class still has room for the student to enroll, FALSE otherwise.

7.       Incorporate the STANDING_MET and CAPACITY_OK into ENROLL_STUDENT.  A student is enrolled only if the class standing is met and the class is not full.

8.       Putting it all together.  Create a procedure, ENROLL_STUDENT(pStudent, pClass), to enroll the student to a course if the following criteria are met:

a.       Student number must be valid,

b.      The class id must be valid,

c.       There is still room in the class, and

d.      After enrolling, the student’s total credit hours do not exceed 15.

If the student number or class id are invalid, your program should not check for c or d. 

Print an error message to explain all the reasons why the enrollment failed.

You must call at least one function and one procedure from ENROLL_STUDENT.