WHAT IS THE THIRD NORMAL FORM? (DATABASES) - TECHNOLOGY

The third normal form (databases) is a relational database design technique, where the different tables that compose it not only comply with the second normal form, but all their attributes or fields depend directly on the primary key.

When designing a database, the main goal is to create an accurate representation of the data, the relationships between them, and the restrictions on the data that are relevant.

Source: pixabay.com

To achieve this goal, some database design techniques can be used, among which is normalization.

This is a process of organizing the data in a database to avoid redundancies and possible anomalies in the insertion, updating or elimination of the data, generating a simple and stable design of the conceptual model.

It begins by examining the functional relationship or dependency between attributes. These describe some property of the data or the relationship between them.

Normal shapes

Normalization uses a series of tests, called normal forms, to help identify the optimal grouping of these attributes and ultimately establish the appropriate set of relationships that support a company's data requirements.

That is, the normalization technique is built around the concept of normal form, which defines a system of constraints. If a relationship meets the constraints of a particular normal form, the relationship is said to be in that normal form.

First normal form (1FN)

A table is said to be in 1FN if all attributes or fields within it contain only unique values. That is, every value for each attribute must be indivisible.

By definition, a relational database will always be normalized to the first normal form, because attribute values are always atomic. All relationships in a database are in 1FN.

However, simply leaving the database like this stimulates a number of problems, such as redundancy and possible upgrade failures. Higher normal forms were developed to correct these problems.

Second normal form (2FN)

It deals with removing circular dependencies from a table. A relation is said to be in 2FN if it is in 1FN and furthermore each non-key field or attribute depends entirely on the primary key, or more specifically, it ensures that the table has a single purpose.

A non-key attribute is any attribute that is not part of the primary key for a relationship.

Third normal form (3FN)

It deals with eliminating transitive dependencies from a table. That is, remove the non-key attributes that do not depend on the primary key, but on another attribute.

A transitive dependency is a type of functional dependency in which the value of a non-key field or attribute is determined by the value of another field that is also not key.

You should look for repeating values in non-key attributes to ensure that these non-key attributes do not depend on anything other than the primary key.

Attributes are said to be mutually independent if none of them is functionally dependent on a combination of others. This mutual independence ensures that attributes can be updated individually, without the danger of affecting another attribute.

Therefore, for a relationship in a database to be in third normal form, it must comply with:

- All the requirements of 2FN.

- If there are attributes that are not related to the primary key, they must be removed and placed in a separate table, relating both tables by means of a foreign key. That is, there should not be any transitive dependencies.

Examples of third normal form

Example 1

Let the table be STUDENT, whose primary key is the student's identification (STUDENT_ID) and is made up of the following attributes: STUDENT_NAME, STREET, CITY and POST_CODE, fulfilling the conditions to be 2FN.

In this case, STREET and CITY do not have a direct relationship with the primary key STUDENT_ID, since they are not directly related to the student, but are totally dependent on the postal code.

As the student is located by the site determined by CODE_POSTAL, STREET and CITY are related is with this attribute. Due to this second degree of dependency, it is not necessary to store these attributes in the STUDENT table.

Create new table

Suppose that there are multiple students located in the same zip code, with the STUDENT table having an immense amount of records, and it is required to change the name of the street or city, then this street or city must be found and updated in the entire table STUDENT.

For example, if you need to change the street “El Limón” to “El Limón II”, you will have to search for “El Limón” in the entire STUDENT table and then update it to “El Limón II”.

Searching in a huge table and updating the single or multiple records will take a long time and therefore affect the performance of the database.

Instead, these details can be kept in a separate table (POSTCARD) that is related to the STUDENT table using the POST_CODE attribute.

The POST table will have comparatively fewer records and this POST table will only need to be updated once. This will be automatically reflected in the STUDENT table, simplifying the database and queries. So the tables will be in 3FN:

Example 2

Let the following table be used with the Project_Num field as the primary key and with repeated values in attributes that are not keys.

The Telephone value is repeated each time a manager's name is repeated. This is because the phone number only has a second degree dependency on the project number. It really depends on the manager first, and this in turn depends on the project number, which makes a transitive dependency.

The Project_Manager attribute cannot be a possible key in the Projects table because the same manager manages more than one project. The solution for this is to remove the attribute with the repeated data (Phone), creating a separate table.

The corresponding attributes must be grouped together, creating a new table to save them. The data is entered and it is verified that the repeated values are not part of the primary key. The primary key is set for each table and, if necessary, foreign keys are added.

To comply with the third normal form, a new table (Managers) is created to solve the problem. Both tables are related through the Project_Manager field:

References

Teradata (2019). First, Second, and Third Normal Forms. Taken from: docs.teradata.com.
Tutorial Cup (2019). Third Normal Form (3NF). Taken from: tutorialcup.com.
Database Dev (2015). Third Normal Form (3NF) - Normalising Your Database. Taken from: databasedev.co.uk.
Relational DB Design (2019). Introduction to Third Normal Form. Taken from: relationaldbdesign.com.
Dummies (2019). SQL First, Second and Third Normal Forms. Taken from: dummies.com.

WHAT IS THE THIRD NORMAL FORM? (DATABASES) - TECHNOLOGY - 2025