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
A privately owned island off the coast of Florida has been left to you by the eccentric owner who passed away recently. The island is large enough...
testimonials icon
 1. Review the following article found at the NAU Library website, based on the article “Business Ethics and Stakeholder Analysis” by K...
testimonials icon
I want help in this assignment. Paper should be written in APA style and must be 100% original. In short plagiarism will not be accepted. For t...
testimonials icon
SEE THE ATTACHEMENTS THIS IS FOR YHTOMIT...
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
HCR 210 Week 1 - CheckPoint Patient Self-Determination Act...
testimonials icon
MasteringPower BIBuild Business Intelligence ApplicationsPowered with DAX Calculations, InsightfulVisualizations, Advanced BI Techniques, andLoads of...
testimonials icon
Create a 1,150-word timeline paper of the historical development of nursing science, starting with Florence Nightingale and continuing to the prese...
testimonials icon
essay topic :why do you want your GED ? a 5 paragraph essay indent your first sentence of all you paragraphs   3 reasons why y...
testimonials icon
Requirements1. Journalize the adjusting entries using the following data:a. Internet revenue accrued, $200b. Salari...

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