In a relational Database, a normal form indicates a particular type of relation between the entities.

The standardization of the data models makes it possible to check the robustness of their design to improve modeling (and thus to obtain a better representation) and to facilitate the memorizing of the data (and thus to avoid the Redondance and the subjacent problems of update or coherence). Standardization applies to all the Entité S and to the Relation S carrying properties.

The standardization of the data models was popularized mainly by the method Merise.

Various normal forms

1FN - first normal form :

• any attribute contains an atomic value

• all the attributes are nonrepetitive
• all the attributes are constant in time.

2FN - second normal form

• it respects the first form normale
  
• all the attributes not-keys are completely dependant functionally on the totality of the key primaire.
  

3FN - third normal form

• it respects the second form normale
  
• any attribute not belonging to a key does not depend on an attribute not clé
  

BCFN - normal form of Boyce - Codd
If an entity or a relation in third normal form has a concaténée key (multiple), none the elementary properties of this key must be in functional dependence of another property. Sometimes this standardization results transforming an entity into relation or in breaking up a relation into two simpler relations.
4FN - fourth normal form
For any relation of dimension N in normal form of Boyce-Codd, the relations of dimension n-1 built on its collection must have a direction. It should not be possible to reconstitute the occurrences of the relation of dimension N by joint of two relations of dimension n-1 . Sometimes this standardization results in breaking up a complex relation into two simpler relations.
5FN - fifth normal form
For any relation of dimension N (with N higher than 2) in fourth normal form, it should not be possible to find the whole of its occurrences per joint on the occurrences of the partial relations taken two to two. Sometimes this standardization results in breaking up a complex relation into several simpler relations.

To remember the order and the characteristics of the first three normal forms, it is enough to remember the oath that all the witnesses must lend in front of justice: I swear to say the truth, all the truth, anything else that the truth.

What gives: 1FN = the key. 2FN = All the key. 3FN = Only the key.

Theoretical examples of standardization

Examples of standardization according to the three principal types of normal forms:

• the first normal form , where each attribute of the entities contains an atomic value (nonmade up);

example:

In this case the values of the supplier are multivaluées and are not atomic. So that this relation is in first normal form, it is necessary to break up the attributes of the column supplier as follows:

solution:

• the second normal form is a relation in first normal form where each attribute which does not belong to the key (the whole of the attributes allowing to identify in a single way a tuple of the entity) does not depend solely on part of the key;

example:

Let us admit that the key of this table is a composite key (produced - supplier). In the case of a change of address of a supplier, it will be necessary to show much attention not to forget any place where the address is mentioned. Indeed, it is noted that the field address depends only on part of the key: the field supplier, which induces the possibility of a redundancy within the table. It is thus advisable to divide the table into two:

solution in second normal form:

In this manner, a change of address gives place only to only one modification in the table of the suppliers.

• the third normal form is a relation in second normal form where the attributes which do not form part of the key do not depend on attributes not forming either part of the key (the attributes are thus completely independent from/to each other).

example:

The country of the address is not depend on the key of the table, namely the name of the supplier, but is function of the city of the address. Again, it is preferable to divide the table into two:

standardized solution:

In this manner, a modification of the orthography for a country (for example: Great Britain in Great-Britain) will give place only to only one modification.

Examples of violation

(them * the attributes indicate belonging to the primary key)

1FN - first normal form :

• any attribute contains an atomic value

attribute CLIENT_ID is composed of 2 atomic attributes.

the attribute NAME is composed of 2 atomic attributes.

• all the attributes are nonrepetitive

the attribute SUPPLIERS is a list.

• all the attributes are constant in time.

the OLD attribute is not constant in time.

2FN - second normal form

• all the attributes not-keys are completely dependant functionally on the totality of the key primaire.
  

attribute DESCRIPTION_ARTICLE depends only on part of the primary key.

3FN - third normal form

• any attribute not belonging to a key does not depend on an attribute not clé
  

attribute NOM_CLIENT depends on CLIENT_ID.

BCFN - normal form of Boyce - Codd

• If an entity or a relation in third normal form has a concaténée key (multiple), none the elementary properties of this key must be in functional dependence of another propriété.
  

If Durand stops teaching Mathematics, one removes the line and one loses the relation Matter-Room.

Random links:

144 (number) | Minor Helpe | Olivier Branch | Striped Surubí | Pavlo Skoropadski | Halebidu