From second normal form to third normal form

Tables in second normal form Orders table

As those with good instincts may have noticed, the PhoneNo for City is functionally subordinate to the City Code, right?

Since the PhoneNo of the City is not a candidate key in the customer table, the Table was not split in the process of making the second normal form, but once the Customer Code is determined, the City Code is determined, and once the City Code is determined, the PhoneNo is determined. Such a situation is called a transitive function dependency. To add, when A is B, B is C, and therefore A is C in the three-stage argument, C is transitive function dependent on A.

Eliminating the transitive function dependence in the second normal form results in the third normal form, so the "B is C" part is split into a separate Table. Then, the table structure is as follows.

Tables in third normal form
Orders table

This completes the third normalized form. In many systems, normalization up to this point is sufficient for practical use. For example, if the phone number of the city hall is changed, multiple phone numbers need to be updated in the second normalized form, but once the third normalized form is completed, only one record in the phone table needs to be updated. If the customer's company name changes, it only needs to be updated in one place, and if the number of goods increases, only one record needs to be added. Such a situation is called "one fact in one place," which means that data manipulation can be done with minimal effort. It is very efficient.

A quick review of the normalization process is as follows.

・Non-normal form to first normal form
To eliminate repeating groups, divide by repeating groups and key items.

・first normalized form to second normalized form
Split function dependencies on key items other than the primary key.

・Second to third normal form
Split transitively function dependent.

These are the three steps.

Some of them may not have transitive functional dependencies. In those cases, when the second normal form is used, it is completed up to the third normal form.

Now, finally, let's solve the past questions.