Normalization Forms in DBMS

Normalization is the process of gathering data items (or properties) into relations. The goal of logical database design is to represent objects in the database using relations that (1) provide the data needed to construct user objects and (2) are robust enough to allow rows to be inserted, deleted, and modified without resulting in inconsistencies or errors in the stored data. The considerable theoretical work on the nature of a well-designed relation is called normalization because one of the pioneers of database technology, E. F. Codd, defined a variety of normal forms that relations (or files) could take.

First Normal Form

If every attribute is single valued for each tuple then relation is in first normal form. A relation has no repeating groups in first normal form
that is the characteristic of the first normal form.

Second Normal Form

A relation is in second normal form if all non-key attributes are dependent on all of the key. Observe that this definition pertains to relations that have composite keys (keys made up of two or more attributes). If the key is a single attribute, then the relation is automatically in second normal form.

Third Normal Form

Relations in second normal form also have anomalies. To eliminate the anomalies from a relation in second normal form, the transitive dependency must be removed. This leads to a definition of third normal form: A relation is in third normal form if it is in second normal form and has no transitive dependencies.

Boyce-Codd Normal Form

A relation is in boyce-codd normal form if every determinant is a candidate key. Relations in BCNF have no anomalies regarding functional dependencies, and this seemed to put the issue of modification anomalies to rest. However, it was soon discovered that anomalies can arise from situations other than functional dependencies.

Fourth Normal Form

A relation is in fourth normal form if it is in BCNF and has no multivalued dependencies.

Fifth Normal Form

Fifth normal form concerns dependencies that are rather obscure. It has to do with relations that can be divided into subrelations, as we have been doing, but then cannot be reconstructed. The condition under which this situation arises has no clear, intuitive meaning. We do not know what the consequences of such dependencies are, or even if they have any practical consequences. Each of the normal forms identified by researchers who found anomalies with some relations that were in a lower normal form. For example, noticing modification anomalies with relations in third normal form led to the definition of Boyce-Codd normal form.

Domain/Key Normal Form

In 1981, R. Fagin published an important paper in which he defined domain/ key normal form. He showed that a relation in domain/key normal form has no modification anomalies and, further, that a relation having no modification anomalies must be in domain/key normal form. This finding establishes a boundary on the definition of normal forms. No higher normal form will be needed, at least for the purpose of eliminating modification anomalies. Equally important, DK/NF involves only the concepts of key and domain. These concepts are fundamental and close to the hearts of database practitioners. They are readily supported by DBMS products (or could be, at least). In a sense, Fagin's work formalized and justified what many practitioners believed intuitively but were unable to express precisely.

The DK/NF concept is quite simple: A relation is in DK/NF if every constraint on the relation is a logical consequence of the definition of keys and domains. Let us consider the important terms in this definition: constraint, key, and domain. Constraint in this definition is a broad term. Fagin defines a constraint as any rule on static values of attributes that is precise enough that we can evaluate whether or not it is true. Thus edit rules, intra- and inter-relation constraints, functional dependencies, and multivalued dependencies are examples of constraints as Fagin has defined them.

Imran Zafar writes articles about computer basics and database management such as free computer dictionary, types of database systems.