I have come across two flavours of the normalization procedure while referring the internet and textbooks. viz.
Type 1. Normal forms based only on the primary key.
In this type,
-> 2NF disallows Partial dependencies on the primary key.
-> 3NF disallows transitive dependencies on the primary key.
Type 2. A much more general Normal form based on ALL of the candidate keys
In this type,
Partial dependencies and transitive dependencies on ALL candidate keys are taken into account.
In most of the sites I referred, I have found tutorials and notes based only on the 1st type.
But the textbook 'Fundamentals of Database Systems' by Navathe and Elmasri describes both the types. Even wikipedia's page on 2NF mentions seperately about '2NF and candidate keys' which is based on the 2nd type.
See 2NF and Candidate Keys – Wiki
But neither the textbook nor wiki mentions anything regarding which type is better or which one is practically preferred.
Which type should be followed when nothing is mentioned about the type?
Hope U have understood my question.
Please help me out on this guys.
Best Answer
Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key.
Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that is "preferred" or deemed to have some special significance for database users. Unfortunately many of the "definitions" you will see online or even in print are wrong as a result.