Types of Normal Forms in DBMS

Database normalization is nothing but the process of structuring an RDBMS by applying some general rules either by creating a new database design or by decomposition with a series of so-called normal forms which are:

1. Unnormalized form or UNF
2. First Normal Form or 1NF
3. Second Normal Form or 2NF
4. Third Normal Form or 3NF
5. Elementary key normal form or EKNF
6. Boyce Codd Normal Form or BCNF
7. Fourth normal form or 4NF
8. Essential tuple normal form or ETNF
9. Fifth normal form or 5NF
10. Domain-key normal form or DKNF
11. Sixth normal form or 6NF

1. Unnormalized form or UNF: It is the simplest database model also known as non-first normal form (NF2).A UNF model will suffer problems like data redundancy thus it lacks the efficiency of database normalization. Example: Student Table:

StudentId      Name       Course 
 
  101           Raj        Mathematics
                          Chemistry
  102          Nilesh      Chemistry
  103          Sanu        Physics
                          Chemistry

In this above example data in unnormalized-form because this table contains multivalued attributes in the course tuple. But there are several advantages also present for the unnormalized forms (That’s why we still use this though it lacks several advantages of database normalization) which are:

1. UNF can deal with the complex data structures,
2. querying in UNF is simpler,
3. Restructuring the data is easier.

Using this easier to query feature NoSQL databases like MongoDB, Apache etc. is more scalable hence the tech-giants like Google, Amazon and Facebook uses this for dealing with a huge amount of data daily that are difficult to store. 2. First Normal Form or 1NF: A relation is in first normal form only if the relational table doesn’t contain any multivalued attribute but contains only single-valued attributes. Example: Student Table:

StudentId      Name       Course1        course2 
 
  101          Raj         Mathematics    Chemistry
  102          Nilesh      Chemistry
  103          Sanu        Physics        Chemistry

To ensure this model is in first normal form, we split the course tuple (previous example) into course1 and course2 to hold our course information as atomic entities so that no row contains more than one courses.no duplicate rows. 3. Second Normal Form or 2NF: A relation is in second normal form if:

• It is in first normal form or 1NF
• It doesn’t contain any partial dependencies. (It shouldn’t have any non-prime attribute which is functionally dependent on any proper subset of the candidate key of the relation.).

4. Third Normal Form or 3NF: Let R be the relational schema, X->Y any non-trivial functional dependency over R is in 3NF if:

• R should be in 2NF
• X should be candidate key or superkey, or
• Y should be prime attribute

(So basically the relation which is in 2NF already if it doesn’t contain any transitive dependencies then it will be in 3NF.). 5. Elementary key normal form or EKNF: It the improve version of the third normal form, thus generally EKNF is itself in 3rd Normal Form. When there is more than one unique compound key and the keys are overlapped then this leads to the redundancy in the overlapping column. Thus if in the 3NF relation each and every non-trivial functional dependency involves with either a superkey or an elementary key’s subkey then it is in EKNF. 6. Boyce Codd Normal Form or BCNF: Let R be a relational schema and

• be any non-trivial functional dependency over the R is BCNF if X is a Candidate Key or a SuperKey.
or
• is a trivial functional dependency (i.e, Y subset of X),

Thus BCNF has no redundancy from any functional dependency and is a slightly stronger version of the 3NF. 7. Fourth normal form or 4NF: 4NF is nothing but the next level of BCNF. While the 2NF, 3NF, and BCNF are concerned with functional dependencies, 4NF is concerned with multivalued dependency. Let R be the relational schema F be the single and multivalued dependency is in 4NF if:

• X is a candidate key or a super key of the relation,
or
• X union Y = R

8. ETNF: ETNF is Essential tuple normal form which is stricter than Fourth Normal Form but less strict than Fifth Normal Form.ETNF is needed to eliminate the redundancy in tuples. a relation will be in ETNF the relation is in BCNF (specified only by Functional and Join Dependencies) and some of the keys have only one attribute. (If every key had only one attribute then the relation in 5NF.). This is the simple and sufficient conditions for a relation in ETNF. In ETNF a component of every explicit join dependency is a superkey. 9. Fifth normal form or 5NF: 5NF is also known as Project-Join Normal Form or PJ/NF. 5NF is designed to reduce the redundancy in relational databases. To avoid redundancy all the tables are broken into as many tables as possible in 5NF. A table is in 5NF when every non-trivial join dependency is implied by the candidate key of that relation. (should not contain any join dependency and join should be lossless.). 10. Domain-key normal form or DKNF: It is a normal form in which database contains only two constraints which are:

1. domain constraints,
2. key constraints.

The function of domain constraint is specifying the permissible values for a given attribute, while the main function of a key constraint is to specify the attributes which uniquely identify a row in a given table. Domain Key Normal Form avoids all non-temporal anomalies. Always Remember that relationships which are impossible to express in foreign keys are obviously violating the Domain Key Normal Form. 11. Sixth normal form or 6NF: A relation is in 6NF only if when it doesn’t support any nontrivial join dependencies. Any relation which is in 6NF should also be in 5NF. Though Some authors used the term sixth normal form as a synonym for DKNF, 6NF is stricter and less redundant that domain key normal form. 6NF decompose the relation variables into irreducible components. This is relatively unimportant for non-temporal relation variables but is important when we are dealing with the temporal variables or other interval data. The sixth normal form is using in many data warehouses where the benefits outweigh the drawbacks. Normalization necessarily involves in organizing the columns or attributes, and tables of a database to ensure that their dependencies are properly enforced by database integrity constraints