I need help with a case study–Case StudyOverview of assignm
I need help with a case study–Case StudyOverview of assignment As a newABC Consultant assigned to the XYZ Company, you have been asked to enhance thecurrent system to include payroll processing. Although the current employee table has monthly salary and commissioncolumns, it does not provide any means for storing employee deductions. You will add the tables necessary to storeemployee deductions. Next you will create a payroll pre-calculation programthat will calculate the net pay for all the employees via a batch process (astored procedure in a package, which will call other stored procedures withinthe package). Although this is not a complete payroll system, the unit testresults must be accurate. Next you will create two PL/SQL blocks for inserting anddeleting rows from the employee deduction table. These PL/SQL blocks will be passed informationfrom host or bind variables and a third PL/SQL block which will assign thevariables defined in SQL*Plus (e.g. employee number, dollar amount anddeduction name). Since the XYZ Company wants to track changes to the employeeand employee deduction tables, you will create two database triggers that willupdate audit tables when rows are changed or deleted. The XYZ Company also requires a view that will displayspecific employee information, plus the number of deductions for anemployee. This will be accomplished bycreating a stored function that will count the number for deductions for anemployee. This function will be invokedin the select statement for the employee view. Task List SetupCreatethe tables in the existing system. Data for these tables can be found inthe appendix for this document. If you wish you may add addition rows tothese tables. CREATE TABLE dept ( deptno Number(2) Primary Key, dname VARCHAR2(14), loc VARCHAR2(13));Create table emp ( empno NUMBER(4) Primary Key, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));Create table salgrade ( grade NUMBER, losal NUMBER, hisal NUMBER);Createa table for deductions with three columns: deduction name (PK), Salarygrade minimum, salary grade maximum. You need to populate the deductiontable any way you wish. Populate the table with as many deductions as youthink would be appropriate to thoroughly test your application. Thesalgrade min and max information will come from the salgradetable. CREATE TABLE deductions ( name VARCHAR2(30), salary_grade_min NUMBER(2), salary_grade_max NUMBER(2));Createa table for employee deductions with a foreign key (name) to thededuction table, a flag that indicates if the deduction is a before tax orafter tax deduction, deduction (dollar) amount and another foreign key(empno) to the employee table. This table is an intersection table and the two foreign keysconcatenated together will be the primary key of this table. CREATE TABLE emp_deductions ( fk_deduction VARCHAR2(30), fk_empno NUMBER(4), before_or_after_flag CHAR(1), deduction_amount NUMBER(6,2));Createtwo audit tables, one for the employee table and one for the employeededuction table. These audit tablesshould contain a unique numeric ID (assigned via a sequence), the currentdate, and the user id of the person that has made the change to heemployee or employee deduction table, and all the columns from the sourcetable. CREATE TABLEemp_audit ( audit_uid NUMBER(15), change_date DATE, change_user VARCHAR2(30), action CHAR(1), empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));CREATE TABLE emp_deductions_audit ( audit_uid NUMBER(15), change_date DATE, change_user VARCHAR2(30), action CHAR(1), fk_deduction VARCHAR2(30), fk_empno NUMBER(4), before_or_after_flag CHAR(1), deduction_amount NUMBER(6,2));Createtwo sequences that will be used to populate the audit tables with a uniqueidentifier.Createa table to keep tax rates, these can be real or bogus, as long as thereare 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)CREATE TABLE tax_rates ( percent NUMBER(3,2), salary_min NUMBER(8,2), salary_max NUMBER(8,2)); Program Description Createtwo PL/SQL blocks that will insert or delete rows from the employeededuction table. Include exceptionprocessing for bad input data and verify that an employee is eligible fora deduction based upon their salary grade. Salary grade is determined by checking the salary grade table toderive the employee’s salary grade. Once you know an employee’s grade, then verify if the employee iseligible for the deduction (e.g. 401K) via comparing it to the minimum andmaximum salary grade that is stored in the deduction table ( seedefinition above).Createtwo database triggers that will update the employee audit table andemployee deduction audit table when rows are either changed ordeleted. Use a sequence number toassign a unique identifier to each row as it is created in the audittable. Capture the user ID, date ofthe change or delete, and action (update or delete), plus all the fieldsin the before image of the row (e.g. before it is changed or deleted).Createa function that will count the number of deductions for an employee. Input to the function is the employee IDand it returns the count of employee deductions. If there aren’t any deductions for theemployee, 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 namingstandard
_v. CREATE or REPLACE VIEWshow_deduction_v ASSELECT empno, ename, hiredate, deptno, job, count_deductions(empno) deduction_cntFROM emp;Createa package that contains multiple stored procedures. Only one procedure will be available toexternal calls (in the package specifications). The other procedure will be subroutinesthat are called by the main procedure in the package body. It is okay to include stored functionsin your package that support the procedure. The procedure will accomplish thefollowing steps for each employee:Subtractthe before tax deductions from the monthly salary.Calculateand subtract Federal Tax and State Income using the tax table that you created(see above). For more of achallenge, initially load the tax table into a PL/SQL table that residesin memory (optional). Use thesame rate for Federal Income Tax (FIT) and State Income Tax (SIT).Excludeother taxes (e.g. SS)Includecommission as part of the salarySubtractafter tax deductions from the remaining monthly salary todetermine the net pay.UseDBMS_OUTPUT to display all deductions/calculations and net pay. This will enable you to turn spool onand capture all the calculations for all employees when you execute the procedurevia SQL*Plus. DeliverablesListingof code for every block (function, trigger, a package with procedures,etc.) defined above.Unittest data for every block defined above (use the spool command). Note: I’ll check test results from the pre-calcprogram with a calculator. The net payamount must be correct for full credit. Include test data dumps of every table so that I can verify your testresults. § MAKEA LIST OF DELIVERABLES AND CHECK IT TWICE. IF YOU LEAVE OUT A PIECE OF CODE OR UNIT TEST DATA, YOU WILL NOT RECEIVEFULL CREDIT. AppendixInsert into deptvalues (10, ‘ACCOUNTING’, ‘NEW YORK’);Insert into deptvalues (20, ‘RESEARCH’, ‘DALLAS’);Insert into deptvalues (30, ‘SALES’, ‘CHICAGO’);Insert into deptvalues (40, ‘OPERATIONS’,’BOSTON’);insert into empvalues (7369, ‘SMITH’, ‘CLERK’, 7902, ’17-DEC-80′,800,NULL,20);insert into empvalues (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ’20-FEB-81′,1600, 300, 30);insert into empvalues (7521, ‘WARD’, ‘SALESMAN’, 7698, ’22-FEB-81′,1250, 500, 30);Insert into empvalues (7566, ‘JONES’, ‘MANAGER’, 7839, ’02-APR-81′,2975,NULL, 20);Insert into empvalues (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ’28-SEP-81′, 1250, 1400, 30);Insert into empvalues (7698, ‘BLAKE’, ‘MANAGER’, 7839, ’01-MAY-81′,2850,NULL, 30);Insert into empvalues (7782, ‘CLARK’, ‘MANAGER’, 7839, ’09-JUN-81′,2450, NULL, 10);Insert into empvalues (7788, ‘SCOTT’, ‘ANALYST’, 7566, ’19-APR-87′,3000,NULL, 20);Insert into empvalues (7839, ‘KING’, ‘PRESIDENT’,NULL , ’17-NOV-81′, 5000,NULL, 10);Insert into empvalues (7844, ‘TURNER’, ‘SALESMAN’, 7698, ’08-SEP-81′,1500,0, 30);Insert into empvalues (7876, ‘ADAMS’, ‘CLERK’, 7788,’23-MAY-87′,1100,NULL, 20);Insert into empvalues (7900, ‘JAMES’, ‘CLERK’, 7698, ’03-DEC-81′,950,NULL, 30);Insert into empvalues (7902, ‘FORD’, ‘ANALYST’, 7566,’03-DEC-81′,3000,NULL, 20);Insert into empvalues (7934, ‘MILLER’, ‘CLERK’, 7782, ’23-JAN-82′,1300, NULL, 10);Insert into salgradevalues(1, 700, 1200);Insert into salgrade values (2, 1201, 1400);Insert into salgrade values (3, 1401, 2000);Insert into salgradevalues (4, 2001, 3000);Insert into salgradevalues (5, 3001, 9999);commit;