Oracle SQL Programming query

Although the current employee table has monthly salary and commission columns, it does not provide any means for storing employee deductions. You will add the tables necessary to store employee deductions. Next you will create a payroll pre-calculation program that will calculate the net pay for all the employees via a batch process (a stored procedure in a package, which will call other stored procedures within the package). Although this is not a complete payroll system, the unit test results must be accurate. Next you will create two PL/SQL blocks for inserting and deleting rows from the employee deduction table.These PL/SQL blocks will be passed information from host or bind variables and a third PL/SQL block which will assign the variables defined in SQL*Plus (e.g. employee number, dollar amount and deduction name). Since the XYZ Company wants to track changes to the employee and employee deduction tables, you will create two database triggers that will update audit tables when rows are changed or deleted. The XYZ Company also requires a view that will display specific employee information, plus the number of deductions for an employee.This will be accomplished by creating a stored function that will count the number for deductions for an employee.This function will be invoked in the select statement for the employee view. Task List: Setup: Create the tables in the existing system. Create a table for deductions with three columns for deduction name (PK), Salary grade minimum, salary grade maximum. You need to populate the deduction table any way you wish. Populate the table with as many deductions as you think would be appropriate to thoroughly test your application. The salgrade min and max information will come from the salgrade table. Create a table for employee deductions with a foreign key (name) to the deduction table, a flag that indicates if the deduction is a before tax or after tax deduction, deduction (dollar) amount and another foreign key (empno) to the employee table.This table is an intersection table and the two foreign keys concatenated together will be the primary key of this table. Create two audit tables, one for the employee table and one for the employee deduction table.These audit tables should contain a unique numeric ID (assigned via a sequence), the current date, and the user id of the person that has made the change to he employee or employee deduction table, and all the columns from the source table. Create two sequences that will be used to populate the audit tables with a unique identifier. Create a table to keep tax rates, these can be real or bogus, as long as there are at least 7 different rates ( For example, if the annual salary is 0 -10000 then a 5% tax rate, 10001 – 20000 then a 7% tax rate, etc) 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)); Program Description Create two PL/SQL blocks that will insert or delete rows from the employee deduction table.Include exception processing for bad input data and verify that an employee is eligible for a deduction based upon their salary grade.Salary grade is determined by checking the salary grade table to derive the employee’s salary grade.Once you know an employee’s grade, then verify if the employee is eligible for the deduction (e.g. 401K) via comparing it to the minimum and maximum salary grade that is stored in the deduction table ( see definition above). Create two database triggers that will update the employee audit table and employee deduction audit table when rows are either changed or deleted.Use a sequence number to assign a unique identifier to each row as it is created in the audit table.Capture the user ID, date of the change or delete, and action (update or delete), plus all the fields in the before image of the row (e.g. before it is changed or deleted). Create a function that will count the number of deductions for an employee.Input to the function is the employee ID and it returns the count of employee deductions.If there aren’t any deductions for the employee, the function returns zero.Within the view, include the employee name, number, hire date, job, dept number and the umber of deductions for the employee. Use the naming standard _v. CREATE or REPLACE VIEW show_deduction_vAS SELECT empno, ename, hiredate, deptno, job, count_deductions(empno) deduction_cnt FROM emp; Create a package that contains multiple stored procedures.Only one procedure will be available to external calls ( in the package specifications).The other procedure will be subroutines that are called by the main procedure in the package body.It is okay to include stored functions in your package that support the procedure.The procedure will accomplish the following steps for each employee: Subtract the before tax deductions from the monthly salary. Calculate and subtract Federal Tax and State Income using the tax table that you created (see above).For more of a challenge, initially load the tax table into a PL/SQL table that resides in memory (optional).Use the same rate for Federal Income Tax (FIT) and State Income Tax (SIT). Exclude other taxes(e.g. SS) Include commission as part of the salary
Subtract after tax deductions from the remaining monthly salary to determine the net pay. Use DBMS_OUTPUT to display all deductions/calculations and net pay.This will enable you to turn spool on and capture all the calculations for all employees when you execute the procedure via SQL*Plus. Deliverables Listing of code for every block (function, trigger, a package with procedures, etc.) defined above. I want to be able to cut and paste your code into SQL*Developer and watch it run.
Unit test data for every block defined above (use the spool command).
Appendix 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;

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more