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.

 

 

Student Information System Subject
PK Subject_Code Campus
CHAR(3) PK Subject_Description VARCHAR2(30) Major
PK Major_Code Campus_Code CHAR(2) Campus_Name VARCHAR2(10) Building Course
CHAR(10) Major_Description VARCHAR2(30) PK,FK1
PK Subject_Code
Course_Number CHAR(3)
CHAR(3) PK,FK1
PK Campus_Code
Building_Code CHAR(2)
CHAR(2) Building_Name VARCHAR2(20) Course_Description VARCHAR2(30)
Credit_Hours
NUMERIC(1,0) Room PK Zipcode CHAR(5) City
State VARCHAR2(20)
CHAR(2) PK,FK3
PK,FK1
PK,FK1
PK Term_Code
Subject_Code
Course_Number
Section CHAR(6)
CHAR(3)
CHAR(3)
CHAR(3) FK2
FK2
FK2 Campus_Code
Building_Code
Room_Code
Enrollment CHAR(2)
CHAR(2)
CHAR(3)
NUMERIC(3,0) Student Campus_Code CHAR(2)
Building_Code CHAR(2)
Room_Code
CHAR(3)
Capacity NUMERIC(3,0) Term
PK Term_Code CHAR(6) Term_Description VARCHAR2(15) PK Student_ID CHAR(6) FK2 Last_Name
First_Name
Birth_Date
Street_Address
Zipcode
GPA
Credit_Hours
Email_Address
Major_Code VARCHAR2(25)
VARCHAR2(25)
DATE
VARCHAR2(30)
CHAR(5)
NUMERIC(3,2)
NUMERIC(3,0)
VARCHAR2(30)
CHAR(10) FK1 PK,FK1
PK,FK1
PK Section Zipcode Enrollment
PK,FK1
PK,FK1
PK,FK1
PK,FK1
PK,FK2 Term_Code
Subject_Code
Course_Number
Section
Student_ID CHAR(6)
CHAR(3)
CHAR(3)
CHAR(3)
CHAR(6) FK3 Grade CHAR(1) Grade
PK Grade CHAR(1) Grade_Value NUMERIC(1,0)

• 1493189613-Student E-R Diagram.pdf

ComputerScienceExpert

(11)

Status NEW Posted 26 Apr 2017 06:04 AM My Price 11.00

-----------

Attachments

file 1493189617-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer