Translate Relations into Third Normal Form

The preceding lessons discussed the limitations of 2NF with regard to transitive dependencies and outlined the process of removing transitive dependencies to put the relation into 3NF. To summarize, follow these steps to translate relations into 3NF:

1. Identify repeating groups.
2. If there are repeating groups, separate the groups into relations of their own. The relations will then be in 1NF.
3. Identify the functional dependencies in the relations.
4. If one or more fields in a relation are not determined by the primary-key fields, move the undetermined fields to new relations. The relations will then be in 2NF.
5. Identify any transitive dependencies in the relations.
6. If there are transitive dependencies (if more than one entity is represented in a relation), break the relation into smaller relations that represent each entity in the larger relation. The relations will then be in 3NF.

As a data modeler working with a relational database that has not been normalized, your objective might involve transitioning the database schema to the Third Normal Form (3NF). This process typically follows a structured approach, though the specific steps can vary depending on the existing database structure and the data involved. Firstly, you would likely start by ensuring that the database is in First Normal Form (1NF). This step usually involves removing any repeating groups or arrays, ensuring that each field contains only atomic values, and establishing a primary key for each table. This step is crucial as it lays the foundation for further normalization. Once the database is in 1NF, you might proceed to bring it into Second Normal Form (2NF). This step generally involves eliminating any partial dependencies, where a non-key attribute is dependent on only a part of the primary key in a table with a composite primary key. To achieve this, you could be splitting the table into two or more tables, each with a part of the original composite key as their primary key.
Finally, to transition the database to Third Normal Form (3NF), you would likely focus on removing transitive dependencies. This involves ensuring that non-key attributes are not dependent on other non-key attributes. In practical terms, this could mean further splitting tables to ensure that each non-key attribute is only dependent on the primary key. The aim here is to ensure that the data is not only properly segmented but also that the structure prevents anomalies and maintains data integrity. Throughout this process, it's important to balance normalization with the practical needs of the database. Over-normalization can lead to complex queries and potential performance issues, so you might find yourself making trade-offs between theoretical ideal structures and pragmatic considerations based on how the database is used. Additionally, documenting each step of the normalization process and testing the impact of changes on database performance and query complexity can be crucial in achieving an optimal database design.

To understand third normal form, you must first understand transitive dependency. An attribute that depends on another attribute that is not the primary key of the relation is said to be transitively dependent.
A relation is said to be in third normal form if it meets both the following criteria:

1. The relation is in second normal form.
2. There is no transitive dependence (that is, all the non-key attributes depend only on the primary key).

To transform a second normal form relation into third normal form, simply move any transitively dependent attributes to relations where they depend only on the primary key. Be careful to leave the attribute on which they depend in the original relation as a foreign key. You will need it to reconstruct the original user view via a join. When it is in third normal form, that tells you to remove easily calculated attributes.
The next lesson describes Codd's 12 criteria for a fully relational database.

Before you move to the next lesson, click the Exercise link to reinforce your understanding of 3NF.
Achieve Third Normal Form - Exercise