Find the old department name from the departmenttable

Assignment Help PL-SQL Programming

Reference no: EM131168741

Assignment: Database Programming

Employees may move to different departments. We want to keep track of the departments where each employee has been. To do so we create a new table emp_dept_change that keeps track of such history.

EMP_DEPT_CHANGE(EMPLOYEE_ID, EMPLOYEE_NAME, OLD_DEPARTMENT_NAME,

NEW_DEPARTMENT_NAME, EFFECTIVE_DATE);Write a trigger emp_dept_change_trg that monitors the employee table as follows.

• When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.

o The OLD_DEPARTMENT_NAMEis always'[New Hire]'.
o Find the new department name from the departmenttable based on the new department_id.

If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.

• When an employee changes his/herdepartment (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)

o Find the old department name from the departmenttable based on the old department_id.
If the olddepartment_id is NULL, the OLD_DEPARTMENT_NAMEwill be '[-----]'.

o Find the new department name from the departmenttable based on the new department_id.
If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.

• The SYSDATEcan be usedin theEFFECTIVE_DATEcolumn.

• You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.

• No temporary table/view/procedure/function is allowed in your trigger.

• You can only use the department, employee, andemp_dept_changetables in your trigger. You will get a zero point if you use a different table (e.g., different table names, column names, or data types) in your trigger.

Step 1) Create the emp_dept_changetable,

CREATE TABLE emp_dept_change

(

EMPLOYEE_ID NUMBER(4) NOT NULL,

EMPLOYEE_NAME VARCHAR2(50) NOT NULL,

OLD_DEPARTMENT_NAME VARCHAR2(100) NOT NULL,

NEW_DEPARTMENT_NAME VARCHAR2(100) NOT NULL,

EFFECTIVE_DATE DATE NOT NULL

);

Step 2) Create the trigger emp_dept_change_trg.

You will get a zero point if you use a different trigger name.

Step 3) Test your trigger.

You need to create/run some test cases to check your trigger. You do not need to submit your test cases.

Q & A)

Q: I keep getting the following prompt when I try to use :OLD and :NEW when doing this week's homework. Does this have to do with my system settings or do I need to enter something on this screen?

A: You should always click the 2^nd button or F5 to run your PL/SQL program. You can find that from lecture note 1, page 8.

Create a trigger called fin_job_min_sal_trg on the employee table. When an INSERT or UPDATE statement is issued against the employee table, the trigger is fired to ensure that the value of the SALARY column meets the criteria in the fin_job_minimum_salarytable in any situations.(For example, you can find that the minimum salary for a programmer is 800 from the fin_job_minimum_salarytable. Your trigger ensures that the salary for a programmer in the employee table is greater than or equal to 800 in any situations.)

Step 1) Create a table fin_job_minimum_salary as follows.

CREATE TABLE fin_job_minimum_salary

(

JOB VARCHAR2(50) PRIMARY KEY,

MINIMUM_SALARY NUMBER(7, 2) NOT NULL

);

Step 2) Populate the fin_job_minimum_salary table as follows.

INSERT INTO fin_job_minimum_salary VALUES ('ANALYST', 2000);

INSERT INTO fin_job_minimum_salary VALUES ('DATABASE ADMINISTRATOR', 2500);

INSERT INTO fin_job_minimum_salary VALUES ('PRESIDENT', 4800);

INSERT INTO fin_job_minimum_salary VALUES ('PROGRAMMER', 800);

INSERT INTO fin_job_minimum_salary VALUES ('PUBLIC ACCOUNTANT', 2400);

INSERT INTO fin_job_minimum_salary VALUES ('SALESMAN', 1800);

INSERT INTO fin_job_minimum_salary VALUES ('VICE PRESIDENT', 3800);

INSERT INTO fin_job_minimum_salary VALUES ('OTHERS', 1800);

COMMIT;

Step 3) Create the trigger fin_job_min_sal_trg.

• The fin_job_minimum_salarytable is read-only. Your trigger cannot modify any rows in the fin_job_minimum_salarytable.

• You must get the minimum salaries from the fin_job_minimum_salarytable in your trigger.

• Thejob is not case sensitive (e.g., SALESMAN = Salesman).

• Hard coding, except the string'OTHERS', is not allowed in your trigger (e.g., IF job = 'SALESMAN' THEN v_min_sal = 1800 ...).

• If the job cannot be found from the fin_job_minimum_salary table(e.g., the job "Program Facilitator" is not in the fin_job_minimum_salary table), the job isconsidered as "OTHERS". (You need to check whetherthe salary is equal to or greater than the minimum salary for job = 'OTHERS'.)

• If the salary is equal to or greater than the minimum salary of the corresponding job, your trigger does not change anything.

• If the salary is less than the minimum salary of the corresponding job, your trigger increases the salary to the minimum salary of the corresponding job.

• No temporary table/view/procedure/function is allowed in your trigger.

• To avoid a mutating table error, please take a look examples on page 8, class handout9. (Hint: you cannot use some INSERT/UPDATE statements to modify the employee table in your trigger.)

• You will get a zero point if you use a different table (e.g., different table names, column names, or data types) in your trigger.

• You will get a zero point if you use a different trigger name.

• If you modified the employee table created in Assignment #1, pleasedelete and re-populate it.

Step 4) Test your trigger.

You need to create/runsome test casesto check whether the values of the salary columnin the employeetable meet the criteria in the fin_job_minimum_salary table in any situations.You do not need to submit your test cases.

Please submit a text file containing all the source codes to D2L before or on due date.

Reference no: EM131168741

Questions Cloud

What does it mean for a to be an inverse of a modulo m : How can you find an inverse of a modulo m when m is a positive integer and gcd(a, m) = 1?

Describe a faster method to check for satisfiability : Describe a faster method to check for satisfiability by formulating the problem on a graph. - Create a vertex for every variable and its negation and two directed edges for each clause.

Explain the three types of attachment styles : In your paper, address the following: Explain the three types of attachment styles. List the type of attachment style you identified with

Check whether a clause set is renamable horn : Formulate the problem of checking whether a clause set is renamable Horn as a 2SAT problem.- show how to add variables to make it linear in n.

Find the old department name from the departmenttable : Find the old department name from the departmenttable based on the old department_id. Create a trigger called fin_job_min_sal_trg on the employee table. When an INSERT or UPDATE statement is issued against the employee table.

Explain why using shift ciphers is a private key system : What is the difference between a public key and a private key cryptosystem?

Identify attributes of leadership styles : For this Assignment, review the Petrakis case study in this week's video and note how supervisory and leadership skills are portrayed. Identify attributes of leadership styles and approaches that facilitate quality supervision

Discuss the strategies of conducting a negative campaign : What did the campaign staff and candidate feel was the most important message to Communicate to the voters? How did the campaign staff and candidate "get the message out" to the voters?

Show that if s dash contains no other clauses : Suppose that constraint set S contains only boolean variables,- Show that if S' contains no other clauses, S and S' are equivalent.

User Account

All Pages