If you choose to use MySQL with Workbench note that anonymous blocks are not supported; also you
Question:
If you choose to use MySQL with Workbench note that anonymous blocks are not supported; also you may have to look up manipulation of environment or bind variables
Create a copy of the departments table and name it DEMO_DEPT:
a. Add a column to the DEMO_DEPT and name it DEPT_EST (for Department establishment. This column is numeric and will contain the number of folk in the department).
b. Write a procedure that INSERTs a new department code into the DEMO_DEPT table. All columns are populated from the user input
c. For a new department (as in b above) write a function that calculates a department code and establishment respectively as follows:
i. Reads the last number of the department id from DEMO_DEPT
ii. Adds 1 to the department id iii. Uses this new department id in the procedure for inserting a new department code.
iv. Calculates an establishment (how many people in a department) with the estimate that asks for a number between 1 and 5, multiplies by 5 and subtracts 5. When a result is 0 or less, the default must be 2
d. Write a procedure that updates an existing department’s name and/or location. If the update is being done, show a message advising the user to write down the old department name or location (note that no automation is expected at this stage… just a note advising physical writing). Show, in the message what the new name is and what the old name was.
e. Write a procedure that deletes a department from the department table. Write a message that advises the user to write down the deleted department code and name only. No automation is expected here. Just a note advising the user to write down the details. The department code and name being deleted must be shown in the message.
2. Write a PL/SQL trigger that uses the following predicates.
a. Call or invoke the appropriate procedure in Question 1 when the action is INSERT.
b. Call or invoke the appropriate procedure in Question 1 when the action is UPDATE.
c. Call or invoke the appropriate procedure in Question 1 when the action is DELETE
Introduction to Probability and Statistics
ISBN: 978-1133103752
14th edition
Authors: William Mendenhall, Robert Beaver, Barbara Beaver