First normal form (1NF). Converting the table to first normal form. Examples
This topic describes the process of converting arbitrary (chaotic) database tables to first normal form. Before exploring a topic, it is recommended that you familiarize yourself with the following topic:
Contents
- 1. First normal form (1NF). Terms of use
- 2. An example of reducing the table to the first normal form (1NF). Accounting for cars sold
- 3. An example of reduction to 1NF. Census data
- 4. Example of conversion to 1NF. Books accounting in the library
- 5. An example of conversion to 1NF. Educational process accounting table
- Related topics
Search other websites:
1. First normal form (1NF). Terms of use
Chaotically generated data tables do not always correspond to first normal form (1NF). As you know, reducing the table to the first normal form is a necessary condition for normalization. More details about the need to apply normalization in the database is described here.
The table is considered converted to the first normal form (1NF) if the following conditions are met:
- all table values are atomic (indivisible). This means that table cells must contain single values and all records in a separate table column (attribute) must be of the same type;
- no groups or arrays of duplicate data are allowed as table attribute values.
⇑
2. An example of reducing the table to the first normal form (1NF). Accounting for cars sold
A database for registering cars is specified, which is described by the Cars table. The structure of the table is as follows
The table contains the following data
According to the definition of the first normal form (1NF), all attributes (columns, fields) of a table must be atomic (indivisible).
In our case, the table should be split into atomic (indivisible parts).
Attribute Number can be divided into three indivisible parts with the following names:
- Series1;
- Number;
- Series2.
The Car attribute includes brand and model of the car. Therefore, it is advisable to split this attribute into two fields:
- Brand – the car brand itself (Mercedes-Benz, Toyota, Subaru, etc.);
- Model – car model (G-400, Sequoia, Avalon, Forester, etc.).
Also, the attribute Characteristics includes several parts that can be indivisible:
- Transmission – type of transmission (mechanical, automatic, tiptronic and others);
- Fuel – the type of fuel (gasoline, diesel, electricity, etc.);
- Volume – engine volume in liters.
After the changes made, the structure of the table is as follows
The data in the modified table can be presented as follows.
The above table is converted to 1NF first normal form. Despite the increase in the number of columns, such a table can already be converted to the second normal form 2NF.
⇑
3. An example of reduction to 1NF. Census data
An arbitrary table containing information about a citizen is given. The table is used in the population census. It is necessary to bring this table to 1NF.
The structure of the table is as follows.
In the table, the attributes Citizen, Number and Address contain data sets (arrays). If you do not implement the division of this data into atoms (indivisible elements), then when entering data, they may be distorted. These distortions will manifest itself in the fact that the same data, entered incorrectly, will be perceived as different data for the database.
Example data distortion. In the field (attribute) Citizen, the same person can be entered in different ways (Johnson J., Johnson John, J. Johnson). These are different people for the database. The same goes for other table attributes.
Therefore, in order to bring the table to the first normal form, the following attributes (fields) of the table must be split into atomic parts
- the Citizen attribute is divided into 2 attributes: Surname, First name;
- attribute Number is divided into 2 attributes: Series, Document number;
- the Address attribute is divided into the following attributes: Country, Region type, Region name, Settlement type, Settlement name, City part name, Room type, House number.
As a result of the changes made, the table structure will be as follows:
Such a table is considered reduced to the first normal form 1NF.
⇑
4. Example of conversion to 1NF. Books accounting in the library
In the example, the table is converted to 1NF, which displays data about the book in the library.
To bring this table to the first normal form, you need to select the indivisible (atomic) elements.
By analyzing the structure of the table, the field (attribute) Book can be broken into atomic elements.
First, there is a partition of the book into two parts:
- Author – surname, name and patronymic of the author;
- Title – the title of the book.
In turn, the Author field can be divided into 3 parts:
- Surname;
- Name;
- Patronymic.
After the performed transformations, the structure of the table is as follows
The problem is solved, the table is reduced to the first normal form (1NF).
⇑
5. An example of conversion to 1NF. Educational process accounting table
A database of accounting of the educational process in an educational institution is set, which is represented by one table. Carry out the reduction of the table to the first normal form (1NF).
The structure of the original table is as follows.
To bring the table to first normal form, you need to make all the attributes atomic.
Here it is advisable to split the field (attribute) Student into several attributes:
- Surname of the student;
- Name of sthe student;
- Middle name of the student.
Also, the Teacher field should be divided into 3 parts:
- Surname of the teacher;
- Name of the teacher;
- Teacher’s middle name.
As a result of the changes made, the table will have a structure that corresponds to the first normal form 1NF.
⇑
Related topics
⇑