Previous | Table of Contents | Next

Page 33

EMPLOYEE DATABASE PRACTICE PROJECT
INSTALLMENT 1

The Greater Midwest Utility District has an existing employee database developed in a software product that is obsolete. You have been asked to develop an Oracle system to replace the existing system. The Employee system is used to record basic information about each of GMUD's employees. The old system tracks historic and current records for appraisals, promotions, annual cost-of-living raises, and safety eyeglass and tool purchases.

GMUD has two types of employees: exempt and contract. Exempt employees consist of management and registered professional engineers. The remainder of the personnel are considered contract personnel because they fall under the union contract. All employees receive an annual performance appraisal. This occurs within 30 days of the anniversary of their hiring date. All the employees receive an annual cost-of-living raise. Exempt personnel get their raises in April, and the contract personnel get theirs in June.

Contract employees are assigned to a classification. Each classification has a series of pay steps. Journeyman electrician has five steps. The electrician has to progress through the five steps to receive the top pay in the classification. Each step takes six months. At the end of two and one-half years, the journeyman electrician will receive top pay.

The company requires all employees to wear safety eyeglasses. Those employees that wear prescription glasses can purchase them from an optician at the company's expense. The company will only purchase one pair every two years. Employees may purchase their tools through the company. The employee has the option to pay immediately or on payroll deduction.

System Requirements:

Data:

The old system was designed by a clerk in the Human Relations department. It consisted of one table. It had multiple fields to contain tool, eyeglass, and classification information. The data fields are shown in Table 2.2.

Page 34

Table 2.2Current Employee Database Items

Item Description Data Type Length
Payroll number Number 4
Last name Character 15
First name Character 15
Absences Number 2
Wages Number 8,2
Street Character 20
City Character 15
State Character 2
Phone Character 13
Soc. Sec. # Character 11
Employment date Date
Birth date Date
Classification Character 15
Class date Date
Department # Character 3
Dept name Character 15

The following set of eyeglass fields are repeated four times in the table to accommodate multiple eyeglass purchases.

Eyeglass purchase date Date
Optician Character 20
Eyeglass cost Number 5
Check number Character 10

The following set of tool purchase fields are repeated eight times in the table to accommodate multiple tool purchases.

Tool purchase date Date
Payroll deduct Character 1
Tool name Character 15
Tool cost Number 5
Payment amount Number 5

Page 35

Item Description Data Type Length
Last payment amount Number 5
First payment date Date
Last payment date Date

The following set of historical classification fields are repeated 20 times in the table to
accommodate multiple classification changes.

Department # Character 4
Classification Character 15
Classification date Date
Wages Number 8
Comments Character 20

Your Assignment: Normalize the preceding data and make a table relationship diagram.

The answer to this installment is in Chapter 20, "Developing a Template Employee Information System.

Page 36

Previous | Table of Contents | Next