Modeling Database - II

Posted on 14th Jan 2017

<-Back to Blogs

Normalization of Database is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

Normalization

Here are the most commonly used normal forms:

• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)

Let's learn about each of them with an example table as shown below:

Table - Sales

1. First normal form(1NF) - The first form of normalization states that there may be no repeating groups of columns in an entity. 
   
   • Each cell to be Single valued.
   • Entries in a column are same type.
   • Row is uniquely identified - Add Unique ID or Add more columns to make it unique (Note: The order of the rows and the order of the columns are irrelevant.)
     
     Let's look at above table Sales record again -  Each cell to be Single valued, Entries in a colum are same type rules are violated here -
     

     Customer	Item	Shipping Address	Newsletter	Supplier	Supplier Phone	Price
     Evan Wilson	Xbox One, PS Vista	28 Rock Av, Denver	Xbox News, Play Station News	Wholesale	Toll Free	450

          
     Note that Item attribute should had one value, Supplier attribute should not have a Supplier name, Supplier Phone must have right phone number.
     • More ever looking at the table above we can't say whether Alan Smith is same person or not?

     • Cust_ID	Customer	Item	Shipping Address	Newsletter	Supplier	Supplier Phone	Price
       at_smith	Alan Smith	Xbox One	35 Palm St, Miami	Xbox News	Microsoft	(800)BUY-XBOX	250
       roger25	Roger Banks	PlayStation 4	47 Campus Rd, Boston	Play Station News	Sony	(800)BUY-SONY	300
       wilson	Evan Wilson	Xbox One	28 Rock Av, Denver	Xbox News	Microsoft	(800)BUY-XBOX	450
       wilson	Evan Wilson	PS Vista	28 Rock Av, Denver	Play Station News	Sony	(800)BUY-SONY	200
       am_smith	Alan Smith	Play Station 4	47 Campus Rd, Boston	Play Station News	Sony	(800)BUY-SONY	300

        
       Now looking at above table we find that the wilson row was seperated out in two different rows so that each attributes is single valued and it has same value type.
2. Second normal form(2NF)- The second form of normalization states that all attributes of an entity should be fully dependent on the whole primary key. This means that each attribute of an entity can only be identified through the whole primary key.
   
   • All attributes (Non-key Columns) depends on the key.Let's see after making the changes in First normal form, what are the changes required to make the table into second normal form.
   • We start by arguing which of the columns or attributes are not dependent on the the primary key. If they are not dependent we have to make a seperate table for them along with other column.
   • Notice that Price attribute is not dependent on the primary key Cust_ID. Rather Price is dependent on the Item attribute. So those have to seperated out as shown below:

   • Cust_ID	Cust Name	Shipping Address	Newsletter
     at_smith	Alan Smith	35 Palm St, Miami	Xbox News
     rogger25	Roger Banks	47 Campus Rd, Boston	Play Station News
     wilson	Evan Wilson	28 Rock Av, Denver	Xbox News
     wilson	Evan Wilson	28 Rock Av, Denver	Play Station News
     am_smith	Alan Smith	47 Campus Rd, Boston	Play Station News

      

     Item	Supplier	Supplier Phone	Price
     Xbox One	Microsoft	(800)BUY-XBOX	250
     Play Station 4	Sony	(800)BUY-SONY	300
     PS Vita	Sony	(800)BUY-SONY	200

      

     Now we would require a junction table which would map the two table together as show below:

     Cust_ID(Primary Key)	Item
     at_smith	Xbox One
     roger25	Play Station 4
     wilson	Xbox One
     wilson	PS Vita
     am_smith	Play Station 4

     In the above table Cust_ID and Item both are the primary keys of respective table. Thus, the above table glues the rows from one table to another.    
      

3. Third normal form(3NF)- All fields(columns) can be determined only by the key in the table and no other columns. The third form of normalization states that all attributes need to be directly dependent on the primary key, and not on other attributes. This seems to be what the second form of normalization states, but in the second form is actually stated the opposite. In the second form of normalization you point out attributes through the PK, in the third form of normalization every attribute needs to be dependent on the PK, and nothing else.
   

   • Cust_ID(Primary Key)	Cust Name	Shipping Address	Newsletter
     at_smith	Alan Smith	35 Palm St, Miami	Xbox News
     rogger25	Roger Banks	47 Campus Rd, Boston	Play Station News
     wilson	Evan Wilson	28 Rock Av, Denver	Xbox News
     wilson	Evan Wilson	28 Rock Av, Denver	Play Station News
     am_smith	Alan Smith	47 Campus Rd, Boston	Play Station News

      

     Supplier(Primary Key)	Supplier Phone
     Microsoft	(800)BUY-XBOX
     Sony	(800)BUY-SONY

                                                                             

     Item(Primary Key)	Supplier(Foreign Key)	Price
     Xbox One	Microsoft	250
     Play Station 4	Sony	300
     PS Vita	Sony	200

       
4. Boyce & Codd Normal Form - No multivalued dependencies.  Let's check how we can normal more the above table to have 4th Normal form of database table.
   • What multivalued dependencies means? Let's check it out with the below table:

   • Cust_ID(Primary Key )	Cust Name	Shipping Address	Newsletter
     at_smith	Alan Smith	35 Palm St, Miami	Xbox News
     rogger25	Roger Banks	47 Campus Rd, Boston	Play Station News
     wilson	Evan Wilson	28 Rock Av, Denver	Xbox News
     wilson	Evan Wilson	28 Rock Av, Denver	Play Station News
     am_smith	Alan Smith	47 Campus Rd, Boston	Play Station News

     
     In the above table, Cust Name, Shipping Address and Newsletter are dependent on the Primary Key Cust_ID. For wilson there are two unique values in Newsletter column which are dependent on the primary key. We need to have to seperate it out as shown below:
   •  

     Cust_ID(Primary Key )	Cust Name	Shipping Address
     at_smith	Alan Smith	35 Palm St, Miami
     rogger25	Roger Banks	47 Campus Rd, Boston
     wilson	Evan Wilson	28 Rock Av, Denver
     wilson	Evan Wilson	28 Rock Av, Denver
     am_smith	Alan Smith	47 Campus Rd, Boston

   • Cust_ID(Primary Key )	Newsletter
     at_smith	Xbox News
     rogger25	Play Station News
     wilson	Xbox News
     wilson	Play Station News
     am_smith	Play Station News

          

These were  the 4 forms of normalization on a given database table and with an example it was explained how it was done. Thanks!

Glossary

Attributes - detailed data about an entity, such as price, length, name

Cardinality - the relationship between two entities, in figures. For example, a person can place multiple orders.

Entities - abstract data that you save in a database. For example: customers, products.

Foreign key (FK) - a referral to the Primary Key of another table. Foreign Key-columns can only contain values that exist in the Primary Key column that they refer to.

Key - a key is used to point out records. The most well-known key is the Primary Key (see Primary Key).

Normalization - A flexible data model needs to follow certain rules. Applying these rules is called normalizing.

Primary key - one or more columns within a table that together form a unique combination of values by which each record can be pointed out separately. For example: customer numbers, or the serial number of a product.