Assignment 6 Solved

1. (PL/SQL Programming) Consider the table STUDENT with attributes ID, Name, Mid, Fin, and HW, and the table PERCENTAGES with attributes MidPercent, FinalPercent, and HWPercent defined and populated by the following script:

DROP TABLE STUDENT CASCADE CONSTRAINTS;

CREATE TABLE STUDENT

(

ID CHAR(4),

Name VARCHAR2(20),

HW NUMBER(3,0) CHECK (HW=0 AND HW<=100),

Mid NUMBER(3,0) CHECK (Mid=0 AND Mid<=100),

Fin NUMBER(3,0) CHECK (Fin=0 AND Fin<=100),

PRIMARY KEY (ID)

);

INSERT INTO STUDENT VALUES ( ‘4145’, ‘Seinfeld’, 98, 85, 90 );

INSERT INTO STUDENT VALUES ( ‘9009’, ‘Costanza’, 80, 74, 72 );

INSERT INTO STUDENT VALUES ( ‘1233’, ‘Kramer’, 91, 94, 89 );

INSERT INTO STUDENT VALUES ( ‘1111’, ‘Benes’, 93, 99, 91 );

INSERT INTO STUDENT VALUES ( ‘6676’, ‘Newman’, 84, 78, 84 );

INSERT INTO STUDENT VALUES ( ‘8889’, ‘Banya’, 50, 52, 65 );

SELECT * FROM STUDENT;

DROP TABLE PERCENTAGES CASCADE CONSTRAINTS;

CREATE TABLE PERCENTAGES

(

HWPercent NUMBER(2,0) CHECK (HWPercent=0 AND HWPercent<=100),

MidPercent NUMBER(2,0) CHECK (MidPercent=0 AND MidPercent<=100),

FinPercent NUMBER(2,0) CHECK (FinPercent=0 AND FinPercent<=100)

);

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

SELECT * FROM PERCENTAGES;

COMMIT;

Write a script file Problem1.sql containing an anonymous PL/SQL block that will do the following:

First, report the three percentages found in the PERCENTAGES table. (You may assume that the PERCENTAGES table contains only one record.) Next, output the name of each student in the STUDENT table and their overall score, computed as x percent Homework, y percent Midterm, and z percent Final, where x, y, and z are the corresponding percentages found in the PERCENTAGES table.

(You may assume that x+y+z=100.) Also convert each student’s overall score to a letter grade by the rule 90-100=A, 80-89.99=B, 70-79.99=C, 60-60.99=D, 0-59.99=F, and include the letter grade in the output. Output each student’s information on a separate line. For the sample data given above, the output should be:

Weights are 40, 30, 30

4145 Seinfeld 91.7 A

9009 Costanza 75.8 C

1233 Kramer 91.3 A

1111 Benes 94.2 A

6676 Newman 82.2 B

8889 Banya 55.1 F

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

2. (Triggers) Consider the JOB and CONTRACT tables defined by the following script, which also populates the JOB table:

DROP TABLE CONTRACT CASCADE CONSTRAINTS;

DROP TABLE JOB CASCADE CONSTRAINTS;

CREATE TABLE JOB

(

JobID CHAR(3),

Title VARCHAR2(20),

ContractCount NUMBER(1,0) DEFAULT 0,

CONSTRAINT PK_JOB

PRIMARY KEY (JobID)

);

CREATE TABLE CONTRACT

(

JobID CHAR(3),

WorkerID CHAR(7),

Payment NUMBER(6,2),

CONSTRAINT PK_CONTRACT

PRIMARY KEY (JobID, WorkerID),

CONSTRAINT FK_CONTRACT_JOB

FOREIGN KEY (JobID)

REFERENCES JOB (JobID)

);

INSERT INTO JOB (JobID, Title) VALUES ( ‘100’, ‘Security’ );

INSERT INTO JOB (JobID, Title) VALUES ( ‘299’, ‘Infrastructure’ );

INSERT INTO JOB (JobID, Title) VALUES ( ‘757’, ‘Compliance’ );

SELECT * FROM JOB;

COMMIT;

The ContractCount attribute of JOB should store a count of how many workers have signed contracts to work on that job – that is, the number of records in CONTRACT with that JobID – and its value should never exceed 4. Your task is to write three triggers that will maintain the value of the ContractCount attribute in JOB as changes are made to the CONTRACT table.

Write a script file Problem2.sql containing definitions of the following three triggers:

1. The first trigger, named NewContract, will fire when a user attempts to INSERT a row into CONTRACT. This trigger will check the value of ContractCount for the corresponding job. If ContractCount is less than 4, then there is still room in the job for another worker, so it will allow the INSERT to occur and will increase the value of ContractCount by one. If ContractCount is equal to 4, then the job is full, so it will cancel the INSERT and display an error message stating that the job is full.

2. The second trigger, named EndContract, will fire when a user attempts to DELETE one or more rows from CONTRACT. This trigger will update the values of ContractCount for any affected jobs to make sure they are accurate after the rows are deleted, by decreasing the value of ContractCount by one each time a worker is removed from a job.

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

NewContract and EndContract 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 CONTRACT table.

Include a comment at the top of each of your script files giving your name, the course number and section, the assignment number, and the date of submission, e.g.: