Data model and implement the pay calculation

You have been asked to extend the current data model and implement the pay calculation

Current Data Model

Entities to be added :

• Timesheet

• Health_plan

• Job_category

• Tax_information

Project Tasks and SQL involved

• Identify the relationship for new entities with the existing data model (DDL, Add foreign key and the primary key for new tables)

• Add new columns to existing tables if required

• The data model should include all entities

• Add 20 New employees ( INSERT)

• Add time worked for all new employees for four weeks for the month of JUNE ( INSERT)

• Create pay_calc procedure to calculate salary for all new employees (SQL Procedure)

• The procedure should read data from the timesheet

• Calculate overtime if needed ( anyone worked more than 40hrs paid by 1.5 of normal pay)

• Pay should be calculated for every two weeks

• Tax should be calculated as per city and State

• The procedure should have a way to recalculate pay if the finance team found some missing information. (DELETE)

• Need to add 2 hours for all employees as a bonus (UPDATE)

• When anyone salary table, audit record should be created ( TRIGGER)

Load the data as we normally to create the database:

group_project_employees_database.sql from eLearning

No.of Records in Each table

SELECT

(select count(*) from dept_emp) dept_emp,

(select count(*) from departments ) departments ,

(select count(*) from employees ) employees,

(select count(*) from job_category ) job_category,

(select count(*) from payroll_common_deductions) common_deductions,

( select count(*) from payslip ) payslip ,

( select count(*) from salaries ) salaries,

( select count(*) from salary_audit ) salary_audit ,

( select count(*) from timesheet ) timesheet,

( select count(*) from titles ) titles ;

List of Tasks required to calculate:

1. Write a stored program to calculate salary and store the data in a payslip

2. Create the trigger on the Salary table for any insert and update activity to audit

Stored Program Logic

1. Select all 20 employees for your group from the timesheet table

2. Identify the hours worked

3. get the job_category for each employee from the employee's table

4. Get the hourly rate from the job_category table

5. Calculate the gross pay based on an hourly rate, if hours are more than 80, give 1.5 times of hourly rate

6. Identify all deductions.

   1. Health plan deduction – take the health plan id from the employee and check the employee_health_plan for deduction

   2. payroll common deduction – all the deductions have to be done

   3. retirement saving- the percentage of salary saved by employees for each pay period

7. Gross pay - all deductions = net pay

8. Store all the details in the payslip table

9. update the salary table with net pay for the period, whenever salary table update trigger should fire and create the data for salary_audit table.

All steps should be included in the stored program.

Deliverables for Final Report

• Description of project

• Conceptual Design

• Logical Data Model

• Physical Data Model with commands used to create physical tables

• Data loading concept used

• Stored Program used for Payroll calculation

• Attach payslip, salary, salary_audit data after the payroll calculation successfully ran. ( create data export of those tables)

---

Are You are looking for Database Project Help or Homework Help? Codersarts Database expert will provide the best quality plagiarism-free solution at an affordable price. We are available 24 * 7 online to assist you. You may chat with us through website chat or email or can fill the contact form.

Submit Project Here