For both Parts 1 and 2 submit a separate .sql file.

Part 1. PL/SQL Programming (40 points) Consider the table STUDENT with attributes ID, Name, Midterm, Final, and Homework, and the table WEIGHTS with attributes MidPct, FinPct, and HWPct defined and populated by the following script:

DROP TABLE STUDENT CASCADE CONSTRAINTS;

CREATE TABLE STUDENT(

ID CHAR(3),

Name VARCHAR2(20),

Midterm NUMBER(3,0) CHECK (Midterm>=0 AND Midterm<=100),

Final NUMBER(3,0) CHECK (Final>=0 AND Final<=100),

Homework NUMBER(3,0) CHECK (Homework>=0 AND Homework<=100),

PRIMARY KEY (ID)

);

INSERT INTO STUDENT VALUES ( '445', 'Seinfeld', 85, 90, 99 );

INSERT INTO STUDENT VALUES ( '909', 'Costanza', 74, 72, 86 );

INSERT INTO STUDENT VALUES ( '123', 'Benes', 93, 89, 91 );

INSERT INTO STUDENT VALUES ( '111', 'Kramer', 99, 91, 93 );

INSERT INTO STUDENT VALUES ( '667', 'Newman', 78, 82, 83 );

INSERT INTO STUDENT VALUES ( '888', 'Banya', 50, 65, 50 );

SELECT * FROM STUDENT;

DROP TABLE WEIGHTS CASCADE CONSTRAINTS;

CREATE TABLE WEIGHTS(

MidPct NUMBER(2,0) CHECK (MidPct>=0 AND MidPct<=100),

FinPct NUMBER(2,0) CHECK (FinPct>=0 AND FinPct<=100),

HWPct NUMBER(2,0) CHECK (HWPct>=0 AND HWPct<=100)

);

INSERT INTO WEIGHTS VALUES ( 30, 30, 40 );

SELECT * FROM WEIGHTS;

COMMIT;

Write an anonymous PL/SQL block that will do the following:

First, report the three weights found in the WEIGHTS table. (You may assume that the WEIGHTS table contains only one record.) Next, output the name of each student in the STUDENT table and their overall score, computed as x percent Midterm, y percent Final, and z percent Homework, where x, y, and z are the corresponding percentages found in the WEIGHTS table. (You may assume that x+y+z=100.) Also convert each students overall score to a letter grade by the rule 90-100=A, 80-89.99=B, 65-79.99=C, 0-64.99=F, and include the letter grade in the output. Output each students information on a separate line. For the sample data given above, the output should be:

Weights are 30, 30, 40

445 Seinfeld 92.1 A

909 Costanza 78.2 C

123 Benes 91 A

111 Kramer 94.2 A

667 Newman 81.2 B

888 Banya 54.5 F

Of course, this is just an example your PL/SQL block should work in general, not just for the given sample data.

Part 2. (Triggers. 60 points) Consider the SECTION and ENROLLMENT tables defined by the following script, which also populates the SECTION table;

DROP TABLE ENROLLMENT CASCADE CONSTRAINTS;

DROP TABLE SECTION CASCADE CONSTRAINTS;

CREATE TABLE SECTION(

SectionID CHAR(5),

Course VARCHAR2(7),

Students NUMBER DEFAULT 0,

CONSTRAINT PK_SECTION

PRIMARY KEY (SectionID)

);

CREATE TABLE ENROLLMENT(

SectionID CHAR(5),

StudentID CHAR(7),

CONSTRAINT PK_ENROLLMENT

PRIMARY KEY (SectionID, StudentID),

CONSTRAINT FK_ENROLLMENT_SECTION

FOREIGN KEY (SectionID)

REFERENCES SECTION (SectionID)

);

INSERT INTO SECTION (SectionID, Course) VALUES ( '12345', 'CSC 355' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '22109', 'CSC 309' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '99113', 'CSC 300' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '99114', 'CSC 300' );

COMMIT;

SELECT * FROM SECTION;

The Students attribute of SECTION should store a count of how many students are enrolled in the section that is, the number of records in ENROLLMENT with that SectionID and its value should never exceed five. (They are very small sections) Your task is to write three triggers that will maintain the value of the Students attribute as changes are made to the ENROLLMENT table.

Write definitions of the following three triggers:

A. Write a trigger that will fire when a user attempts to INSERT a row into ENROLLMENT. This trigger will check the value of SECTION.Students for the corresponding section. If SECTION.Students is less than 5, then there is still room in the section so allow the insert and update SECTION.Students. If SECTION.Students is equal to 5, then the section is full so it will cancel the INSERT and display an error message stating that the section is full.

Sample Data:

INSERT INTO ENROLLMENT VALUES ('12345', '1234567');

INSERT INTO ENROLLMENT VALUES ('12345', '2234567');

INSERT INTO ENROLLMENT VALUES ('12345', '3234567');

INSERT INTO ENROLLMENT VALUES ('12345', '4234567');

INSERT INTO ENROLLMENT VALUES ('12345', '5234567');

INSERT INTO ENROLLMENT VALUES ('12345', '6234567');

SELECT * FROM Section;

SELECT * FROM Enrollment;

The last insert should return an error message that looks like:

Error starting at line : 27 in command -

INSERT INTO ENROLLMENT VALUES ('12345', '6234567')

Error report -

SQL Error: ORA-20200: Section is full.

ORA-06512: at "JWAGNE32.ADDSTUDENT", line 14

ORA-04088: error during execution of trigger 'JWAGNE32.ADDSTUDENT'

The output from the SELECT queries should look like:

SECTIONID COURSE STUDENTS

--------- ------- ----------

12345 CSC 355 5

22109 CSC 309 0

99113 CSC 300 0

99114 CSC 300 0

SECTIONID STUDENTID

--------- ---------

12345 1234567

12345 2234567

12345 3234567

12345 4234567

12345 5234567

B. Write a trigger that will fire when a user attempts to DELETE one or more rows from ENROLLMENT. This trigger will update the values of SECTION.Students for any affected sections to make sure they are accurate after the rows are deleted, by decreasing the value of SECTION.Students by one each time a student is removed from a section.

Sample Data:

DELETE FROM ENROLLMENT WHERE StudentID = '1234567';

SELECT * FROM Section;

SELECT * FROM Enrollment;

The output from the SELECT queries should look like:

SECTIONID COURSE STUDENTS

--------- ------- ----------

12345 CSC 355 4

22109 CSC 309 0

99113 CSC 300 0

99114 CSC 300 0

SECTIONID STUDENTID

--------- ---------

12345 2234567

12345 3234567

12345 4234567

12345 5234567

C. The third trigger, named NoChanges, will fire when a user attempts to UPDATE one or more rows of ENROLLMENT. The trigger will cancel the UPDATE and display an error message stating that no updates are permitted to existing rows of ENROLLMENT.

Sample Data:

UPDATE Enrollment

SET StudentID = '7654321'

WHERE StudentID = '4234567';

The output from the UPDATE query should look like:

Error starting at line : 59 in command -

UPDATE Enrollment

SET StudentID = '7654321'

WHERE StudentID = '4234567'

Error report -

SQL Error: ORA-20201: Update is not allowed ENROLLMENT.

ORA-06512: at "JWAGNE32.UPDATESTUDENT", line 2

ORA-04088: error during execution of trigger 'JWAGNE32.UPDATESTUDENT'

Hint: EnrollStudent and WithdrawStudents should be row-level triggers; NoChanges should be a statement-level trigger.

Run the script to define your triggers and test them to make sure they work by doing a few INSERTS, DELETES, and UPDATES on the ENROLLMENT table.