Can anyone please try and help me with this project
Background
ABC Mining Ltd is a company operating in the mining industry. The company was hard hit by the current economic recession. In order to remain in business the company is contemplating introducing voluntarily retrenchment packages. You have been hired as a DBA
programming consultant to assist the company with a code that will enable the company to calculate the severance packages based on certain guidelines.
Remuneration policy
- Each employee receives a basic salary in line with his job grade.
- Each employee also receives an annual bonus (13th Cheque) equal to 1 month basic salary.
- Each employee contributes 7% of his basic salary to the pension fund and the company contributes 14% of the basic salary to the employee pension fund.
- Employee earning a basic salary of N$15000 and above receives 10% of basic salary of housing allowance and the rest receive 15% of basic salary.
- Employees earning a basic salary of N$20000 and above receive N$3500 car allowance.
- Employees earning a basic salary of N$30000 and above receive N$2500 entertainment
allowance.
- The medical aid contribution is based on the tables in the
2010BankMedCont ributions document, which is provided.
- The company medical aid contribution is 65% of these amounts for each member
and a maximum of 2 dependents.
- The default medical aid scheme is Bankmed Essence.
- However Employees earning N$5650 basic salary or less have a choice between
Bankmed Essence (E) and Bankmed Care (C).
Severance package calculation
- 3 months salary ( The monthly salary should include all allowances and bonuses
including the appropriate portion of the 13th Cheque)
- 1 week salary for each year of service (Weekly salary should include all the allowances
and bonuses).
- All leave days
- NB: Average working days per month are 22.
- The value of leave days is based on the overall package and not just the basic
salary.
3
Assignment
- The source information/data needed to calculate the severance package lies in an
oracle database table.
- See Appendix 1 (Excel spreadsheet) for sample data/structure of the source table.
- Create two tables for this assignment.
- The first table will be the source table – containing the employees’ information.
- Populate the first table with test data (Cater for all possible test cases).
- The second table should store the following details of the severance package:
- Employee number, First Name, Last Name, Years of service, salary, pension (company
portion),housin g allowance, medical allowance(compa ny), entertainment allowance,
car allowance, bonus(monthly portion),value of leave days, week pay *
years of service, monthly package, final severance pay.
- Write PL/SQL program/s (Procedures, functions, triggers etc) that will extract the information
from the source table, do the necessary calculations and populate the
target table with the required information.
- The management also want a summary which should look like the one below:
Summary Report:
Total number of employees: 2
Number of employees 31 yrs old and above: 1(50% )
Total salary all employees: 17800
Total pension all employees: 2492
Total housing all employees: 2670
Total medical all employees: 2789.8
Total car allowance all employees: 0
Total bonus all employees: 1483.34
Total leave value all employees: 14857.92
Total severance cost all employees: 158755
- Write the necessary PL/SQL block to produce the required summary report.
- For the number of employees above a certain age, the program/code must prompt
you to enter the age limit or alternatively it can be passed as a parameter to the
procedure. If you enter 40 or pass it as a parameter for the age limit, then the program
must give the number of employees who are 40 years and above including
the percentage between the brackets as shown in the sample summary report
above.
- Consider only the company contribution for all allowances.
Deliverables
- Scripts used to create the two tables and the appropriate test data.
- NB: Test data should cover all test cases/possibilities.
- PL/SQL code to populate the target table.
- NB: Use separate PL/SQL function/s (called by the main program) to make complicated
calculations where applicable e.g. the medical allowance.
- PL/SQL code to produce the summary report.
Necessary documents are attached
Background
ABC Mining Ltd is a company operating in the mining industry. The company was hard hit by the current economic recession. In order to remain in business the company is contemplating introducing voluntarily retrenchment packages. You have been hired as a DBA
programming consultant to assist the company with a code that will enable the company to calculate the severance packages based on certain guidelines.
Remuneration policy
- Each employee receives a basic salary in line with his job grade.
- Each employee also receives an annual bonus (13th Cheque) equal to 1 month basic salary.
- Each employee contributes 7% of his basic salary to the pension fund and the company contributes 14% of the basic salary to the employee pension fund.
- Employee earning a basic salary of N$15000 and above receives 10% of basic salary of housing allowance and the rest receive 15% of basic salary.
- Employees earning a basic salary of N$20000 and above receive N$3500 car allowance.
- Employees earning a basic salary of N$30000 and above receive N$2500 entertainment
allowance.
- The medical aid contribution is based on the tables in the
2010BankMedCont ributions document, which is provided.
- The company medical aid contribution is 65% of these amounts for each member
and a maximum of 2 dependents.
- The default medical aid scheme is Bankmed Essence.
- However Employees earning N$5650 basic salary or less have a choice between
Bankmed Essence (E) and Bankmed Care (C).
Severance package calculation
- 3 months salary ( The monthly salary should include all allowances and bonuses
including the appropriate portion of the 13th Cheque)
- 1 week salary for each year of service (Weekly salary should include all the allowances
and bonuses).
- All leave days
- NB: Average working days per month are 22.
- The value of leave days is based on the overall package and not just the basic
salary.
3
Assignment
- The source information/data needed to calculate the severance package lies in an
oracle database table.
- See Appendix 1 (Excel spreadsheet) for sample data/structure of the source table.
- Create two tables for this assignment.
- The first table will be the source table – containing the employees’ information.
- Populate the first table with test data (Cater for all possible test cases).
- The second table should store the following details of the severance package:
- Employee number, First Name, Last Name, Years of service, salary, pension (company
portion),housin g allowance, medical allowance(compa ny), entertainment allowance,
car allowance, bonus(monthly portion),value of leave days, week pay *
years of service, monthly package, final severance pay.
- Write PL/SQL program/s (Procedures, functions, triggers etc) that will extract the information
from the source table, do the necessary calculations and populate the
target table with the required information.
- The management also want a summary which should look like the one below:
Summary Report:
Total number of employees: 2
Number of employees 31 yrs old and above: 1(50% )
Total salary all employees: 17800
Total pension all employees: 2492
Total housing all employees: 2670
Total medical all employees: 2789.8
Total car allowance all employees: 0
Total bonus all employees: 1483.34
Total leave value all employees: 14857.92
Total severance cost all employees: 158755
- Write the necessary PL/SQL block to produce the required summary report.
- For the number of employees above a certain age, the program/code must prompt
you to enter the age limit or alternatively it can be passed as a parameter to the
procedure. If you enter 40 or pass it as a parameter for the age limit, then the program
must give the number of employees who are 40 years and above including
the percentage between the brackets as shown in the sample summary report
above.
- Consider only the company contribution for all allowances.
Deliverables
- Scripts used to create the two tables and the appropriate test data.
- NB: Test data should cover all test cases/possibilities.
- PL/SQL code to populate the target table.
- NB: Use separate PL/SQL function/s (called by the main program) to make complicated
calculations where applicable e.g. the medical allowance.
- PL/SQL code to produce the summary report.
Necessary documents are attached
Comment