From non-normal form to first normal form

Now it is time to get down to the business of normalization. In this section, we will explain normalization using an example of a question that appeared on the past Information Technology Engineer Examination. (Some modifications have been made)

Question
　Normalize the data, which consists of records with the following repetitive structure, to the third normalized form. Here, the underlined part is the primary key. The unit and unit price are determined for each goods code. The company is required to call the city where the goods are purchased because the goods are hazardous materials.

Table of denormalized states

The table in the denormalized state is an RDB table, with the No's assigned from the goods code to the unit price, defined from 1 to 3. This situation is fraught with problems.

So what is the problem?

Customers cannot purchase more GOODS than the number defined in the table.

In this case, we have defined up to 3, which means that the customer cannot purchase more than 4 goodies. If we were to define as many tables as we can think of, we would be wasting a huge amount of space (and not being able to handle it properly in SQL). (And it can't be handled properly in SQL...).

So, we need to normalize it properly. First, let's take the first step from denormalization to the first normalized form, which is called a repeating group, as in the case of items from goods code to unit price.

Eliminating repeating groups is completed by splitting the repeating items into separate Tables and making them separate Tables. Care must be taken not to lose relationships in doing so. For example, if we make only the repeating items into a separate Table, we will not know who purchased what. Therefore, we put a dependent item (in this case, the primary key) on a separate Table.

table of the first normalized form

Now the customer can purchase GOODS without being limited by the table definition. You can keep adding records as you go.