Third Normal Form (3NF)

Third Normal Form (3NF)

For a relation to be in third normal form:

  • it should meet all the requirements of both 1NF and 2NF
  • If there is any columns which are not related to primary key, then remove them and put it in a separate table, relate both the table by means of foreign key i.e.; there should not be any transitive dependency.

Let’s add three more columns – STREET, CITY and ZIP to STUDENT table to explain 3NF. Below is the table satisfies conditions for 2NF – there is primary key, no repeating columns, no duplicate datas.

Here, STREET and CITY have no relation with Student. It’s not directly related to student. They fully depend upon zip code. Since Student stays in that area, through zip code, street and city are related to him. This kind of relationship is called transitive dependency. Since its second level of dependency, it is not necessary to store these details in STUDENT table.

Similarly, if there are multiple STUDENTs staying in same area, STUDENT table is having huge amount of records and there is a change requested for street or city name, then whole STUDENT table needs to be searched and updated. Imagine, we have to update ‘Fraser Village Drive’ to Fraser Village Dr’. The Update statement would be

UPDATE STUDENT std
SET std.STREET = 'Fraser Village Dr'
WHERE std .STREET =  'Fraser Village Drive';

Above query will search whole student table for ‘Fraser Village Drive’ and then update it to ‘Fraser Village Dr’. But searching a huge table and updating the single or multiple records will be a very time consuming, hence affecting the performance of the database.

Instead, if we have these details in a separate table ZIPCODE and is related to STUDENT table using zip? However ZIPCODE table will have comparatively less amount of records and we just have to update ZIPCODE table once. It will automatically reflect in the STUDENT table! Hence making the database and query simpler! And table is in 3NF.

UPDATE ZIPCODE z
SET z.STREET = 'Fraser Village Dr'
WHERE z .STREET =  'Fraser Village Drive';

Now if we have to select the whole address of a student, Chris, we join both STUDENT and ZIPCODE table using ZIP and get the whole address.

SELECT std.STUDENT_NAME, 
	z.STREET, 
	z.CITY,
	z.ZIP
FROM STUDENT std, ZIPCODE z
WHERE std.ZIP = z.ZIP
AND std.STUDENT_NAME = 'Chris';

 

Translate »