It is said that the table is in the second normal form if it is in 1NF and each non-key column is completely dependent on the primary key. In other words, the value of each field must be completely determined by the value of the primary key. It is important to note that the dependence on the primary key is understood precisely as the dependence on the entire key, and not on its separate component (in the case of a composite key).
Here is an example of a table that is not in 2NF. To do this, let us return to the example of weather forecasting and supplement the table with another column the time of sunrise (this is a completely plausible example, this kind of information is often given on weather forecast sites). The use of the 먹튀검증 this is important now.
The Right Table Options
As we recall, this table has a composite key Date and Time of day. The Temperature field is completely dependent on the primary key – there are no problems with it. But the Sunrise field depends only on the Date field. The time of day does not naturally affect the time of sunrise.
It is appropriate to ask the question: what is the practical meaning of 2NF? What are the benefits of these restrictions? It turns out big. Assume that in the above example, the developer will ignore the requirements of 2NF. Firstly, the so-called redundancy is likely to arise the storage of excess data. Indeed, if sunrise time is already stored for one record with a given date, then for all other records with a given date it should be the same and there is no need to store it, generally speaking. Let’s pay attention to the words “should be”.
And if not?
Indeed, at the database level this is not controlled in any way – the key in the table is composite, the same dates can be (and most likely will be within the meaning). And no formal restrictions (and our understanding that “this cannot be” does not apply to them) do not prohibit specifying different sunrise times for the same date.
Third normal form
- It is said that a table is in 3NF if it corresponds to 2NF and all non-key columns are mutually independent.
- The interdependence of columns is conveniently understood as follows: columns are mutually dependent if one of them cannot be changed without changing the other.
- Here is an example of a table that is not in 3NF. Consider the example of a simple notebook for storing home phones of people living, possibly in different regions of the country.
- In this table there is a relationship between the non-key columns City and City Code, therefore, the table is not in 3NF.
Note that the developer determines the presence of the above dependence by analyzing the subject area such formal collisions cannot be seen by any formal methods. When changing the properties of the subject area, the dependence between the columns may disappear. For example, if different codes are entered within the same city, the corresponding columns cease to be related in terms of violation of 3NF requirements.