Prerequisite – Keys in Relational Model
1. Super Key:
Super Key is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. All super keys can’t be candidate keys but its reverse is true. In a relation, number of super keys are more than number of candidate keys.
Example:
We have a given relation R(A, B, C, D, E, F) and we shall check for being super keys by following given dependencies:
Functional dependencies Super key AB->CDEF YES CD->ABEF YES CB->DF NO D->BC NO
By Using key AB we can identify rest of the attributes (CDEF) of the table. Similarly Key CD. But, by using key CB we can only identifies D and F not A and E. Similarly key D.
2. Primary Key:
Candidate key is a set of attributes (or attribute) which uniquely identify the tuples in a relation or table. There can be more than one candidate key in relation out of which one can be chosen as the primary key.
Example:
Student{Stud_No, Stud_name, Stud_phone, Stud_state, Stud_country, Stud_age}
Here we can see the two candidate keys Stud_No and Stud_phone. STUD_No can be chosen as the primary key (only one out of many candidate keys).
Difference between Super Key and Primary Key:
S.NO | Super Key | Primary Key |
---|---|---|
1. | Super Key is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. | Primary Key is a minimal set of attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. |
2. | All super keys can’t be primary keys. | Primary key is a minimal super key. |
3. | Various super keys together makes the criteria to select the candidate keys. | We can choose any of the minimal candidate key to be a primary key. |
4. | In a relation, number of super keys are more than number of primary keys. | While in a relation, number of primary keys are less than number of super keys. |
5. | Super key’s attributes can contain NULL values. | Primary key’s attributes cannot contain NULL values. |