Example Of Essay On Normalization

As the Chief Information Officer, I am mandated with developing a summary of normalizing tables to be used in the upcoming training session.

Converting First Normal to Third Normal steps

Normalizing a database involve the process of organizing attributes in a database model to increase cohesion and reduce redundancy. Storing information in a database which are exhibited in more than one location is a challenging task. Thus, data is normalized so that each table contains a distinct set of data different from others.
Normalization from 1NF, 2NF to 3NF is guided by rules. The first normal form is guided by three rules; eliminate repeating attributes in a table, separate tables for each and every set of related data, assign a primary key to each related set of data.
The second normal form requires that records should be dependent upon the table’s primary key. The rules for 2NF normalization include creation of a separate table for each set of data that conform to more than one record and assigning a foreign key to the created table.
The third normal form has record keys in record files. The rule applicable to this form is elimination of fields that are not dependent on the primary key.

Example

An example of a normalization process is a university table with project number, project name, employee number, employee name, job class, charged hour and hours. For the table to be normalized to 1NF, all the attributes are defined and there is no repeating groups. Also, all attributes are dependent on the primary key. However, the problem is that, the table structure contain partial dependencies as seen in the relationship between project name, employee name, job class and hours.

1NF

In the 2NF form, the table is normalized if it satisfy 1NF rules and has no partial dependencies. Hence, introduction of employee table and assignment table will break the dependency and allow the database developer to associate as many or as few attributes to the project and employees, thus creating schema flexibility.

2NF

In 3NF, the requirements of 2NF are satisfied and transitive dependencies are eliminated. A new table is introduced to break the transitive dependencies between employee table and job characteristics. The resultant is two additional tables; a job table with Job code as the primary key and job class and Charge Hour. The other table is assignment table with project number, employee number and hours. Employee table only has three attributes; employee number, employee name and job code.

The resultant database is as follows:

Typical situations that necessitate denormalizing a database
In some instances, it is essential to denormalize database for performance reasons. De-normalizing can be termed as a time-space trade-off. Normalized data are usually contained in a single location and de-nomarlization replicates the data in multiple locations. It takes more space but takes less time to view; hence the desired view is easily available. Another reason for de-normalizing a database is when there is a complex schema. When the normalization process is difficult, it is time to de-normalize the database.
Finally, in the development of a prototype database, it is essential to do what is really faster to execute. Rapid application development is

Example

In the case where there is a problem calculating the total time spent on a project, and given multiple products that need to be tracked at the same time, requiring to run a report multiple times a day, de-normalizing the database is advisable.

Ways in which in which business rules impact database normalization and denormalization.

The needs of the enterprise or business requirements determine the process of normalization of de-normalization. Usually, the needs of the organization determine the process to be adopted. The primary goal of any of the two processes is based on the ease with which the users front-end tool would be utilized. Some of the considerations or business determinants that affect the choice of any of the two processes include the type of data stored in the database, how users access the database, the need to group data, the data that is commonly accessed and the relationship between the various data in the database. For example, when the purpose of the database is purely a decision support system, it is important to understand the tradeoffs between performance and access. For an Online Analytical Processing system, the requirement may be faster access to some kind of data multiple times a day. Here the database may be better when denormalized than when normalized for increased performance. They choice of normalization/denormalization is a factor of the size of the database.
Another consideration is whether data need to be accessed and updated frequently. The separation of the disks to limit disk head movement and contention require fragmenting the database tables such that data can be accessed in a parallel format. Performance can be achieved by de-normalization and parallel query method. If it is appropriate that fragments in the database can be eliminated to optimize performance, then denomalization is performed.

References

Silberschatz A., K. F. (2012). Database system and Concepts: Sixth edition. McGraw Hill.
Steven, C. (2013). Hands on Databases: Introduction to database design and development. Seattle Central Community College.