After talkling about the first normal form earlier, its time for second normal form.
But, before we start, first go through this and this article serially if you haven't.
Now, to understand it, take a look on this statement
Second Normal Form = First Normal Form + [ ADDITIONAL PROPERTY ]
From this, we can conclude that for a table to have second normal form, it must have first normal form plus some additional property.
We already have discussed first normal form. So, In this post, I will discuss this additional property for a table (already having first normal form) to qualify for second normal form.
And this addtional characteristic is that every column in the table depends on the whole candidate key (column suitable to be primary key), NOT on just a subset column of candidate key in case of composite candidate key.
Let's continue the example of table EMP containg employee recordswe took to understand first normal form. Now we have the table having first normal form as shown. Here the primary key is "ID".
Does this have second normal form? YES. Why? because, here every column other than primary key column (ID) is funtionally dependent on the primary key. So, we can say a first normal table with non-composite candiidate key have second normal form already.
Now, we see the case of a composite primary key in a first normal table. We take other example here as shown.
This table have first normal form and it has composite primary key. i.e. "Bike_brand - colors".
But it doesn't have second normal form. because the values of column "Price" are dependent only on "Bike_brand" column, not on whole primary key.
So, if we want to make it into second normal form we will separate this column to a new table (with the same column as primary key on which these value s are dependent) as shown below.
Look at the structure of these new tables.
The main table has second normal form now as all non-candiadate columns (here "Discount_percentage") are dependent on whole primary key ( "Bike_brand - colors"). While the new table contains the columns "Bike_brand" (primary key in new table) and "Price" wih primary-foreign key relationship with main table.
Now, The way I remember it is that to make a first normal form (1-N) table to second normal form (2-N), the column with redundant values are taken in new table. And there is many-to-one relationship between main table and new table. We have done same in second example.
I hope this article was some help on the topic. The next post will be discussing the third normal form. bye !
But, before we start, first go through this and this article serially if you haven't.
Now, to understand it, take a look on this statement
Second Normal Form = First Normal Form + [ ADDITIONAL PROPERTY ]
From this, we can conclude that for a table to have second normal form, it must have first normal form plus some additional property.
We already have discussed first normal form. So, In this post, I will discuss this additional property for a table (already having first normal form) to qualify for second normal form.
And this addtional characteristic is that every column in the table depends on the whole candidate key (column suitable to be primary key), NOT on just a subset column of candidate key in case of composite candidate key.
Let's continue the example of table EMP containg employee recordswe took to understand first normal form. Now we have the table having first normal form as shown. Here the primary key is "ID".
Does this have second normal form? YES. Why? because, here every column other than primary key column (ID) is funtionally dependent on the primary key. So, we can say a first normal table with non-composite candiidate key have second normal form already.
Now, we see the case of a composite primary key in a first normal table. We take other example here as shown.
This table have first normal form and it has composite primary key. i.e. "Bike_brand - colors".
But it doesn't have second normal form. because the values of column "Price" are dependent only on "Bike_brand" column, not on whole primary key.
So, if we want to make it into second normal form we will separate this column to a new table (with the same column as primary key on which these value s are dependent) as shown below.
Look at the structure of these new tables.
Now, The way I remember it is that to make a first normal form (1-N) table to second normal form (2-N), the column with redundant values are taken in new table. And there is many-to-one relationship between main table and new table. We have done same in second example.
I hope this article was some help on the topic. The next post will be discussing the third normal form. bye !
No comments:
Post a Comment