Database normalization

Grouping the same data into tables can be done in various ways. Attributes in relationships should be grouped according to the relational principle, that is, duplication of data should be completely minimized, and the processing procedure with subsequent updating should be simplified. One of the primary tasks in the design of databases is the elimination of redundancy, and it is achieved through normalization.

Normalization of databases is a kind of formal device for restrictions on the creation of tables, which allows eliminating duplication, with mandatory maintenance of consistency of stored information, reducing the labor costs associated with maintaining and maintaining the database. The normalization operation consists in decomposing the initial tables of the database into simpler ones. At each stage of the process, the tables are necessarily put into normal forms. Each stage of normalization is characterized by a certain set of constraints, which all tables must match. Thus, it is possible to remove non-key information from the tables, which is redundant.

The normalization of databases is based on the notion of a functional relationship between attributes. It is considered that one attribute depends on another if at each moment of time the specified value of the second attribute corresponds to no more than one value of the first one.

Normalization of databases is a general concept, however, it is accepted to subdivide it into several normal forms, which will be discussed below.

Any information object is considered to correspond to the first normal form, when the value of each of its attributes is unique. If any attribute has a repeating value, then the object belonging to the first normal form can not be considered. It turns out that you can create some other entity, that is, an information object.

Any information object is considered to belong to the second normal form, when it already consists in the first normal form, but each of its attributes, not consisting in a potential key, completely depends on the functional plan from each of the potential keys.

Any information object is considered to belong to the third normal form, if it already consists in the second normal form, but there is no transitive dependence of non-key objects on the keys. Under the transitive relationship, it is customary to understand the apparent relationship between fields.

Normalization of the database sets the main goal for the developer, which is to bring all relations to the third normal form. This is the only way to create an effective information system in the future.

Database Normalization: Basic Rules

It is necessary to formulate a set of rules that should be adhered to in the work on normalization. First of all it is necessary to exclude repeating groups. It is necessary to create a separate table that stores each set of related attributes, in which to create a separate key. Further, it is necessary to exclude redundant data. In cases where the dependence of the attribute is observed only on the part of the key, it must be put into a separate table. The third rule is the mandatory exclusion of columns that do not depend on the key. Attributes should be placed in an isolated table if they do not have the proper effect on the key. It is necessary to isolate independent multiple relations. In this case, we are talking about the fact that there is no specific connection between several relations. Finally, it is worth isolating multiple relationships that are semantically related. This completes the normalization of the database, after which the development process begins.