UPDATE and display an error message stating that no updates are permitted to the STUDENT table.

(PL/SQL Programming) Consider the table EMPLOYEE with attributes ID, Name, and Hours, and the table PAYINFO with attributes Regular, Overtime, and Cutoff, defined and populated by the following script:
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
CREATE TABLE EMPLOYEE
(
ID CHAR(9),
Name VARCHAR2(12),
Hours NUMBER(3),
CONSTRAINT PK_EMPLOYEE
PRIMARY KEY (ID)
);
INSERT INTO EMPLOYEE VALUES (‘717222144’, ‘Dolenz’, 30);
INSERT INTO EMPLOYEE VALUES (‘013331902’, ‘Jones’, 50);
INSERT INTO EMPLOYEE VALUES (‘591342771’, ‘Nesmith’, 65);
INSERT INTO EMPLOYEE VALUES (‘822182889’, ‘Tork’, 25);
SELECT * FROM EMPLOYEE;
DROP TABLE PAYINFO;
CREATE TABLE PAYINFO
(
Regular NUMBER(4,2),
Overtime NUMBER(4,2),
Cutoff NUMBER(2)
);
INSERT INTO PAYINFO VALUES (15.00, 18.50, 40);
SELECT * FROM PAYINFO;
COMMIT;
Write a script file Problem1.sql containing an anonymous PL/SQL block that will do the following:
First, report three values found in the PAYINFO table and store them in variables. (You may assume that the PAYINFO table contains only one record.) Next, output the ID and Name of each employee in the EMPLOYEE table and their total pay, computed as:
Regular * Hours if Hours
(Regular * Cutoff) + (Overtime * (Hours – Cutoff)) if Hours > Cutoff
(
Values are 15, 18.5, 40
717222144 Dolenz 450
013331902 Jones 785
591342771 Nesmith 1062.5
822182889 Tork 375
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 MAJOR and STUDENT tables defined by the following script, which also populates the MAJOR table:

DROP TABLE MAJOR CASCADE CONSTRAINTS;
DROP TABLE STUDENT CASCADE CONSTRAINTS;
CREATE TABLE STUDENT
(
ID CHAR(7),
Name VARCHAR2(20),
Major VARCHAR2(3)
CHECK (Major in (‘CS’, ‘IT’, ‘SE’)),
CONSTRAINT PK_STUDENT
PRIMARY KEY (ID)
);
CREATE TABLE MAJOR
(
Name CHAR(2)
PRIMARY KEY,
Count NUMBER
);
INSERT INTO MAJOR VALUES (‘CS’, 0);
INSERT INTO MAJOR VALUES (‘IT’, 0);
INSERT INTO MAJOR VALUES (‘SE’, 0);
SELECT * FROM MAJOR;
COMMIT;
The Count attribute of MAJOR should store a count of how many students have declared that major – that is, the number of records in STUDENT with that Major. Your task is to write three triggers that will maintain the value of the Count attribute in MAJOR as changes are made to the STUDENT table.
Write a script file Problem2.sql containing definitions of the following three triggers:

The first trigger should fire whenever a user INSERTS a row into STUDENT. The trigger will increase the value of Count in the MAJOR table by one in the row corresponding to the Major in the new row of STUDENT.
The second trigger will fire when a user attempts to UPDATE one or more rows of STUDENT. The trigger will simply cancel the UPDATE and display an error message stating that no updates are permitted to the STUDENT table.
The third trigger should fire when a user DELETES one or more rows from STUDENT. This trigger will update the values of Count in the MAJOR table for all affected rows to make sure that they are correct, by decreasing the value of Count by one each time a row with that Major is removed from STUDENT.

The first and third triggers will have to be row-level triggers; the second 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, UPDATES, and DELETES on the STUDENT table.

Get a 10 % discount on an order above $ 100
Use the following coupon code :

Order Now


 

PLACE THIS ORDER OR A SIMILAR ORDER WITH STUDENT HOMEWORKS TODAY AND GET AN AMAZING DISCOUNT

get-your-custom-paper

The post UPDATE and display an error message stating that no updates are permitted to the STUDENT table. appeared first on STUDENT HOMEWORKS .

 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.

Buy Custom Nursing Papers