Search code examples
database-normalization3nf

Third Normal Form uniqeness constraint

I am trying to make my database in 3NF i am confused about one thing. In the explanation below i do not understand how Zip can be the primary key of the address table if the zip can occur more than once. In the Student_Detail table a reoccuring zip is fine but as a primary key wont it lose its uniqeness?

Third Normal Form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table :

Student_id - Student_name - DOB - Street - city - State - Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table :

Student_id - Student_name - DOB - Zip

Address Table :

Zip - Street - city - state

The advantage of removing transtive dependency is,

Amount of data duplication is reduced. Data integrity achieved.

Example: http://www.studytonight.com/dbms/database-normalization.php

Solution

  • I'm assuming this is your question

    i do not understand how Zip can be the primary key of the address table if the zip can occur more than once.

    and the reason why you don't understand is just because Zip is a bad example.

    All the explanation is correct. If you can infer any "non-prime" attribute base upon another "non-prime" attribute you have what is called "transitive dependency". You pull those to a different table and in its place you insert a FK reference.

    Zip will not be able to appear more than once for that attribute is a PK. I believe it is just a bad example although the explanation is correct. Try to analyse it with different subjects.

    Check if this example helps you in any way.