CSIS-114: HW 4

TFK Inc.

The Troy Fancy Kandy Company (TFK for short) is a Troy NY based company that makes candies shaped like Uncle Sam. The candy is in such demand that they must keep their factory and assembly line running 24-hour a day, 7 days a week. The President of the company has hired you to help create Support System to help schedule their employees.

Vital Facts

  1. Five employees are required to keep the assembly line running. All employees are trained to run any part of the assembly line.
  1. There are three shifts during the day:
  • Shift 1 is 8AM-4PM
  • Shift 2 is 12AM to 8AM
  • Shift 3 is 4PM-12AM
  1. Employees are prohibited from working more than one shift per day, so the company needs to schedule 15 different employees each day.
  1. Overtime: Employees who work more than 40 hours in a calendar week get paid 2 times their normal rate for every hour over 40 hours.
  2. Employees can work more than 5 days per week, but you’ll have to pay them overtime.
  3. Each week, 105 slots must be filled (15 employees each day time 7 days).
  1. Night-shift bonus: Employees who work Shift 2 or Shift 3 get paid 1.5 times their normal rate.
  1. Each employee’s hourly rate depends upon years of experience.
  1. Each employee has a productivity rating, which is not necessarily related to years of experience.
  1. Before the start of a week, employees fill out a preference card indicating which days and which shifts they are free to work. An employee can not be schedule unless they indicate that they are available on their preference card.

Current Database

Tables

Availability [Do Not Modify]– The Days and Shifts that each employee is available.

Employee [Do Not Modify] – Hourly Wage and Rating for each employee.

Schedule [Must Modify] – Specifies the five employees scheduled for each Day and Shift in one calendar week. There are 7 days, 3 shifts per day, and 5 employees per shift, so the table should have exactly 105 records.

Queries

Check Availability [Can Modify] – The user specifies the Day and Shift, and the query returns all available employees.

Overtime Days [Do Not Modify] – Returns all employees who worked more than 5 days. Used by the Overtime Pay query.

Overtime Pay [Do Not Modify] – Returns the overtime pay for all employees who worked more than 5 days.

Payroll [Do Not Modify] – Computes the pay of each scheduled employee

Verify Schedule [Do Not Modify]– Checks to see that the schedule employees match their availability. This query should return the value 105. If it returns less than you’ve scheduled someone who is unavailable.

Reports

Payroll [Do Not Modify] – Computes the total payroll amount for the week and the total employee rating (displayed at the bottom of this report).

Overtime Pay [Do Not Modify] – Computes the total overtime pay.

Company Goals

The President of the company has the following goals in order of importance.

  1. Minimize the total cost of labor for the week and Maximize the productivity rating of the labor force scheduled for the week.
  2. Eliminate overtime.

Your Task

Design and implement an information system that

  1. helps automate the process of scheduling employees and
  2. helps make better scheduling decisions to meet the Company’s goals.

Deliverables

On the website, you will find an initial database with the tables, queries, and reports described above. Inside this database, create additional queries and reports that help you schedule the employees for the given week. While it may be possible to create a single query or report to help you, it’s more likely that a combination of queries and reports will be needed. Use your reports and queries to reschedule the employees to reduce the labor costs, increase the total productivity rating, and to eliminate overtime.

  • You must reschedule the employees. Thus, you should delete all the current employees in the Schedule table and re-schedule them to meet the Company’s goals. This will require you to input 105 entries into the schedule table.
  • You can create new queries and reports and you can modify the Check Availability query to your liking, but you should NOT modify any existing table, query, or report. If you do, there will be a severe penalty.
  • Submit your final database which will include a revised schedule and the queries/reports that you created to help make your decisions.
  • Prepare a brief written report explaining your overall strategy for achieving the goals.
  • For each query or report you create, briefly explain how you used it to help make scheduling decisions.
  • Describe any queries or reports that you would have liked to create, but were NOT able to implement.
  • The report should be a maximum of one page. Submit your final report as a word document.

Challenge

To earn a passing grade you have to have a payroll that is lower than $7800.00 and a total rating that is higher than 720. Bonuses will be given if you have the lowest payroll or the highest rating.