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