7

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.

Improve this question
1
  • 4
    Think like this: if it was just the primary key, we could add a new "row number" column to any table, use that one as primary key, and thereby turn the table into a higher normal form. But all the problems, with redundancy and so on, that normalization is designed to solve, would still be there! Therefore, it would be stupid if it was just the primary key.
    – Thomas Padron-McCarthy
    Commented Oct 19, 2011 at 10:58

1 Answer 1

Reset to default
15

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.

Improve this answer
5
  • So should we always follow normalization based on candidate keys?? Suppose that we are asked to check whether the following relation is in 2NF or not? R=(A,B,C,D,E,F) F={A->BCDEF, BC->ADEF, B->D, E->F} Candidate keys are A, BC. If 'A' is selected as primary key, and the 1st approach is followed, then there is no partial dependencies on A, implying that the relation is in 2NF. But if the 2nd approach is followed, although there are no partial dependencies on A, we have a partial dependency B->D on candidate key BC, implying that the relation is only in 1NF!!
    – Nabeel
    Commented Oct 19, 2011 at 7:24
  • This is where I got confused because our teacher at college had taught us this concept purely based on the Primary Key without taking into account the candidate keys.
    – Nabeel
    Commented Oct 19, 2011 at 7:26
  • 4
    @Nabeel, In your example, if BC is a candidate key then B->D is a partial key dependency and therefore R is not in 2NF. If a relation has more than one candidate key then it doesn't matter which you call "primary" - the choice is arbitrary and doesn't make any fundamental or practical difference. What matters is which keys and dependencies you want to be satisfied. Maybe the examples your teacher used only had one key anyway? You will find plenty of examples around that only identify one candidate key per relation.
    – nvogel
    Commented Oct 19, 2011 at 9:20
  • Thanks a ton for your insight!! Hmmm. Yes, most of the questions we had come across in our problem solving sessions were one candidate key type of qns. When you refer the Elmasri-Navathe textbook on Fundamentals of DB Systems, normalization is given as two seperate sections first which takes into account only the Primary key and then it introduces the more general normalization based on all the candidate keys. I wonder what is the need in giving it like that. Making it all the more complicated and ambiguous for already confused students like me!! :D
    – Nabeel
    Commented Oct 19, 2011 at 11:52
  • 1
    I agree the Elmasri and Navathe explanation is over-complicated - perhaps because their book also covers more than just the relational model. In other contexts (e.g. ER modelling) the idea of designating a "primary" key is given more significance than it is in relational database terms. My own view is that the "primary key" is essentially an outdated concept that should not figure prominently when teaching relational database design. The concept is really worth not much more than a paragraph a footnote.
    – nvogel
    Commented Oct 19, 2011 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.