a) Q4. Views Provide an example of a view that is not updatable. Briefly explain why....

  
 
 

Transcribed Image Text:

a) Q4. Views Provide an example of a view that is not updatable. Briefly explain why. b) Assuming the following view has been created. How would you modify the existing view to remove the column first? You can provide a query or describe what you plan to do. CREATE VIEW HISTMAJ (last, first, stuID) AS SELECT lastName, firstName, stuID FROM Student WHERE major='History'; Q5. Permissions Suppose the following command is issued: GRANT SELECT ON Student TO U1, U2, U3 WITH GRANT OPTION; Complete the answer below assuming the order of the commands in which they are presented to you. a) Can U3 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U4 WITH GRANT OPTION; b) Can U4 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U5; c) A DBA issued the following command REVOKE SELECT ON Student FROM U3; WEES Can U4 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U6 WITH GRANT OPTION; Assume that the code below works. Answer the following questions. DECLARE v_emp_id NUMBER := 101; v_salary NUMBER; v_bonus NUMBER := 500; BEGIN SAVEPOINT start_transaction; Q6. Transaction Management SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id; v_salary := v_salary + v_bonus; UPDATE employees SET salary = v_salary WHERE employee_id = v_emp_id; DBMS_OUTPUT.PUT_LINE('New salary for employee ' || v_emp_id || ': ' || v_salary); SAVEPOINT after_update; -- Simulate an error condition -- RAISE_APPLICATION ERROR(-20001, 'Simulation of an error during transaction.'); 11 If no error occurs, commit the changes COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT start_transaction; DBMS_OUTPUT.PUT_LINE('Error:' || SQLCODE ||-' || SQLERRM); END; 1 (1) Explain the purpose and functionality of the SAVEPOINT statements in the provided code. Discuss how the use of savepoints contributes to the management of transactions and error handling. (2) Explain how the code would behave in the following scenarios: a) When no error occurs during the execution of the code. b) When an error occurs during the execution of the code. You are given the following code: create or replace trigger MAINTAIN_TOTAL after insert or delete or update of PRICE, QUANTITY on S ITEM Q7. Triggers, Exceptions, Constraints for each row begin if deleting then INCREMENT TOTAL (:OLD. ORD_ID, -1 * nvl (:OLD. PRICE, 0) * nvl (:OLD.QUANTITY, 0)); INCREMENT TOTAL (:NEW.ORD ID, nvl (: NEW.PRICE, 0) * nvl (:NEW.QUANTITY,0) - nvl (:OLD. PRICE, 0) * nvl (:OLD. QUANTITY, 0)); elsif updating then else /* inserting */ INCREMENT_TOTAL (: NEW.ORD_ID, end if; end; nvl (:NEW. PRICE, 0) * nvl (:NEW.QUANTITY, 0)); Will the commands below fire the trigger above? For each of the questions, please suggest what the output will be and briefly explain. a) UPDATE S_ITEM SET RATING = 4; b) INSERT INTO S_ITEM values('anna001', 41, 'cnit372'); c) INSERT INTO S_ITEM values('anna002', 140, 'cs101'); d) UPDATE S_ITEM SET QUANTITY = 2; e) DELETE FROM S_ITEM; Q3. Packages You do not need to trace the code for the following questions, just look at where and how the procedures and functions are defined. Assume that the code for the package below works: CREATE OR REPLACE PACKAGE emp_admin AS Declare externally visible types, cursor, exception TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER); CURSOR desc_salary RETURN EmpRecTyp; invalid_salary EXCEPTION; RETURN NUMBER; PROCEDURE fire_employee (emp_id NUMBER); PROCEDURE fire_employee (emp_email VARCHAR2); PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER); FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp; END emp_admin; Declare externally callable subprograms FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) - 1 (The following screenshots all describe the package body definition) CREATE OR REPLACE PACKAGE BODY emp_admin AS number_hired NUMBER; visible only in this package Fully define cursor specified in package CURSOR desc_salary RETURN EmpRecTyp IS SELECT employee_id, salary FROM employees ORDER BY salary DESC; Fully define subprograms specified in package FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) RETURN NUMBER IS new_emp_id NUMBER; BEGIN -- overloaded subprogram 11 RETURN new_emp_id; overloaded subprogram END hire_employee; SELECT employees_seq.NEXTVAL INTO new_emp_id FROM dual; INSERT INTO employees VALUES (new_emp_id, last_name, first_name, email, phone_number, SYSDATE, job_id, salary, commission_pct, manager_id, department_id); number_hired := number_hired + 1; DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' || TO_CHAR (number_hired) ); a) Q4. Views Provide an example of a view that is not updatable. Briefly explain why. b) Assuming the following view has been created. How would you modify the existing view to remove the column first? You can provide a query or describe what you plan to do. CREATE VIEW HISTMAJ (last, first, stuID) AS SELECT lastName, firstName, stuID FROM Student WHERE major='History'; Q5. Permissions Suppose the following command is issued: GRANT SELECT ON Student TO U1, U2, U3 WITH GRANT OPTION; Complete the answer below assuming the order of the commands in which they are presented to you. a) Can U3 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U4 WITH GRANT OPTION; b) Can U4 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U5; c) A DBA issued the following command REVOKE SELECT ON Student FROM U3; WEES Can U4 issue the following command? Why or why not? (5 pts) GRANT SELECT ON Student TO U6 WITH GRANT OPTION; Assume that the code below works. Answer the following questions. DECLARE v_emp_id NUMBER := 101; v_salary NUMBER; v_bonus NUMBER := 500; BEGIN SAVEPOINT start_transaction; Q6. Transaction Management SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id; v_salary := v_salary + v_bonus; UPDATE employees SET salary = v_salary WHERE employee_id = v_emp_id; DBMS_OUTPUT.PUT_LINE('New salary for employee ' || v_emp_id || ': ' || v_salary); SAVEPOINT after_update; -- Simulate an error condition -- RAISE_APPLICATION ERROR(-20001, 'Simulation of an error during transaction.'); 11 If no error occurs, commit the changes COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT start_transaction; DBMS_OUTPUT.PUT_LINE('Error:' || SQLCODE ||-' || SQLERRM); END; 1 (1) Explain the purpose and functionality of the SAVEPOINT statements in the provided code. Discuss how the use of savepoints contributes to the management of transactions and error handling. (2) Explain how the code would behave in the following scenarios: a) When no error occurs during the execution of the code. b) When an error occurs during the execution of the code. You are given the following code: create or replace trigger MAINTAIN_TOTAL after insert or delete or update of PRICE, QUANTITY on S ITEM Q7. Triggers, Exceptions, Constraints for each row begin if deleting then INCREMENT TOTAL (:OLD. ORD_ID, -1 * nvl (:OLD. PRICE, 0) * nvl (:OLD.QUANTITY, 0)); INCREMENT TOTAL (:NEW.ORD ID, nvl (: NEW.PRICE, 0) * nvl (:NEW.QUANTITY,0) - nvl (:OLD. PRICE, 0) * nvl (:OLD. QUANTITY, 0)); elsif updating then else /* inserting */ INCREMENT_TOTAL (: NEW.ORD_ID, end if; end; nvl (:NEW. PRICE, 0) * nvl (:NEW.QUANTITY, 0)); Will the commands below fire the trigger above? For each of the questions, please suggest what the output will be and briefly explain. a) UPDATE S_ITEM SET RATING = 4; b) INSERT INTO S_ITEM values('anna001', 41, 'cnit372'); c) INSERT INTO S_ITEM values('anna002', 140, 'cs101'); d) UPDATE S_ITEM SET QUANTITY = 2; e) DELETE FROM S_ITEM; Q3. Packages You do not need to trace the code for the following questions, just look at where and how the procedures and functions are defined. Assume that the code for the package below works: CREATE OR REPLACE PACKAGE emp_admin AS Declare externally visible types, cursor, exception TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER); CURSOR desc_salary RETURN EmpRecTyp; invalid_salary EXCEPTION; RETURN NUMBER; PROCEDURE fire_employee (emp_id NUMBER); PROCEDURE fire_employee (emp_email VARCHAR2); PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER); FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp; END emp_admin; Declare externally callable subprograms FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) - 1 (The following screenshots all describe the package body definition) CREATE OR REPLACE PACKAGE BODY emp_admin AS number_hired NUMBER; visible only in this package Fully define cursor specified in package CURSOR desc_salary RETURN EmpRecTyp IS SELECT employee_id, salary FROM employees ORDER BY salary DESC; Fully define subprograms specified in package FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) RETURN NUMBER IS new_emp_id NUMBER; BEGIN -- overloaded subprogram 11 RETURN new_emp_id; overloaded subprogram END hire_employee; SELECT employees_seq.NEXTVAL INTO new_emp_id FROM dual; INSERT INTO employees VALUES (new_emp_id, last_name, first_name, email, phone_number, SYSDATE, job_id, salary, commission_pct, manager_id, department_id); number_hired := number_hired + 1; DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' || TO_CHAR (number_hired) );