create a script named final-trg.sql that contains the PL/SQL code to create a tr
ID: 657139 • Letter: C
Question
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 PL/SQL code to create a package named student_pkg with the following stored program unit.
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 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 processed 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.
CREATE TABLE Major (Major_Code CHAR(10), PRIMARY KEY (Major_Code),
Major_Description VARCHAR2(30) );
CREATE TABLE Zipcode (Zipcode CHAR(5), PRIMARY KEY (Zipcode),
City VARCHAR2(20), State CHAR(2) );
CREATE TABLE Student (Student_ID CHAR(6), PRIMARY KEY (Student_ID),
Last_Name VARCHAR2(25), First_Name VARCHAR2(25), Birth_Date DATE,
Street_Address VARCHAR2(30), Zipcode CHAR (5), GPA NUMBER(3,2), FOREIGN KEY (Zipcode) REFERENCES Zipcode(Zipcode),
Credit_Hours NUMBER(3,0), Email_Address VARCHAR2(30), Major_Code CHAR(10), FOREIGN KEY (Major_Code) REFERENCES Major(Major_Code) );
CREATE TABLE Subject ( Subject_Code CHAR(3), PRIMARY KEY (Subject_Code),
Subject_Description VARCHAR2(30) );
CREATE TABLE Course (Subject_Code CHAR (3), Course_Number CHAR(3), PRIMARY KEY (Subject_Code, Course_Number),
FOREIGN KEY (Subject_Code) REFERENCES Subject(Subject_Code), Course_Description VARCHAR2(30), Credit_Hours NUMBER(1,0) );
CREATE TABLE Campus (Campus_Code CHAR(2), PRIMARY KEY (Campus_Code), Campus_Name VARCHAR2(10) );
CREATE TABLE Building (Campus_Code CHAR(2), Building_Code CHAR(2), Building_Name VARCHAR2(20),
PRIMARY KEY (Campus_Code, Building_Code),
FOREIGN KEY (Campus_Code) REFERENCES Campus(Campus_Code) );
CREATE TABLE Room (Campus_Code CHAR(2), Building_Code CHAR(2), Room_Code CHAR(3), Capacity NUMBER(3,0),
PRIMARY KEY (Campus_Code, Building_Code, Room_Code), FOREIGN KEY (Campus_Code, Building_Code) REFERENCES Building(Campus_Code, Building_Code),
FOREIGN KEY (Campus_Code, Building_Code) REFERENCES Building(Campus_Code, Building_Code) );
CREATE TABLE Section (Term_Code CHAR(6), Subject_Code CHAR(3), Course_Number CHAR(3),
Section CHAR(3), Campus_Code CHAR(2), Building_Code CHAR(2), Room_Code CHAR(3), Enrollment NUMBER(3,0),
PRIMARY KEY (Term_Code, Subject_Code,Course_Number, Section),
FOREIGN KEY (Subject_Code, Course_Number) REFERENCES Course(Subject_Code, Course_Number),
FOREIGN KEY (Subject_COde,Course_Number) REFERENCES Course(Subject_Code, Course_Number),
FOREIGN KEY (Campus_Code, Building_Code, Room_Code) REFERENCES Room(Campus_Code, Building_Code, Room_Code),
FOREIGN KEY (Campus_Code, Building_Code, Room_Code) REFERENCES Room(Campus_Code, Building_Code, Room_Code),
FOREIGN KEY (Campus_Code, Building_Code, Room_Code) REFERENCES Room(Campus_Code, Building_Code, Room_Code)
);
CREATE TABLE Grade (Grade CHAR(1), Grade_Value NUMBER(1,0), PRIMARY KEY (Grade) );
CREATE TABLE Enrollment (Term_Code CHAR(6), Subject_Code CHAR(3), Course_Number CHAR(3), Section CHAR(3), Student_ID CHAR(6),
Grade CHAR(1),
PRIMARY KEY (Term_Code, Subject_Code, Course_Number, Section, Student_ID),
FOREIGN KEY (Term_Code, Subject_Code, Course_Number, Section) REFERENCES Section (Term_Code, Subject_Code, Course_Number, Section),
FOREIGN KEY (Term_Code, Subject_Code, Course_Number, Section) REFERENCES Section (Term_Code, Subject_Code, Course_Number, Section),
FOREIGN KEY (Term_Code, Subject_Code, Course_Number, Section) REFERENCES Section (Term_Code, Subject_Code, Course_Number, Section),
FOREIGN KEY (Term_Code, Subject_Code, Course_Number, Section) REFERENCES Section (Term_Code, Subject_Code, Course_Number, Section),
FOREIGN KEY (Student_ID) REFERENCES Student (Student_ID),
FOREIGN KEY (Grade) REFERENCES Grade (Grade)
);
CREATE TABLE Term (Term_Code CHAR(6), Term_Description VARCHAR2(15), PRIMARY KEY (Term_Code) );
REM INSERTING into ZIPCODE
Insert into ZIPCODE (ZIPCODE,CITY,STATE) values ('50023','Ankeny','IA');
Insert into ZIPCODE (ZIPCODE,CITY,STATE) values ('50266','West Des Moines','IA');
Insert into ZIPCODE (ZIPCODE,CITY,STATE) values ('50310','Des Moines','IA');
commit;
REM INSERTING into MAJOR
Insert into MAJOR (MAJOR_CODE,MAJOR_DESCRIPTION) values ('LIB-ARTS ','Liberal Arts');
Insert into MAJOR (MAJOR_CODE,MAJOR_DESCRIPTION) values ('MGT-INFO ','Management Information Systems');
Insert into MAJOR (MAJOR_CODE,MAJOR_DESCRIPTION) values ('ACCT-INFO ','Accounting Information Systems');
commit;
REM INSERTING into STUDENT
Insert into STUDENT (STUDENT_ID,LAST_NAME,FIRST_NAME,BIRTH_DATE,STREET_ADDRESS,ZIPCODE,GPA,CREDIT_HOURS,EMAIL_ADDRESS,MAJOR_CODE) values ('111111','Smith','John',to_timestamp('01-JAN-92 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'123 Main Street','50023',0,0,'jsmith@school.edu','LIB-ARTS ');
Insert into STUDENT (STUDENT_ID,LAST_NAME,FIRST_NAME,BIRTH_DATE,STREET_ADDRESS,ZIPCODE,GPA,CREDIT_HOURS,EMAIL_ADDRESS,MAJOR_CODE) values ('222222','Doe','Jane',to_timestamp('02-FEB-93 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'101 Park Avenue','50310',0,0,'jdoe@school.edu','MGT-INFO ');
Insert into STUDENT (STUDENT_ID,LAST_NAME,FIRST_NAME,BIRTH_DATE,STREET_ADDRESS,ZIPCODE,GPA,CREDIT_HOURS,EMAIL_ADDRESS,MAJOR_CODE) values ('333333','Brown','Joe',to_timestamp('03-MAR-94 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'200 Elm Street','50266',0,0,'jbrown@school.edu','ACCT-INFO ');
Insert into STUDENT (STUDENT_ID,LAST_NAME,FIRST_NAME,BIRTH_DATE,STREET_ADDRESS,ZIPCODE,GPA,CREDIT_HOURS,EMAIL_ADDRESS,MAJOR_CODE) values ('444444','Anderson','Andy',to_timestamp('04-APR-95 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'1 2nd Avenue','50310',0,0,'aanderson@school.edu','LIB-ARTS ');
commit;
REM INSERTING into GRADE
Insert into GRADE (GRADE,GRADE_VALUE) values ('A',4);
Insert into GRADE (GRADE,GRADE_VALUE) values ('B',3);
Insert into GRADE (GRADE,GRADE_VALUE) values ('C',2);
Insert into GRADE (GRADE,GRADE_VALUE) values ('D',1);
Insert into GRADE (GRADE,GRADE_VALUE) values ('F',0);
commit;
REM INSERTING into CAMPUS
Insert into CAMPUS (CAMPUS_CODE,CAMPUS_NAME) values ('AN','Ankeny');
Insert into CAMPUS (CAMPUS_CODE,CAMPUS_NAME) values ('UC','Urban');
Insert into CAMPUS (CAMPUS_CODE,CAMPUS_NAME) values ('WC','West');
commit;
REM INSERTING into BUILDING
Insert into BUILDING (CAMPUS_CODE,BUILDING_CODE,BUILDING_NAME) values ('AN','08','Business');
Insert into BUILDING (CAMPUS_CODE,BUILDING_CODE,BUILDING_NAME) values ('AN','06','Science');
Insert into BUILDING (CAMPUS_CODE,BUILDING_CODE,BUILDING_NAME) values ('UC','01','Main');
Insert into BUILDING (CAMPUS_CODE,BUILDING_CODE,BUILDING_NAME) values ('UC','04','Betts');
Insert into BUILDING (CAMPUS_CODE,BUILDING_CODE,BUILDING_NAME) values ('WC','01','Main');
commit;
REM INSERTING into ROOM
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('AN','08','110',20);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('AN','08','120',25);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('AN','06','120',24);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('UC','01','101',40);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('UC','01','102',40);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('UC','04','101',25);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('WC','01','100',24);
Insert into ROOM (CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,CAPACITY) values ('WC','01','200',24);
commit;
REM INSERTING into TERM
Insert into TERM (TERM_CODE,TERM_DESCRIPTION) values ('201203','Summer 2012');
Insert into TERM (TERM_CODE,TERM_DESCRIPTION) values ('201301','Fall 2012');
Insert into TERM (TERM_CODE,TERM_DESCRIPTION) values ('201302','Spring 2013');
Insert into TERM (TERM_CODE,TERM_DESCRIPTION) values ('201202','Spring 2012');
Insert into TERM (TERM_CODE,TERM_DESCRIPTION) values ('201201','Fall 2011');
commit;
REM INSERTING into SUBJECT
Insert into SUBJECT (SUBJECT_CODE,SUBJECT_DESCRIPTION) values ('ACC','Accounting');
Insert into SUBJECT (SUBJECT_CODE,SUBJECT_DESCRIPTION) values ('ENG','English');
Insert into SUBJECT (SUBJECT_CODE,SUBJECT_DESCRIPTION) values ('SPC','Speech');
Insert into SUBJECT (SUBJECT_CODE,SUBJECT_DESCRIPTION) values ('MAT','Mathematics');
commit;
REM INSERTING into COURSE
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('ACC','131','Principles of Accounting',4);
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('ACC','132','Intermediate Accounting',4);
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('ENG','105','Composition I',3);
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('ENG','106','Composition II',3);
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('SPC','101','Small Group Discussion',3);
Insert into COURSE (SUBJECT_CODE,COURSE_NUMBER,COURSE_DESCRIPTION,CREDIT_HOURS) values ('MAT','157','Business Statistics',4);
commit;
REM INSERTING into SECTION
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201201','ENG','105','U1 ','UC','01','101',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201201','ENG','106','U1 ','UC','01','102',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201201','ACC','131','A1 ','AN','08','110',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201201','ACC','132','A1 ','AN','08','120',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201202','MAT','157','W1 ','WC','01','100',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','ACC','131','U1 ','UC','04','101',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201202','SPC','101','W1 ','WC','01','200',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201203','ENG','105','A1 ','AN','08','110',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201203','MAT','157','A1 ','AN','06','120',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','ENG','105','W1 ','WC','01','100',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','ENG','106','W1 ','WC','01','200',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','ACC','132','U1 ','UC','04','101',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','MAT','157','A1 ','AN','06','120',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201301','SPC','101','A1 ','AN','08','110',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201302','SPC','101','W1 ','WC','01','100',0);
Insert into SECTION (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,CAMPUS_CODE,BUILDING_CODE,ROOM_CODE,ENROLLMENT) values ('201302','MAT','157','W1 ','WC','01','200',0);
commit;
REM INSERTING into ENROLLMENT
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201201','ENG','105','U1 ','111111','A');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201201','ACC','131','A1 ','111111','B');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201201','ENG','105','U1 ','222222','C');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201201','ACC','131','A1 ','222222','D');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201202','MAT','157','W1 ','111111','A');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201202','MAT','157','W1 ','222222','B');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201203','MAT','157','A1 ','333333','C');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201301','ENG','105','W1 ','333333','D');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201301','ACC','131','U1 ','333333','D');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201301','ACC','132','U1 ','111111','A');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201301','ACC','132','U1 ','222222','A');
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201302','SPC','101','W1 ','111111',null);
Insert into ENROLLMENT (TERM_CODE,SUBJECT_CODE,COURSE_NUMBER,SECTION,STUDENT_ID,GRADE) values ('201302','MAT','157','W1 ','444444',null);
commit;
update section s
set enrollment = (select count(*)
from enrollment e
where e.term_code = s.term_code
and e.subject_code = s.subject_code
and e.course_number = s.course_number
and e.section = s.section);
commit;
Explanation / Answer
create table student(
sno number primary key,
sname char(10),
total number,
obt number,
grade char(10));
insert into student(sno,sname,total) values(1,'Ronak',250);
insert into student(sno,sname,total) values(2,'Reena',250);
create table subject(
sbno number primary key,
sbname char(10),
sbm number);
insert into subject values(301,'CPP',48);
insert into subject values(302,'OR',48);
create table tran(
sno number references student,
sbno number references subject,
obt number);
insert into tran values(1,301,25);
insert into tran values(1,302,48);
insert into tran values(2,301,55);
insert into tran values(2,302,48);
create or replace package mixt as
procedure stdins(no number);
function stdgrd(no number) return number;
procedure disp;
end mixt;
create or replace package body mixt as
procedure stdins(no number) as
cursor c_mark is select sum(obt) from tran where sno=no;
tt number;
begin
open c_mark;
loop
fetch c_mark into tt;
exit when c_mark%notfound;
update student set obt=tt where sno=no;
end loop;
end;
function stdgrd(no number) return number as
cursor c_mark is select obt from student where sno=no;
tt number;
g char(10);
begin
open c_mark;
loop
fetch c_mark into tt;
exit when c_mark%notfound;
if tt>=35 and tt<=50 then
g:='Pass';
elsif tt>=51 and tt<=100 then
g:='Seond';
elsif tt>=101 and tt<=200 then
g:='First';
elsif tt>=200 and tt<=250 then
g:='Dist';
end if;
update student set grade=g where sno=no;
end loop;
return 0;
end;
procedure disp as
i number:=1;
cursor c_stud is select sno,sname,total,obt,grade from student;
no student.sno%type;
name student.sname%type;
t student.total%type;
o student.obt%type;
g student.grade%type;
begin
Dbms_output.put_line('Sr.No Rollno Name Totalmarks Marksobtain Grade');
open c_stud;
loop
fetch c_stud into no,name,t,o,g;
exit when c_stud%notfound;
dbms_output.put_line( i ||' '||no||' '||name||' '||t||' '||o||' '||g);
i:=i+1;
end loop;
end;
end mixt;
declare
n number;
a number;
begin
n:=&n;
mixt.stdins(n);
a:=mixt.stdgrd(n);
mixt.disp;
end;
create or replace trigger tck
before insert on tran
for each row
declare
t number;
m number;
begin
select sum(obt) into t from tran where sno=:new.sno;
t:=t+:new.obt;
select sbm into m from subject where sbno=:new.sbno;
if t>250 or :new.obt>m then
raise_application_error(-200004,'Total of obtain marks must less than 250');
end if;
end;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.