The first normal form (1NF) - Databases: design

First normal form (1NF)

When modeling a database, work with relationships begins with their representation in the first normal form, which is the initial and basic for all relationships.

The relation is in the first normal form (1NF) if and only if any value of each tuple (instance) for each attribute contains only one value.

This definition formulates the basic requirement for the values ​​of each attribute of the relation of any of the instances - the value must be represented by a simple type and not be broken down into separate components (attributes). Here it is necessary to understand that individual attributes, especially denoted by symbolic data types, can be represented in a structured form. For example, the address of the location of the organization, described by the elements: index, country, city, street, house, etc. - can be broken down into separate components. If this is the case with the domain requirements, then such an attribute will not meet the requirements of the specified rule and should be broken down into separate attributes: Index, State, City, Street, Home; etc. If the attribute is defined as an indistinguishable address description string, then it can be represented by a single attribute Location address and will meet the requirements of the rule.

Take for example the relation "Order Goods", having the structure shown in Fig. 2.71 in the first normal form, where the attributes Order and Item in aggregate are the linked primary key and uniquely determine each instance of the relationship.

Fig. 2.71. Structure of the order Order Goods in the first normal form

The

The peculiarity of the relation under consideration is the fact that the value of the attribute "Price segment", in addition to the functional dependence on the product, also has a functional dependence on the supplier, thus complicating the procedures for correct data processing (Figure 2.72).

At the same time, the relation has a functional dependence of the supplier on one of the attributes of the primary key - Item & quot ;. These and other features of the functional dependencies of the attributes from each other lead to the occurrence of update anomalies, represented by problem situations when adding, changing or deleting data (Table 2.23). The presence of anomalies requires the developer to perform a procedure to normalize the relationship and transition to the second and subsequent normal forms.

Fig. 2.72. Functional dependencies of the "Order Goods" relationship

The

Table 2.23

Example data for the "Order Items" relation

Order

Product

Product Quantity

Vendor

Price segment

31

A1

10

P1

Economy

31

A2

25

P1

31

AZ

2

P2

Suite

32

A1

5

P1

Economy

32

A4

1

PP

33

A4

5

PP

- -

The

The

In the example presented, there is no "& ab; quot; from the supplier P2 & quot ;, because this product has not yet been ordered and placed in orders. This means that the use of this product A6 will not be possible until it is ordered, which also generates update anomalies that will interfere with efficient database operations.

Anomaly of the addition. This anomaly lies in the fact that it is impossible to add information about a new product with its article and supplier until it is added to the order with at least one unit. This is explained by the fact that the primary key, in essence of its type, must be filled in with all the attributes included in it. Hence, if a new product is added, the value of the attribute Order must be filled in with the value of the Item attribute.

Anomaly of the change. This anomaly occurs if you want to change the provider from a value, for example, PZ on P5 & quot ;. Because the product can be placed in many orders, the bundle "Item" - Vendor will be repeated in multiple instances (tuples) of the relationship. By changing the supplier in one instance, you must find all the other references to it and change it in the other instances. Otherwise, the relationship will become contradictory and the stored information will no longer be complete, where. the integrity of the data will be compromised.

Anomaly of removal. This anomaly arises if you try to delete an order with a single item of the goods, which, in turn, is not used in other orders. This operation, in addition to information about the order, will delete information about the product. In fact, the ratio in the first normal form contains too many different types of information collected in one place, which leads to the loss of a large amount of information when performing the corresponding operation on the data.

These difficulties in working with data require modification of the relationship structure in terms of its decomposition (separation) into several relationships using the exclusion rule, which are not irreducible, i.e. can be separated without losing data and go to the second normal form.

thematic pictures

Ошибка в функции вывода объектов.