Even complicated and confusing topics will be easily developed and covered if you request our help writing an essay. Place an order today!

As a new ABC Consultant assigned to the XYZ Company, you have been asked to enhance the current system to include payroll processing.Although the current employee table has monthly salary and commission columns, it does not provide any means for storing employee deductions.

Setup:

Note: Data for these tables may be found in the Appendix of this Final Exam document. If you wish, you may add addition rows to these tables.

CREATE TABLE dept (

deptnoNumber(2) Primary Key,

dnameVARCHAR2(14),

locVARCHAR2(13));

Create table emp (

empnoNUMBER(4) Primary Key,

enameVARCHAR2(10),

jobVARCHAR2(9),

mgrNUMBER(4),

hiredateDATE,

salNUMBER(7,2),

commNUMBER(7,2),

deptnoNUMBER(2));

Create table salgrade (

gradeNUMBER,

losalNUMBER,

hisalNUMBER);

CREATE TABLE deductions(

nameVARCHAR2(30),

salary_grade_min NUMBER(2),

salary_grade_max NUMBER(2));

CREATE TABLE emp_deductions(

fk_deductionVARCHAR2(30),

fk_empnoNUMBER(4),

before_or_after_flagCHAR(1),

deduction_amountNUMBER(6,2));

CREATE TABLE emp_audit(

audit_uidNUMBER(15),

change_dateDATE,

change_userVARCHAR2(30),

actionCHAR(1),

empnoNUMBER(4),

enameVARCHAR2(10),

jobVARCHAR2(9),

mgrNUMBER(4),

hiredateDATE,

salNUMBER(7,2),

commNUMBER(7,2),

deptnoNUMBER(2));

CREATE TABLE emp_deductions_audit(

audit_uidNUMBER(15),

change_dateDATE,

change_userVARCHAR2(30),

actionCHAR(1),

fk_deductionVARCHAR2(30),

fk_empnoNUMBER(4),

before_or_after_flagCHAR(1),

deduction_amountNUMBER(6,2));

CREATE TABLE tax_rates (

percentNUMBER(3,2),

salary_min NUMBER(8,2),

salary_max NUMBER(8,2));

CREATE or REPLACE VIEW show_deduction_vAS

SELECT empno,

ename,

hiredate,

deptno,

job,

count_deductions(empno) deduction_cnt

FROM emp;


Insert into dept

values (10, 'ACCOUNTING', 'NEW YORK');

Insert into dept

values (20, 'RESEARCH', 'DALLAS');

Insert into dept

values (30, 'SALES', 'CHICAGO');

Insert into dept

values (40, 'OPERATIONS','BOSTON');

insert into emp

values (7369, 'SMITH', 'CLERK', 7902, '17-DEC-80', 800,NULL,20);

insert into emp

values (7499, 'ALLEN','SALESMAN',7698, '20-FEB-81', 1600, 300,30);

insert into emp

values (7521, 'WARD','SALESMAN',7698, '22-FEB-81', 1250, 500,30);

Insert into emp

values (7566, 'JONES','MANAGER',7839, '02-APR-81', 2975,NULL, 20);

Insert into emp

values (7654, 'MARTIN', 'SALESMAN',7698, '28-SEP-81', 1250, 1400, 30);

Insert into emp

values (7698, 'BLAKE','MANAGER',7839, '01-MAY-81', 2850,NULL, 30);

Insert into emp

values (7782, 'CLARK','MANAGER',7839, '09-JUN-81', 2450, NULL, 10);

Insert into emp

values (7788, 'SCOTT','ANALYST',7566, '19-APR-87', 3000,NULL, 20);

Insert into emp

values (7839, 'KING','PRESIDENT',NULL , '17-NOV-81', 5000,NULL, 10);

Insert into emp

values (7844, 'TURNER', 'SALESMAN',7698, '08-SEP-81',1500,0, 30);

Insert into emp

values (7876, 'ADAMS','CLERK',7788, '23-MAY-87',1100,NULL, 20);

Insert into emp

values (7900, 'JAMES','CLERK',7698, '03-DEC-81', 950,NULL, 30);

Insert into emp

values (7902, 'FORD','ANALYST',7566, '03-DEC-81',3000,NULL, 20);

Insert into emp

values (7934, 'MILLER', 'CLERK',7782, '23-JAN-82',1300, NULL, 10);

Insert into salgrade

values(1, 700, 1200);

Insert into salgrade

values (2, 1201, 1400);

Insert into salgrade

values (3, 1401, 2000);

Insert into salgrade

values (4, 2001, 3000);

Insert into salgrade

values (5, 3001, 9999);

commit;

testimonials icon
respond + summary As a project manager, you must identify and understand the resources that will be needed to achieve the desired outcome. The...
testimonials icon
Post Abby-EBPPost Abby-EBP Respond using one or more of the following approaches: Ask a probing question, substan...
testimonials icon
/*! elementor - v3.6.5 - 27-04-2022 */ .elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading...
testimonials icon
{Hint: need two steps to solve this qu...
testimonials icon
DFDs are process diagrams that emphasize the data consumed, produced, and stored by processes under discussion. Complex processes are decomposed in...
testimonials icon
Regulatory Measures This assignment is a PowerPoint presentation with speaker’s notes. To prepare for this assignm...
testimonials icon
Prepare a critical analysis of the statement of cash flows in the Form 10K report for Amazon.com Inc. Using actual financial data, ex...
testimonials icon
Assignment 4: Annual ReviewDue Week 9 and worth 125 pointsImagine you work at a company and it is time for an employ...
testimonials icon
inbox me for the login information...
testimonials icon
Analyze Chowkidar Narendra Modi’s Twitter. He is an Indian politician serving as the 14th and current Prime Minister of India since 2014. Here...
testimonials icon
I need a 7-10 page (not including citations, sources) term paper world Geography. It must be completed and done by Monday May 8 at 10am EST. I do n...
testimonials icon
When analyzing the history of Greek Tragedies it is impossible not to address one of the main focuses of tragedies; t...

Other samples, services and questions:

Calculate Price

When you use PaperHelp, you save one valuable — TIME

You can spend it for more important things than paper writing.

Approx. price
$65
Order a paper. Study better. Sleep tight. Calculate Price!
Created with Sketch.
Calculate Price
Approx. price
$65