How many types of normal forms?

Answer 1

The formal classifications describing the level of database normalization in a data model are called Normal Forms (NF) and the process of doing this is Normalization.

First normal form

First normal form (1NF) lays the groundwork for an organised database design:

Ensure that each table has a primary key: minimal set of attributes which can uniquely identify a record.

Eliminate repeating groups (categories of data which would seem to be required a different number of times on different records) by defining keyed and non-keyed attributes appropriately.

Atomicity: Each attribute must contain a single value, not a set of values.

'First normal form' depends on functional dependency formula f(x)=y. For every value of x there is value for y.

Second normal form

Second normal form (2NF) If a table has a composite key, all attributes must be related to the whole key:

The database must meet all the requirements of the first normal form.

The relational schema should not have any partial functional dependency i.e. No proper subset of the primary key should derive a functional dependency belonging to the same schema. For example, consider functional dependencies FD:{AB->C, A->D, C->D} here AB is the primary key, as A->D this relational schema is not in 2NF.

Third normal form

Third normal form (3NF) requires that data stored in a table be dependent only on the primary key, and not on any other field in the table.

The database must meet all the requirements of the first and second normal form.

All fields must be directly dependent on the primary key field. Any field which is dependent on a non-key field which is in turn dependent on the Primary Key (ie a transitive dependency) is moved out to a separate database table.

4 FOURTH AND FIFTH NORMAL FORMS
Fourth [5] and fifth [6] normal forms deal with multi-valued facts. The multi-valued fact may correspond to a many-to-many relationship,

4.1 Fourth Normal Form
Under fourth normal form, a record type should not contain two or more independent multi-valued facts about an entity. In addition, the record must satisfy third normal form.

Fifth normal form deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy. Second, third, and fourth normal forms also serve this purpose, but fifth normal form generalizes to cases not covered by the others.

Boyce-Codd normal form

Boyce-Codd normal form (or BCNF) requires that there be no non-trivial functional dependencies of attributes on something other than a superset of a candidate key (called a superkey).