Normal Forms are used to eliminate or reduce redundancy in database tables. The different forms are as follows:
First Normal Form
A relation is in first normal form if every attribute in that relation is a single-valued attribute. If a relation contains composite or multi-valued attribute, it violates the first normal form. Let's consider the following students table. Each student in the table, has a name, his/her address and the books they issued from the public library -
Students Table
Student Address Books Issued Salutation
Sara Amanora Park Town 94 Until the Day I Die (Emily Carpenter),
Inception (Christopher Nolan) Ms.
Ansh 62nd Sector A-10 The Alchemist (Paulo Coelho),
Inferno (Dan Brown) Mr.
Sara 24th Street Park Avenue Beautiful Bad (Annie Ward),
Woman 99 (Greer Macallister) Mrs.
Ansh Windsor Street 777 Dracula (Bram Stoker) Mr.
As we can observe, the Books Issued field has more than one values per record and to convert it into 1NF, this has to be resolved into separate individual records for each book issued. Check the following table in 1NF form -
Students Table (1st Normal Form)
Student Address Books Issued Salutation
Sara Amanora Park Town 94 Until the Day I Die (Emily Carpenter) Ms.
Sara Amanora Park Town 94 Inception (Christopher Nolan) Ms.
Ansh 62nd Sector A-10 The Alchemist (Paulo Coelho) Mr.
Ansh 62nd Sector A-10 Inferno (Dan Brown) Mr.
Sara 24th Street Park Avenue Beautiful Bad (Annie Ward) Mrs.
Sara 24th Street Park Avenue Woman 99 (Greer Macallister) Mrs.
Ansh Windsor Street 777 Dracula (Bram Stoker) Mr.
Second Normal Form
A relation is in second normal form if it satisfies the conditions for first normal form and does not contain any partial dependency. A relation in 2NF has no partial dependency, i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table. Often, specifying a single column Primary Key is the solution to the problem. Examples -
Example 1 - Consider the above example. As we can observe, Students Table in 1NF form has a candidate key in the form of [Student, Address] that can uniquely identify all records in the table. The field Books Issued (non-prime attribute) depends partially on the Student field. Hence, the table is not in 2NF. To convert it into 2nd Normal Form, we will partition the tables into two while specifying a new Primary Key attribute to identify the individual records in the Students table. The Foreign Key constraint will be set on the other table to ensure referential integrity.
Students Table (2nd Normal Form)
Student_ID Student Address Salutation
1 Sara Amanora Park Town 94 Ms.
2 Ansh 62nd Sector A-10 Mr.
3 Sara 24th Street Park Avenue Mrs.
4 Ansh Windsor Street 777 Mr.
Books Table (2nd Normal Form)
Student_ID Book Issued
1 Until the Day I Die (Emily Carpenter)
1 Inception (Christopher Nolan)
2 The Alchemist (Paulo Coelho)
2 Inferno (Dan Brown)
3 Beautiful Bad (Annie Ward)
3 Woman 99 (Greer Macallister)
4 Dracula (Bram Stoker)
Example 2 - Consider the following dependencies in relation R(W,X,Y,Z)
WX -> Y [W and X together determine Y]
XY -> Z [X and Y together determine Z]
Here, WX is the only candidate key and there is no partial dependency, i.e., any proper subset of WX doesn’t determine any non-prime attribute in the relation.
Third Normal Form
A relation is said to be in the third normal form, if it satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes, i.e.,all non-prime attributes are determined only by the candidate keys of the relation and not by any other non-prime attribute.
Example 1 - Consider the Students Table in the above example. As we can observe, Students Table in 2NF form has a single candidate key Student_ID (primary key) that can uniquely identify all records in the table. The field Salutation (non-prime attribute), however, depends on the Student Field rather than the candidate key. Hence, the table is not in 3NF. To convert it into 3rd Normal Form, we will once again partition the tables into two while specifying a new Foreign Key constraint to identify the salutations for individual records in the Students table. The Primary Key constraint for the same will be set on the Salutations table to identify each record uniquely.
Students Table (3rd Normal Form)
Student_ID Student Address Salutation_ID
1 Sara Amanora Park Town 94 1
2 Ansh 62nd Sector A-10 2
3 Sara 24th Street Park Avenue 3
4 Ansh Windsor Street 777 1
Books Table (3rd Normal Form)
Student_ID Book Issued
1 Until the Day I Die (Emily Carpenter)
1 Inception (Christopher Nolan)
2 The Alchemist (Paulo Coelho)
2 Inferno (Dan Brown)
3 Beautiful Bad (Annie Ward)<
