enrollment_trg

CIS338 Final Project Spring 2017 Overview : The final project will be worth 100 points . It will consist of the creation of tables and development of PL/SQL objects for a student information system. An Entity Relationship diagram of the tables in the schema will be provided. In order to receive full credit, your code must co nform to the object names defined in the requirements and work without alteration against the tables as illustrated in the E -R diagram. My expectation is t hat your code will be thoroughly tested and include comment s. Also, I would encourage everyone to use the SQL Developer software for the project . In addition to the submission of scripts through Blackboard , all of the objects (tables, trigger and package) should be deployed on your Oracle account (e.g. ORA1, ORA2 , etc.) on the edu1 DMACC database. Please post any questions on the Discussion Board under the Final Project forum. Requirements : Create a script named final -ddl .sql that builds the tables described in the E-R diagram. The primary key and foreign key s noted in the E -R diagram should be applied to the schema to ensure referential integrity . You may use any naming convention that you prefer for the constraints. A DML script named final -dml.sql will be provided to populate th e tables with initial test d ata. Create a script named final -trg .sql that contains the PL/SQL code to create a t rigger 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 s hould 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 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 t he 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 calc ulation of the GPA is the sum of quality points ( credit hours multiplied by g rade 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. CIS338 Final Project Spring 2017 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 f or 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 tab le. No parameters will be passed to it. It should use the CALCULATE_GPA and CALCULATE_HOURS functions described above. Deliverables : Submit your final -ddl .sql , final -trg .sql and final -pkg .sql scripts as a ttachments to the Final Project assignment in Blackboard. The s ubmission is due at 6:00 P M on May 2nd .