The second normal form (211F) - Databases: design

The second normal form (211F)

The transformation of the relation in the first normal form by dividing the functional dependencies that are not irreducible is based on the analysis of the subject area and the understanding of the essence of the functional dependencies in relation to (Figure 2.73). In this example, there is the "Order Goods" relationship, described by the primary linked key and the "Quantity of Item" attribute, as well as the "Goods" relation that contains the attributes "Goods", "Supplier" and Price segment .

Fig. 2.73. Separation of the ratio according to the transition rule to 2NF


The above update anomalies are now resolved and these problems will not occur.

Anomaly of the addition. Now, product information A6 from the supplier P2 can be added to the Goods relationship, without requiring that this article be specified in the order. Thus, the goods will be placed in the database, but the order will be indicated in the orders.

Anomaly of the change. Changing a vendor to a product requires that you enter data in only one instance of the Goods relationship, and a bundle of relationships Order Goods and Products through the Item allows you to automatically take into account this change in all orders, which allows you to resolve the previously considered anomaly.

Anomaly of removal. Here you can delete the item position specified only once in the orders, and do not lose information about the suppliers of this product, since this information is now placed in a different relation, and the removal procedure will be performed only for the goods item itself in the order, without affecting the information about the supplier of this product (Figure 2.74).

Fig. 2.74. Functional dependencies after transition to 2NF

As it is now obvious from the scheme of functional dependencies, information about the suppliers of goods and price segments are not components of the relation "Order goods". (Table 2.24), which is logical from the point of view of the subject area, since the customer order can not be characterized by suppliers, and the price segment can refer to the supplier's products, and not to the order.

Table 2.24

Ordering Order
Order Product Quantity of goods
31 А1 10
31 A2 25
31 AZ 2
32 А1 5
32 А4 1
33 А4 5

Product Supplier Price segment
A1 P1 Econom
A2 P1
AZ P2 Lux
А4 ПZ Econom
A6 P5 Suite

Sample relationship data Order goods in 2NF


For Products there was a product "A6", which was not previously. However, this product is not present in orders. This became possible due to the allocation in a separate relationship of a set of attributes that are assigned according to the exception rule for dependencies that are not irreducible, giving the initial relation to 2NF, thereby facilitating work with the database and making it more efficient.

The relation is in the second normal form if and only if it is in the first normal form and each nonkey attribute is irreducibly dependent on its primary key.

In fact, the transition to 211F is to identify attributes that are functionally dependent on a part of the primary key of the original relationship, and to isolate these attributes with the primary key into a separate relationship, which was done in this example. Attributes Vendor and Price range functionally depend on the part of the primary key represented by the Item attribute, and functionally does not depend on the concatenated primary key of the original Order relationship. In this case, it is quite logical to select the attributes Item (primary key), Vendor and Price range in a separate relationship. In this case, the "Item" attribute, which is part of the linked primary key of the "Order Items" relationship, remains in this respect because it reflects the functional dependency of the "Quantity of Item" attribute. from a set of attributes in a linked primary key.

The rule of transition 1NF - 2NF

If the ratio is in 111F and there are functional dependencies on the part of the primary key, then from these attributes a new relation (projection) is compiled into which all the functionally dependent attributes and the part of the primary key on which they depend are transferred, and the original relationship is deleted only the selected attributes that are not related to its primary key.

Both relations obtained are in the 2NF, since all functional dependencies in them are associated with a single primary relationship key: with respect to the "Order Items" all attributes are functionally dependent on the concatenated key Order - Product & quot ;, for Goods all attributes are functionally dependent on a single primary key Item & quot ;.

However, the attitude of "Goods" contains some problems that the non-key attributes are still not mutually independent, namely the "Price range" attribute. is functionally dependent on the Supplier attribute. This leads to certain update anomalies that need to be resolved.

Anomaly of the addition. Regarding the Products attributes Vendor and Price segment have a functional relationship that leads to the situation that it is impossible to keep information about the vendor price segment in the database until this provider appears in relation to.

Anomaly of the change. Similarly to the anomaly for the first normal form, any change in the price segment for the supplier in one of the relationship instances requires a repetition of this operation for all other instances of the relationship where the goods are provided by the relevant supplier. If this is not done, then the meaning of the attribute Price segment will be violated. When considering the attribute Price segment in the form of the market segment to which the supplier is oriented, if the value of this attribute is changed for one instance (we assume that there are several instances of goods from the variable supplier in the relation), the attribute ceases to characterize the supplier, but becomes the product characteristic-the price segment of the goods, which is incorrect for the essence of the database development, where the change in the semantic load of attributes as a result of normalization should not occur.

Since the semantic load of attributes must be unchanged from the moment the attribute is detected before it is used in the database, one of the significant problems in the development is their correct naming, reflecting the unambiguous meaning of the data represented by the corresponding attribute. In this example, it is reasonable, for the sake of unambiguous understanding, the attribute "Price segment", due to the fact that it is placed along with attributes that may also have a similar characteristic, is called the "Price segment of the supplier". This naming rule eliminates the ambiguity of using the attribute, and then the anomaly of the change with respect to the "Goods" is clearly revealed.

Anomaly of removal. If the Goods there is only one instance for a particular vendor, then deleting this instance will result in loss of information about the provider and its price segment.

Again, the problem of functional dependencies that are not related to the existing primary key is "Goods". Understanding that the attribute Price segment is a characteristic of the "Supplier", this relationship must be normalized by translating it into the third normal form.

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)