Explanation on Various Normal Forms, description on first, second, third, fifth and fourth normal forms, domain key normal form, real world example or live project scenarios on normal forms.
Various Normal Forms with Explained Description
(1NF) First
Normal Form: Eliminate Repeating Groups- A relation R is said to be in First
Normal Form (1NF) if and only if all the attributes of the relation R are atomic in nature. A table (relation) is in 1NF if
- There are no duplicate rows in the table
- Each cell is single-valued (i.e., there are no repeating groups or arrays)
- Entries in a column (attribute, field) are of the same kind
Keep in Mind: Make a
separate table for each set of related attributes, and give each table a primary key. Each field contains at most
one value from its attribute domain.
(2NF) Second Normal Form: Redundant Data Eliminated- A relation R is said to be in Second Normal Form (2NF) if and only if
(2NF) Second Normal Form: Redundant Data Eliminated- A relation R is said to be in Second Normal Form (2NF) if and only if
- It is in the First Normal Form (1NF), and
- No partial dependency exists between non-key attributes and key attributes
Keep in Mind: If an
attribute depends on only a part of the multi-valued key, remove it to a
separate table.
(3NF) Third Normal Form: Eliminated Columns are Not Dependant On Key- A relation R is said to be in Third Normal Form (3NF) if and only if
(3NF) Third Normal Form: Eliminated Columns are Not Dependant On Key- A relation R is said to be in Third Normal Form (3NF) if and only if
- It is in Second Normal Form (2NF)
- No transitive dependency exists between non-key attributes and key attributes
Keep in Mind: If
attributes do not contribute to a description of the key, remove them to a
separate table. All attributes must be directly dependent on the primary key.
(BCNF) Boyce-Codd Normal Form- A table is in BCNF if it is in 3NF and if every determinant is a candidate key. A 3NF relation is almost always in BCNF. However, the following conditions define some situations when a 3NF relation may not be in BCNF:
(BCNF) Boyce-Codd Normal Form- A table is in BCNF if it is in 3NF and if every determinant is a candidate key. A 3NF relation is almost always in BCNF. However, the following conditions define some situations when a 3NF relation may not be in BCNF:
- The candidate keys are composite
- There are more than one candidate keys in the relation
- There are some common attributes in the relation
Keep in Mind: If there are non-trivial dependencies between
candidate key attributes, separate them out into distinct tables.
(4NF) Fourth Normal Form: Isolate Independent Multiple Relationships- A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
Note: No table may contain two or more 1:n or n:m relationships that are not directly related.
(5NF) Fifth Normal Form: Isolate Semantically Related Multiple Relationships- A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Keep in Mind: There may be practical constrains on information that justify separating logically related many-to-many relationships.
(DKNF) Domain-Key Normal Form: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains. It is a model which is free from all modification anomalies.
(4NF) Fourth Normal Form: Isolate Independent Multiple Relationships- A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
Note: No table may contain two or more 1:n or n:m relationships that are not directly related.
(5NF) Fifth Normal Form: Isolate Semantically Related Multiple Relationships- A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Keep in Mind: There may be practical constrains on information that justify separating logically related many-to-many relationships.
(DKNF) Domain-Key Normal Form: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains. It is a model which is free from all modification anomalies.