473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Database programming assignment

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
2010BankMedContributions 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),housing allowance, medical allowance(company), 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
Attached Files
File Type: pdf 20010BankmedContributions.pdf (13.1 KB, 401 views)
May 16 '10 #1
1 2758
Atli
5,058 Expert 4TB
Hey.

The experts on this site are more than happy to help you with your problems but they cannot do your assignment/program for you. Attempt the assignment/program yourself first and post questions regarding any difficulties you have or about a particular function of the code that you don't know how to achieve.

Please read the Posting Guidelines and particularly the Coursework Posting Guidelines.

Then when you are ready post a new question in this thread.

Moderator
May 16 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: aus7in | last post by:
Hello, I am a newbie to database programming in VB. Could any of you please recomend a good book for tutorial/reference? Thanks Austin
1
by: anandakumarpc | last post by:
I'm just learning PHP. I would like to know if database programming in PHP can be done using MS-Access. Can any one help me with complete example codes please.
4
by: janconway | last post by:
Hey, kinda bit lost with the lab assignment in college. any tips would be really appreciated. basically we were given a flex file and we've to modify it to see if a string is a keyword or identifier...
0
by: Jeff Louie | last post by:
Answer: Yes you can do RAD client server database programming in C# using Windows Forms. Proof of Concept at: http://www.geocities.com/jeff_louie/windows_forms.htm Regards, Jeff *** Sent...
3
by: Alan | last post by:
I am coming from other programming tools field, and new to VB .NET. Just wondering what is the common approaching in database programming in VB .NET? 1) Are you guru always using the data aware...
3
by: jaYPee | last post by:
i am searching a lot of resources in the net that discusses about database programming. i'm still searching on it and would like to received any links that discusses about database programming in...
2
by: smartie_zhuo | last post by:
Hi,There Recently ,I bought "Beginning Visual Basic 6 Database Programming" by John Connell from second book store,According to book,I should download source code from Wrox website,But I cann't...
10
by: jmarcrum | last post by:
Hi everyone! I have a church database that keeps track of all of our church's members: first and last name, email address, phone number, home address (street, city, zipcode). I was...
5
by: mangoxoxo | last post by:
Hey I'm not sure if this is where I should be asking this but I'm going to ask anyway. I'm doing an assignment for my Intro to Comp Programming class. This is the assignment: Define a class...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.