-1

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

philipxy
  • 14,867
  • 6
  • 39
  • 83
Christopher
  • 53
  • 8
  • They are just assuming that a given ZIP code has a single city, street and state. Which isn't actually true about ZIP codes. But even under that assumption "The dependency between zip and other fields is called transitive dependency" is poorly phrased, and so is their characterization of 3NF on it. This is a poor resource. Find a college/university textbook/presentation/course. Many are online. – philipxy May 09 '17 at 02:11

1 Answers1

1

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.

Pete Gee
  • 26
  • 2
  • Oh okay. So the proper way to do it in the above example would be to have created an Address_ID foreign key in the Student_Detail table and then and address_id primary key in the zip table and carry on from there. Thanks. The example you sent me help ed a lot as well. – Christopher May 01 '16 at 18:03
  • That's the idea exactly ! Glad I could help. – Pete Gee May 01 '16 at 18:08
  • @Christopher & PedroGabriel Normalization does not involve introducing new column names, just reusing the same column names in different tables. – philipxy May 03 '16 at 01:31