Previous | Table of Contents | Next

Page 525

CHAPTER 20

Developing a Template
Employee Information
System

This chapter describes the processes and methodology used to fulfill the requirements outlined in the various installments at the end of key portions of the book. Each installment covers a portion of the tasks needed to design and implement a working system. The installments are based upon real-life requirements of a system developed at my company. The result of performing the installments is a system that can be used as a guide for other Oracle databases.

Page 526

Installment 1—Data Normalization and Database Design

The purpose of this installment is to produce and document a design for a normalized set of relational tables for a new employee information system. This system will replace an existing system that contains unnormalized data. The attributes contained in the new set of tables are to be based upon the existing employee table and future requirements. To perform this task, you will use the normalization model to normalize the data in the existing tables to the third normal form. The first principle or step in the normalization methodology is to place all repeating fields in a table of their own. This will place the data in first normal form.

The existing employee database consists of one table. It contains repeated sets of fields for eyeglass purchases, tool purchases, and historic employee classifications. These sets of fields need to be placed in their own tables. The payroll number is the piece of information that can be used to relate the sets of records to the main entity, the employee. Payroll numbers should be included in the new tables as a foreign key. Table 20.1 illustrates the new tables and fields:

Table 20.1 The Employee Database Tables and Fields In the First Normal Form

Employees
Eyeglasses
Tools
Classifications
Payroll number Payroll number Payroll number Payroll number
Last name Purchase Date Purchase Date Department
First name Optician Payroll Deduct Classification
Absences Cost Tool Name Classification Date
Wages Check Number Tool Cost Wages
Street Payment Comments
City Last Payment Amount
State First Payment Date
Phone Last Payment Date
Social Security Number
Employment Date

Page 527

Employees
Eyeglasses
Tools
Classifications
Birth Date
Classification
Classification Date
Department
Department Name

Normalizing the data to the first normal form allows the developer to reduce the size of the database. The extra fields placed in the original file for repeating records are eliminated. These fields consume disk space even when empty. Eliminating them reduces the size of the table record. Any values contained in these fields will exist in their own tables. The three new tables will take disk space only when actual records exist in the table. The next step is to place the database in the second normal form.

To place the data in the second normal form, you must first look at the keys to each table. Each nonkey field in the table must be fully dependent upon the primary key. The primary key to the employees table is the Payroll Number. One field in the table, the Department Name, is not dependent upon the Payroll Number. The field is fully dependent upon the Department field. This means Department Name should be removed from the employee table and a new department table created. Department will be the key to the new table. It will also be a foreign key in the employees table. The fields in the remainder of the tables are dependent upon their primary key.

Normalizing a database to the third normal form requires that all nonkey fields be fully dependent upon the primary key and not dependent upon any other field in the table. The database fulfills this requirement.

Two additional changes need to be made in order to satisfy user requirements. The first, a new requirement, is to add a table to track past performance appraisals. The old system did not track these items. It will require a new table because multiple appraisal dates must be tracked. The second change is to add a department name column in the classification table. This violates the second normal form principle of having columns not fully dependent upon the key. It is necessary because this is a historical table. Throughout the career of the employee, the name of a department may change or a department may be eliminated. In order to retain this information the column must be added.

Table 20.2 illustrates the file design for the new employee system.

Page 528

Table 20.2 The Employee Database Tables and Fields In the First Normal Form

Employees
Eyeglasses
Tools
Classifications
Security
Appraisals
Departments
Payroll number Payroll number Payroll number Payroll number Payroll Number Payroll Number Department
Last name Purchase Date Purchase Date Department Security Option Appraisal Date Dept Name
First name Optician Payroll Deduct Classification
Absences Cost Tool Name Classification Date
Wages Check Number Payment Department Name
Street Last Payment Amount Wages
City First Payment Date Comments
State Last Payment Date
Phone Tool Name
SocialSecurity Number
Employment Date Date
Birth Date
Classification
Classification Date Number
Class Comments
Gender
Department

Previous | Table of Contents | Next