The third normal form (ZNF) - Databases: design

The third normal form (ZNF)

The process of moving to the third normal form will consist in performing a projection operation on the "Goods" relation, dividing it into two relations - "Products" with the attributes Item and Vendor & quot ;, Vendors with the attributes Vendor and Price segment & quot ;. This separation is logical, based on an understanding of the fact that it is the attribute "Supplier" is the link between the product and the price segment. Thus, for the relationship "Suppliers attribute Vendor can be considered as a primary key, and the "Price segment" - its characteristics.

The ratio is also located in the third normal form if and only if it is in the second normal form and none of the key attribute is transitively dependent on its primary key.

The first attitude is "Order goods" and " is in the third normal form, because the only non-key attribute is the "Quantity of Item" directly depends on the full concatenated primary key, and the transitive dependence of the data on the attribute "Item" in the second normal form with the data of the attribute Price segment through the attribute "Supplier", which creates a functional dependence on non-key attributes, is normalized to the third normal form by dividing into two relations, which, in the end, leads to the three relations that are in the third normal form. Regarding the Products only directly dependent attributes remain, one of which is the primary key, and with respect to the Vendors of two generated attributes that are directly functionally dependent, one attribute is the Vendor in fact, the data represented by unique values ​​is defined as the primary key, which also makes it possible to talk about finding the ratio in the third normal form (Figure 2.75).

Fig. 2.75. Separation of the ratio by the rule of transition to the ZNF


And again the above anomalies become allowed. The allocation of suppliers in a separate relationship fixes the meaningfulness of the attribute "Price segment", concretizing its functional meaning, that all the goods of a particular supplier belong to a precisely fixed price segment. At the same time, it becomes possible to fill the relations with suppliers, not taking into account the interconnection of goods with them (Figure 2.76).

Fig. 2.76. Functional dependencies after passing to the ZNF


The essence of the transition to the third normal form is to identify the transitive dependencies and their separation according to the rule of transitivity

If A - B and B - C, then A - C. (2.4)

This logical formula forms one of the basic rules in relational relationships and is reflected in the rule for the formation of the third normal form.

The 2NF transition rule is & gt; ZNF

If the relation contains attributes in a non-key functional relationship that is functionally related to the primary relationship key, forming a transitive relationship, then such attributes are allocated to a separate relationship, and the dependent relationship is not dependent on a key attribute.

This rule requires the developer to pinpoint the binding attribute, which is dependent on the primary key and the determinant in a non-key functional relationship. This attribute will be present in both generated relations, transiting the remaining attribute of the primary key and the dependent attribute not of the key functional dependence, which was formed as a result of model building (see Figure 2.75).

It's important to note that with respect to the "Goods" there are two functional dependencies: a transitive dependency on the attribute Item to the Price segment and a direct functional relationship between the same attributes. The presence of such a situation leads to ambiguity of the functional dependence, as discussed earlier. If you consider that the attribute Price segment has a direct relationship to the goods, and the supplier determines which products of which price segments they are supplied, then the transition to the ZNF is possible by another algorithm (Figure 2.77).

Fig. 2.77. Functional dependencies of the "Goods" relationship


In this case, there is no need to generate a new primary key, preserving the original representation of the relationship, and the relationship of the relations "Goods" and Product segment through the Item allows you to connect the supplier to the price segment, without losing the content of the initial ratio (Figure 2.78).

Fig. 2.78. Variant of relations in the ZNF


However, considering the bundle of goods with the price segment, you can see some assumption, which in real life is infrequent. This assumption is that the chain segment reflects the characteristic of the product or supplier. If we consider the domain from the point of view of the realism of the representation in the database, the attribute "Price segment" must be functionally dependent on the concatenated primary key Item - Vendor & quot ;. But then the reduction to the third normal form of the original ratio becomes problematic, because the intersecting primary key is formed.

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)