SophiaPretty
$14/per page/Negotiable
Please help me create following script. Attach is database diagram, I already create tables. Thank you!
Â
Create a script named final-trg.sql that contains the PL/SQL code to create a trigger named ENROLLMENT_TRG. The trigger should be set up to fire whenever a student registers (insert) or withdraws (delete) from a course in the Enrollment table. It should increment the Enrollment column for the appropriate course in the Section table by 1 for each registration. Conversely, it should decrement the Enrollment column for the appropriate course in the Section table by 1 for each withdrawal.
Create a script named final-pkg.sql that contains the PL/SQL code to create a package named Student_pkg with the following stored program units.
1.Function named CALCULATE_GPA that will calculate and return a student's grade point average.It should accept the ID for the student to be processed as a parameter and use only courses that have a grade entered in the Enrollment table for the student ID. The calculation of the GPA is the sum of quality points (credit hours multiplied by grade value) for all graded courses that is divided by the sum of credit hours for all graded courses. The grade value for a letter grade can be determined by referencing the Grade table. For example, a letter grade of A has a grade value of 4 in the Grade table. The quality points for a 3 hours class with a grade of A would be 12 (3 credit hours multiplied by a grade value of 4). Do not hard-code the grade values in your function. The GPA should be rounded to 2 positions to the right of the decimal point (e.g. 3.33).A return value of 0.00 should be returned for any students that do not have any graded courses in the Enrollment table.
2. Function named CALCULATE_HOURS that will calculate and return the number of hours a student has completed. It should accept the ID for the student to be processes as a parameter.Only courses that have a grade entered in the Enrollment table should be counted in the calculation of hours for the student ID. A return value of 0 should be returned for any students that do not have any graded courses in the Enrollment table.
3. Procedure named UPDATE_STUDENTS that will update the GPA and Credit_Hours columns for every student in the Student table. No parameters will be passed to it. It should use the CALCULATE_GPA and CALCULATE_HOURS functions described above.