Databases

Databases. Normalization. Second normal form (2NF)

Normalization. Second normal form (2NF)

It is recommended that you familiarize yourself with the following topics before exploring this topic:

Contents

Search other websites:




1. Second normal form. Definition

Таблица (отношение) соответствует второй нормальной форме, если:

  • the table is reduced to the first normal form 1NF;
  • there are no partial dependencies in the table. This means that every non-key attribute (field) of a table irreducibly depends on the primary key of the table (the key of the relation).

The second normal form is relevant only for those relationships in which there are composite keys.

  ⇑

2. Ways to reduce the table to the second normal form 2NF

As you know, the primary key can be simple or composite. If the key is composite, it can be difficult to avoid partial dependencies. One of the fields can functionally depend on a part of the primary key (depend on some key attributes).

If the table has a composite (composite) key and contains partial dependencies, then you can use the following casting methods:

  • introduce an additional field-counter (counter), which will be the primary key. This field contains unique values that identify each row in the table. Since the primary key is not composite, there are no partial dependencies. After entering an additional counter field, a one-to-one correspondence is established between this counter field and any other field in the table;
  • break the original table into several tables so that the key attributes (key fields) diverge in different relationships. This method can also be used in higher normal forms.

  ⇑

3. An example of a table in second normal form. Dependency of a non-key attribute on a composite key

The dependence of an attribute on a composite key is demonstrated. Figure 1 shows a table of production of different car models in different years.

The relationship key is the Brand–Model-“Year of manufacture” attributes. The attribute “Number of released models” irreducibly dependent on the key.

Databases. Functional dependence of the attribute on the key. Table in second normal form 2NF

Figure 1. Functional dependence of the “Number of released models” attribute on the key. Table in second normal form 2NF

The above table (Figure 1) is in second normal form for the following reasons:

  • the table is in the first normal form 1NF;
  • the non-key attribute (Number of released models) irreducibly depends on the primary key (Brand – Model – “Year of manufacture“);
  • there are no partial dependencies in the table. This means that the attribute “Number of released models” does not depend separately on one of the attributes included in the relationship key (Brand, Model, Year of manufacture). This attribute depends on the combination of these attributes Brand–Model–Year of manufacture.

  ⇑

4. Examples of converting a table to second normal form by adding a counter field

Example 1. An employee accounting table with the following structure is set.

Employee accounting table

Attributes Number – Position were selected as the key of the relationship (Figure 2).

Databases. Partial dependence of the non-key attribute on the key of the relationship

Figure 2. Partial dependence of the non-key attribute Salary on the key of the Number–Position relationship

The size of the employee’s salary depends on the position held. This means that there is a functional dependence between the part of the key of the relationship (attribute Position) and the non-key attribute Salary. This is a partial dependency. Therefore, the table is not in 2NF second normal form.

To bring the table to the second normal form 2NF, you need to enter an additional key field as shown in Figure 3.

Databases. Adding a new field. Eliminating partial dependency

Figure 3. Adding a new ID field, which is the key of the relationship. Eliminating partial dependency

After the introduction of the additional field, partial dependencies are eliminated. The table is reduced to the second normal form 2NF.

Example 2. Let there be given a table of accounting of occupations in an educational institution.

A table of accounting of occupations in an educational institution

In the table, the attributes “Classroom number” – “Lesson number” – “Date” – “Teacher” are selected as the key of the relationship. Figure 4 shows this relationship.

Databases. Partial dependence of the non-key attribute on the relationship key

Figure 4. Partial dependence of the non-key Subject attribute on the relationship key

The Subject attribute is dependent on the Teacher attribute. This is due to the fact that a specific subject is assigned to a specific teacher. Since the Teacher attribute is part of the primary key, the Subject attribute depends on the part of the primary key. Hence, there is a partial relationship between the primary key and the attribute Subject.

To reduce the table to the second normal form 2NF, you need to add an additional counter field and make it a key. After the changes have been made, the table looks as shown in Figure 5.

Databases. Table in second normal form 2NF

Figure 5. Table in second normal form 2NF

  ⇑

Related topics

  ⇑