Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.

QUESTION

Need help with the following Oracle SQL Question, simple scripts only not complex..

Need help with the following Oracle SQL Question, simple scripts only not complex... after this there are questions coming along with this so i will send in a separate questions..

Attached are all necessary files

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 conform 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 that your code will be thoroughly tested and include comments. 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 keys 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 the tables with initial test data.

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 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.

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.

Deliverables:

Submit your final-ddl.sql, final-trg.sql and final-pkg.sql scripts as attachments to the Final Project assignment in Blackboard.

Show more
LEARN MORE EFFECTIVELY AND GET BETTER GRADES!
Ask a Question