Hey ! As said previously, now, we will discuss types of database normalization. And we're gonna begin with First Normal form.
[Note: I suggest you to visit my earlier post discussing the concept of normalization in general, if haven't done already.]
The two criteria to be satisfied for a relational table to be having First Normal form (without going to exact academic definition) are:
As you can see an employee can have more than one phone number,so there are three columns in the table storing phone numbers.
Let's see whether this table qualifies for first normal form or not.
It satisfies the first criterion, it has a column "ID" congaing unique and non-null values. So, does this table have first normal form? NO, the second criterion isn't satisfied here, as the table has three columns "Phone1", "Phone2", and "Phone3" which contain same type of values phone numbers.
Now, if we want to normalize this table to first normal form, we have to make sure it fulfills the second criteion as well. To do so, we can separate columns containing
same types of values (i.e. phone number columns in this example) and create a separate table for them. After making this structural change, we'll be having two tables as shown here.
Observe both the tables, Here the new table contains two columns, "ID" and "Phone" . And ID is foreign key in new table to make a primary - foreign key relation with main table. Now, we have the two tables with fiest normal form. In main table, the primary key is ID, while in new second table, the primary key is composite i.e. both "ID" and "Phone" together make primary key in second table.
Now, here is a important tip I use to keep remembering the first normal form: To make first normal form from a denormalized table, we create one-to-many relationship between main table and new second table. Look the example again, we have done same thing.
Hope, this post was of some benefit to you. We will discuss the second normal form in the next post.
Bye !!!
[Note: I suggest you to visit my earlier post discussing the concept of normalization in general, if haven't done already.]
The two criteria to be satisfied for a relational table to be having First Normal form (without going to exact academic definition) are:
- The table should have a primary key (A column with unique and non-null values. Since it contains unique values, primary key values can identify each records of table.)
- There should not be two or more columns containing same type of values.
As you can see an employee can have more than one phone number,so there are three columns in the table storing phone numbers.
Let's see whether this table qualifies for first normal form or not.
It satisfies the first criterion, it has a column "ID" congaing unique and non-null values. So, does this table have first normal form? NO, the second criterion isn't satisfied here, as the table has three columns "Phone1", "Phone2", and "Phone3" which contain same type of values phone numbers.
Now, if we want to normalize this table to first normal form, we have to make sure it fulfills the second criteion as well. To do so, we can separate columns containing
same types of values (i.e. phone number columns in this example) and create a separate table for them. After making this structural change, we'll be having two tables as shown here.
Observe both the tables, Here the new table contains two columns, "ID" and "Phone" . And ID is foreign key in new table to make a primary - foreign key relation with main table. Now, we have the two tables with fiest normal form. In main table, the primary key is ID, while in new second table, the primary key is composite i.e. both "ID" and "Phone" together make primary key in second table.
Now, here is a important tip I use to keep remembering the first normal form: To make first normal form from a denormalized table, we create one-to-many relationship between main table and new second table. Look the example again, we have done same thing.
Hope, this post was of some benefit to you. We will discuss the second normal form in the next post.
Bye !!!
No comments:
Post a Comment