Question

What are the various forms of Normalization?

Answer 1

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), ***** 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	***** 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	***** 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)
      3	***** 99 (Greer Macallister)
      4	Dracula (Bram Stoker)

      Salutations Table (3rd Normal Form)

      Salutation_ID	Salutation
      1	Ms.
      2	Mr.
      3	Mrs.

    • Example 2 - Consider the following dependencies in relation R(P,Q,R,S,T)

        P -> QR     [P together determine C] 
        RS -> T     [B and C together determine D] 
        Q -> S 
        T -> P 

      For the above relation to exist in 3NF, all possible candidate keys in above relation should be {P, RS, QR, T}.

  • Boyce-Codd Normal Form

    A relation is in Boyce-Codd Normal Form if satisfies the conditions for third normal form and for every functional dependency, Left-Hand-Side is super key. In other words, a relation in BCNF has non-trivial functional dependencies in the form X –> Y, such that X is always a super key. For example - In the above example, Student_ID serves as the sole unique identifier for the Students Table and Salutation_ID for the Salutations Table, thus these tables exist in BCNF. Same cannot be said for the Books Table and there can be several books with common Book Names and same Student_ID.