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
Question Description I’m working on a engineering Other and...
testimonials icon
EssayAn essay is an extended piece of writing that presents and supports a thesis or proposition.Suggested structure for a standard essay:depends on...
testimonials icon
During the spring and summer, black students involved in the movement, would take buses and trains...
testimonials icon
For your final assignment, select a contemporary ethical dilemma in health care of your choice (e.g., physician-assisted suicide, abortion, confide...
testimonials icon
Details:The problems assigned here are intended to give you contextual experience with the types of statistics you will...
testimonials icon
First paragraph: Preliminarily, you need to help the staff understand the differences between various translation documents. Therefore, in this...
testimonials icon
PICOT and Statement Literature Search Nursing Practice Problem Running head: PICOT AND LITERATURE SEARCH 1...
testimonials icon
  Develop your own definition of the word work. Using this definition, explain whether you think everyone naturally has a desire...
testimonials icon
Running head: MOTION IN LIMINE1IN THE UNITED STATES DISTRICT COURT FOR THE EASTERN DISTRICT OFWISCONSINBILLY BOBPlaintiff*Case No.*Judge*PLAINTIFFS M...
testimonials icon
Malnutrition is a broad term which refers to both under nutrition (sub nutrition) and over nutrition. Individuals are malnourished, or suffer from...
testimonials icon
Semi-Structured Interview Questions, Fall 1989. The predictive items in Julia Gomezs Resume are:She has a good experience on secretarial jobs based o...
testimonials icon
Reading Research Literature Type your answers to the following questions using complete sentences and correct grammar, spelling, and syntax....

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