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