์Excercise:Normalize Table - TOP10

์Excercise:Normalize Table


Normalize Table Three Steps

Suppose that you have table Name Emoyee_Training that does not in normalization like bellow :


Problem :This table is mix all information of employees store in together some columns have repeating data in the long string follow by employee's record. In this problem we need to normalize in three steps, they are First Normalize Form (1NF), Second Normalize Form(2NF) and Third Normalize Form (3NF) .

Normalize Form (1NF)

Definition :In First Normal Form (1NF) says that all column values not contains multi value and not allow repeating group. 

-In this exercise we note all of data that have multi value in one columns (Example multi values:value1,value2,value3,... in one column) if the table have like this we need to suppurate that column to be another table with have relationship key to old table. we need only one information in a column.but for this problem it doesn't contain multi values in table but it have repeating group of value a few columns.
-Soving In 1NF For This Exercise:
We note all of columns which have repeating group data in this table and collect them to in a group and then we make up it to another table that have relationship to old table like picture bellow:


Normalize Form (2NF)

Definition :
- 2NF only applies to tables with composite primary keys. 
- A table that is in 1NF and in which the values of each non-primary-key column can be worked out from the values in all the columns that make up the primary key. 
- A record design is in second normal form (2NF) if it is in  
1NF and if all fields that are not part of the primary key  
are dependent on the entire primary key. 
All partial functional dependencies have been  
removed.

  • We get on this step we have more tables but don't worry we only note the table have composite primary keys(multi primary keys in a table) to normalize in this step. In this normalize we find the fields are partial functional dependencies  primary key in the table. We move these are partial functional fields name to create new table and create own primary key for them.Now this exercise we find two fields are partial functional, CuorseName and Price.
  • Look Bellow :

Normalize Form (3NF)

 Definition :When all tables are in 3NF, a database design avoids  
redundancy and data integrity problems. 
Every nonkey fields depends on the key. 
 ●  We design   in step  3NF on the tables that were in 1NF and 2NF already. 

-In This step we normalize more only from step 2nf we find all the fields name in the table that not have own primary to describe them and then we create new one table form them have relationship to old table . 

Look bellow:


Show relationship all tables 





Previous
Next Post »