Page 33
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